Question

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.)

Can you solve it by using excel just

Answer 1

Beginning of year cash for option 2 will be treated as end of year cash for the previous year

using Excel

Year	Option 1			Option 2
	investment	O&M	Net Cash flow	Net Cash flow
0	-900000		-900000	-280000
1		-79000	-79000	-280000
2	-560000	-79000	-639000	-280000
3		-79000	-79000	-280000
4		-79000	-79000	-280000
5		-79000	-79000	-280000
6		-79000	-79000	-280000
7		-79000	-79000	-280000
8		-79000	-79000	-280000
9		-79000	-79000	-280000
10		-79000	-79000
Net Present Value			-1620094	-1408671

As present cost of option 2 is less, it should be selected

Showing formula for excel

Year	Option 1			Option 2
	investment	O&M	Net Cash flow	Net Cash flow
0	-900000		=I3+J3	-280000
1		-79000	=I4+J4	-280000
2	-560000	-79000	=I5+J5	-280000
3		-79000	=I6+J6	-280000
4		-79000	=I7+J7	-280000
5		-79000	=I8+J8	-280000
6		-79000	=I9+J9	-280000
7		-79000	=I10+J10	-280000
8		-79000	=I11+J11	-280000
9		-79000	=I12+J12	-280000
10		-79000	=I13+J13
Net Present Value			=NPV(20%,K4:K13)+K3	=NPV(20%,L4:L12)+L3