FI 311 Fall 2020

Module 4 Assignment DCF Analysis

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:

FI 311 - Fall 2020 Module 4 Assignment - DCF Analysis 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 firm's 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. Part 2: Alternate Scenario 1 (5 points): Assume that expenses as a percentage of sales will be 67.8% instead of 67.4% (all other estimates remain the same). Recalculate NPV and IRR, and somewhere on the Part 2 sheet state whether you would accept or reject the project based on this scenario alone. Note: this scenario has a 10% chance of occurring. Part 3: Alternate Scenario 2 (5 points): Reverting back to the Base Case estimates and assumptions, now assume that sales growth in Years 2 and 3 will be 5.3% each year instead of 5.5%, and that sales in Years 4 and 5 will decline by 27.5% per year rather than 25% (all other estimates remain the same). Recalculate NPV and IRR, and somewhere on the Part 3 sheet state whether you would accept or reject the project based on this scenario alone. Note: this scenario has a 10% chance of occurring. Part 4: Alternate Scenario 3 (5 points): Revert back to the Base Case estimates and assumptions. Williamston Widgets has announced that they will be producing a competing product using the old system they purchased from MMMI. Financial analysts at MMMI have estimated that Williamston Widgets' new product will be released to the marketplace in Year 5 and has a 25% chance of success; this will cause sales to decline by 35% in Year 5 only instead of 25% (all other estimates remain the same). Recalculate NPV and IRR, and somewhere on the Part 4 sheet state whether you would accept or reject the project based on this scenario alone. Part 5: Conclusion (20 points): Make a decision regarding whether or not to recommend to MMMI that they purchase the new system. Write 3-4 sentences giving your recommendation and explain why. Note: you must clearly state whether MMMI should accept or reject this project. Simply restating what would happen in each scenario without making a clear recommendation will earn 0 points for this section of the assignment. It should be very clear as to what MMMI should do and why. FI 311 - Fall 2020 Module 4 Assignment - DCF Analysis 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 firm's 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. Part 2: Alternate Scenario 1 (5 points): Assume that expenses as a percentage of sales will be 67.8% instead of 67.4% (all other estimates remain the same). Recalculate NPV and IRR, and somewhere on the Part 2 sheet state whether you would accept or reject the project based on this scenario alone. Note: this scenario has a 10% chance of occurring. Part 3: Alternate Scenario 2 (5 points): Reverting back to the Base Case estimates and assumptions, now assume that sales growth in Years 2 and 3 will be 5.3% each year instead of 5.5%, and that sales in Years 4 and 5 will decline by 27.5% per year rather than 25% (all other estimates remain the same). Recalculate NPV and IRR, and somewhere on the Part 3 sheet state whether you would accept or reject the project based on this scenario alone. Note: this scenario has a 10% chance of occurring. Part 4: Alternate Scenario 3 (5 points): Revert back to the Base Case estimates and assumptions. Williamston Widgets has announced that they will be producing a competing product using the old system they purchased from MMMI. Financial analysts at MMMI have estimated that Williamston Widgets' new product will be released to the marketplace in Year 5 and has a 25% chance of success; this will cause sales to decline by 35% in Year 5 only instead of 25% (all other estimates remain the same). Recalculate NPV and IRR, and somewhere on the Part 4 sheet state whether you would accept or reject the project based on this scenario alone. Part 5: Conclusion (20 points): Make a decision regarding whether or not to recommend to MMMI that they purchase the new system. Write 3-4 sentences giving your recommendation and explain why. Note: you must clearly state whether MMMI should accept or reject this project. Simply restating what would happen in each scenario without making a clear recommendation will earn 0 points for this section of the assignment. It should be very clear as to what MMMI should do and why