Use the Majestic Mulch Company spreadsheet in the module for Chapter 9 to answer the following questions
Make sure the following blue colored cells are set to the following values:
Variable costs = $60.00 (CELL B6)
Fixed costs = $25,000 C=(CELL B8)
Taxes = 34.00% (CELL B12)
NWC = 15.00% (CELL B21)
What impact would a 10% increase SEPARATELY on each input have on the project's NPV? (Be sure to reset the previous input cell back to it's original value before calculating the next input change.)
For example, a 10% increase in the rate of taxes on cell B12 (+34% x 1.1) to 37.4% will cause the NPV to drop to $44,217, a decrease in NPV of -$21,279.
Which separate 10% input increase has the most significant decrease on NPV?
D E F G H | J K 1 120 $ 3,000 360,000 $ 180,000 $ 2 120 $ 5,000 600,000 $ 300,000 $ 3 120 $ 6,000 720,000 $ 360,000 $ 4 110 $ 6,500 715,000 $ 390,000 $ 5 110 $ 6,000 660,000 $ 360,000 $ 6 110 $ 5,000 550,000 $ 300,000 $ 7 110 $ 4,000 440,000 $ 240,000 $ 8 110 3,000 330,000 180,000 A B 1 Information 2 100.00% 0 3 $ 4 Unit sales 5 Revenues $ 6 Variable costs $ 60.00 $ 7 A Variable Costs 0.00% 8 Fixed costs $ 25,000 $ 9 MACRS rate 10 Depreciation Exp. $ 11 EBIT $ 12 Taxes 34.00% $ 13 Net income after I 14 15 EBIT $ 16 Depreciation Exp. $ 17 Taxes $ 18 OCF $ 19 20 Initial NWC $ 20,000 21 NWC 15.00% $ 22 Increase in NWC $ 23 +NWC recovery 24 Total NWC change $ 25 25,000 $ 14.29% 114,320 $ 40,680 $ 13,831 $ 26,849 $ 25,000 $ 24.49% 195,920 $ 79,080 $ 26,887 $ 52,193 $ 25,000 $ 17.49% 139,920 $ 195,080 $ 66,327 $ 128,753 $ 25,000 $ 12.49% 99,920 $ 200,080 $ 68,027 $ 132,053 $ 25,000 $ 8.93% 71,440 $ 203,560 $ 69,210 $ 134,350 $ 25,000 $ 8.93% 71,440 $ 153,560 $ 52,210 $ 101,350 $ 25,000 $ 8.93% 71,440 $ 103,560 $ 35,210 $ 68,350 $ 25,000 4.46% 100.01% 35,680 89,320 30,369 58,951 40,680 $ 114,320 $ 13,831 $ 141,169 $ 79,080 $ 195,920 $ 26,887 $ 248,113 $ 195,080 $ 139,920 $ 66,327 $ 268,673 $ 200,080 $ 99,920 $ 68,027 $ 231,973 $ 203,560 $ 71,440 $ 69,210 $ 205,790 $ 153,560 $ 71,440 $ 52,210 $ 172,790 $ 103,560 $ 71,440 $ 35,210 $ 139,790 $ 89,320 35,680 30,369 94,631 54,000 $ (34,000) $ 90,000 $ (36,000) $ 108,000 $ (18,000) $ 107,250 $ 750 $ 99,000 $ 8,250 $ 82,500 $ 16,500 $ 66,000 $ 16,500 $ $ 16,500 $ 49,500 16,500 49,500 66,000 (34,000) $ (36,000) $ (18,000) $ 750 $ 8,250 $ 16,500 $ B D E G H $ 800,000 $ $ $ 160,000 54,400 105,600 $ $ $ (820,000) $ 141,169 $ (34,000) $ $ 107,169 $ 248,113 $ (36,000) $ $ 212,113 $ 268,673 $ (18,000) $ $ 250,673 $ 231,973 $ 750 $ $ 232,723 $ 205,790 $ 8,250 $ $ 214,040 $ 172,790 $ 16,500 $ $ 189,290 $ 139,790 $ 16,500 $ $ 156,290 $ 94,631 66,000 105,600 266,231 $ A 25 26 Initial outlay 27 Sale price 28 -Taxes 29 After tax salvage 30 31 OCF 32 Change NWC 33 Capital spending 34 Total project CF 35 36 Discounted CF 37 38 Cumlative CF 39 40 Rate of discount 41 NPV 42 IRR 43 MIRR 44 45 46 47 $ 93,190 $ 160,388 $ 164,821 $ 133,060 $ 106,416 $ 81,835 $ 58,755 $ 87,031 $ (820,000) $ (712,831) $ (500,718) $ (250,046) $ (17,323) $ 196,717 $ 386,006 $ 542,296 $ 808,527 $ 65,497 15.00% 65,497 $ 17.24% 16.11% D E F G H | J K 1 120 $ 3,000 360,000 $ 180,000 $ 2 120 $ 5,000 600,000 $ 300,000 $ 3 120 $ 6,000 720,000 $ 360,000 $ 4 110 $ 6,500 715,000 $ 390,000 $ 5 110 $ 6,000 660,000 $ 360,000 $ 6 110 $ 5,000 550,000 $ 300,000 $ 7 110 $ 4,000 440,000 $ 240,000 $ 8 110 3,000 330,000 180,000 A B 1 Information 2 100.00% 0 3 $ 4 Unit sales 5 Revenues $ 6 Variable costs $ 60.00 $ 7 A Variable Costs 0.00% 8 Fixed costs $ 25,000 $ 9 MACRS rate 10 Depreciation Exp. $ 11 EBIT $ 12 Taxes 34.00% $ 13 Net income after I 14 15 EBIT $ 16 Depreciation Exp. $ 17 Taxes $ 18 OCF $ 19 20 Initial NWC $ 20,000 21 NWC 15.00% $ 22 Increase in NWC $ 23 +NWC recovery 24 Total NWC change $ 25 25,000 $ 14.29% 114,320 $ 40,680 $ 13,831 $ 26,849 $ 25,000 $ 24.49% 195,920 $ 79,080 $ 26,887 $ 52,193 $ 25,000 $ 17.49% 139,920 $ 195,080 $ 66,327 $ 128,753 $ 25,000 $ 12.49% 99,920 $ 200,080 $ 68,027 $ 132,053 $ 25,000 $ 8.93% 71,440 $ 203,560 $ 69,210 $ 134,350 $ 25,000 $ 8.93% 71,440 $ 153,560 $ 52,210 $ 101,350 $ 25,000 $ 8.93% 71,440 $ 103,560 $ 35,210 $ 68,350 $ 25,000 4.46% 100.01% 35,680 89,320 30,369 58,951 40,680 $ 114,320 $ 13,831 $ 141,169 $ 79,080 $ 195,920 $ 26,887 $ 248,113 $ 195,080 $ 139,920 $ 66,327 $ 268,673 $ 200,080 $ 99,920 $ 68,027 $ 231,973 $ 203,560 $ 71,440 $ 69,210 $ 205,790 $ 153,560 $ 71,440 $ 52,210 $ 172,790 $ 103,560 $ 71,440 $ 35,210 $ 139,790 $ 89,320 35,680 30,369 94,631 54,000 $ (34,000) $ 90,000 $ (36,000) $ 108,000 $ (18,000) $ 107,250 $ 750 $ 99,000 $ 8,250 $ 82,500 $ 16,500 $ 66,000 $ 16,500 $ $ 16,500 $ 49,500 16,500 49,500 66,000 (34,000) $ (36,000) $ (18,000) $ 750 $ 8,250 $ 16,500 $ B D E G H $ 800,000 $ $ $ 160,000 54,400 105,600 $ $ $ (820,000) $ 141,169 $ (34,000) $ $ 107,169 $ 248,113 $ (36,000) $ $ 212,113 $ 268,673 $ (18,000) $ $ 250,673 $ 231,973 $ 750 $ $ 232,723 $ 205,790 $ 8,250 $ $ 214,040 $ 172,790 $ 16,500 $ $ 189,290 $ 139,790 $ 16,500 $ $ 156,290 $ 94,631 66,000 105,600 266,231 $ A 25 26 Initial outlay 27 Sale price 28 -Taxes 29 After tax salvage 30 31 OCF 32 Change NWC 33 Capital spending 34 Total project CF 35 36 Discounted CF 37 38 Cumlative CF 39 40 Rate of discount 41 NPV 42 IRR 43 MIRR 44 45 46 47 $ 93,190 $ 160,388 $ 164,821 $ 133,060 $ 106,416 $ 81,835 $ 58,755 $ 87,031 $ (820,000) $ (712,831) $ (500,718) $ (250,046) $ (17,323) $ 196,717 $ 386,006 $ 542,296 $ 808,527 $ 65,497 15.00% 65,497 $ 17.24% 16.11%
