Question

x fx Capital Budgeting Capital Budgeting Wenling Consulting Services is considering an eight year investment in two projects, A and B. Both projects will have Initial outlay, $120,000, and the terminal cash flow, $11,000. The annual after-tax operating cash flows are as follo 1 $ 3 4 5 6 7 $ $ $ $ $ Project A 31,000.00 28,700.00 23,440.00 23,200.00 21,000.00 19,900.00 18,900.00 16,500.00 $ $ $ $ $ $ $ 5 Project B 19,000.00 19,500.00 22,700.00 24,300.00 27,000.00 27,600.00 28,000.00 29,000.00 8 $ 15 Assume that Wenling's WACC is 10% 16 1. Calculate the NPV, PI, IRR, and MIRR. If the projects are mutually exclusive, which project should be selected? 13 2. Create an NPV profile chart for projects A and B. What is the exact crossover rate for these two projects? 203. Create a scenario analysis showing the NPV, IRR, and Pl for this investment, assuming the following three scenarios 211) Best Case: initial outlay would be only $100,000 and terminal cash flow would be $25,000 222) Base Case: Initial outlay and terminal cash would be exactly as expected 233) Worst Case: initial outlay would be $170,000 but terminal cash flow would be only $8,000

Answer 1

		Present Value(PV) of Cash Flow:
		(Cash Flow)/((1+i)^N)
		i=discount rate =WACC=10%
		N=Year   of Cash Flow
		Future Value (FV)at Year T=(Cash Flow)*((1+i)^(T-N))
		N	CFA	PVA=CFA/(1.1^N)	CFB	PVB=CFB/(1.1^N)	FVA=CFA*(1.1^(8-N)	FVB=CFB*(1.1^(8-N)
		Year	Cash Flow Project A	PV of cash flowProject A	Cash Flow Project B	PV of cash flowProject B	FVof Project A at year8)	FVof Project B at year8)
		0	-$120,000	-$120,000	-$120,000	-$120,000
		1	$31,000	$28,181.82	$19,000	$17,272.73	$60,410.23	$37,025.62
		2	$28,700	$23,719.01	$19,500	$16,115.70	$50,843.80	$34,545.44
		3	$23,440	$17,610.82	$22,700	$17,054.85	$37,750.35	$36,558.58
		4	$23,200	$15,845.91	$24,300	$16,597.23	$33,967.12	$35,577.63
		5	$21,000	$13,039.35	$27,000	$16,764.88	$27,951.00	$35,937.00
		6	$19,900	$11,233.03	$27,600	$15,579.48	$24,079.00	$33,396.00
		7	$18,900	$9,698.69	$28,000	$14,368.43	$20,790.00	$30,800.00
	Including Terminal Cash flow ($11000)	8	$27,500	$12,828.95	$40,000	$18,660.30	$27,500.00	$40,000.00
	NPV=Net Present Value		SUM	$12,157.58	SUM	$12,413.58	$283,291.51	$283,840.27
	NPV=Sum of PV of Cash Flows
	NPV of Project A	$12,157.58
	NPV of Project B	$12,413.58
	PI=Profitability Index
	PI=(NPV+Initial Cash Outlay)/Initial Outlay
	PI of Project A=(12157.58+120000)/120000=	1.10131
	PI of Project B=(12413.58+120000)/120000=	1.10345
	IRR=Internal Rate of Return
	IRR of Project A	12.9264%	(Using IRR function of excel over the cash flow)
	IRR of Project B	12.4729%	(Using IRR function of excel over the cash flow)
	SUM of Future Value of Positive Cash Flows of Project A	$283,291.51
	SUM of Future Value of Positive Cash Flows of Project A	$283,840.27
	(Initial Cash Flow)*(1+MIRR)^8=Sum of Future Value of Positive Cash Flows
	120000*(1+MIRR(ProjectA))^8=	$283,291.51
	MIRR (Project A)=((283291.51/120000)^(1/8))-1	0.1133
	MIRR (Project A)=	11.33%
	MIRR (Project B)=((283840.27/120000)^(1/8))-1	0.1136
	MIRR (Project B)	11.36%
	PROJECT B Should be selected
	Because it has higher NPV
		H31 : f =IRR(112:120) K CFA PVA=CFA/(1.1^N) CFB Year Cash Flow PV of cash Cash Flow P Project A flowProject A Project B fl $120,000 -$120,000 $120,000 $31,000 $28,181.82 $19,000 $28,700 $23,719.01 $19,500 $23,440 $17,610.82 $22,700 $23,200 $15,845.91 $24,300 $21,000 $13,039.35 $27,000 $19,900 $11,233.03 $27,600 $18,900 $9,698.69 $28,000 $27,500 $12,828.95 $40,000 SUM $12,157.58 SUM Including Terminal Cash flow ($11000) NPV=Net Present Value NPV=Sum of PV of Cash Flows NPV of Project A NPV of Project B $12,157.58 $12,413.58 PI=Profitability Index PI=(NPV+Initial Cash Outlay)/Initial Outlay PI of Project A=(12157.58+120000)/120000= Pl of Project B=(12413.58+120000)/120000= IRR=Internal Rate of Return IRR of Project A 1.10131 1.10345 12.9264%! (Using IRR function of excel over the cash flow)