Pharma Company is considering upgrading its manufacturing equipment. The Vice-President of Production has identified three possible actions Pharma 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 Pharma 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 $141,022. 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 $36,000. Option Two: This option would require Pharma to purchase new up-to-date equipment at a cost of $300,000. This new equipment would have an expected useful life of 6 years and result in an annual tax savings of $14,500. At the end of its useful life, it is estimated the new equipment can be sold for $50,000. Option Three: Another equipment manufacturer has provided a proposal offering to supply a competing brand. Under this proposal, Pharma would purchase the equipment for $300,000. This equipment has a 10 year useful life and an estimate $71,022 salvage value with an annual tax savings of $7,500. Additional Information: In order to accommodate any of the equipment options, Pharma would need to first upgrade the IT system and modify some structures in the manufacturing building. The work would be done by Pharmas maintenance department at a cost of $17,000. If any of the 3 options are implemented, labor costs will increase by $6,000 per year due to increased training requirements. Based on your discussions with the Maintenance Department supervisor, it is estimated that annual maintenance costs would increase by $15,200, $10,220 and $9,900 for the three options respectively. The Production Department supervisor estimates that the upgrade to the new equipment would reduce annual materials cost by $19,700 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 $26,100, $45,100, and $35,000 per year for options 1, 2, and 3 respectively

Required: 1. Using the Excel file you download from the ExPrep web site, prepare a schedule showing the annual cash inflows/outflows for each option and the present value of each option (use the PV function). Enter which of the options, if any, is best based on Net Present Value of the project. Assume Pharma can invest its money at a 9% 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, but not Salvage Value 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 11%, and your selection of the best option (1,2 or 3) have to be input to show the analysis. Be sure your schedule is properly formatted and clearly labeled.

Please help and provide me with formula.

Excel Asst 2 \begin{tabular}{|l} \hline Note: Input O (zero) \\ Option 1 \\ \hline \end{tabular} Total upfront costs Annual Flows Total annual cost (revenue) Salvage Value (End of project) NPV of Cash flows Best Option (enter 1,2 , or 3 ) \begin{tabular}{l|l|l|l} Cost of Capital - & Cost of Capital - 11 & Lists & \end{tabular} Excel Asst 2 11 Note: Input O (zero) value if row blank Cost of Capital (\%) # of Yrs Upfront Cash flows (Yr 0) Total upfront costs Annual Flows Total annual cost (revenue) Salvage Value (End of project) NPV of Cash flows Best Option (enter 1,2, or 3) \begin{tabular}{l|l|l|l|l} \hline & Cost of Capital - 9 & Cost of Capital - 11 & Lists \end{tabular} Excel Asst 2 \begin{tabular}{|l} \hline Note: Input O (zero) \\ Option 1 \\ \hline \end{tabular} Total upfront costs Annual Flows Total annual cost (revenue) Salvage Value (End of project) NPV of Cash flows Best Option (enter 1,2 , or 3 ) \begin{tabular}{l|l|l|l} Cost of Capital - & Cost of Capital - 11 & Lists & \end{tabular} Excel Asst 2 11 Note: Input O (zero) value if row blank Cost of Capital (\%) # of Yrs Upfront Cash flows (Yr 0) Total upfront costs Annual Flows Total annual cost (revenue) Salvage Value (End of project) NPV of Cash flows Best Option (enter 1,2, or 3) \begin{tabular}{l|l|l|l|l} \hline & Cost of Capital - 9 & Cost of Capital - 11 & Lists \end{tabular}