Question

1 Approved Answer

Posted on Sep 09, 2024

please provide excel formulas to solve the bottom highlighted values and check against Financial Calculator. 0 1 2 3 4 5 BEST CASE Year Initial

image text in transcribed please provide excel formulas to solve the bottom highlighted values and check against Financial 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 120,000 120,000 120,000 $260 $135 $260 $260 $260 120,000 $260 $135 $135 $135 $135 Revenue Variable costs Fixed costs year Depreciation EBIT Tax $ $ $ $ 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 $ 31,200,000 $ 31,200,000 $ 31,200,000 16,200,000 $ 16,200,000 $ 16,200,000 $ 16,200,000 29,700 $ 29,400 $ 29,100 $ 28,800 2,000,000 $ 2,000,000 $ 2,000,000 $ 2,000,000 12,970,300 $ 12,970,600 $ 12,970,900 $ 12,971,200 2,723,763 $ 2,723,826 $ 2,723,889 $ 2,723,952 10,246,537 $ 10,246,774 $ 10,247,011 $ 10,247,248 2,000,000 $ 2,000,000 $ 2,000,000 $ 2,000,000 110,600 700,000 12,246,537 12,246,774 12,247,011 13,057,848 $ $ Net Income Depreciation After tax salvage Return of Working Capital Cash Flow -10,700,000 12,246,300 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 $ 26,039,611 $ 38,286,622 $ 51,344,470 9,423,313 8,266,224 7,251,214 6,781,837 9,465,681 $ 17,731,905 $ 24,983,118 $ 31,764,955 Net Present value 14% Internal Rate of Return Profitability Index Average Accounting Return Payback Discounted Payback Use calculator Use calculator BEST CASE Year 0 1 3 4 5 =B5 =B6 Initial FA Investment Net Working Capital Rqt Unit Sales Price/unit VarCost/unit = 120000 =260 =$C$86 =$C$87 =$C$88 =$C$86 =$C$87 =$C$88 =$C$86 =$C$87 =$C$88 =$C$86 =$C$87 =$C$88 = 135 Revenue Variable costs Fixed costs year Depreciation EBIT Tax =C86*C87 =C88* 086 =30000 B84/5 =C91-SUM(C92:094) =C95*$B$8 =D86*D87 =D88*D86 =C93-300 =$C$94 =E86*E87 =E88*E86 =D93-300 =$C$94 =F86*F87 =F88*F86 =E93-300 =$C$94 =D91-SUM(D92:D94) =D95*$B$8 =E91-SUM(E92:E94) =E95*$B$8 =F91-SUM(F92:F94) =F95*$B$8 =F95-F96 =$C$94 =G86*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 =C95-C96 =$C$94 =D95-D96 =$C$94 =E95-E96 =$C$94 Net Income Depreciation After tax salvage Return of Working Capital Cash Flow -B84+-B85 =SUM(C97:C100)+C84+C85 =SUM(D97:D100)+D84+D85 =SUM(E97:E100)+E84+E85 =SUM(F97:F100)+F84+F85 Accumulated cash flow Discounted Cash flow Accumulated disc cash flow =B101 =B101/(1+$B$12)^B83 =B104 =C101+B103 =C101/(1+$B$12)^c83 =C104+B105 =D101+C103 =D101/(1+$B$12)^D83 =D104+C105 =E101+D103 =E101/(1+$B$12)^E83 =E104+D105 =F101+E103 =F101/(1+$B$12)^F83 =F104+E105 =G101+F103 =G101/(1+$B$12)^683 =G104+F105 Net Present value 14% Internal Rate of Return Profitability Index Average Accounting Return Payback Discounted Payback 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 120,000 120,000 120,000 $260 $135 $260 $260 $260 120,000 $260 $135 $135 $135 $135 Revenue Variable costs Fixed costs year Depreciation EBIT Tax $ $ $ $ 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 $ 31,200,000 $ 31,200,000 $ 31,200,000 16,200,000 $ 16,200,000 $ 16,200,000 $ 16,200,000 29,700 $ 29,400 $ 29,100 $ 28,800 2,000,000 $ 2,000,000 $ 2,000,000 $ 2,000,000 12,970,300 $ 12,970,600 $ 12,970,900 $ 12,971,200 2,723,763 $ 2,723,826 $ 2,723,889 $ 2,723,952 10,246,537 $ 10,246,774 $ 10,247,011 $ 10,247,248 2,000,000 $ 2,000,000 $ 2,000,000 $ 2,000,000 110,600 700,000 12,246,537 12,246,774 12,247,011 13,057,848 $ $ Net Income Depreciation After tax salvage Return of Working Capital Cash Flow -10,700,000 12,246,300 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 $ 26,039,611 $ 38,286,622 $ 51,344,470 9,423,313 8,266,224 7,251,214 6,781,837 9,465,681 $ 17,731,905 $ 24,983,118 $ 31,764,955 Net Present value 14% Internal Rate of Return Profitability Index Average Accounting Return Payback Discounted Payback Use calculator Use calculator BEST CASE Year 0 1 3 4 5 =B5 =B6 Initial FA Investment Net Working Capital Rqt Unit Sales Price/unit VarCost/unit = 120000 =260 =$C$86 =$C$87 =$C$88 =$C$86 =$C$87 =$C$88 =$C$86 =$C$87 =$C$88 =$C$86 =$C$87 =$C$88 = 135 Revenue Variable costs Fixed costs year Depreciation EBIT Tax =C86*C87 =C88* 086 =30000 B84/5 =C91-SUM(C92:094) =C95*$B$8 =D86*D87 =D88*D86 =C93-300 =$C$94 =E86*E87 =E88*E86 =D93-300 =$C$94 =F86*F87 =F88*F86 =E93-300 =$C$94 =D91-SUM(D92:D94) =D95*$B$8 =E91-SUM(E92:E94) =E95*$B$8 =F91-SUM(F92:F94) =F95*$B$8 =F95-F96 =$C$94 =G86*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 =C95-C96 =$C$94 =D95-D96 =$C$94 =E95-E96 =$C$94 Net Income Depreciation After tax salvage Return of Working Capital Cash Flow -B84+-B85 =SUM(C97:C100)+C84+C85 =SUM(D97:D100)+D84+D85 =SUM(E97:E100)+E84+E85 =SUM(F97:F100)+F84+F85 Accumulated cash flow Discounted Cash flow Accumulated disc cash flow =B101 =B101/(1+$B$12)^B83 =B104 =C101+B103 =C101/(1+$B$12)^c83 =C104+B105 =D101+C103 =D101/(1+$B$12)^D83 =D104+C105 =E101+D103 =E101/(1+$B$12)^E83 =E104+D105 =F101+E103 =F101/(1+$B$12)^F83 =F104+E105 =G101+F103 =G101/(1+$B$12)^683 =G104+F105 Net Present value 14% Internal Rate of Return Profitability Index Average Accounting Return Payback Discounted Payback Use calculator Use calculator