Question

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. PLEASE show formulas

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 1.0%, Market Return Rate is 7.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
Wd	Weight of Debt	0.7
We	Weight of Equity	0.3
Cd	After tax cost of debt	5.85
	Interest rate * (1-tax)
	(7.5 * (1-0.22)
Ce	Cost of Equity
	Required return on equity
	= Rf + Beta*(Rm-Rf)
	Rf = Risk free return = 1%
	Beta = 1.50
	Rm = Market Return = 7%
	Required return on equity
	=1 + (1.5)*(7-1)	10.00
WACC	Wd*Cd + We*Ce
	(0.7*5.85) + (0.3 * 10)	7.095
i	Discount Rate	0.07095

	Inflation Rate 3%	0.03
	Selling Price - Year 1 ($)	40.00
	Selling Price - Year 2 ($) (40*1.03)	41.20
	Selling Price - Year 3 ($) (41.20*1.03)	42.44
	Selling Price - Year 4 ($) (42.44*1.03)	43.71
	Variable Cost Price - Year 1 ($)	30.00
	Variable Cost - Year 2 ($) (30*1.03)	30.90
	Variable Cost - Year 3 ($) (30.90*1.03)	31.83
	Variable Cost - Year 4 ($) (31.83*1.03)	32.78

After Tax Salvage Value = 30000 (1-0.22) = $ 23400

N	Year	0	1	2	3	4
a	Initial Cash flow	-10,00,000
b	Sales in unit		40,000	40,000	40,000	40,000
c	Sales Price per unit		40.00	41.20	42.44	43.71
d=b*c	Annual Sales Revenue		16,00,000	16,48,000	16,97,440	17,48,363
e	Variable Cost per unit		30.00	30.90	31.83	32.78
f=b*e	Annual Variable Cost		12,00,000	12,36,000	12,73,080	13,11,272
g	Annual Fixed Costs(excluding depreciation)		1,75,000	1,75,000	1,75,000	1,75,000
h	Annual Depreciation expense		1,00,000	1,00,000	1,00,000	1,00,000
i=d-(f+g+h)	Before tax operating profit		1,25,000	1,37,000	1,49,360	1,62,091
j=-i*25%	Tax Expenses(Tax Rate =25%)		31,250	34,250	37,340	40,523
k=i-j	After tax operating profit		93,750	1,02,750	1,12,020	1,21,568
l	Add depreciation expenses(non cash)		1,00,000	1,00,000	1,00,000	1,00,000
m=k+l	Annual Operating Cash Flow		1,93,750	2,02,750	2,12,020	2,21,568
n	Working Capital Needed(10% of next years sale)	1,60,000	1,64,800	1,69,744	1,74,836	-
p	Cash flow due to change in working capital	-1,60,000	-4,800	-4,944	-5,092	1,74,836
q	Salvage Cash Flow					23,400
CF=a+m+p+q	Net Cash Flow	-11,60,000	1,88,950	1,97,806	2,06,928	4,19,804
	Cumulative Net Cash flow	-11,60,000	-9,71,050	-7,73,244	-5,66,316	-1,46,512
PV=CF/(1.07095^N)	Net Present Value	-11,60,000	1,76,432	1,72,465	1,68,466	3,19,132
Sum of NPV	Net Present Value	-3,23,505
PI	Profitability Index (NPV+Initial Outlay)/(Initial Outlay)	0.72
IRR	Internal Rate of Return (using excel function)	5%

Payback	Payback Period	more than 4 years
	Recommendation	NO GO
	NPV is Negative
	IRCC is less than WACC
	PI is also less than 1