PLEASE SHOW EXCEL FORMULAS

FIN 3320 Excel Project Use Excel to evaluate the following capital budgeting praject for the best, base, and worst cases. You will calculate the following values for each case: Net present value Internal rate of return Profitability Index Average Accounting Returni Payback Discounted payback The cash flows change every year because of the assumptions below. Note: You must do your calculations in Excel. Do not do them with a calculator and type them in, except for PAYBACK and DISCOUNTED PAYBACK. Example: For the base case, unit sales is 110,000 in year 1. The assumptions state that unit sales will decline in the second year by 1,000. Assuming that the year 1 unit sales is in cell M246 (note: it isn't really in cell M246), year 2 unit sales would be in N246 and the formula in that cell would be M246-1,000 Here is information about the project Initial Fixed Asset investment Net Working Cap Requirement Time frame 5 years $10,000,000 $800,000 Marginal tax rate 21 % Required rate of return 12 % 5-year straight line depreciation with assumption of zero salvage value For negative net pre-tax incomes, you must include negative taxes, i.., if the project loses money, it will reduce the company's taxable income, thus lowering its tax bill. PUT ALL THREE CASES ON ONE EXCEL SPREADSHEET; DO NOT USE TABS TO HAVE THREE SEPARATE SHEETS ALL COLUMNS SHOULD FIT WITHIN THE WIDTH OF ONE PRINTED PAGE. THEY SHOULD NOT BLEED ONTO A SECOND PAGE Base Worst Best Unit Sales year 1 110,000 98,000 122,000 Change in unit sales per year 1,000 -2,000 0 Price/unit year 1 $220 $200 $240 -$5 -$10 Change in price per year $0 Variable Cost/unit year 1 $145 $150 $140 Change in variable cost per year +$1 +$2 S0 Fixed costs year 1 $40,000 $40,000 $40,000 +S500 -$500 Change in fixed costs per year S0 Actual salvage value $120,000 $100,000 $140,000 FIN 3320 Excel Project Name (insert in cell F3) hia FA investment- $10,000,000 sa00,000 Net Warking Cap Reqt. Time frame in yoars Marginal tax rate Base actual salvage value Warst salvage valua- Best salvage value Requirad rate of roturn 21% $120,000 $100,000 $140,000 0.12 BASE CASE Year hid FA Investiment Net Warking Capital Rq! Unit Sales 0 2 Pricelunit VarCostunit Revenue Variable casts Fixad costs yoar Deprociaion BT Net Incame Depreciaion Ahar tax salvage Return of Warking Capital Cash Flow Accumulated cash fow Discounted Cash fow Nat Prasent value 12 % Indernal Rate ol Raurn Profability Index Avarage Accounting Return Payback Discounted Payback Use caloulator Use caloulator WORST CASE Yoar 0 5 Inial FA Investmant Net Warking Capital Rq Unit Sales Pricalunit VarCostunit Revanue Variable costs Fixad costs yoar Deprociaion BT Tax Not Income Deprociation Ahor tax salvage Roturn of Warking Capital Cash Flow Accumulatod cash flow Discountod Cash fow Nat Prasant value 12 % Intarnal Rate of Raturn Profitability Indax Avarage Accounting Roturn Payback Discounted Payback Use caloulator Use caloulator BEST CASE Yoar 2 3 5 4. Iniial FA Investmant Net Warking Capital Rq Unit Salas Pricalunit VarCostunit Ravanua Variable costs Fixod costs yoar Deprociaion EBIT Net Income Deprociaion Ator tax salvage Return of Working Capital Cash Flow Accumulatod cash fow Discountod Cash low Not Prasant valua 12 % Intarnal Rate of Raturn Profitability Index Avarage Accounting Return Payback Discounted Payback Use caloulator Use caloulator FIN 3320 Excel Project Use Excel to evaluate the following capital budgeting praject for the best, base, and worst cases. You will calculate the following values for each case: Net present value Internal rate of return Profitability Index Average Accounting Returni Payback Discounted payback The cash flows change every year because of the assumptions below. Note: You must do your calculations in Excel. Do not do them with a calculator and type them in, except for PAYBACK and DISCOUNTED PAYBACK. Example: For the base case, unit sales is 110,000 in year 1. The assumptions state that unit sales will decline in the second year by 1,000. Assuming that the year 1 unit sales is in cell M246 (note: it isn't really in cell M246), year 2 unit sales would be in N246 and the formula in that cell would be M246-1,000 Here is information about the project Initial Fixed Asset investment Net Working Cap Requirement Time frame 5 years $10,000,000 $800,000 Marginal tax rate 21 % Required rate of return 12 % 5-year straight line depreciation with assumption of zero salvage value For negative net pre-tax incomes, you must include negative taxes, i.., if the project loses money, it will reduce the company's taxable income, thus lowering its tax bill. PUT ALL THREE CASES ON ONE EXCEL SPREADSHEET; DO NOT USE TABS TO HAVE THREE SEPARATE SHEETS ALL COLUMNS SHOULD FIT WITHIN THE WIDTH OF ONE PRINTED PAGE. THEY SHOULD NOT BLEED ONTO A SECOND PAGE Base Worst Best Unit Sales year 1 110,000 98,000 122,000 Change in unit sales per year 1,000 -2,000 0 Price/unit year 1 $220 $200 $240 -$5 -$10 Change in price per year $0 Variable Cost/unit year 1 $145 $150 $140 Change in variable cost per year +$1 +$2 S0 Fixed costs year 1 $40,000 $40,000 $40,000 +S500 -$500 Change in fixed costs per year S0 Actual salvage value $120,000 $100,000 $140,000 FIN 3320 Excel Project Name (insert in cell F3) hia FA investment- $10,000,000 sa00,000 Net Warking Cap Reqt. Time frame in yoars Marginal tax rate Base actual salvage value Warst salvage valua- Best salvage value Requirad rate of roturn 21% $120,000 $100,000 $140,000 0.12 BASE CASE Year hid FA Investiment Net Warking Capital Rq! Unit Sales 0 2 Pricelunit VarCostunit Revenue Variable casts Fixad costs yoar Deprociaion BT Net Incame Depreciaion Ahar tax salvage Return of Warking Capital Cash Flow Accumulated cash fow Discounted Cash fow Nat Prasent value 12 % Indernal Rate ol Raurn Profability Index Avarage Accounting Return Payback Discounted Payback Use caloulator Use caloulator WORST CASE Yoar 0 5 Inial FA Investmant Net Warking Capital Rq Unit Sales Pricalunit VarCostunit Revanue Variable costs Fixad costs yoar Deprociaion BT Tax Not Income Deprociation Ahor tax salvage Roturn of Warking Capital Cash Flow Accumulatod cash flow Discountod Cash fow Nat Prasant value 12 % Intarnal Rate of Raturn Profitability Indax Avarage Accounting Roturn Payback Discounted Payback Use caloulator Use caloulator BEST CASE Yoar 2 3 5 4. Iniial FA Investmant Net Warking Capital Rq Unit Salas Pricalunit VarCostunit Ravanua Variable costs Fixod costs yoar Deprociaion EBIT Net Income Deprociaion Ator tax salvage Return of Working Capital Cash Flow Accumulatod cash fow Discountod Cash low Not Prasant valua 12 % Intarnal Rate of Raturn Profitability Index Avarage Accounting Return Payback Discounted Payback Use caloulator Use caloulator