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. Use your Investment Return Analysis as an example for this capital budget analysis.

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

Step 1). WACC calculation:

CAPM ke = risk-free rate + beta*(market return - risk-free rate)

Capital structure:
Debt (D)	70%
Equity €	30%
Cost of debt:			Cost of equity (using CAPM):
Interest rate	7.50%		risk-free rate	3%
Tax rate	22%		market return	9%
After-tax cost of debt (kd) (interest rate*(1-tax rate))	5.85%		beta	1.5
			cost of equity (ke)	12.00%
WACC: (kd*D) + (ke*E)	7.695%

Nominal discount rate = WACC = 7.695%; inflation rate = 3%

Real discount rate = (nominal discount rate - inflation rate)/(1+inflation rate)

= (7.695% - 3%)/(1+3%) = 4.558%

Step 2). Calculation of after-tax salvage value:

Salvage value = 30,000

Tax rate = 30%

Book value (at the end of the project) = 0 (Straight line depreciation of 10,000/year)

Thus, after-tax salvage value = salvage value*(1-tax-rate) = 30,000*(1-30%) = 21,000

Step 3). Change in working capital:

Year 1 sales = number of units*selling price/unit = 40,000*40 = 1,600,000

Working capital is 10% of sales = 10%*1,600,000 = 160,000

This will be the increase in WC for Year 1 and will be returned at the end of the project i.e. in Year 10.

Step 4). Calculation of Operating Cash Flows:

	Year (n)	1	2	3	4	5	6	7	8	9	10
Formula	Number of units (N)	40,000	40,000	40,000	40,000	40,000	40,000	40,000	40,000	40,000	40,000
	Sales price/unit (s)	40	40	40	40	40	40	40	40	40	40
(s*N)	Total sales (S)	16,00,000	16,00,000	16,00,000	16,00,000	16,00,000	16,00,000	16,00,000	16,00,000	16,00,000	16,00,000
	Variable cost/unit (v)	30	30	30	30	30	30	30	30	30	30
(v*N)	Total variable cost (V)	12,00,000	12,00,000	12,00,000	12,00,000	12,00,000	12,00,000	12,00,000	12,00,000	12,00,000	12,00,000
	Total fixed cost (F)	1,75,000	1,75,000	1,75,000	1,75,000	1,75,000	1,75,000	1,75,000	1,75,000	1,75,000	1,75,000
(S-V-F)	EBITDA	2,25,000	2,25,000	2,25,000	2,25,000	2,25,000	2,25,000	2,25,000	2,25,000	2,25,000	2,25,000
	Depreciation	1,00,000	1,00,000	1,00,000	1,00,000	1,00,000	1,00,000	1,00,000	1,00,000	1,00,000	1,00,000
(EBITDA-dep.)	EBIT	1,25,000	1,25,000	1,25,000	1,25,000	1,25,000	1,25,000	1,25,000	1,25,000	1,25,000	1,25,000
30%*EBIT	Tax @30%	37,500	37,500	37,500	37,500	37,500	37,500	37,500	37,500	37,500	37,500
(EBIT-tax)	Net income	87,500	87,500	87,500	87,500	87,500	87,500	87,500	87,500	87,500	87,500
	Add: depreciation	1,00,000	1,00,000	1,00,000	1,00,000	1,00,000	1,00,000	1,00,000	1,00,000	1,00,000	1,00,000
(Net income + dep.)	Operating cash flow (OCF)	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500

Step 5). Calculation of NPV:

Formula	Year (n)	0	1	2	3	4	5	6	7	8	9	10
	Initial investment (I)	-10,00,000
	Operating cash flow (OCF)		1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500
(The invested WC is returned at the end of the project)	Add: Change in working capital		-1,60,000									1,60,000
	Add: After-tax salvage value (SV)											21,000
(I + OCF + Change in WC + SV)	Total cash flows	-10,00,000	27,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	3,68,500
1/(1+d)^n	Discount factor @4.558%	1.0000	0.9564	0.9147	0.8748	0.8367	0.8002	0.7653	0.7320	0.7001	0.6695	0.6403
(Total cash flow*discount factor)	Discounted cash flow (DCF)	-10,00,000.00	26,301.13	1,71,508.10	1,64,031.15	1,56,880.15	1,50,040.91	1,43,499.83	1,37,243.90	1,31,260.71	1,25,538.35	2,35,968.66
Sum of all DCF	NPV	4,42,272.90

Thus, NPV = $442,272.90

IRR = 11.48% (using the IRR function in excel)

Step 6). Calculation of Payback Period:

Year (n)	0	1	2	3	4	5	6	7	8	9	10
Total cash flows	-10,00,000	27,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	1,87,500	3,68,500
Cumulative cash flow	-10,00,000	-9,72,500	-7,85,000	-5,97,500	-4,10,000	-2,22,500	-35,000	1,52,500	3,40,000	5,27,500	8,96,000

Cash flow turns positive in Year 7.

Fraction of Year 7 = cumulative cash flow in Year 6/Cash flow in Year 7 = 35,000/187,500 = 0.19

Thus, payback period = 6 years + 0.19 year = 6.19 years

Step 7). Calculation of Profitability Index:

PI = Sum of present value of all future cash flows/initial investment = 1,442,272.90/1,000,000 = 1.44

Conclusions:

1. The project has a positive NPV.

2. The IRR is greater than the discount rate

3. Payback period is much earlier than the end of project.

4. Profitability index is greater than 1 so the project is profitable.

Based on these findings, the project should be accepted.