Question

solve it in spreadsheet

1 Leonard, a company that manufactures explosion- proof motors, is considering two alternatives for ex- panding its international export capacity. Option 1 requires equipment purchases of $900,000 now and $560,000 two years from now, with annual M&O costs of $79,000 in years 1 through 10. Option 2 involves subcontracting some of the production at costs of $280,000 per year beginning now through the end of year 10. Neither option will have a sig- nificant salvage value. Use a present worth analysis to determine which option is more attractive at the company's MARR of 20% per year. (Note: Check out the spreadsheet exercises for new options that Leonard has been offered recently.)

Answer 1

Option 1:

Formula	Year (n)	0	1	2	3	4	5	6	7	8	9	10
	Purchase (P)	-900000		-560000
	M&O cost		-79000	-79000	-79000	-79000	-79000	-79000	-79000	-79000	-79000	-79000
P + M&O	Total cost	-900000	-79000	-639000	-79000	-79000	-79000	-79000	-79000	-79000	-79000	-79000
1/(1+20%)^n	Discount factor @ 20%	1.000	0.833	0.694	0.579	0.482	0.402	0.335	0.279	0.233	0.194	0.162
TC*Discount factor	Present Value of TC (PV)	-900000	-65833.33	-443750.00	-45717.59	-38097.99	-31748.33	-26456.94	-22047.45	-18372.88	-15310.73	-12758.94
Sum of all PVs	PW of Option 1	-1620094.18

Option 2:

Formula	Year (n)	0	1	2	3	4	5	6	7	8	9	10
	Subcontracting cost (SC)	-280000	-280000	-280000	-280000	-280000	-280000	-280000	-280000	-280000	-280000	-280000
1/(1+20%)^n	Discount factor @ 20%	1.000	0.833	0.694	0.579	0.482	0.402	0.335	0.279	0.233	0.194	0.162
SC*Discount factor	Present Value of TC (PV)	-280000	-233333.33	-194444.44	-162037.04	-135030.86	-112525.72	-93771.43	-78142.86	-65119.05	-54265.88	-45221.56
Sum of all PVs	PW of Option 2	-1453892.18

As can be seen, Option 2 has lower cost than Option 1, so that should be chosen.