Please help by:

a. checking numbers and formulas

b. correcting IRR error on worst case

c. please show how to calculate payback and discounted payback using a financial calculator for each case

Base Worst Best Unit Sales year 1= 110,000 100,000 120,000 Change in unit sales per year= -1,000 -2,000 0 Price/unit year 1= $220 $180 $260 Change in price per year= -$5 $10 $0 Variable Cost/unit year 1= $145 $155 $135 Change in variable cost/unit per year= +$1 +$2 $0 Fixed costs year 1= $30,000 $30,000 $30,000 Change in fixed costs per year= +$100 +$500 $300 Actual salvage value= $120,000 $100,000 $140,000 Name (insert in cell F3) Initial FA investment Net Working Cap Reqt.= Time frame in years= Marginal tax rate= Base actual salvage value= Worst salvage value= Best salvage value= Required rate of return= $10,000,000 $700,000 5 21% $120,000 $100,000 $140,000 0.14 BASE CASE Year 3 4 5 0 -10,000,000 -700,000 Initial FA Investment Net Working Capital Rat Unit Sales Price/unit VarCost/unit 110,000 $220 $145 109,000 $215 $146 108,000 $210 $147 107,000 $205 $148 106,000 $200 $149 Revenue Variable costs Fixed costs year Depreciation EBIT $ $ $ 24.200.000 $ 15,950,000 $ 30,000 $ 2,000,000 $ 6,220,000 $ 1,306,200 $ 4,913,800 $ 2,000,000 $ $ $ $ $ 23.435.000 $ 15,914,000 $ 30,100 $ 2,000,000 $ 5,490,900 $ 1,153,089 $ 4,337,811 $ 2,000,000 $ 22.680.000 $ 15,876,000 $ 30,200 $ 2,000,000 $ 4,773,800 $ 1,002,498 $ 3,771,302 $ 2,000,000 $ 21.935.000 $ 15,836,000 $ 30,300 $ 2,000,000 $ 4,068,700 $ 854,427 $ 3,214,273 $ 2,000,000 $ 21.200.000 15,794,000 30,400 2,000,000 3,375,600 708,876 Tax $ Net Income Depreciation After tax salvage Return of Working Capital Cash Flow 2,666,724 2,000,000 94.800 700.000 5,461,524 -10,700,000 6,913,800 6,337,811 5,771,302 5,214,273 Accumulated cash flow Discounted Cash flow Accumulated disc cash flow -10, 700,000 $ -10,700,000 -10,700,000 $ (3,786,200) $ 6,064,737 (4,635,263) $ 2,551,611 $ 4,876,740 241,477 $ 8,322,913 $ 3,895,464 4,136,941 $ 13,537,186 $ 3,087,268 7,224,209 $ 18,998,710 2,836,544 10,060,754 Net Present value 14% Internal Rate of Return Profitability Index Average Accounting Return Payback Discounted Payback $10,060,753.70 51.22% -1.940257355) -0.353344112) Use calculator Use calculator 0 1 2 3 4 5 WORST CASE Year Initial FA Investment Net Working Capital Rat Unit Sales Price/unit VarCost/unit -10,000,000 -700,000 100,000 $180 $155 98,000 $170 $157 96,000 $160 $159 94,000 $150 $161 92,000 $140 $163 Revenue Variable costs Fixed costs year Depreciation EBIT $ $ $ $ 18.000.000 15,500,000 $ 30,000 $ 2,000,000 $ 470,000 $ 98,700 $ 371,300 $ 2,000,000 $ 16.660.000 15,386,000 $ 30,500 $ 2,000,000 $ (756,500) $ (158,865) $ (597,635) $ 2,000,000 $ 15.360.000 $ 15,264,000 $ 31,000 $ 2,000,000 $ (1,935,000) $ (406,350) $ (1,528,650) $ 2,000,000 $ 14.100.000 $ 15,134,000 $ 31,500 $ 2,000,000 $ (3,065,500) $ (643,755) $ (2,421,745) $ 2,000,000 $ $ $ Tax 12.880.000 14,996,000 32,000 2,000,000 (4,148,000) (871,080) (3,276,920) 2,000,000 79.000 700.000 (497,920) $ $ Net Income Depreciation After tax salvage Return of Working Capital Cash Flow -10,700,000 $ 2,371,300 $ 1,402,365 $ 471,350 $ (421,745) $ Accumulated cash flow Discounted Cash flow Accumulated disc cash flow -10,700,000 $ -10,700,000 -10,700,000 $ (8,328,700) $ 2,080,088 (8,619,912) $ (6,926,335) $ 1,079,074 (7,540,838) $ (6,454,985) $ 318,148 (7,222,690) $ (6,876,730) $ -249,707 (7,472,397) $ (7,374,650) -258,604 (7,731,001) Net Present value 14% Internal Rate of Return Profitability Index Average Accounting Return Payback Discounted Payback ($7,731,001.07) #NUM! -0.277476536 0.14 Use calculator Use calculator $ 0 1 2 3 4 5 BEST CASE Year Initial FA Investment Net Working Capital Rat Unit Sales Price/unit VarCost/unit -10,000,000 -700,000 120,000 $260 $135 120,000 $260 $135 120,000 $260 $135 120,000 $260 $135 120,000 $260 $135 Revenue Variable costs Fixed costs year Depreciation EBIT $ $ $ $ 31.200.000 16,200,000 $ 30,000 $ 2,000,000 $ 12,970,000 $ 2,723,700 $ 10,246,300 $ 2,000,000 $ 31.200.000 16,200,000 $ 29,700 $ 2,000,000 $ 12,970,300 $ 2,723,763 $ 10,246,537 $ 2,000,000 $ 31.200.000 $ 16,200,000 $ 29,400 $ 2,000,000 $ 12,970,600 $ 2,723,826 $ 10,246,774 $ 2,000,000 $ 31.200.000 $ 16,200,000 $ 29,100 $ 2,000,000 $ 12,970,900 $ 2,723,889 $ 10,247,011 $ 2,000,000 $ $ Tax $ 31.200.000 16,200,000 28,800 2,000,000 12,971,200 2,723,952 10,247,248 2,000,000 110.600 700.000 13,057,848 $ $ Net Income Depreciation After tax salvage Return of Working Capital Cash Flow -10,700,000 12,246,300 12,246,537 12,246,774 12,247,011 Accumulated cash flow Discounted Cash flow Accumulated disc cash flow -10,700,000 $ -10,700,000 -10,700,000 $ 1,546,300 $ 10,742,368 42,368 $ 13,792,837 $ 9,423,313 9,465,681 $ 26,039,611 $ 8,266,224 17,731,905 $ 38,286,622 $ 7,251,214 24,983,118 $ 51,344,470 6,781,837 31,764,955 Net Present value 14% Internal Rate of Return Profitability Index Average Accounting Return Payback Discounted Payback $31,764,955.34 111.98% -3.968687415 (1) Use calculator Use calculator $ BASE CASE Year 0 2 3 4 5 5 --B5 --B6 Initial FA Investment Net Working Capital Rqt Unit Sales Price/unit VarCost/unit 110000 220 =145 =C18-1000 =C19-5 =C20+1 D18-1000 =D19-5 =D20+1 =E18-1000 =E19-5 =E20+1 =F18-1000 =F19-5 =F20+1 Revenue Variable costs Fixed costs year Depreciation EBIT Tax Net Income Depreciation After tax salvage Return of Working Capital Cash Flow =C18*019 =C20*C18 30000 -$B$ 16/5 =+C23-SUM(C24:C26) =C27*$B$8 =C27-C28 =$C$26 ED18*019 1 =D20*018 =C25+100 -$B$ 16/5 =+D23-SUM(D24:D26) =D27*$B$8 E18*519 =E20*E18 =D25+100 --$B$16/5 =+E2 3-SUM(E24:E26) =E27*$B$8 =E27-E28 $C$ 26 F18*519 =F20*F18 =E2 5+100 -$B$16/5 =+F23-SUM(F24:F26) =F27*$B$8 =F27-F28 =$C$26 EG18*619 EG20*G18 =F25+100 -$B$ 16/5 =+G23-SUM(G24:G26) =G27*$B$8 =G27-G28 =$C$26 =B9*$B$8) =B6 SUM(G29:G32)+G16+G17 =D27-D28 =$C$ 26 1 SUM(B29:B32)+B16+B17 ESUM(C29:C32)+C16+C17 SUM(D29:03 2)+D16+017 ESUM(E29:E32)+E16+E17 SUM(F29:F32)+F16+F17 Accumulated cash flow Discounted Cash flow Accumulated disc cash flow =B33 =B33/(1+$B$12)^B15 =B36 =C33+B35 =C33/(1+$B$12)^015 =C36 +B37 =D33+C35 =D33/(1+$B$12)^015 -=D36+C37 =E33+D35 =E33/(1+$B$12) "E15 =E36+D37 =F3 3+E35 =F33/(1+$B$12)^F15 =F36+E37 =G33+F35 =G33/(1+$B$12)^G15 =G36+F37 1 Net Present value 14% Internal Rate of Return Profitability Index Average Accounting Return Payback Discounted Payback -SUM(B36:G36) =IRR(B33:633) -SUMC36:G36/B36 EAVERAGE(C29:G29B33) Use calculator Use calculator WORST CASE Year 0 1 2 3 4 5 --B5 --B6 Initial FA Investment Net Working Capital Rot Unit Sales Price/unit VarCost/unit =100000 =180 =155 =C52-2000 !=C53-10 =C54+2 =D52-2000 =D53-10 =D54+2 =E52-2000 =E53-10 =E5 4+2 =F52-2000 =F53-10 =F54+2 Revenue Variable costs Fixed costs year Depreciation EBIT Tax Net Income Depreciation After tax salvage Return of Working Capital Cash Flow =C52 *C53 =C52 *C54 30000 -$B$50/5 =C57-SUM(C58:C60) =C61"$B$8 =D52 D53 =D52 *D54 =C59+500 -$B$ 50/5 =D57-SUM(D58:060) =D61*$B$8 =E52E53 =E52 E54 =D59+500 -$B$50/5 =E57-SUM(E58:E60) =E61 $B$ 8 =552553 =F52 *F54 =E59+500 -$B$50/5 =F57-SUM(F58:F60) =F61 $B$8 =F61-F62 =F60 EG52 G53 =G52 *G54 =F59+500 --$B$50/5 =G57-SUM(G58:660) =G61 $B$ 8 =G61-G62 =G60 =B10*1-$B$8) =B6 SUM(G63:666)+G50+G51 =C61-C62 =C60 =D61-D62 =D60 =E61-E62 =E60 =SUM(B50:351) SUM(C63:C66)+C50+C51 SUM(D63:D66)+D50+D51 SUM(E63:E66)+E50+E51 SUM(F63:F66)+F50+F51 Accumulated cash flow Discounted Cash flow Accumulated disc cash flow =B67 =B67/(1+$B$12)^B49 =B70 =C67+B69 =C67/(1+$B$12)^C49 =C70+B71 =D6 7+C69 =D67/(1+$B$12)^D49 =D70+C71 =E67 +D69 =E67/(1+$B$12) "E49 =E70+D71 =F6 7+E69 =F67/(1+$B$12)^F49 =F70+E71 =G6 7+F69 =G67/(1+$B$12)4649 =G70+F71 Net Present value 14% Internal Rate of Return Profitability Index Average Accounting Retur Payback Discounted Payback =SUM(B70:670) EIRR(B67:667) -SUMC70:670/370) EAVERAGE C63:663/867 Use calculator Use calculator BEST CASE Year 0 --B5 --B6 Initial FA Investment Net Working Capital Rat Unit Sales Price/unit VarCost/unit =120000 =260 =135 =$C$86 =$C$87 1 =$C$88 =$C$86 -$C$87 =$C$88 =$C$86 =$C$87 =$C$88 =$C$86 -$C$87 =$C$88 Revenue Variable costs Fixed costs year Depreciation EBIT Tax Net Income Depreciation After tax salvage Return of Working Capital Cash Flow =C86*C8Z =C88*C86 =30000 >B84/5 =C91-SUM(C92:094) =C95*$B$8 =C95-C96 =$C$94 =D86 *D87 1 =D88*D86 !=C93-300 =$C$94 =D91-SUM(D9 2:094) =D95 $B$8 =D95-D96 =$C$94 =E86E87 =E88 E86 =D93-300 =$C$94 =E91-SUM(E92:194) =E95 $B$8 =E95-E96 =$C$94 =F86*F87 =F88*F86 =E93-300 =$C$94 =F91-SUM(F92:F94) =F95*$B$8 =F95-F96 =$C$94 EG86*687 =G88*686 =F93-300 =$C$94 =G91-SUM(G92:694) =G95 $B$ 8 =G95-G96 =$C$94 =B11*(1-$B$8) =B6 SUM(G97:6100)+G84+G85 SUM(B97:B100)+B84+385 B84/5 =C91-SUM(C92:094) =C95*$B$8 =C95-C96 =$C$94 =D86 *D87 1 =D88*D86 !=C93-300 =$C$94 =D91-SUM(D9 2:094) =D95 $B$8 =D95-D96 =$C$94 =E86E87 =E88 E86 =D93-300 =$C$94 =E91-SUM(E92:194) =E95 $B$8 =E95-E96 =$C$94 =F86*F87 =F88*F86 =E93-300 =$C$94 =F91-SUM(F92:F94) =F95*$B$8 =F95-F96 =$C$94 EG86*687 =G88*686 =F93-300 =$C$94 =G91-SUM(G92:694) =G95 $B$ 8 =G95-G96 =$C$94 =B11*(1-$B$8) =B6 SUM(G97:6100)+G84+G85 SUM(B97:B100)+B84+385