Question

Two insulation thickness alternatives have been proposed for a process steam line subject to severe weather conditions. One alternative must be selected. Estimated savings in heat loss and installation cost are given below:

THICKNESS	INSTALLATION COST	ANNUAL SAVINGS	MAINTENANCE ONCE IN EACH 2 YEARS
2cm	$125000	$62000	$4800
5cm	$408000	$125000	$0

C.) Useful lives of both alternatives are 8 years. Find the best alternative using a.) discounted payback period b.) conventional benefit cost ratio method c.) modified benefit cost ratio method. When MARR= 9% per year

IN EXCEL PLEASE!!

Answer 1

a) In Discounted Payback method, the PV of each year cashflow is calculated using Excel as per the following Table

Year	2cm	PV of 2cm	Cumulative Payback	5cm	PV of 5cm	Cumulative Payback
A	B	C=B/1.09^A	D(n)=D(n-1)+C(n)	E	F=E/1.09^A	G(n)=G(n-1)+F(n)
0	-125000.00	-125000.00	-125000.00	-408000.00	-408000.00	-408000.00
1	62000.00	56880.73	-68119.27	125000.00	114678.90	-293321.10
2	57200.00	48144.10	-19975.17	125000.00	105210.00	-188111.10
3	62000.00	47875.38	27900.21	125000.00	96522.94	-91588.17
4	57200.00	40521.92	68422.13	125000.00	88553.15	-3035.02
5	62000.00	40295.75	108717.87	125000.00	81241.42	78206.41
6	57200.00	34106.49	142824.36	125000.00	74533.42	152739.82
7	62000.00	33916.12	176740.49	125000.00	68379.28	221119.10
8	57200.00	28706.75	205447.24	125000.00	62733.28	283852.39

Here n = Row Number.

For the 2cm thickness, the cumulative payback becomes positive from negative in year 3.

So Discounted Payback Period = 2+ 19975.17/47875.38 = 2.417years

For the 5cm Thickness, the cumulative Payback becomes positive from negative in year 5.

So discounted Payback Period = 4 + 3035.02/81241.42 = 4.037 years

Since the discounted payback period of the 2cm thickness insulation is lower, it makes sense to go ahead with that.

b) In B/C ratio, the tables can be made as follows

	2cm	5cm
Year	Benefits	Costs	PV of Benefits	PV of Costs	Benefits	Costs	PV of Benefits	PV of Costs
A	B	C	D=B/1.09^A	E=C/1.09^A	F	G	H=F/1.09^A	I=G/1.09^A
0		125000.00	0.00	125000.00	0.00	408000.00	0.00	408000.00
1	62000.00		56880.73	0.00	125000.00		114678.90	0.00
2	62000.00	4800.00	52184.16	4040.06	125000.00		105210.00	0.00
3	62000.00		47875.38	0.00	125000.00		96522.94	0.00
4	62000.00	4800.00	43922.36	3400.44	125000.00		88553.15	0.00
5	62000.00		40295.75	0.00	125000.00		81241.42	0.00
6	62000.00	4800.00	36968.57	2862.08	125000.00		74533.42	0.00
7	62000.00		33916.12	0.00	125000.00		68379.28	0.00
8	62000.00	4800.00	31115.71	2408.96	125000.00		62733.28	0.00
		Total	343158.79	137711.55			691852.39	408000.00

B/C Ratio of 2cm Thickness = 343158.79/137711.55 = 2.49

B/C Ratio of 5cm Thickness = 691852.39/408000 = 1.70

Since the B/C Ratio is higher for 2cm thickness insulation, that should be used.

c) In case of Modified B/C Ratio, the tables are again calculated as follows

	2cm	5cm
Year	Benefits	Recurring Costs	Installation Cost	PV of Benefits	PV of Costs	PV of Installation Costs	Benefits	Recurring Costs	Installation Cost	PV of Benefits	PV of Costs	PV of Installation Costs
A	B	C	D	E=B/1.09^A	F=C/1.09^A	G=D/1.09^A	H	I	J	K=H/1.09^A	L=I/1.09^A	M=J/1.09^A
0			125000.00	0.00	0.00	125000.00	0.00		408000.00	0.00	0.00	408000.00
1	62000.00			56880.73	0.00	0.00	125000.00			114678.90	0.00	0.00
2	62000.00	4800.00		52184.16	4040.06	0.00	125000.00			105210.00	0.00	0.00
3	62000.00			47875.38	0.00	0.00	125000.00			96522.94	0.00	0.00
4	62000.00	4800.00		43922.36	3400.44	0.00	125000.00			88553.15	0.00	0.00
5	62000.00			40295.75	0.00	0.00	125000.00			81241.42	0.00	0.00
6	62000.00	4800.00		36968.57	2862.08	0.00	125000.00			74533.42	0.00	0.00
7	62000.00			33916.12	0.00	0.00	125000.00			68379.28	0.00	0.00
8	62000.00	4800.00		31115.71	2408.96	0.00	125000.00			62733.28	0.00	0.00
		Total		343158.79	12711.55	125000.00				691852.39	0.00	408000.00

For 2cm Thickness, Modified B/C Ratio = (343158 - 12711.55) / 125000 = 2.64

For 5cm thickness, Modified B/C Ratio = (691852.39-0)/408000 = 1.70

Since the Modified B/C Ratio of 2cm thickness is higher than 5cm thickness, so it is advisable to go ahead with 2cm insulation.

The screenshots of the tables are given below

For discounted Payback Method

For B/C Ratio Method

For Modified B/C Ratio Method

If you found this helpful, please rate it so that I can have higher earnings at no extra cost to you. This will motivate me to write more.