Question

5.28 Parker Hannifin of Cleveland, Ohio manufactures CNG fuel dispensers. It needs replacement equip- ment to streamline one of its production lines for a new contract, but plans to sell the equipment at or before its expected life is reached at an estimated market value for used equipment. Select between the two options using the corporate MARR of 15% per year and a future worth analysis for the ex- pected use period. Alsot write the FV spreadsheet functions that will display the correct future worth values. 5. Option First cost, $ AOC, $ per year Expected market value, $ Expected use, years --62,000 -15,000 8,000 -77,000 -21,000 10,000 rules 5 32

can you solve it by using Excel

Answer 1

Analysis will be carried for 6 yrs (LCM of 3 & 6)

Using NPV and FV function in excel

	option D				option E
Year	Investment	Maint Cost	Salvage value	Net cash Flow	Investment	Maint Cost	Salvage value	Net cash Flow
0	-62000			-62000	-77000			-77000
1		-15000		-15000		-21000		-21000
2		-15000		-15000		-21000		-21000
3	-62000	-15000	8000	-69000		-21000		-21000
4		-15000		-15000		-21000		-21000
5		-15000		-15000		-21000		-21000
6		-15000	8000	-7000		-21000	10000	-11000
NPW				-150814.5				-152150.9
Future Worth				-348843.1				-351934.2

As the future cost of option D is less, it should be selected

Showing formula in excel

	option D				option E
Year	Investment	Maint Cost	Salvage value	Net cash Flow	Investment	Maint Cost	Salvage value	Net cash Flow
0	-62000			=P23+Q23+R23	-77000			=T23+U23+V23
1		-15000		=P24+Q24+R24		-21000		=T24+U24+V24
2		-15000		=P25+Q25+R25		-21000		=T25+U25+V25
3	-62000	-15000	8000	=P26+Q26+R26		-21000		=T26+U26+V26
4		-15000		=P27+Q27+R27		-21000		=T27+U27+V27
5		-15000		=P28+Q28+R28		-21000		=T28+U28+V28
6		-15000	8000	=P29+Q29+R29		-21000	10000	=T29+U29+V29
NPW				=NPV(15%,S24:S29)+S23				=NPV(15%,W24:W29)+W23
Future Worth				=FV(15%,6,,-S30)				=FV(15%,6,,-W30)