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

Payback

Discounted payback

Average Accounting Return

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. Example: For the base case, unit sales is 100,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 B46, year 2 unit sales would be in C46 and the formula in that cell would be

=B46-1,000

Here is information about the project.

Initial Fixed Asset investment= $9,000,000

Net Working Cap Requirement= $800,000

Time frame = 5 years

Marginal tax rate= 35%

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.

EACH CASE MUST BE ON A SEPARATE PAGE AND FORMATTED TO BE ON ONE PAGE, OR MINUS 20 POINTS.

Base	Worst	Best
Unit Sales year 1=	100000	88000	112000
Change in unit sales per year=	-1000	-2000	0
Price/unit year 1=	200	180	220
Change in price per year=	-5	-10	0
Variable Cost/unit year 1=	150	154	146
Change in variable cost per year=	1	2	0
Fixed costs year 1=	30000	30000	30000
Change in fixed costs per year=	0	500	-500
Actual salvage value=	100000	80000	120000

FIN 3320 Spring 2016 -- Excel Project 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 Payback Discounted payback Average Accounting Return 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. Example: For the base case, unit sales is 100,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 B46, year 2 unit sales would be in C46 and the formula in that cell would be =B46-1,000 Here is information about the project. Initial Fixed Asset investment= $9,000,000 Net Working Cap Requirement= $800,000 Time frame = 5 years Marginal tax rate= 35% 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. EACH CASE MUST BE ON A SEPARATE PAGE AND FORMATTED TO BE ON ONE PAGE, OR MINUS 20 POINTS. Base Worst Best Unit Sales year 1= 100,000 88,000 112,000 Change in unit sales per year= -1,000 -2,000 0 Price/unit year 1= $200 $180 $220 Change in price per year= -$5 -$10 $0 Variable Cost/unit year 1= $150 $154 $146 Change in variable cost per year= +$1 +$2 $0 Fixed costs year 1= $30,000 $30,000 $30,000 Change in fixed costs per year= $0 +$500 -$500 Actual salvage value= $100,000 $80,000 $120,000 FIN 3320 Excel Project Name (insert in cell F3) Initial FA investment= Net Working Cap Reqt.= Time frame in years= Marginal tax rate= Base salvage val.= Worst salvage val.= Best salvage val.= Required rate of return= $9,000,000 $800,000 5 35% $100,000 $80,000 $120,000 0.10 BASE CASE Year Initial FA Investment Net Working Capital Rqt Unit Sales Price/unit VarCost/unit 0 1 2 3 4 5 0 1 2 3 4 5 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 Net Present value 10% Internal Rate of Return Profitability Index Payback Discounted Payback Average Accounting Return 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 Net Present value 10% Internal Rate of Return Profitability Index Payback Discounted Payback Average Accounting Return BEST 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 Net Present value 10% Internal Rate of Return Profitability Index Payback Discounted Payback Average Accounting Return 0 1 2 3 4 5