Question

Use an Excel spreadsheet to evaluate the ABCD Company proposal. 2) Conduct a sensitivity analysis that focuses on the cost of capital. For a best case scenario, decrease the cost of capital by three percentage points. For a worst case scenario, increase the cost of capital by three percentage points. 3) You must provide one spreadsheet for each of the three situations—the base case estimate, the best case, and the worst case. 4) What do you recommend? Explain. You may type your recommendation and explanation on the Excel sheet. MAXIMIZE THE USE OF FORMULAS! NOTE: THIS INCLUDES USING CELL REFERENCES RATHER THAN NUMBERS IN THE FORMULAS The ABCD Co. wants to add a production line. To do this, the company must spend $200,000 to expand its current building and purchase $1 million in new equipment. The new production line is expected to produce 100,000 units per year of a new product, which has a projected sales price of $7.75 per unit and a variable cost of $3.90 per unit. Introducing the new product is expected to cause sales of existing products to decrease by $89,000 per year and existing costs to decline by $49,000 per year. Fixed costs of the new line will be $142,000 annually. The company expects net working capital to increase by $1,800,000 when the new line is added, and then decrease by that amount when the project ends in five years. ABCD also expects to sell the equipment and building space at the end of the project in five years to net $320,990.36 after taxes. The company has a 34 percent marginal tax rate. ABCD’s cost of capital is 11%. Depreciation would be as follows, Year 1: $206,349.20 Year 2: $326,349.20 Year 3: $198,349.20 Year 4: $121,549.20 Year 5: $121,549.20

Answer 1

The excel representation is attached as below for your clear understanding.

Answer:

1.

Excel spreadsheet to evaluate the ABCD Company proposal:

S T U V W X R Linkage A = (7.75-3.90) x 100000 CE - (89,000 - 49,000) 0 (1,200,000.00) 1 Year 2 Initial Investment 3 Incremental contribution B 4 Reduction in margin 5 Fixed costs 6 Annual depreciation 7 EBIT 8 NOPAT 9 OCF 10 Investment in working capital 11 Post tax salvage value 12 Net Cash flows 13 14 Cost of capital 15 NPV E F = B+C+D+E G=Fx (1-34%) H=G-E 385,000.00 385,000.00 (40,000.00) (40,000.00) (142,000.00) (142,000.00) (206,349.20) (326,349.20) (3,349.20) (123,349.20) (2,210.47) (81,410.47) 204,138.73 244,938.73 385,000.00 (40,000.00) (142,000.00) (198,349.20) 4,650.80 3,069.53 201,418.73 385,000.00 (40,000.00) (142,000.00) (121,549.20) 81,450.80 53,757.53 175,306.73 385,000.00 (40,000.00) (142,000.00) (121,549.20) 81,450.80 53,757.53 175,306.73 1,800,000.00 320,990.36 2,296,297.09 (1,800,000.00) K = A+H+1+) (3,000,000.00) 204,138.73 244,938.73 2 01,418.73 175,306.73 Excel formula 808,202.70 =NPV(S14,T12:X12)+S2

The project has positive NPV in the base case.

2.

sensitivity analysis that focuses on the cost of capital is:

Base case NPV = 808,202.70

Best Case NPV =  599,913.77

Worst case NPV =  1,050,582.39

3.

spreadsheet for each of the three situations :

Best Case screenshot is :

S T U V W X R Linkage A = (7.75-3.90) x 100000 CE - (89,000 - 49,000) 0 (1,200,000.00) 1 Year 2 Initial Investment 3 Incremental contribution B 4 Reduction in margin 5 Fixed costs 6 Annual depreciation 7 EBIT 8 NOPAT 9 OCF 10 Investment in working capital 11 Post tax salvage value 12 Net Cash flows 13 14 Cost of capital 15 NPV E F = B+C+D+E G=Fx (1-34%) H=G-E 385,000.00 385,000.00 (40,000.00) (40,000.00) (142,000.00) (142,000.00) (206,349.20) (326,349.20) (3,349.20) (123,349.20) (2,210.47) (81,410.47) 204,138.73 244,938.73 385,000.00 (40,000.00) (142,000.00) (198,349.20) 4,650.80 3,069.53 201,418.73 385,000.00 (40,000.00) (142,000.00) (121,549.20) 81,450.80 53,757.53 175,306.73 385,000.00 (40,000.00) (142,000.00) (121,549.20) 81,450.80 53,757.53 175,306.73 1,800,000.00 320,990.36 2,296,297.09 (1,800,000.00) K = A+H+1+) (3,000,000.00) 204,138.73 244,938.73 2 01,418.73 175,306.73 Excel formula 808,202.70 =NPV(S14,T12:X12)+S2

Worst case screenshot is :

R T U V W X Linkage (1,200,000.00) = (7.75-3.90) x 100000 C = -(89,000 - 49,000) 1 Year 2 Initial Investment 3 Incremental contribution B 4 Reduction in margin 5 Fixed costs 6 Annual depreciation 7 EBIT 8 NOPAT 9 OCF 10 Investment in working capital 11 Post tax salvage value 12 Net Cash flows 385,000.00 385,000.00 (40,000.00) (40,000.00) (142,000.00) (142,000.00) (206,349.20) (326,349.20) (3,349.20) (123,349.20) (2.210.4711 (81.410.4211 204,138.73 244,938.73 385,000.00 (40,000.00) (142,000.00) (198,349.20) 4,650.80 3069.53 2 01,418.73 385,000.00 (40,000.00) (142,000.00)| (121,549.20) 81,450.80 53.757.53 175,306.73 F=B+C+D+E G=Fx (1-34%) HEGE 385,000.00 (40,000.00) (142,000.00) (121,549.20) 81,450.80 53.757.53 175,306.73 1,800,000.00 320.990.36 2,296, 297.09 (1,800,000.00) K =A+H+1+) (3,000,000.00) 204,138.73 244,938.73 201,418.73 175,306.73 14 Cost of capital 15 NPV Excel formula 1,050,582.39 ENPV/S14,T12:X12)+52

4.

recommended is:

Recommendation: NPV is positive in all the three cases.

Since NPV is positive in all the three cases, this project should be undertaken by the company.