Question

Q.#1 Using Excel format:

A company is studying two different production methods to produce its product. Both production methods have the same life and the same maintenance and repair record.

• Production Method 1: costs $120,000 and uses 20 gallons per hours of operation.
• Production Method 2: costs $175,000 and uses 18 gallons per hours of operation at the same level of production.

Both production methods have four-year lives before any major overhaul is required and the 10% of initial its cost as a salvage value. The fuel currently costs $2.40

The fuel consumption is expected to increase 1gallon/hr. every year because of degrading engine efficiency and fuel costs is expected to increase at the rate of 5% per year.

Required:

Assume 5,000 hours of operation per year and a MARR of 12%. Use the AE criterion.

1. Summarize the Net Cash Flow of Production Method 1 in table format.
2. Summarize the Net Cash Flow of Production Method 2 in table format

Answer 1

Using NPV & PMT function in EXCEL

Cash Flow associated with Method 1

		Method 1
Year	Investment	Annual Fuel consumption	Salvage	Net Cash Flow
0	-120000.00			-120000.00
1		-252000.00		-252000.00
2		-277830.00		-277830.00
3		-305613.00		-305613.00
4		-335479.73	12000.00	-323479.73
			Annual worth	-3,25,807.40

Cash Flow associated with Method 2

		Method 2
Year	Investment	Annual Fuel consumption	Salvage	Net Cash Flow
0	-175000.00			-175000.00
1		-226800.00		-226800.00
2		-251370.00		-251370.00
3		-277830.00		-277830.00
4		-306307.58	17500.00	-288807.58
			Annual worth	-3,15,797.38

As Annual cost of method 2 is lower, it should be selected

Showing formula in excel

		Method 1
Year	Investment	Annual Fuel consumption	Salvage	Net Cash Flow
0	-120000			=B28+C28+D28
1		=-20*5000*2.4*1.05^A29		=B29+C29+D29
2		=-21*5000*2.4*1.05^A30		=B30+C30+D30
3		=-22*5000*2.4*1.05^A31		=B31+C31+D31
4		=-23*5000*2.4*1.05^A32	=0.1*120000	=B32+C32+D32
			Annual worth	=PMT(12%,4,-(NPV(12%,E29:E32)+E28))
		Method 2
Year	Investment	Annual Fuel consumption	Salvage	Net Cash Flow
0	-175000			=B37+C37+D37
1		=-18*5000*2.4*1.05^A38		=B38+C38+D38
2		=-19*5000*2.4*1.05^A39		=B39+C39+D39
3		=-20*5000*2.4*1.05^A40		=B40+C40+D40
4		=-21*5000*2.4*1.05^A41	=0.1*175000	=B41+C41+D41
			Annual worth	=PMT(12%,4,-(NPV(12%,E38:E41)+E37))