Done on excel

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

• MMMI has decided to set a project timeline of 4 years.
• The new machine will cost $1,100,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 machine, which has been fully depreciated, could be sold today for $253,165. The company has received a firm offer for the machine from Williamston Widgets, and will sell it only if they purchase the new machine.

• Additional Sales generated by the superior products made by the new machine would be $665,000 in Year 1. In Years 2 & 3 sales are projected to grow by 8.5% per year. However, in Year 4, sales are expected to decline by 5% as the market starts to become saturated.

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

• The firm is in the 21% marginal tax bracket and requires a minimum return on the replacement decision of 9%.

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

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

Part A: Base Case project decision (20 points)

• On the Assignment spreadsheet, on the part A tab, fill in the value drivers.
• Build the DCF Model, and calculate NPV and IRR
• Somewhere on this tab, state whether or not the company should purchase the new machine just based on the Base Case
• Note: your company has estimated that the Base Case has a 50% chance of being correct.

Sensitivity/Scenario Analysis (5 points for each part)

Copy the Part A tab to the tabs for Parts B through D. Analyze the following scenarios, making the relevant changes to each model. On each tab, state whether or not you would recommend purchasing the machine under that scenario:

Part B: Best Case evaluation

Assume Sales Growth in Years 2 & 3 are 9% instead of 8.5% (Year 4s projections remain a sales decline of 5%). Also assume costs are 60.5% of sales instead of 60.75%.

Note: Your company has determined that this scenario has a 5% chance of occurring.

Part C: Worst Case evaluation

Assume Sales Growth in Years 2 & 3 are 7% instead of 8.5% (Year 4s projections remain a sales decline of 5%). Also assume costs are 61.0% of sales instead of 60.75%.

Note: Your company has determined that this scenario has a 15% chance of occurring.

Part D: Alternative Case evaluation

Assume Sales Growth in Years 2 & 3 are 8% instead of 8.5% (Year 4s projections remain a sales decline of 5%). Also assume costs are 60.9% of sales instead of 60.75%.

Note: Your company has determined that this scenario has a 30% chance of occurring.

Conclusion (20 points):

Looking at all 4 of the scenarios above, write a minimum of 3-4 sentences about whether or not you would recommend that the company purchase the new machine, and why.

To get all of the points for this section, you will need to write something meaningful. Simply summarizing the data for each of the scenarios will earn ZERO points for this section. I expect you to clearly state your recommendation for whether the machine should be purchased (as in Buy or Dont Buy). Saying something like I would purchase the machine under the base scenario, but not if Part B if occurs is NOT making a recommendation! We dont know which scenario will occur; we only have probabilities assigned to each. Make a decision, and defend your choice. A well thought out answer will earn full points, even if your decision is not the same as the one I would make.

5 Extra Credit Points will be awarded for a well-thought-out and well-defended conclusion.