Use Excel to evaluate the following capital budgeting project for the best, base
ID: 2764779 • Letter: U
Question
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
same as with worst case and best case
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
Explanation / Answer
Solution:
BEST CASE:
BASE CASE Year 0 1 2 3 4 5 Initial FA Investment -9000000 Net Working Capital Rqt -800000 Unit Sales 100000 99000 98000 97000 96000 Price/unit 200 195 190 185 180 VarCost/unit 150 149 148 147 146 Revenue 20000000 19305000 18620000 17945000 17280000 Variable costs 15000000 14751000 14504000 14259000 14016000 Fixed costs year 30000 30000 30000 30000 30000 Depreciation9000000/5 1800000 1800000 1800000 1800000 1800000 EBIT 3170000 2724000 2286000 1856000 1434000 Tax 35% 1109500 953400 800100 649600 501900 Net Income 2060500 1770600 1485900 1206400 932100 Depreciation 1800000 1800000 1800000 1800000 1800000 After tax salvage 0 0 0 0 65000 Return of Working Capital 0 0 0 0 800000 Cash Flow 3860500 3570600 3285900 3006400 3597100 Accumulated cash flow -9800000 3860500 3570600 3285900 3006400 3597100 Discount at 10%=1/1.1^n 1 0.9090909091 0.826446281 0.7513148009 0.6830134554 0.6209213231 Present value= cash flow*rate -9800000 3509545.455 2950909.091 2468745.304 2053411.652 2233516.091 Net Present value= total 3416127 Internal Rate of Return= 23% Profitability Index = Net present /Initial outflow 1.283778638 Payback = iniflow - outflow At the end of 3 years the total outflow gets repaid hence the payback period is 2 years 8 months Discounted Payback= discounted inflow - outflow At the end of 4 years the total outflow gets repaid hence the payback period is 3 years 8 monthsRelated Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.