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 book value of $100,000 at the end of its estimated useful life, i.e. at the end of Year 5.
• 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

• 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

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.