please use excel to answer!
Miami Metal Company (MMC), a small manufacturer of fabricated metal parts, must decide whether to enter the competition to become the supplier of transmission housings for Bay Electric, a company that produces the housings in its own in-house manufacturing facility but has almost reached its maximum production capacity. Therefore, Bay is looking for an outside supplier. To compete, MMC must design a new fixture for the production process and purchase a new forge. The available details for this purchase are on the following slide. Financial Facts : Known with Great Confidence Required investment = $120,000 Fixed cost = $18,000/Yr. Project Life = 4 years Income tax rate = 40% MARR = 12% Unknown but Predictable (Most Likely Values) Q: Number of units = 2,000 units P: Unit Price = $50 per unit VC: Unit variable cost = $20 per unit S: Salvage value = $36,000 (a) Determine the annual net cash flows from the project (30 marks) (b) Based on the most likely estimates, should you accept or reject the project? (10 marks) (c) Conduct a sensitivity analysis for each of the four key uncertain variables Q, P, VC and S in the range of -20% to 20% around the most likely value (the incremental is 2%). Present your result in both tables and scattered graphs. (40 marks) (d) Conduct a break-even analysis on Q. Use the goal-seeking function to find the break-even value of Q. (20marks) Miami Metal Company (MMC), a small manufacturer of fabricated metal parts, must decide whether to enter the competition to become the supplier of transmission housings for Bay Electric, a company that produces the housings in its own in-house manufacturing facility but has almost reached its maximum production capacity. Therefore, Bay is looking for an outside supplier. To compete, MMC must design a new fixture for the production process and purchase a new forge. The available details for this purchase are on the following slide. Financial Facts : Known with Great Confidence Required investment = $120,000 Fixed cost = $18,000/Yr. Project Life = 4 years Income tax rate = 40% MARR = 12% Unknown but Predictable (Most Likely Values) Q: Number of units = 2,000 units P: Unit Price = $50 per unit VC: Unit variable cost = $20 per unit S: Salvage value = $36,000 (a) Determine the annual net cash flows from the project (30 marks) (b) Based on the most likely estimates, should you accept or reject the project? (10 marks) (c) Conduct a sensitivity analysis for each of the four key uncertain variables Q, P, VC and S in the range of -20% to 20% around the most likely value (the incremental is 2%). Present your result in both tables and scattered graphs. (40 marks) (d) Conduct a break-even analysis on Q. Use the goal-seeking function to find the break-even value of Q. (20marks)
