Question

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:

*DONE ON EXCEL*

• 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

• 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.

art B: Best Case evaluation

Assume Sales Growth in Years 2 & 3 are 9% instead of 8.5% (Year 4’s 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 4’s 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 4’s 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:

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.

Answer 1

Conclusion

• The probability weighted NPV of the project is less than $0 which means it is not worth going through with the project given the assumptions and the probability of the scenarios
• The base case probability is 50%. The NPV under this scenario is positive
• However, the NPV under the first scenario is quite low compared to the initial outlay required, so the project is not lucrative under the base case
• While base case 2 is more favorable, its probability is very low.
• The probability of case 3 & 4, which yield negative NPV is high at 45%. Refer to attachment for details

Excel File Edit View Insert Format Tools Data Window Help 0972% O Sat 3:02 PM Q E Yalev NPV.xlsx Q- Search in Sheet H A Home Edit e Tables 100% Review Layout 42 · 45 Y- Fx Charts SmartArt Formulas D ata Font Alignment 12 A+ A- = = = abc 1. do A E = = Format Cells Normal Number Number - % , Calibri (Body) B I U % Wrap Text Merge Clear Conditional • Formatting Bad BA Delete Insert Format: Themes fx =(H13/(1.0942)) A F M N O P 1 Amounts in $ 2 Cost of machine 3 Net working capital 4 After-tax proceeds on old machine 5 Initial outlay 1100000 100000 (200000) 253,165*(1-0.21) since book value is 0 1000000 10 11 8 Depreciation = (depreciable amount/life of machine) 9 (1100000-100000)/5 200000 Cash flows Additional Sales Total expens Depreciatior EBT Tax EAT Add back depreciation Present value of cash flows 12 Year 1 665000 403987.5 200000 61012.5 12812.63 48199.88 248199.88 227706.31 13 Year 2 721525 438326.438 200000 83198.5625 17471.70 65726.86 265726.86 223656.991 14 Year 3 782854.625 475584.185 200000 107270.44 22526.79 84743.65 284743.65 219874.34 15 Year 4 743711.8938 451804.975 200000 91906.918319300.45 72606.47 272606.47 193121.29 16 Terminal value at year 4 196000 138851.34 17 Book value of asset after 4 years = (1100000-04.200000) Total 1003210.27 300000 NPV 3210.27 NPV = present value of cash flows - initial outlay 19 Since salvage value is lower than book value, the company will get a tax refund NPV= $3,210.27 20 Tax refund = (300000-100000)*0.21 42000 21 Terminal value = salvage value + tax refund + recapture of working capital 9.13 22 100000+42000+ 0.54100000) use financial calculator to compute IRR IRR rities ind 23 24 Net present value the sum of : (net cash flow for the year)/(1 + discount rate or WACC)^n over the five year period minus the initial outlay 25 n is the year of the cash flow Buy the machine under this scenario Case B Case C Case D + K Normal View Base Case Ready Sum-223656.99

Excel File Edit View Insert Format Tools Data Window Help 0972% O Sat 3:02 PM Q E Yalev NPV.xlsx Q- Search in Sheet Format Cells H. M Gon: 3 · 45 · Y= fa 4 100% A Home Layout Tables Charts SmartArt Formulas D ata Review Edit Font Alignment Calibri (Body) 12 A- A = = = abc Wrap Text Clear B I U 1. do A E = = Merge % fx =(H14/(1.0943)) Number Number - % , Normal Conditional • Formatting Bad BA Delete Insert Format: Themes 114 A C F M N O P 1 Amounts in $ 2 Cost of machine 3 Net working capital 4 After-tax proceeds on old machine 5 Initial outlay 1100000 100000 (200000) 253,165*(1-0.21) since book value is 0 1000000 10 8 Depreciation = (depreciable amount/life of machine) 9 (1100000-100000)/5 200000 Cash flows 11 Additional Sales Total expens Depreciatior EBT Tax EAT Add back depreciation Present value of cash flows 12 Year 1 665000 402325 200000 62675 13161.75 49513.25 249513.25 228911.24 13 Year 2 724850 438534.25 200000 86315.75 18126.31 68189.44 268189.44 225729.69 14 Year 3 790086.5 478002.333 200000 112084.168 23537.68 88546.49 288546.49 222810.83 15 Year 4 750582.175 454102.216 2 00000 96479.9591 20260.79 76219.17 276219.17 195680.62 16 Terminal value at year 4 196000 138851.34 17 Book value of asset after 4 years = (1100000-04.200000) Total 1011983.72 300000 NPV 11983.72 NPV = present value of cash flows - initial outlay 19 Since salvage value is lower than book value, the company will get a tax refund NPV= $11,983.72 20 Tax refund = (300000-100000)*0.21 42000 21 Terminal value = salvage value + tax refund + recapture of working capital IRR 9.49 22 100000+42000+ 0.54*100000) rities ind Buy the machine under this scenario Case B Case C Case D + * Normal View Base Case Ready Sum-222810.83

Excel File Edit View Insert Format Tools Data Window Help og 72% O Sat 3:03 PM Q E a NPV.xlsx @ Q- 100% Review Search in Sheet Font Format Cells Number Number - % Wrap Text Merge Normal 4:0 . Conditional • Formatting Bad BA Delete Insert Format: O HA Bony E 44" Y A Home Layout Tables Charts SmartArt Formulas D ata Edit Alignment A , Fill Calibri (Body) 12 A- A = = = abc Clear B I U 6 A E E = E % fx =(H15/(1.0944)) A 1 Amounts in $ 2 Cost of machine 1100000 3 Net working capital 100000 4 After-tax proceeds on old machine (200000) 253,165*(1-0.21) since book value is 0 5 Initial outlay 1000000 Themes 115 F M N O P 8 9 Depreciation = (depreciable amount/life of machine) (1100000-100000)/5 200000 10 EAT 11 Additional Sales Total expens Depreciatior EBT 12 Year 1 665000 405650 200000 59350 13 Year 2 711550 434045.5 200000 77504.5 14 Year 3 761358.5 464428.685 2 00000 96929.815 15 Year 4 723290.575 441207.251 2 00000 82083.3243 16 Terminal value at year 4 17 Book value of asset after 4 years = (1100000-(4*200000) 300000 Tax 12463.50 16275.95 20355.26 17237.50 Cash flows Add back depreciation Present value of cash flows 46886.50 246886.50 226501.38 61228.56 261228.56 219870.85 76574.55 276574.55 213566.30 64845.83 264845.83 187623.461 196000 138851.34 Total 986413.33 NPV -13586.67 NPV = present value of cash flows - initial outlay NPV= $-13,586.67 19 Since salvage value is lower than book value, the company will get a tax refund 20 Tax refund = (300000-100000)*0.21 42000 21 Terminal value = salvage value + tax refund + recapture of working capital 22 100000+42000+(0.54*100000) IRR 8.44 Don't buy under this scenario Base Case Ready Case B Case Case D + Normal View Sum-187623.46

í Excel File Edit View Insert Format Tools Data Window Help o 72% O Sat 3:06 PM Q E Q- Search in Sheet AN Format Cells Themes Normal X Aa 1 NPV.xlsx H. Gon: { · 45 · Ye Fa 4 100% A Home Layout Tables Charts SmartArt Formulas Data Review Edit Font Alignment Number A . Fill - Calibri (Body) - 12 - A- A = = = abc Wrap Text General Paste Clear B I U EESE Merge - - % ) C14 % fx =B14*0.609 | Ε F G i Intitial outlay in $ Cost of machine 1100000 Net working capital 100000 After-tax proceeds on old machine (200000) 253,165*(1-0.21) since book value is o Initial outlay 1000000 6. E . 00. 0 0.0 Bad Conditional Formatting Insert Delete Format Themes Aa н L M N O 8 Depreciation = (depreciable amount/life of machine) 9 (1100000-100000)/5 200000 10 Cash flows Additional Sales Total expens Depreciatior EBT Tax EAT Add back depreciation Present value of cash flows 12 Year 1 665000 404985 200000 6 0015 12603.15 47411.85 247411.85 226983.35 13 Year 2 718200 437383.8 200000 80816.2 16971.40 63844.80 263844.80 222072.89 14 Year 3 775656 472374.504| 200000 103281.496 21689.11 81592.38 281592.38 217440.99 15 Year 4 736873.2 448755.779 200000 88117.4212 18504.66 69612.76 269612.76 191000.48 Terminal value at year 4 196000 138851.34 17 Book value of asset after 4 years = (1100000-(4*200000) Total 996349.04 300000 NPV -3650.96 NPV = present value of cash flows - initial outlay 19 Since salvage value is lower than book value, the company will get a tax refund NPV= $-3690.96 20 Tax refund = (300000-100000)*0.21 42000 21 Terminal value = salvage value + tax refund + recapture of working capital IRR 8.85 Don't buy under this scenario 100000+42000+ 0.54 100000) 18 Conclusion We will use the probability-weighted method to find out the probable NPV of the project Probable NPV = (0.50*3,210.27) + (0.05 11,983.72) + (0.15*-13,586.67) +(0.3*-3690.96) = (0.50*3,210.27) + (0.05*11,983.72) + (0.15*-13,586.67) +(0.3*-3690.96 $-940.96 Recommendation: Don't buy The probability weighted NPV of the project is less than $0 which means it is not worth going through with the project given the assumptions and the probability of the scenarios The base case probability is 50%. The NPV under this scenario is positive However, the NPV under the first scenario is quite low compared to the initial outlay required, so the project is not lucrative under the base case While base case 2 is more favorable, its probability is very low, The probability of case 3 & 4, which yield negative NPV is high at 45% Case B Case C Case D + Base Case Ready Normal View Sum-472374.504 90909 190@7dI WAO E-