In: Accounting
Based on the inputs below prepare a capital budget analysis for this Base Case using the Net Present Value, Internal Rate of Return, Profitability Index and Payback in years methods, determining whether the project is feasible. Please show your spreadsheet calculations and your final determinations of “go” or “no go” on the project.
Project Inputs:
WACC – Debt is 70% and Equity is 30% of this firm’s capital structure. Interest rate on the debt is 7.5%, firm’s tax rate is 22%. Firm’s beta is 1.50, Risk Free Rate is 3.0%, Market Return Rate is 9.0%.
Project Investment Outlay, Year 0 - $1,000,000
Project Investment Life – 10 years
Project Depreciation - $100,000 / year
Project Salvage Value - $30,000
Working Capital Base of Annual Sales – 10%
Expected inflation rate per year – 3.0%
Project Tax Rate – 30%
Units sold per year – 40,000
Selling Price per Unit, Year 1 - $40.00
Fixed operating costs per year excluding depreciation - $175,000
Manufacturing (Variable) costs per unit, Year 1 - $30.00
Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
1.Units sold/yr. | 40000 | 40000 | 40000 | 40000 | 40000 | 40000 | 40000 | 40000 | 40000 | 40000 | |
2.S.P./unit | 40 | 41.2 | 42.436 | 43.70908 | 45.02035 | 46.37096 | 47.76209 | 49.19495 | 50.6708 | 52.1909 | |
3.Total sales value(1*2) | 1600000 | 1648000 | 1697440 | 1748363 | 1800814 | 1854839 | 1910484 | 1967798 | 2026832 | 2087637 | |
Less: | |||||||||||
4.Fixed opg. Costs/yr. | -175000 | -180250 | -185658 | -191227 | -196964 | -202873 | -208959 | -215228 | -221685 | -228335 | |
5.Variable costs(30*40000) | -1200000 | -1236000 | -1273080 | -1311272 | -1350611 | -1391129 | -1432863 | -1475849 | -1520124 | -1565728 | |
6.Depn. | -100000 | -100000 | -100000 | -100000 | -100000 | -100000 | -100000 | -100000 | -100000 | -100000 | |
7.EBT(3+4+5+6) | 125000 | 131750 | 138702.5 | 145863.6 | 153239.5 | 160836.7 | 168661.8 | 176721.6 | 185023.3 | 193574 | |
8.Tax at 30%*EBT(7*30%) | -37500 | -39525 | -41610.8 | -43759.1 | -45971.8 | -48251 | -50598.5 | -53016.5 | -55507 | -58072.2 | |
9.EAT(7-8) | 87500 | 92225 | 97091.75 | 102104.5 | 107267.6 | 112585.7 | 118063.2 | 123705.1 | 129516.3 | 135502 | |
10.Add Back :depn.(row 6) | 100000 | 100000 | 100000 | 100000 | 100000 | 100000 | 100000 | 100000 | 100000 | 100000 | |
11.Operating cash flows(9+10) | 187500 | 192225 | 197091.8 | 202104.5 | 207267.6 | 212585.7 | 218063.2 | 223705.1 | 229516.3 | 235502 | |
CAPEX & WC cash flows | |||||||||||
12.Investment outlay | -1000000 | ||||||||||
13.After-tax salvage(30000*(1-30%)) | 21000 | ||||||||||
14.Beginning W/C | 0 | 160000 | 164800 | 169744 | 174836.3 | 180081.4 | 185483.9 | 191048.4 | 196779.8 | 202683.2 | 208764 |
15.Ending W/c(10%*Sales) | 160000 | 164800 | 169744 | 174836.3 | 180081.4 | 185483.9 | 191048.4 | 196779.8 | 202683.2 | 208763.7 | 0 |
16.Change in w/c | -160000 | -4800 | -4944 | -5092.32 | -5245.09 | -5402.44 | -5564.52 | -5731.45 | -5903.39 | -6080.5 | 208764 |
17. Total CAPEX & WC cash flows(12+13+16) | -1160000 | -4800 | -4944 | -5092.32 | -5245.09 | -5402.44 | -5564.52 | -5731.45 | -5903.39 | -6080.5 | 229764 |
18. Total annual cash flows(11+17) | -1160000 | 182700 | 187281 | 191999.4 | 196859.4 | 201865.2 | 207021.2 | 212331.8 | 217801.7 | 223435.8 | 465265 |
19.PV F at 7.7%(1/1.077^No.of yr.) | 1 | 0.92851 | 0.86212 | 0.80048 | 0.74325 | 0.69012 | 0.64078 | 0.59496 | 0.55243 | 0.51293 | 0.47626 |
20.PV at 7.7%(18*19) | -1160000 | 169637.9 | 161459 | 153692.6 | 146316.5 | 139310.2 | 132654 | 126329.6 | 120319.4 | 114607.1 | 221587 |
21. NPV(Sum of Row 20) | 325913.1 | ||||||||||
-160000 | -4456.82 | -4262.33 | -4076.32 | -3898.43 | -3728.31 | -3565.6 | -3410 | -3261.19 | -3118.87 | 99425.5 | |
22.Profitability Index= 1+(NPV/Initial Investment) | -94352.3 | ||||||||||
1+(325913/1000000)= | |||||||||||
1.33 | |||||||||||
23.IRR(of row 18) | 13% | ||||||||||
24.Pay back in Yrs. | |||||||||||
18. Total annual cash flows(11+17) | -1160000 | 182700 | 187281 | 191999 | 196859 | 201865 | 207021 | 212332 | 217802 | 223436 | 465265 |
Cumulative cash flows | -1160000 | -977300 | -790019 | -598020 | -401160 | -199295 | 7726 | 220058 | 437860 | 661296 | 1126561 |
Payback period= | |||||||||||
5+(199295/207021)= | |||||||||||
5.96 | |||||||||||
Years | |||||||||||
Summary | |||||||||||
NPV | 325913 | ||||||||||
PI | 1.33 | ||||||||||
IRR | 13% | ||||||||||
Pay back in Yrs. | 5.96 | ||||||||||
The project is highly feasible & hence recommended as its NPV is POSITIVE & PI > 1 , IRR> WACC,7.7%,& pays back with-in 6 years. |