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 your firm (your group) 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 5 years.

The new system will cost $1,900,000. It will be depreciated (straight line) over a six-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 $253,165. 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 $1,500,000 in Year 1. In Years 2 and 3 sales are projected to grow by 5.5% per year. However, in Years 4 and 5, sales are expected to decline by 25% per year as the market starts to become saturated.

Total expenses have been estimated at 67.4% of Sales.

The firms tax rate 21%.

MMMI requires a minimum return on the replacement decision of 9.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 5) for $200,000. MMMI has decided to include this in the terminal value of the project.

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

Part 1: Base Case (65 points): Complete the DCF Model using the above data. Calculate NPV and IRR, and somewhere on the Base Case sheet state whether you would accept or reject the project just on the base case data alone. Note: MMMI has estimated that there is a 55% chance that the base case will occur. Before continuing to Parts 2-5, copy the entire Base Case sheet, and paste to the Part 2, Part 3, and Part 4 tabs.

Here is what I have so far and it has to fit into this template model. I am mainly confused with if we include the $253,165 from the sale of the old system as a revenue in year 0. Other than that, I think everything is right but would like to be checked.

A B D E F G 3 Sales Growth Years 2 & 3 4 Sales Growth Year 4 5 Sales Growth Year 5 6 Expenses as a % of sales 7 Cost of New System 8 Salvage Value of New System 9 Old System Resale 10 New System Resale in Year 5 11 Tax Rate 12 Required Rate of Return 13 Project Time Period 14 5.5% -25.0% -25.0% 67.40% $1,900,000 $100,000 $253,165 $200,000 21.00% 9.50% 5 15 16 1 Years 17 0 2 3 4 5 18 Capital Spending $ (1,900,000) $158,000! 19 20 OCF: 21 Revenues $ 253,165 $1,500,000 $1,582,500 $1,669,538 $1,252,153 $ 939,115 22 Expenses $ $1,011,000 $1,066,605 $1,125,268 $ 843,951 $ 632,963 23 Depreciation $ $ 300,000 $ 300,000 $ 300,000 $ 300,000 $ 300,000 24 EBIT $ 253,165 $ 189,000 $ 215,895 $ 244,269 $ 108,202 $ 6,151 25 Taxes $ 53,165 $ 39,690 $ 45,338 $ 51,297 $ 22,722 $ 1,292 26 Net Income $ 200,000 $ 149,310 $ 170,557 $ 192,973 $ 85,480 $ 4,860 27 Depreciation $ $ 300,000 $ 300,000 $ 300,000 $ 300,000 $300,000 28 OPERATING CASH FLOW $ 200,000 $ 449,310 $ 470,557 $ 492,973 $ 385,480 $ 304,860 29 30 Net Working Capital $(150,000.00) $ 100,500 31 32 33 Total Cash Flow -$1,850,000 $ 449,310 $ 470,557 $ 492,973 $ 385,480 $ 563,360 34 35 NPV $ (45,755.33) 36 IRR 8.55%