Question

Scenario Analysis

Consider a 4-year project with the following information. Initial investment = $420,000; straight-line depreciation over the 4-year life No salvage value Quantity sold = 110,000 units Price per unit = $23 Tax rate = 34 percent Variable costs per unit = $19 Discount rate = 10 percent Fixed costs = $190,000 1. How sensitive is OCF to changes in quantity sold? ii. How sensitive is NPV to changes in quantity sold?

Answer 1

i]

operating cash flow (OCF) = earnings after tax + depreciation.

Base case OCF = $200,700.

A. C E F 0 1 2 2 Initial Investment $420,000 3 Initial Investment 4 5 OCF 6 Quantity sold 110,000 110,000 110,000 110,000 $2,530,000 $2,530,000 $2,530,000 $2,530,000 7 Revenues $2,090,000 $2,090,000 $2,090,000 $2,090,000 8 -Variable Costs $190,000 $190,000 $190,000 $190,000 - Fixed Costs 10- Depreciation $105,000 $105,000 $105,000 $105,000 $145,000 $49,300 $145,000 $49,300 $95,700 $105,000 $200,700 $145,000 $49,300 $145,000 $49,300 $95,700 $105,000 $200,700 11 Earnings before tax 12 - Taxes 13 Earnings after tax 14 +Depreciation $95,700 $95,700 $105,000 $105,000 $200,700 $200,700 15 OCF 16 17 NPV Analysis |($420,000) $200,700 $200,700 $200,700 $200,700 18 Project Cash Flows CO

A. C Е F 1 0 2 4 2 Initial Investment 3 Initial Investment 420000 4 5 ОСCF 6 Quantity sold 110000 110000 110000 110000 -Сб*23 -D6*23 =E6*23 =F6*23 7 Revenues - Variable Costs C6*19 D6*19 -E6*19 =F6*19 - Fixed Costs 190000 190000 9 190000 190000 10- Depreciation $B$3/4 $B$3/4 -$B$3/4 $B$3/4 11 Earnings before tax C7-C8-C9-C10D7-D8-D9-D10E7-E8-E9-E10 -F7-F8-F9-F10 C11*34% D11*34% E11*34% =F11*34% 12 -Taxes Earnings after tax 13 -C11-C12 D11-D12 -E11-E12 =F11-F12 14 +Depreciation -C10 D10 =E10 =F10 15 OCF -с13+C14 D13+D14 -E13+E14 =F13+F14 16 17 NPV Analysis 18 Project Cash Flows -B3 -С15 D15 -E15 F15 19 L

Now, we decrease quantity sold by 1%. The new OCF is $197,796. Change in OCF = ($197,796 - $200,700) / $200,700 = -1.45%. For a 1% decrease in quantity sold, OCF has decreased by 1.45%.

A. C E F 1 1 4 2 Initial Investment $420,000 3 Initial Investment 5 OCF 6 Quantity sold 7 Revenues 108,900 108,900 108,900 108,900 |$2,504,700 $2,504,700 $2,504,700 $2,504,700 $2,069,100 $2,069,100 $2,069,100 $2,069,100 8 -Variable Costs $190,000 $190,000 $190,000 $190,000 - Fixed Costs 9 10 Depreciation 11 Earnings before tax $105,000 $105,000 $105,000 $105,000 $140,600 $140,600 $140,600 $140,600 $47,804 $47,804 $47,804 $47,804 12 - Taxes 13 Earnings after tax 14 Depreciation $92,796 $92,796 $92,796 $92,796 $105,000 $105,000 $105,000 $105,000 $197,796 $197,796 $197,796 $197,796 15 OCF 16 17 NPV Analysis ($420,000) $197,796 $197,796 $197,796 $197,796 18 Project Cash Flows 19 L

A. C E 21 0 4 2 Initial Investment $420,000 3 Initial Investment 4 5 OCF 6 Quantity sold 108,900 $2,504,700 $2,504,700 $2,504,700 $2,504,700 108,900 108,900 108,900 7 Revenues $2,069,100 $2,069,100 $2,069,100 $2,069,100 8 -Variable Costs $190,000 $190,000 $190,000 $190,000 $105,000 9 Fixed Costs 10- Depreciation 11 Earnings before tax $105,000 $105,000 $105,000 $140,600 $140,600 $140,600 $140,600 $47,804 $47,804 $47,804 $47,804 12 - Taxes $92,796 $92,796 $92,796 $92,796 13 Earnings after tax $105,000 $105,000 $105,000 $105,000 14 +Depreciation 15 OCF $197,796 $197,796 $197,796 $197,796 16 17 NPV Analysis 18 Project Cash Flows ($420,000) $197,796 $197,796 $197,796 $197,796 L

ii]

NPV is calculated using NPV function in Excel.

Base case NPV = $216,192.

foNPV(10%,C18:F18) +B18 B20 А C E F 4 2 Initial Investment $420,000 3 Initial Investment 4 5 OCF 6 Quantity sold 110,000 110,000 110,000 110,000 $2,530,000 $2,530,000 $2,530,000 $2,530,000 7 Revenues $2,090,000 $2,090,000 $2,090,000 $2,090,000 - Variable Costs $190,000 $190,000 $190,000 $190,000 - Fixed Costs 10- Depreciation 11 Earnings before tax $105,000 $105,000 $105,000 $105,000 $145,000 $145,000 $145,000 $145,000 $49,300 $49,300 $49,300 $49,300 12 - Taxes $95,700 $95,700 $95,700 $95,700 13 Earnings after tax $105,000 $105,000 $105,000 $105,000 14 +Depreciation $200,700 $200,700 $200,700 $200,700 15 OCF 16 17 NPV Analysis 18 Project Cash Flows ($420,000) $200,700 $200,700 $200,700 $200,700 19 $216,192 20 NPV 31

Now, we decrease quantity sold by 1%. The new NPV is $206,987. Change in OCF = ($206,987 - $216,192) / $216,192 = -4.26%. For a 1% decrease in quantity sold, NPV has decreased by 4.26%

fiNPV(10%,C18: F18) +B 18 B20 A B C E F 1 2 3 4 2 Initial Investment $420,000 3 Initial Investment 4 5 OCF 6 Quantity sold 108,900 108,900 108,900 108,900 $2,504,700 $2,504,700 $2,504,700 $2,504,700 7 Revenues $2,069,100 $2,069,100 $2,069,100 $2,069,100 Variable Costs 8 $190,000 $190,000 $190,000 $190,000 Fixed Costs $105,000 $105,000 $105,000 $105,000 10 - Depreciation $140,600 $140,600 $140,600 $140,600 11 Earnings before tax $47,804 $47,804 $92,796 $47,804 $47,804 $92,796 12- Taxes $92,796 $92,796 Earnings after tax 13 14 Depreciation $105,000 $197,796 $105,000 $105,000 $105,000 $197,796 $197,796 $197,796 15 OCF 16 17 NPV Analysis ($420,000) $197,796 $197,796 $197,796 $197,796 18 Project Cash Flows 19 $206,987 20 NPV LU