PLEASE USE EXCEL FORMULA AND SHOW HOW YOU DID IT FOR EACH EQUATION

Widget Company is considering upgrading its manufacturing equipment. The Vice-President of Production has identified three possible actions Widget 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 Widget 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 $80,000. The upgrades would extend the useful life of the equipment by 6 years. The cost of the upgrade would be depreciated over the new useful life and result is tax savings (a cash inflow) of $1,000 per year. At the end of its useful life, you estimate the equipment can be sold for $43,000. Option Two: This option would require Widget to purchase new up-to-date equipment at a cost of $320,000. This new equipment would have an expected useful life of 6 years and result in an annual tax savings of $4,700. At the end of its useful life, it is estimated the new equipment can be sold for $135,600. Option Three: Another equipment manufacturer has provided a proposal offering to supply a competing brand. Under this proposal, Widget would purchase the equipment for $257,000. This equipment has an 8 year useful life and an estimate $102,000 salvage value with an annual tax savings of $3,500. Additional Information: In order to accommodate any of the equipment options, Widget would need to first upgrade the electrical supply capacity and the floor supports in the manufacturing building. The work would be accomplished using Widget's maintenance department at a cost of $11,420. Based on your discussions with the Maintenance Department supervisor, it is estimated that annual maintenance costs would increase by $19,800, $16,000 and $17,000 for the three options respectively. The Production Department supervisor estimates that the upgrade to the new equipment would reduce annual materials cost by $29,400 for option 1, $17,900 for option 2 and $19,000 for option 3. The Sales Manager provided estimates of the effect on sales revenue based on the lower prices Widget can charge due to the labor cost savings. She estimates gross margin increases of $9,300, $56,600, and $39,700 per year for options 1, 2, and 3 respectively Required: 1. Prepare a schedule showing the annual cash inflows/outflows for each option (19 pts) and the present value of each option (use PV and/or NPV function) (28 pts) Your layout should distinguish between cash flows based on their timing. Use highlighting for input cells (5 pts) Use appropriate titles/labels for columns and rows. (18 pts) 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). (2 points) Assume Widget can invest its money at a 10% 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. A. Indicate on your worksheet the option(s) that have a positive value for the company based on your initial screening; (3 pts) B. Recommend a course of action (just indicate which option, if any, should be chosen) (3 pts). 2. Create a second worksheet that references the first one so that no numbers except a new interest rate of 3.5% have to be input to show the analysis. (35 pts) Does your recommendation change if the annual interest rate changes to 3.5%? (3 pts) Widget Company is considering upgrading its manufacturing equipment. The Vice-President of Production has identified three possible actions Widget 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 Widget 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 $80,000. The upgrades would extend the useful life of the equipment by 6 years. The cost of the upgrade would be depreciated over the new useful life and result is tax savings (a cash inflow) of $1,000 per year. At the end of its useful life, you estimate the equipment can be sold for $43,000. Option Two: This option would require Widget to purchase new up-to-date equipment at a cost of $320,000. This new equipment would have an expected useful life of 6 years and result in an annual tax savings of $4,700. At the end of its useful life, it is estimated the new equipment can be sold for $135,600. Option Three: Another equipment manufacturer has provided a proposal offering to supply a competing brand. Under this proposal, Widget would purchase the equipment for $257,000. This equipment has an 8 year useful life and an estimate $102,000 salvage value with an annual tax savings of $3,500. Additional Information: In order to accommodate any of the equipment options, Widget would need to first upgrade the electrical supply capacity and the floor supports in the manufacturing building. The work would be accomplished using Widget's maintenance department at a cost of $11,420. Based on your discussions with the Maintenance Department supervisor, it is estimated that annual maintenance costs would increase by $19,800, $16,000 and $17,000 for the three options respectively. The Production Department supervisor estimates that the upgrade to the new equipment would reduce annual materials cost by $29,400 for option 1, $17,900 for option 2 and $19,000 for option 3. The Sales Manager provided estimates of the effect on sales revenue based on the lower prices Widget can charge due to the labor cost savings. She estimates gross margin increases of $9,300, $56,600, and $39,700 per year for options 1, 2, and 3 respectively Required: 1. Prepare a schedule showing the annual cash inflows/outflows for each option (19 pts) and the present value of each option (use PV and/or NPV function) (28 pts) Your layout should distinguish between cash flows based on their timing. Use highlighting for input cells (5 pts) Use appropriate titles/labels for columns and rows. (18 pts) 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). (2 points) Assume Widget can invest its money at a 10% 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. A. Indicate on your worksheet the option(s) that have a positive value for the company based on your initial screening; (3 pts) B. Recommend a course of action (just indicate which option, if any, should be chosen) (3 pts). 2. Create a second worksheet that references the first one so that no numbers except a new interest rate of 3.5% have to be input to show the analysis. (35 pts) Does your recommendation change if the annual interest rate changes to 3.5%? (3 pts)