Question

5.6 Toro Company is expanding its U.S.-based plastic molding plant as it continues to transfer work from Juarez, Mexico contractors. The plant bought a $1.1 million precision injection molding machine to make plastic parts for Toro lawn mowers, trim- mers, and snow blowers. The plant also spent $275,000 for three smaller plastic injection mold- ing machines to make plastic parts for a new line of sprinkler systems. The plant expects to hire 13 people, including some engineers for the ex- pansion. If the average loaded cost (i.e., including benefits) of each employee is $100,000 per year, determine the annual worth of the new systems over a five-year planning period at an interest rate of 10% per year. Assume a 25% salvage value for the new equipment.

I need this in excel spreadsheet form with any formulas used in excel clearly stated. There is no depreciation schedule.

Answer 1

Compute the annual worth of new systems, using MS-excel as shown below:

13 13 B Annual Worth Particulars Years Total 0 6 Purchase cost 1100000 7 Additional cost 275000 8 Total machine cost =SUM(C6:C7) 9 Number of employees 13 13 13 Cost of each employee 100000 100000 100000 100000 100000 Annual cost =D9*D10 =E9*E10 =F9*F10 =G9*G10 =H9*H10 Salvage value =-C8*25% 13 Total cost =C8 =SUM(D11:012) =SUM(E11:E12) =SUM(F11:F12) =SUM(G11:G12) =SUM(H11:H12) 14 PVIF =POWER(1/1.1,C5) =POWER(1/1.1,D5) =POWER(1/1.1,E5) =POWER(1/1.1,F5) =POWER(1/1.1,65) =POWER(1/1.1,H5) =SUM(D14:H14) 15 Present value =C13*C14 =D13*D14 =E13*E14 =F13*F14 =G13*G14 =H13*H14 =SUM(C15:H15) 16 Annual worth =115/114 10 so | 17

The result of the above excel table is as follows:

I B C D E F G H Annual Worth Particulars Years Total 10 3 4 5 6 Purchase cost $1,100,000 7 Additional cost $ 275,000 8 Total machine costſ $ 1,375,000 9 Number of employees 13 13 13 13 13 10 Cost of each employee $100,000 $100,000 $100,000 $100,000 $100,000 Annual cost $ 1,300,000 $1,300,000 $1,300,000 $1,300,000 $1,300,000 Salvage value ($ 343,750 13 Total cost $ 1,375,000 $1,300,000 $1,300,000 $1,300,000 $1,300,000 $956,250 14 PVIF 1 0.909090909 0.826446281 0.751314801 0.683013455 0.6209213233.790786769 15 Present value $1,375,000.00 $ 1,181,818.18 $ 1,074,380.17 $ 976,709.24 $ 887,917.49 $ 593,756.02 $6,089,581.10 16 Annual worth $ 1,606,416.15

Hence, the annual worth of new systems is $1,606,416.15.