Medical Company is considering upgrading its manufacturing equipment. The Vice-President of Production has identified three possible actions Medical could take to accomplish the upgrade, though none is required. One option would upgrade their current equipment and the other two options would replace the existing equipment with purchases of new equipment. The review committee has asked you to review each of the options to identify relevant cost data and to prepare a schedule comparing the three options to see if any are worth implementing. Your analysis schedule is to include comparative relevant costs and a present value calculation.

Option One: Using this option Medical would upgrade their current equipment to make it more efficient. The equipment, which is three quarters depreciated but has reached the end of its projected useful life, would be upgraded at a cost of $142,000. The upgrades would extend the useful life of the equipment by 7 years. The cost of the upgrade would be depreciated over the new useful life and result is tax savings (a cash inflow) of $5,100 per year. At the end of its useful life, you estimate the equipment can be sold for $38,000.

Option Two: This option would require Medical to purchase new up-to-date equipment at a cost of $335,000. This new equipment would have an expected useful life of 8 years and result in an annual tax savings of $9,700. At the end of its useful life, it is estimated the new equipment can be sold for $58,000.

Option Three: Another equipment manufacturer has provided a proposal offering to supply a competing brand. Under this proposal, Medical would purchase the equipment for $280,000. This equipment has a 9 year useful life and an estimate $74,000 salvage value with an annual tax savings of $7,500.

Additional Information: In order to accommodate any of the equipment options, Medical would need to first upgrade the IT system and modify some structures in the manufacturing building. The work would be done by Medicals maintenance department at a cost of $17,500. If any of the 3 options are implemented, a one-time environmental study of the local river will have to be done at the end of the first year to ensure new chemicals used in the process are leaking out of the plant. This will cost $41,000 Based on your discussions with the Maintenance Department supervisor, it is estimated that annual maintenance costs would increase by $15,000, $9,800 and $10,000 for the three options respectively. The Production Department supervisor estimates that the upgrade to the new equipment would reduce annual materials cost by $18,800 for option 1, $21,600 for option 2 and $19,000 for option 3. The Sales Manager provided estimates of the effect on sales revenue based on the increased quality of products that can be produced with the upgrade. She estimates gross margin increases of $25,100, $45,100, and $35,000 per year for options 1, 2, and 3 respectively

Required: 1. Using the Excel file you download, prepare a schedule showing the annual cash inflows/outflows for each option and the present value of each option (use the PV function). Identify which of the options, if any, should be adopted based on the highest Net Present Value of the project. If two options have the same NPV, select them both. Assume Medical can invest its money at a 11% annual interest rate and expects all capital projects to return this rate at a minimum. Also, assume that all upfront costs are paid for immediately, but all other cash flows, including payments, occur at the end of the year. Use formulas wherever possible to eliminate user input errors (users should not have to rekey input data where they must be the same as other cells). Use appropriate formatting for cells. All $ amounts should show cents (i.e., .00), not rounded $ amounts. Underline only above subtotals, and double-underline the final total NPV for each project. 2. Create a second worksheet that references the first one so that no cells (labels or numbers), except a new interest rate of 7%, have to be input to show the analysis. Be sure your schedule is properly formatted and clearly labeled.

Show answers with details!!!

Note: Input a 0 (zero) if you leave a row blank 11% Option 1 Option 2 Option 3 Nm Excel Cost of Capital (%) # of Yrs Upfront Cash flows (Yr O) 1 2 3 4 5 6 7 8 Total upfront costs Annual Flows 10 11 12 13 14 15 16 17 18 19 20 Total annual cost (revenue) Salvage Value (End of project) 21 NPV of Cash flows 2 4 Upfront Cash flows (Yr 0) 5 6 7 8 Equipment update/change IT/Structure upgrade Environmental study Tax savings Incr. Maintenance Decr. Materials Incr. Margin (Leave Blank) 10 11 12 13 14 15 Total annual cost (revenue) 16 17 18 19 Salvage Value (End of project) 20 21 NPV of Cash flows Note: Input a 0 (zero) if you leave a row blank 11% Option 1 Option 2 Option 3 Nm Excel Cost of Capital (%) # of Yrs Upfront Cash flows (Yr O) 1 2 3 4 5 6 7 8 Total upfront costs Annual Flows 10 11 12 13 14 15 16 17 18 19 20 Total annual cost (revenue) Salvage Value (End of project) 21 NPV of Cash flows 2 4 Upfront Cash flows (Yr 0) 5 6 7 8 Equipment update/change IT/Structure upgrade Environmental study Tax savings Incr. Maintenance Decr. Materials Incr. Margin (Leave Blank) 10 11 12 13 14 15 Total annual cost (revenue) 16 17 18 19 Salvage Value (End of project) 20 21 NPV of Cash flows