Template:

Use Excel to evaluate the following capital budgeting project 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 Return 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= $11,000,000 Net Working Cap Requirement= $800,000, returned at the end of the project Time frame = 5 years 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.e., if the project loses money, it will reduce the company's taxable income, thus lowering its tax bill. 10 points will be subtracted if you do not follow the following requirements: 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 $190 $250 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= $40,000 $40,000 $40,000 Change in fixed costs per year= +$100 +$500 -$300 Actual salvage value= $120,000 $100,000 $140,000 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= $11,000,000 $800,000 5 21% $120,000 $100,000 $140,000 0.12 0 1 2 BASE CASE Year Initial FA Investment Net Working Capital Rat Unit Sales Price/unit VarCost/unit Revenue Variable costs Fixed costs year Depreciation EBIT Tax Net Income Depreciation After tax salvage Return of Working Capital Cash Flow Accumulated cash flow Discounted Cash flow Accumulated disc cash flow Net Present value 12% Internal Rate of Return Profitability Index Average Accounting Return Payback Discounted Payback Use calculator Use calculator 0 1 2 3 WORST CASE Year Initial FA Investment Net Working Capital Rqt Unit Sales Price/unit VarCost/unit Revenue Variable costs Fixed costs year Depreciation EBIT Tax Net Income Depreciation After tax salvage Return of Working Capital Cash Flow Accumulated cash flow Discounted Cash flow Accumulated disc cash flow Net Present value 12% Internal Rate of Return Profitability Index Average Accounting Return Payback Discounted Payback Use calculator Use calculator 0 1 2 3 BEST CASE B Year 1 Initial FA Investment 5 Net Working Capital Rqt 5 Unit Sales - Price/unit VarCost/unit 2 Revenue 2 Variable costs 3 Fixed costs year 4 Depreciation 5 EBIT 6 - Net Income 3 Depreciation After tax salvage Return of Working Capital 1 Cash Flow 2 3 Accumulated cash flow 4 Discounted Cash flow 5 Accumulated disc cash flow 6 7 Net Present value 12% 8 Internal Rate of Return 9 Profitability Index 0 Average Accounting Return 1 Payback 2 Discounted Payback 3 Use calculator Use calculator 4 5 6 Use Excel to evaluate the following capital budgeting project 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 Return 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= $11,000,000 Net Working Cap Requirement= $800,000, returned at the end of the project Time frame = 5 years 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.e., if the project loses money, it will reduce the company's taxable income, thus lowering its tax bill. 10 points will be subtracted if you do not follow the following requirements: 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 $190 $250 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= $40,000 $40,000 $40,000 Change in fixed costs per year= +$100 +$500 -$300 Actual salvage value= $120,000 $100,000 $140,000 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= $11,000,000 $800,000 5 21% $120,000 $100,000 $140,000 0.12 0 1 2 BASE CASE Year Initial FA Investment Net Working Capital Rat Unit Sales Price/unit VarCost/unit Revenue Variable costs Fixed costs year Depreciation EBIT Tax Net Income Depreciation After tax salvage Return of Working Capital Cash Flow Accumulated cash flow Discounted Cash flow Accumulated disc cash flow Net Present value 12% Internal Rate of Return Profitability Index Average Accounting Return Payback Discounted Payback Use calculator Use calculator 0 1 2 3 WORST CASE Year Initial FA Investment Net Working Capital Rqt Unit Sales Price/unit VarCost/unit Revenue Variable costs Fixed costs year Depreciation EBIT Tax Net Income Depreciation After tax salvage Return of Working Capital Cash Flow Accumulated cash flow Discounted Cash flow Accumulated disc cash flow Net Present value 12% Internal Rate of Return Profitability Index Average Accounting Return Payback Discounted Payback Use calculator Use calculator 0 1 2 3 BEST CASE B Year 1 Initial FA Investment 5 Net Working Capital Rqt 5 Unit Sales - Price/unit VarCost/unit 2 Revenue 2 Variable costs 3 Fixed costs year 4 Depreciation 5 EBIT 6 - Net Income 3 Depreciation After tax salvage Return of Working Capital 1 Cash Flow 2 3 Accumulated cash flow 4 Discounted Cash flow 5 Accumulated disc cash flow 6 7 Net Present value 12% 8 Internal Rate of Return 9 Profitability Index 0 Average Accounting Return 1 Payback 2 Discounted Payback 3 Use calculator Use calculator 4 5 6