Question

Consider a project to supply Detroit with 25,000 tons of machine screws annually for automobile production. You will need an initial $4,500,000 investment in threading equipment to get the project started: the project will last for 5 vears. The accounting department estimates that annual fixed costs will be $1,075,000 and that variable costs should be $200 per ton; accounting will depreciate the initial fixed asset investment straight-line to zero over the 5-year project life. It also estimates a salvage value of $450,000 after dismantling costs. The marketing department estimates that the automakers will let the contract at a selling price of $302 per ton. The engineering department estimates you will need an initial net working capital investment of $430,000. You require a return of 11 percent and face a tax rate of 22 percent on this project. a. Suppose you're confident about your own projections, but you're a little unsure about Detroit's actual machine screw requirement. What is the sensitivity of the project OCF to changes in the quantity supplied? (Do not round intermediate calculations and round your answer to 2 decimal places, e.g., 32.16.) b. What is the sensitivity of NPV to changes in quantity supplied? (Do not round intermediate calculations and round your answer to 2 decimal places, e.g., 32.16.) c. Given the sensitivity number you calculated, what is the minimum level of output below which you wouldn't want to operate? (Do not round intermediate calculations and round your answer to the nearest whole number, e.g., 32) hoc: This is a numeric cell, so please enter numbers only. b. ANPU c. Minimum level of output

Answer 1

a]

Operating cash flow (OCF) each year = earnings after tax + depreciation

OCF at 25,000 tons of output is $1,348,500.00.

А с гр — 2 Initial Investment 3 Cost of equipment 4 Investment in net working capital $4,500,000 $430,000 5 6 OCF 7 Number of tons supplied 8 Selling price per ton 9 Revenues 10 - Fixed Costs 11 - Variable Costs 12 - Depreciation 13 Earnings before tax 14 - Taxes 15 Earnings after tax 16 + Depreciation 17 OCF 25,000 $ 302.00 $7,550,000 $1,075,000 $5,000,000 $900,000 $575,000 $126,500 $448,500 $900,000 $1,348,500.00 25,000 $ 302.00 $7,550,000 $1,075,000 $5,000,000 $900,000 $575,000 $126,500 $448,500 $900,000 $1,348,500.00 25,000 $ 302.00 $7,550,000 $1,075,000 $5,000,000 $900,000 $575,000 $126,500 $448,500 $900,000 $1,348,500.00 25,000 $ 302.00 $7,550,000 $1,075,000 $5,000,000 $900,000 $575,000 $126,500 $448,500 $900,000 $1,348,500.00 25,000 $ 302.00 $7,550,000 $1,075,000 $5,000,000 $900,000 $575,000 $126,500 $448,500 $900,000 $1,348,500.00

А 3 Cost 2 Initial Investment Cost of equipment 4500000 4 Investment in net working capital 430000 5 6 OCE 7 Number of tons supplied 8 Selling price per ton 9 Revenues 10 - Fixed Costs 11 - Variable Costs 12 - Depreciation 13 Earnings before tax 14 - Taxes 15 Earnings after tax 16 + Depreciation 17 OCF 25000 25000 25000 25000 25000 302 302 302 302 302 =C7*08 =D7*D8 =E7 E8 =F7*F8 =G7*G8 1075000 1075000 1075000 1075000 1075000 =C7*200 =D7*200 =E7*200 =F7*200 =G7*200 =$B$3/5 =$B$3/5 =$B$3/5 =$B$3/5 =$B$3/5 =C9-C10-C11-C12 =D9-D10-D11-D12 =E9-E10-E11-E12 =F9-F10-F11-F12 =G9-G10-G11-G12 =C13*22% =D13*22% =E13*22% =F13*22% =G13*22% =C13-C14 =D13-D14 =E13-E14 =F13-F14 =G13-G14 =C12 =D12 =E12 =F12 =G12 =C15+C16 =D15+D16 =E15+E16 =F15+F16 =G15+G16

If the number of tons is decreased by 1% , the number of tons supplied is 25,000 * (1 - 1%) = 24,750.

At this level of output, the OCF is $1,328,610.00

F $4,500,000 $430,000 A 1 2 Initial Investment 3 Cost of equipment 4 Investment in net working capital 5 6 OCF 7 Number of tons supplied 8 Selling price per ton 9 Revenues 10 - Fixed Costs 11 - Variable Costs 12 - Depreciation 13 Earnings before tax 14 - Taxes 15 Earnings after tax 16 + Depreciation 17 OCF 24,750 $ 302.00 $7,474,500 $1,075,000 $4,950,000 $900,000 $549,500 $120,890 $428,610 $900,000 $1,328,610.00 24,750 24,750 24,750 $ 302.00 $ 302.00 $ 302.00 $7,474,500 $7,474,500 $7,474,500 $1,075,000 $1,075,000 $1,075,000 $4,950,000 $4,950,000 $4,950,000 $900,000 $900,000 $900,000 $549,500 $549,500 $549,500 $120,890 $120,890 $120,890 $428,610 $428,610 $428,610 $900,000 $900,000 $900,000 $1,328,610.00 $1,328,610.00 $1,328,610.00 24,750 $ 302.00 $7,474,500 $1,075,000 $4,950,000 $900,000 $549,500 $120,890 $428,610 $900,000 $1,328,610.00

Change in OCF = ($1,328,610.00 - $1,348,500.00) / $1,348,500.00

Change in OCF = -1.47%.

For a 1% decrease in output, the OCF has decreased by 1.47%.

Sensitivity = 1.47% / 1% = 1.47.

b]

In year 5, the entire working capital investment is recovered.

after-tax salvage value = salvage value - tax on sale of equipment (the equipment is depreciated to zero, and hence its book value is zero. The entire salvage value is subject to tax).

Total cash outflow in year 0 = cost of equipment + investment in net working capital.

Total cash inflow in years 1 to 4 = OCF.

Total cash inflow in year 5 = OCF + after-tax salvage value + recovery of net working capital.

NPV is calculated using NPV function in Excel.

NPV is $517,402.62.

B29 fx =NPV(11%,C27:627)+B27 А 0 1 2 3 2 Initial Investment Cost of equipment 4 Investment in net working capital $4,500,000 $430,000 6 OCF 7 Number of tons supplied 8 Selling price per ton 9 Revenues 10 - Fixed Costs 11 - Variable Costs 12 - Depreciation 13 Earnings before tax 14 - Taxes 15 Earnings after tax 16 + Depreciation 17 OCF 18 19 Terminal Cash Flow 20 Salvage Value 21 Tax 22 Salvage Value after tax 23 Recovery of net working capital 24 Terminal Cash Flow 25,000 $ 302.00 $7,550,000 $1,075,000 $5,000,000 $900,000 $575,000 $126,500 $448,500 $900,000 $1,348,500.00 25,000 $ 302.00 $7,550,000 $1,075,000 $5,000,000 $900,000 $575,000 $126,500 $448,500 $900,000 $1,348,500.00 25,000 $ 302.00 $7,550,000 $1,075,000 $5,000,000 $900,000 $575,000 $126,500 $448,500 $900,000 $1,348,500.00 25,000 25,000 $ 302.00 $ 302.00 $7,550,000 $7,550,000 $1,075,000 $1,075,000 $5,000,000 $5,000,000 $900,000 $900,000 $575,000 $575,000 $126,500 $126,500 $448,500 $448,500 $900,000 $900,000 $1,348,500.00 $1,348,500.00 $450,000 $99,000 $351,000 $430,000 $781,000 25 26 NPV Analysis 27 Project Cash Flows ($4,930,000) $1,348,500 $1,348,500 $1,348,500 $1,348,500 $2,129,500 28 29 NPV $517,402.62

А 12 13 2 Initial Investment 3 Cost of equipment 4 Investment in net working capital 4500000 430000 302 6 OCE 7 Number of tons supplied 8 Selling price per ton 9 Revenues 10 - Fixed Costs 11 - Variable Costs 12 - Depreciation 13 Earnings before tax 14 - Taxes 15 Earnings after tax 16 + Depreciation 17 OCF 25000 25000 25000 25000 25000 302 302 302 302 =C7*C8 =D7*D8 =E7 E8 =F7*F8 =G7*G8 1075000 1075000 1075000 1075000 1075000 =C7.200 =D7*200 =E7*200 =F7.200 =G7.200 $B$3/5 =$B$3/5 =$B$3/5 =$B$3/5 =$B$3/5 =C9-C10-C11-C12 =D9-D10-D11-D12 =E9-E10-E11-E12 =F9-F10-F11-F12 =G9-G10-G11-G12 =C13.22% =D13*22% =E13*22% =F1322% =G13*22% =C13-C14 =D13-D14 =E13-E14 =F13-F14 =G13-G14 =C12 =D12 =E12 =F12 =G12 =C15+C16 =D15+D16 =E15+E16 =F15+F16 =G15+G16 18 19 Terminal Cash Flow 20 Salvage Value 21 Tax 22 Salvage Value after tax 23 Recovery of net working capital 24 Terminal Cash Flow 450000 =G20*22% =G20-G21 =B4 =G22+G23 25 26 NPV Analysis 27 Project Cash Flows --B3-B4 =C17 =D17 =E17 =F17 =G17+G24 28 29 NPV =NPV(11%,C27:627)+B27

If the output is decreased by 1% to 24,750 tons, the NPV is decreased to $443,891.22.

Change in NPV = ($443,891.22 - $517,402.62) / 517,402.62 = -14.21%.

For a 1% decrease in output, NPV has decreased by 14.21%.

Sensitivity = 14.21.

c]

You would not want to operate if the NPV becomes zero.

Maximum decrease in NPV = 100%.

Sensitivity of NPV to output = 14.21.

Maximum decrease in output = 100% / 14.21 = 7.04%.

Minimum level of output = 25,000 - (25,000 * 7.04%)

Minimum level of output = 23,240.