Question

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

please can you solve with excel and show formula

Answer 1

MARR = 20% = 0.2

Option 1:

Equipment purchase now = $900,000

Equipment purchase 2 years from now = $560,000

Annual cost = $79,000 from year 1 to 10

Present value of any payment is calculated as: [Payment / (1 + MARR)^^Year]

Present value of equipment purchase after 2 years = [560,000 / (1 + 0.2)^²] = 388,888.89

Present value of annual cost is depicted in table below:

Year	Annual Cost	Present value
1	79,000.00	65,833.33
2	79,000.00	54,861.11
3	79,000.00	45,717.59
4	79,000.00	38,097.99
5	79,000.00	31,748.33
6	79,000.00	26,456.94
7	79,000.00	22,047.45
8	79,000.00	18,372.88
9	79,000.00	15,310.73
10	79,000.00	12,758.94
		331,205.29

Total cost of option 1 = Equipment cost now + Equipment cost after 2 years + Present value of annual cost = $900,000 + $388,888.89 + $331,205.29 = 1,620,094.18

Option 2:

Cost per year = $280,000

Present value of annual cost is depicted below:

Year	Annual Cost	Present value
1	280,000.00	233,333.33
2	280,000.00	194,444.44
3	280,000.00	162,037.04
4	280,000.00	135,030.86
5	280,000.00	112,525.72
6	280,000.00	93,771.43
7	280,000.00	78,142.86
8	280,000.00	65,119.05
9	280,000.00	54,265.88
10	280,000.00	45,221.56
		1,173,892.18

As present value of option 2 is less, it is more attractive to company.