Question

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.

Answer 1

Depreciatiom = Value Cort dalvage Number of years 1lopeto-loo0o0 200000 Imitial cash outflos 1100000 Cost of mmachime 25 3165 sale value af old mach looo00 946835 + Net working Captal

dubsequent catlour excel eud n 3 alei 730373 775656 66 5000 718200 te the a - Expensus 447050 406306 403988 Roooe0 2o0000 200000 ep 200000 89223 l04445 8 1894 EBT 61012 shes to 1873 7 - Tax1 21933 12 8 13 17198 a new d be p 70486 8.25 12 EAT 64696 48199 200c00 200000 200000 200000 +Dep ight lin today hase new r pect 54000 + warking cafital Pueleased ooe00 +Sale af equitment 248199 2 8 2512 4486 264616 0.108 0.772 CFAT 0-842 0.917 Pv.Fa 300536 218019 222574 Onecert value of cash unflews 969108 Prusent value ef Cayh eutklews 9468 35 NPV 969 los-946335: $22973 fv.CFAT 27598

IRR Pvaf catuntous at P.v of lash untles e 10 PUFCIO P.V CFAT 2 2 5613 0.909 148199 2 18639 0.8 26 26469 0.751 e12166 28992 4 946342 2 82512 0.683 424486 NPV 946342-946835=493 Interpalatiom % (H-L) Lawer Radte + (NPV at L.R) 01 NPVat L.R-NPVat H.R 9..+ (22213) x I 22273-(-493 9.977
The IRR is just some points above the cut off rate. If the Base case has 50% chance of being correct, then the company might not take the proposal as it might seem risky for this IRR.