DO IN EXCEL PLEASE AND SHOW WORK

Mid-Michigan Manufacturing Inc. (MMMI) wishes to determine whether it would be advisable to replace an existing production system with a new automated one. They have hired you as a consultant to determine whether the new system should be purchased. The data you will need is as follows:

• MMMI has decided to set a project timeline of 4 years.
• The new system will cost $2,600,000. It will be depreciated (straight line) over a five-year period (its estimated useful life), assuming a salvage value of $100,000.
• The old system, which has been fully depreciated, could be sold today for $379,747. The company has received a firm offer for the system from Williamston Widgets, and MMMI will sell it only if they purchase the new system.

• Additional sales generated by the superior products made by the new system would be $2,400,000 in Year 1. In Years 2 and 3 sales are projected to grow by 4.5% per year. However, in Year 4 sales are expected to decline by 10% per year as the market starts to become saturated.

• Total expenses have been estimated at 72.5% of Sales.

• The firms tax rate 21%.

• MMMI requires a minimum return on the replacement decision of 7.5%.

• A representative from Stockbridge Sprockets has told MMMI that they will buy the system from them at the end of the project (the end of Year 4) for $446,835. MMMI has decided to include this in the terminal value of the project.

• The project will require $300,000 in additional Net Working Capital, 45% of which will be recovered at the end of the project.

A) Complete the DCF Model using the above data, and calculate NPV and IRR. Note: A consultant has told MMMI that they have estimated there is a 45% chance that the base case will occur.