Question

WACC & Capital Budget Analysis

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

Answer 1

WACC to discount cash flows:

After-tax cost of debt=7.5%*(1-22%)=

5.85%

Cost of equity as per CAPM

Ke=RFR+(Beta*(Market Risk premium-RFR))

ie.Ke=3%+(1.5*(9%-3%))=

12%

WACC=(Wt.e*Ke)+(Wt.d*Kd)

ie.(30%*12%)+(70%*5.85%)=

7.70%

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
22.Profitability Index= 1+(NPV/Initial Investment)
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.