Question

needs to be in excel and need formulas shown

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. AND explain your answer. Use your Investment Return Analysis as an example for this capital budget analysis Project Inputs: WACC - Debt is 60% and Equity is 40% of this firm's capital structure. Interest rate on the debt is 6.5%, firm's tax rate is 25%. Firm's beta is 1.25, Risk Free Rate is 2.0%, Market Return Rate is 9.0%. Project Investment Outlay, Year 0 - $400,000 Project Investment Life - 4 years Project Depreciation - $100,000 / year Project Salvage Value - $20,000 Working Capital Base of Annual Sales - 10% Expected inflation rate per year - 3.5% Project Tax Rate - 25% Units sold per year - 40,000 Selling Price per Unit, Year 1 - $20.00 Fixed operating costs per year excluding depreciation - $100,000 Manufacturing (Variable) costs per unit, Year 1 - $15.00

Answer 1

			WACC:
		Wd	Weight of debt	0.6
		We	Weight of Equity	0.4
		Cd	After tax cost of debt =6.5*(1-Tax Rate)	4.88%	(6.5*(1-0.25)
			Required Return on Equity=Rf+Beta*(Rm-Rf)
			Rf=Risk Free Rate=2%
			Beta=1.25
			Rm=Market Return =9%
			Required Return on Equity=2+1.25*(9-2)	10.75%
		Ce	Cost of Equity	10.75%
			WACC=Wd*Cd+We*Ce=0.6*4.88+0.4*10.75=	7.23%	0.0723
			Present Value(PV) of Cash Flow:
			(Cash Flow)/((1+i)^N)
			i=discount rate =WACC=0.0723
			N=Year   of Cash Flow
			Inflation Rate	3.50%	0.035
			Selling Price in year 2=20*1.035	$20.70
			Selling Price in year 3=20.7*1.035	$21.42
			Selling Price in year 4=21.42*1.035	$22.17
			Variable Cost per unit in year2	$15.53	(15*1.035)
			Variable Cost per unit in year3	$16.07	(15.53*1.035)
			Variable Cost per unit in year4	$16.63	(16.07*1.035)
			After tax Salvage Value=20000*(1-Tax Rate)=	$15,000	20000*(1-0.25)
		N	Year	0	1	2	3	4
		a	Initial Cash flow	-$400,000
		b	Sales in unit		40,000	40,000	40,000	40,000
		c	Sales Price per unit		$20.00	$20.70	$21.42	$22.17
		d=b*c	Annual Sales Revenue		$800,000	$828,000	$856,800	$886,800
		e	Variable Cost per unit		$15.00	$15.53	$16.07	$16.63
		f=b*e	Annual Variable Cost		-$600,000	-$621,200	-$642,800	-$665,200
		g	Annual Fixed Costs(excluding depreciation)		-$100,000	-$100,000	-$100,000	-$100,000
		h	Annual Depreciation expense		-$100,000	-$100,000	-$100,000	-$100,000
		i=d+f+g+h	Before tax operating profit		$0	$6,800	$14,000	$21,600
		j=-i*25%	Tax Expenses(Tax Rate =25%)		$0	-$1,700	-$3,500	-$5,400
		k=i+j	After tax operating profit		$0	$5,100	$10,500	$16,200
		l	Add depreciation expenses(non cash)		$100,000	$100,000	$100,000	$100,000
		m=k+l	Annual Operating Cash Flow		$100,000	$105,100	$110,500	$116,200
		n	Working Capital Needed(10% of next years sale)	$80,000	$82,800	$85,680	$88,680	$0
		p	Cash flow due to change in working capital	-$80,000	-$2,800	-$2,880	-$3,000	$88,680
		q	Salvage Cash Flow					$15,000
		CF=a+m+p+q	Net Cash Flow	($480,000)	$97,200	$102,220	$107,500	$219,880	SUM
			Cumulative Net Cash Flow	($480,000)	($382,800)	($280,580)	($173,080)	$46,800
		PV=CF/(1.0723^N)	Present Value of Net Cash Flow	($480,000)	$90,646	$88,900	$87,189	$166,311	($46,954)
		NPV=Sum of PV	Net Present Value(NPV)	($46,954)
		PI	Profitability Index=(NPV+Initial Outllay)/(Initial Outlay)
		PI	Profitability Index=(-46954+480000)/480000	0.90
		IRR	Internal Rate of Return	3.34%	(Using IRR function of excel over Net Cash Flow)
			Payback Period =Period when Cumulative cash flow=NIL
			Payback Period =3+(173080/219880)	3.79	Years
			Recommendation : NO GO
			NPV Negative
			IRR less than WACC
			PI less than 1
				=IRR(142:M42) fox 147 D E F G H K M N b Sales in unit 40,000 27 40,000 40,000 40,000 $20.00 $800,000 $15.00 $20.70 $21.42 $22.17 Sales Price per unit 28 C $828,000 Annual Sales Revenue $856,800 $886,800 d-b*c 29 Variable Cost per unit $15.53 $16.07 $16.63 30 -$642,800 -$100,000 Annual Variable Cost -$600,000 -$621,200 -$665,200 f-b*e 31 Annual Fixed Costs(excluding depreciation) Annual Depreciation expense Before tax operating profit Tax Expenses(Tax Rate 25% After tax operating profit Add depreciation expenses(non cash) Annual Operating Cash Flow -$100,000 -$100,000 -$100,000 32 -$100,000 -$100,000 -$100,000 -$100,000 h 33 $21,600 id+f+g+h $0 $6,800 $14,000 34 $0 -$1,700 -$3,500 -$5,400 j-i*25% 35 $5,100 $100,000 $0 $10,500 $16,200 k=i+j 36 $100,000 $100,000 $100,000 37 $100,000 $105,100 $110,500 $116,200 m ktl 38 $80,000 $82,800 $85,680 $88,680 $0 Working Capital Needed (10% of next years sale) Cash flow due to change in working capital 39 $88,680 $15,000 -$80,000 -$2,800 -$2,880 -$3,000 40 p Salvage Cash Flow 41 q ($480,000) ($480,000) ($46,954) $97,200 $102,220 $107,500 $219,880 SUM Net Cash Flow 42 CF=a+m+p+q PV CF/(1.0723AN) Present Value of Net Cash Flow NPV Sum of PV $90,646 $88,900 $87,189 $166,311 ($46,954) 43 Net Present Value(NPV) 44 Profitability Index-(NPV+Initial Outlay)/(Initial Outlay) 45 PI Profitability Index-(-46954+480000)/480000 46 PI 0.90 3.34% Internal Rate of Return (Using IRR function of excel over Net Cash Flow) 47 IRR 48