Question

Now that you have chosen your portfolio, the management is considering two alternative investment proposals. The first proposal calls for a major renovation of the company’s manufacturing facility, while the second proposal involves replacing the obsolete pieces of equipment in the facility. The company will choose only one project and the company will use the WACC at 15%.
YEAR RENOVATE REPLACE
0 -$90,000 -$240,000
1 $30,000 $200,000
2 $30,000 $80,000
3 $30,000 $20.000
4 $30,000 $20,000
5 $30,000 $20,000

1. Calculate the payback period of each project and, based on this criterion, indicate which project you would recommend for acceptance.
2. Calculate the Net Present Value of each project and, based on this criterion, indicate which project you would recommend for acceptance.
3. Calculate the Internal Rate of Return of each project and based on this criterion, indicate which project you would recommend for acceptance.
4. Calculate the Profitability Index of each project and based on this criterion, indicate which project you would recommend for acceptance.
5. Overall, you should find conflicting recommendations based on the various criteria. Why is this occurring?

Answer 1

A	B	C	D	E	F	G	H	I	J
2
3		Year	Renovate	Replace
4		0	($90,000)	($240,000)
5		1	$30,000	$200,000
6		2	$30,000	$80,000
7		3	$30,000	$20,000
8		4	$30,000	$20,000
9		5	$30,000	$20,000
10
11		1)
12
13		Calculation of Payback period for Renovate:
14		Payback period is the period when investment amount is recovered.
15		Year	0	1	2	3	4	5
16		Free Cash Flow	($90,000)	$30,000	$30,000	$30,000	$30,000	$30,000
17		Cumulative cash flow	($90,000)	($60,000)	($30,000)	$0	$30,000	$60,000
18
19		Since the Payback period is when cumulative free cash flow becomes zero,
20		and it can be seen from above that cumulative cash flow becomes zero at year 3.
21
22		Hence Payback period is	3.00	Years
23
24		Calculation of Payback period for replace:
25		Payback period is the period when investment amount is recovered.
26		Year	0	1	2	3	4	5
27		Free Cash Flow	($240,000)	$200,000	$80,000	$20,000	$20,000	$20,000
28		Cumulative cash flow	($240,000)	($40,000)	$40,000	$60,000	$80,000	$100,000
29
30		Since the Payback period is when cumulative free cash flow becomes zero,
31		and it can be seen from above that cumulative cash flow becomes zero between year 4 and year 5.
32		therefore the payaback period will be in between year 4 and 5.
33		To estimate the exact payback period cumulative free cash flow can be proprated over the years as follows:
34		Payback period	1.50	=E26+(0-E28)/(F28-E28)
35
36		Hence Payback period is	1.50	Years
37		Since payback period for replace is lower, therefore it should be accepted.
38
39		2)
40		Calculation of NPV for renovate:
41		NPV of the project is present value of future cash flows discounted at required rate of return less the initial investment.
42		Given the following cash flow and MARR, NPV for the project can be calculated as follows:
43		Year	0	1	2	3	4	5
44		Cash Flow	($90,000)	$30,000	$30,000	$30,000	$30,000	$30,000
45		MARR (i)	15%
46		(P/F,i,n) for each year		0.87	0.76	0.66	0.57	0.50
47		Present Value of cash flows = FCF*(P/F,i,n)		$26,086.96	$22,684.31	$19,725.49	$17,152.60	$14,915.30
48		Present value if future cash flows	$100,564.65	=SUM(E47:I47)
49
50		NPV for Project	=Present value fo future cash flows - Initial investment
51			$10,564.65	=D48+D44
52
53		Hence NPV for renovate is	$10,564.65	million
54
55		Calculation of NPV for replace:
56		NPV of the project is present value of future cash flows discounted at required rate of return less the initial investment.
57		Given the following cash flow and MARR, NPV for the project can be calculated as follows:
58		Year	0	1	2	3	4	5
59		Cash Flow	($240,000)	$200,000	$80,000	$20,000	$20,000	$20,000
60		MARR (i)	15%
61		(P/F,i,n) for each year		0.87	0.76	0.66	0.57	0.50
62		Present Value of cash flows = FCF*(P/F,i,n)		$173,913.04	$60,491.49	$13,150.32	$11,435.06	$9,943.53
63		Present value if future cash flows	$268,933.46	=SUM(E62:I62)
64
65		NPV for Project	=Present value fo future cash flows - Initial investment
66			$28,933.46	=D63+D59
67
68		Hence NPV for to replace is	$28,933.46	million
69		Since NPV for replace is higher, therefore it should be accepted.
70		3)
71		IRR is the rate at which NPV of the project will be zero.
72		IRR can also be found using IRR function in excel as follows:
73		Year	Renovate	Replace
74		0	($90,000)	($240,000)
75		1	$30,000	$200,000
76		2	$30,000	$80,000
77		3	$30,000	$20,000
78		4	$30,000	$20,000
79		5	$30,000	$20,000
80		IRR	19.86%	23.69%	=IRR(E74:E79)
81
82		Hence
83		IRR for Renovate is	19.86%
84		IRR for replace is	23.69%
85		Since IRR for projects are higher than MARR and IRR for replace is higher, therefore it should be accepted.
86		4)
87
88		Profitability index	=(Initial investment +NPV)/Initial Investment
89
90		Profitability index for renovate	=(Initial investment +NPV)/Initial Investment
91			1.117	=(-D4+D53)/(-D4)
92
93		Profitability index for replace	=(Initial investment +NPV)/Initial Investment
94			1.121	=(-E4+D68)/(-E4)
95
96		Profitability index for renovate	1.117
97		Profitability index for replace	1.121
98
99		Since profitability index for replace is higher than renovate, therefore it should be accepted.
100
101		5)
102
103		Conflicting cases occur due to different assumptions such as IRR assumes reinvestment rate at IRR
104		whereas NPV assumes reinvestment rate at MARR.
105

Formula sheet

A	B	C	D	E	F	G	H	I	J
2
3		Year	Renovate	Replace
4		0	-90000	-240000
5		1	30000	200000
6		2	30000	80000
7		3	30000	20000
8		4	30000	20000
9		5	30000	20000
10
11		1)
12
13		Calculation of Payback period for Renovate:
14		Payback period is the period when investment amount is recovered.
15		Year	0	1	2	3	4	5
16		Free Cash Flow	=D4	=D5	=D6	=D7	=D8	=D9
17		Cumulative cash flow	=D16	=D17+E16	=E17+F16	=F17+G16	=G17+H16	=H17+I16
18
19		Since the Payback period is when cumulative free cash flow becomes zero,
20		and it can be seen from above that cumulative cash flow becomes zero at year 3.
21
22		Hence Payback period is	=G15	Years
23
24		Calculation of Payback period for replace:
25		Payback period is the period when investment amount is recovered.
26		Year	0	1	2	3	4	5
27		Free Cash Flow	=E4	=E5	=E6	=E7	=E8	=E9
28		Cumulative cash flow	=D27	=D28+E27	=E28+F27	=F28+G27	=G28+H27	=H28+I27
29
30		Since the Payback period is when cumulative free cash flow becomes zero,
31		and it can be seen from above that cumulative cash flow becomes zero between year 4 and year 5.
32		therefore the payaback period will be in between year 4 and 5.
33		To estimate the exact payback period cumulative free cash flow can be proprated over the years as follows:
34		Payback period	=E26+(0-E28)/(F28-E28)	=E26+(0-E28)/(F28-E28)
35
36		Hence Payback period is	=D34	Years
37		Since payback period for replace is lower, therefore it should be accepted.
38
39		2)
40		Calculation of NPV for renovate:
41		NPV of the project is present value of future cash flows discounted at required rate of return less the initial investment.
42		Given the following cash flow and MARR, NPV for the project can be calculated as follows:
43		Year	0	1	2	3	4	5
44		Cash Flow	=D16	=E16	=F16	=G16	=H16	=I16
45		MARR (i)	0.15
46		(P/F,i,n) for each year		=1/((1+$D45)^E43)	=1/((1+$D45)^F43)	=1/((1+$D45)^G43)	=1/((1+$D45)^H43)	=1/((1+$D45)^I43)
47		Present Value of cash flows = FCF*(P/F,i,n)		=E44*E46	=F44*F46	=G44*G46	=H44*H46	=I44*I46
48		Present value if future cash flows	=SUM(E47:I47)	=SUM(E47:I47)
49
50		NPV for Project	=Present value fo future cash flows - Initial investment
51			=D48+D44	=D48+D44
52
53		Hence NPV for renovate is	=D51	million
54
55		Calculation of NPV for replace:
56		NPV of the project is present value of future cash flows discounted at required rate of return less the initial investment.
57		Given the following cash flow and MARR, NPV for the project can be calculated as follows:
58		Year	0	1	2	3	4	5
59		Cash Flow	=D27	=E27	=F27	=G27	=H27	=I27
60		MARR (i)	0.15
61		(P/F,i,n) for each year		=1/((1+$D60)^E58)	=1/((1+$D60)^F58)	=1/((1+$D60)^G58)	=1/((1+$D60)^H58)	=1/((1+$D60)^I58)
62		Present Value of cash flows = FCF*(P/F,i,n)		=E59*E61	=F59*F61	=G59*G61	=H59*H61	=I59*I61
63		Present value if future cash flows	=SUM(E62:I62)	=SUM(E62:I62)
64
65		NPV for Project	=Present value fo future cash flows - Initial investment
66			=D63+D59	=D63+D59
67
68		Hence NPV for to replace is	=D66	million
69		Since NPV for replace is higher, therefore it should be accepted.
70		3)
71		IRR is the rate at which NPV of the project will be zero.
72		IRR can also be found using IRR function in excel as follows:
73		Year	Renovate	Replace
74		0	-90000	-240000
75		1	30000	200000
76		2	30000	80000
77		3	30000	20000
78		4	30000	20000
79		5	30000	20000
80		IRR	=IRR(D74:D79)	=IRR(E74:E79)	=IRR(E74:E79)
81
82		Hence
83		IRR for Renovate is	=D80
84		IRR for replace is	=E80
85		Since IRR for projects are higher than MARR and IRR for replace is higher, therefore it should be accepted.
86		4)
87
88		Profitability index	=(Initial investment +NPV)/Initial Investment
89
90		Profitability index for renovate	=(Initial investment +NPV)/Initial Investment
91			=(-D4+D53)/(-D4)	=(-D4+D53)/(-D4)
92
93		Profitability index for replace	=(Initial investment +NPV)/Initial Investment
94			=(-E4+D68)/(-E4)	=(-E4+D68)/(-E4)
95
96		Profitability index for renovate	=D91
97		Profitability index for replace	=D94
98
99		Since profitability index for replace is higher than renovate, therefore it should be accepted.
100
101		5)
102
103		Conflicting cases occur due to different assumptions such as IRR assumes reinvestment rate at IRR
104		whereas NPV assumes reinvestment rate at MARR.
105