Mid Michigan Manufacturing, Inc. (hereinafter referred to as the "company"), has had a surge in orders that they believe will continue into the foreseeable future, and if so, will ilikely necessitate building or buying a new factory to keep up with product orders. They have decided to do an analysis on potentially building a factory on a tract of land they currently own near Mason. They have hired you to complete this analysis and make a recommendation. To perform the necessary analysis, you have compiled the following data: - The project has a 5 year timeline. - The company purchased the land in Mason that the factory would be built on for $13,000,000. The purchase was made in 2009. should they decide to bulld the factory on that site. - The company has performed Research and Development on the products that the factory would bulld over the past year in the amount of $650,000. - The cost of building and equipping the factory is estimated at $28,000,000. - The Marketing Department of the company has spent $400,000 over the past year to try to increase demand in the company's products. - Only the original cost of the factory and its equipment would be depreciated; they would be depreciated straight-line to $0 over their estimated 7 -year useful life. In the end of year 5 there will be residual price and I have to calculate - A residential property developer has offered the company $11,000,000 for the site should they not decide to build the factory on it. - If the company decides to build the factory, they will sell a factory that they own that has been closed for several years located Portland, MI. A competitor has made an offer to purchase the closed facility for $2,531,645.57. This transaction would take place immediately at the beginning of the project (Year 0 ). - Another competitor has told the company that they will buy the new factory and all of its equipment for $5,000,000 at the end of the project (the end of Year 5). - Products produced by the factory will add an estimated $45,000,000 to the company's revenue in Year 1. - Sales growth in Years 283 is expected to be 4.6% per year. - As the market begins to become saturated, sales are expected to decline in Years 4&5 by 5% per year. - Total Costs (Expenses) are estimated to be 75.9\% of sales. - Additional Net Working Capital will be required in Year 0 of $800,000,30% of which will be recovered in the project's terminal year. - The company's tax rate is 21%. - The required rate of return on the project is 10.5%. Part 1- Base Case: Using the above data, complete the DCF Model in Excel posted on Connect. Compute the Base Case's NPV and IRR. Then copy the Base Case worksheet and post to the tabs marked Part 2, Part 3, and Part 4. A consulting firm as suggested a few alternate scenarios based on their analysis, and has computed their estimate of the likelihood of each scenario occurring. They have also estimated that the Base Case has a 50% chance of occurring. Part 2- Alternate Scenarlo 1: The consulting firm belleves that the company may have been too pessimistic with their Year 1 revenue estimates, and a little too optimistic about expenses. Under this scenario, Year 1's Revenue should be 45,500,000, and Expenses as a \% of Sales should be increased to 76.4%. Recalculate NPV and IRR. The consulting firm has estimated that this scenario has a 15% chance of occurring. Part 3-Alternate Scenario 2t: Under this scenario, Year 1 Revenue was overestimated, and should be reduced to \$44,500,000. Expenses were overestimated, so Expenses as a \% of Sales should Using the above data, complete the DCF Model in Excel posted on Connect. Compute the Base Case's NPV and IRR. Then copy the Base Case worksheet and post to the tabs marked Part 2, Part 3, and Part 4. A consulting firm as suegested a few alternate scenarios based on their analysis, and has computed their estimate of the likelihood of each scenario occurring. They have also estimated that the Base Case has a 50% chance of occurring. Part 2 - Alternate Scenario 1t The consulting firm believes that the company may have been too pessimistic With their Year 1 revenue estimates, and a little too optimistic about expenses. Under this scenario, Year 1's Revenue should be 45,500,000, and Expenses as a\% of Sales should be increased to 76.4%. Recalculate NPV and IRR. The consulting firm has estimated that this scenario has a 15% chance of occurring. Part 3-Alternate Scenario 2 : Under this scenario, Year 1 Revenue was overestimated, and should be reduced to $44,500,000. Expenses were overestimated, so Expenses as a \$6 of Sales should be decreased to 75.5\%. Recalculate NPV and IRR. The consulting firm has estimated that this scenario has a 10% chance of occurring. Part 4- Alternate Scenario 3: For this scenarlo, only change the Year 223 Sales Growth to 4.3% per year. The consulting firm has estimated that this scenario has a 25% chance of occurring. Conclusion: You need to provide the company with an Accept or Reject recommendation, which will be one of the questions in Connect. You do not have to put your VALUE DRIVERS sales growth years 2 and 3 slaes growth year 4 and 5 cost as a % of sales 75.90% Years 0 1 2 3 Capital Spending OCF: Revenues Expenses \begin{tabular}{|r|r|r|r|r|} $45,000,000 & $47,070,000 & $49,235,220 & $46,773,459 & $44,434,786 \\ \hline$34,155,000 & $35,726,130 & $37,369,532 & $35,501,055 & $33,726,003 \\ \hline$1,571,429 & $1,571,429 & $1,571,429 & $1,571,429 & $1,571,429 \\ \hline \end{tabular} Taxes Net Income OPERATING CASH FLOW Net Working Capital $800,000 $240,000 Total Cash Flow NPV 35 IRR 36 Mid Michigan Manufacturing, Inc. (hereinafter referred to as the "company"), has had a surge in orders that they believe will continue into the foreseeable future, and if so will likely necessitate building or buying a new factory to keep up with product orders. They have decided to do an analysis on potentially building a factory on a tract of land they currently own near Mason. They have hired you to complete this analysis and make a recommendation. To perform the necessary analysis, you have compiled the following data: - The project has a 5 year timeline. - The company purchased the land in Mason that the factory would be built on for $13,000,000. The purchase was made in 2009. - The factory site will require $2,000,000 in infrastructure improvements should they decide to build the factory on that site. - The company has performed Research and Development on the products that the factory would build over the past year in the amount of $650,000. - The cost of building and equipping the factory is estimated at $28,000,000. - The Marketing Department of the company has spent $400,000 over the past year to try to increase demand in the company's products. - Only the original cost of the factory and its equipment would be depreciated; they would be depreciated straight-line to $0 over their estimated 7-year useful life. In the end of year 5 there will be residual price and I have to calculate - A residential property developer has offered the company $11,000,000 for the site should they not decide to build the factory on it. - If the company decides to build the factory, they will sell a factory that they own that has been closed for several years located Portland, MI. A competitor has made an offer to purchase the closed facility for $2,531,645.57. This transaction would take place immediately at the beginning of the project (Year 0 ). - Another competitor has told the company that they will buy the new factory and all of its equipment for $5,000,000 at the end of the project (the end of Year 5). - Products produced by the factory will add an estimated $45,000,000 to the company's revenue in Year 1. - Sales growth in Years 2&3 is expected to be 4.6% per year. - As the market begins to become saturated, sales are expected to decline in Years 4&5 by 5% per year. - Total Costs (Expenses) are estimated to be 75.9% of sales. - Additional Net Working Capital will be required in Year 0 of $800,000,30% of which will be recovered in the project's terminal year. - The company's tax rate is 21%. - The required rate of return on the project is 10.5%. Part 1-Base Case: Using the above data, complete the DCF Model in Excel posted on Connect. Compute the Base Case's NPV and IRR. Then copy the Base Case worksheet and post to the tabs marked Part 2, Part 3, and Part 4. A consulting firm as suggested a few alternate scenarios based on their analysis, and has computed their estimate of the likelihood of each scenario occurring. They have also estimated that the Base Case has a 50% chance of occurring. Part 2- Alternate Scenario 1: The consulting firm belleves that the company may have been too pessimistic with their Year 1 revenue estimates, and a little too optimistic about expenses. Under this scenario, Year 1's Revenue should be 45,500,000, and Expenses as a\% of Sales should be increased to 76.4%. Recalculate NPV and IRR. The consulting firm has estimated that this scenario has a 15% chance of occurring. Part 3- Alternate Scenario 2: Part3-Alternate Scenario 2: Under this scenario, Year 1 Revenue was overestimated, and should be reduced to $44,500,000. Expenses were overestimated, so Expenses as a \% of Sales should be decreased to 75.5%. Recalculate NPV and IRR. The consulting firm has estimated that this scenario has a 10% chance of occurring. Part 4- Alternate Scenario 3: For this scenario, only change the Year 2&3 Sales Growth to 4.3% per year. The consulting firm has estimated that this scenario has a 25% chance of occurring. Conclusion: You need to provide the company with an Accept or Reject recommendation, which will be one of the questions in Connect. You do not have to put your recommendation on the spreadsheet. VALUE DRIVERS 3 sales growth years 2 and 3 4 slaes growth year 4 and 5 B D E G 5 cost as a % of sales 4.6%5.0%75.90% Years 19 OCF: \begin{tabular}{l|l|} \hline 20 & Revenues \\ \hline 21 & Expenses \\ \hline 22 & Depreciation \\ \hline 23 & \\ \hline 24 & Taxes \\ \hline 25 & EBIT \\ \hline 26 & Net Income \\ \hline 27 & OPERATING CASH FLOW \end{tabular} Mid Michigan Manufacturing, Inc. (hereinafter referred to as the "company"), has had a surge in orders that they believe will continue into the foreseeable future, and if so, will ilikely necessitate building or buying a new factory to keep up with product orders. They have decided to do an analysis on potentially building a factory on a tract of land they currently own near Mason. They have hired you to complete this analysis and make a recommendation. To perform the necessary analysis, you have compiled the following data: - The project has a 5 year timeline. - The company purchased the land in Mason that the factory would be built on for $13,000,000. The purchase was made in 2009. should they decide to bulld the factory on that site. - The company has performed Research and Development on the products that the factory would bulld over the past year in the amount of $650,000. - The cost of building and equipping the factory is estimated at $28,000,000. - The Marketing Department of the company has spent $400,000 over the past year to try to increase demand in the company's products. - Only the original cost of the factory and its equipment would be depreciated; they would be depreciated straight-line to $0 over their estimated 7 -year useful life. In the end of year 5 there will be residual price and I have to calculate - A residential property developer has offered the company $11,000,000 for the site should they not decide to build the factory on it. - If the company decides to build the factory, they will sell a factory that they own that has been closed for several years located Portland, MI. A competitor has made an offer to purchase the closed facility for $2,531,645.57. This transaction would take place immediately at the beginning of the project (Year 0 ). - Another competitor has told the company that they will buy the new factory and all of its equipment for $5,000,000 at the end of the project (the end of Year 5). - Products produced by the factory will add an estimated $45,000,000 to the company's revenue in Year 1. - Sales growth in Years 283 is expected to be 4.6% per year. - As the market begins to become saturated, sales are expected to decline in Years 4&5 by 5% per year. - Total Costs (Expenses) are estimated to be 75.9\% of sales. - Additional Net Working Capital will be required in Year 0 of $800,000,30% of which will be recovered in the project's terminal year. - The company's tax rate is 21%. - The required rate of return on the project is 10.5%. Part 1- Base Case: Using the above data, complete the DCF Model in Excel posted on Connect. Compute the Base Case's NPV and IRR. Then copy the Base Case worksheet and post to the tabs marked Part 2, Part 3, and Part 4. A consulting firm as suggested a few alternate scenarios based on their analysis, and has computed their estimate of the likelihood of each scenario occurring. They have also estimated that the Base Case has a 50% chance of occurring. Part 2- Alternate Scenarlo 1: The consulting firm belleves that the company may have been too pessimistic with their Year 1 revenue estimates, and a little too optimistic about expenses. Under this scenario, Year 1's Revenue should be 45,500,000, and Expenses as a \% of Sales should be increased to 76.4%. Recalculate NPV and IRR. The consulting firm has estimated that this scenario has a 15% chance of occurring. Part 3-Alternate Scenario 2t: Under this scenario, Year 1 Revenue was overestimated, and should be reduced to \$44,500,000. Expenses were overestimated, so Expenses as a \% of Sales should Using the above data, complete the DCF Model in Excel posted on Connect. Compute the Base Case's NPV and IRR. Then copy the Base Case worksheet and post to the tabs marked Part 2, Part 3, and Part 4. A consulting firm as suegested a few alternate scenarios based on their analysis, and has computed their estimate of the likelihood of each scenario occurring. They have also estimated that the Base Case has a 50% chance of occurring. Part 2 - Alternate Scenario 1t The consulting firm believes that the company may have been too pessimistic With their Year 1 revenue estimates, and a little too optimistic about expenses. Under this scenario, Year 1's Revenue should be 45,500,000, and Expenses as a\% of Sales should be increased to 76.4%. Recalculate NPV and IRR. The consulting firm has estimated that this scenario has a 15% chance of occurring. Part 3-Alternate Scenario 2 : Under this scenario, Year 1 Revenue was overestimated, and should be reduced to $44,500,000. Expenses were overestimated, so Expenses as a \$6 of Sales should be decreased to 75.5\%. Recalculate NPV and IRR. The consulting firm has estimated that this scenario has a 10% chance of occurring. Part 4- Alternate Scenario 3: For this scenarlo, only change the Year 223 Sales Growth to 4.3% per year. The consulting firm has estimated that this scenario has a 25% chance of occurring. Conclusion: You need to provide the company with an Accept or Reject recommendation, which will be one of the questions in Connect. You do not have to put your VALUE DRIVERS sales growth years 2 and 3 slaes growth year 4 and 5 cost as a % of sales 75.90% Years 0 1 2 3 Capital Spending OCF: Revenues Expenses \begin{tabular}{|r|r|r|r|r|} $45,000,000 & $47,070,000 & $49,235,220 & $46,773,459 & $44,434,786 \\ \hline$34,155,000 & $35,726,130 & $37,369,532 & $35,501,055 & $33,726,003 \\ \hline$1,571,429 & $1,571,429 & $1,571,429 & $1,571,429 & $1,571,429 \\ \hline \end{tabular} Taxes Net Income OPERATING CASH FLOW Net Working Capital $800,000 $240,000 Total Cash Flow NPV 35 IRR 36 Mid Michigan Manufacturing, Inc. (hereinafter referred to as the "company"), has had a surge in orders that they believe will continue into the foreseeable future, and if so will likely necessitate building or buying a new factory to keep up with product orders. They have decided to do an analysis on potentially building a factory on a tract of land they currently own near Mason. They have hired you to complete this analysis and make a recommendation. To perform the necessary analysis, you have compiled the following data: - The project has a 5 year timeline. - The company purchased the land in Mason that the factory would be built on for $13,000,000. The purchase was made in 2009. - The factory site will require $2,000,000 in infrastructure improvements should they decide to build the factory on that site. - The company has performed Research and Development on the products that the factory would build over the past year in the amount of $650,000. - The cost of building and equipping the factory is estimated at $28,000,000. - The Marketing Department of the company has spent $400,000 over the past year to try to increase demand in the company's products. - Only the original cost of the factory and its equipment would be depreciated; they would be depreciated straight-line to $0 over their estimated 7-year useful life. In the end of year 5 there will be residual price and I have to calculate - A residential property developer has offered the company $11,000,000 for the site should they not decide to build the factory on it. - If the company decides to build the factory, they will sell a factory that they own that has been closed for several years located Portland, MI. A competitor has made an offer to purchase the closed facility for $2,531,645.57. This transaction would take place immediately at the beginning of the project (Year 0 ). - Another competitor has told the company that they will buy the new factory and all of its equipment for $5,000,000 at the end of the project (the end of Year 5). - Products produced by the factory will add an estimated $45,000,000 to the company's revenue in Year 1. - Sales growth in Years 2&3 is expected to be 4.6% per year. - As the market begins to become saturated, sales are expected to decline in Years 4&5 by 5% per year. - Total Costs (Expenses) are estimated to be 75.9% of sales. - Additional Net Working Capital will be required in Year 0 of $800,000,30% of which will be recovered in the project's terminal year. - The company's tax rate is 21%. - The required rate of return on the project is 10.5%. Part 1-Base Case: Using the above data, complete the DCF Model in Excel posted on Connect. Compute the Base Case's NPV and IRR. Then copy the Base Case worksheet and post to the tabs marked Part 2, Part 3, and Part 4. A consulting firm as suggested a few alternate scenarios based on their analysis, and has computed their estimate of the likelihood of each scenario occurring. They have also estimated that the Base Case has a 50% chance of occurring. Part 2- Alternate Scenario 1: The consulting firm belleves that the company may have been too pessimistic with their Year 1 revenue estimates, and a little too optimistic about expenses. Under this scenario, Year 1's Revenue should be 45,500,000, and Expenses as a\% of Sales should be increased to 76.4%. Recalculate NPV and IRR. The consulting firm has estimated that this scenario has a 15% chance of occurring. Part 3- Alternate Scenario 2: Part3-Alternate Scenario 2: Under this scenario, Year 1 Revenue was overestimated, and should be reduced to $44,500,000. Expenses were overestimated, so Expenses as a \% of Sales should be decreased to 75.5%. Recalculate NPV and IRR. The consulting firm has estimated that this scenario has a 10% chance of occurring. Part 4- Alternate Scenario 3: For this scenario, only change the Year 2&3 Sales Growth to 4.3% per year. The consulting firm has estimated that this scenario has a 25% chance of occurring. Conclusion: You need to provide the company with an Accept or Reject recommendation, which will be one of the questions in Connect. You do not have to put your recommendation on the spreadsheet. VALUE DRIVERS 3 sales growth years 2 and 3 4 slaes growth year 4 and 5 B D E G 5 cost as a % of sales 4.6%5.0%75.90% Years 19 OCF: \begin{tabular}{l|l|} \hline 20 & Revenues \\ \hline 21 & Expenses \\ \hline 22 & Depreciation \\ \hline 23 & \\ \hline 24 & Taxes \\ \hline 25 & EBIT \\ \hline 26 & Net Income \\ \hline 27 & OPERATING CASH FLOW \end{tabular}