Please only answer question 4: Compute the NPV of the expansion project in the spreadsheet.

In June 2017 Michelle McGregor was thinking back to her MBA program and trying to remember how to decide whether a project is Net Present Value positive or not. She knew she had to identify the relevant costs and benefits, but which was which? Textbook problems in school were one thing. Real life was something else. There were substantial sums of money at stake for her family, so she wanted to get it right. Expansion McGregor Carbon (MC) had been in business for several years and had enjoyed some success, achieving Sales of around $3.8M in 2017. MC produced and sold Medechar, a superior substitute to charcoal. Demand was high, and Michelle was seriously considering expanding their operations. Cal was adamant that they should expand capacity to meet demand, whatever it cost. Their accountant and bookkeeper Elroy disagreed. He felt that rapid expansion would cost more than it was worthand hurt the business. Michelle saw both points of view. On one hand, she was concerned that the expansion might over-extend the business financially and end up destroying value if not bankrupting the business outright. On the other hand, she did not want to overlook a profitable alternative either. To overcome her concerns, she had identified a list of costs and benefits that she thought might be relevant. She wasn't sure, however, how to decide which was which. Potential Costs and Benefits The proposed expansion that Michelle was considering would involve opening a new plant about 40 minutes away from their existing plant. The new location would be closer to some new customers and produce about 2000 additional tons of Medechar per year. Michelle was confident that they would be able to sell the additional 2000 tons as they already had more demand than MC could meet. At the current price of $1,500 per ton, this meant additional revenues of about $3M per year, nearly double their current revenue. There were numerous other factors to consider. Michelle had prepared a preliminary list of these potential additional costs and benefits: 1) Opening the new plant would take a great deal of Cal's time. Michelle estimated that it would reduce shipments at the existing plant by about 500 tons or $750K in revenue during the first year (2018). 2) As the new plant would have the same yield as the existing plant, Michelle expected Cost of Goods Sold (COGS) to run about $0.76 on a dollar of sales. 3) Michelle believed that the new plant would require about the same working capital as the existing one. That is, she believed that the new plant would require about $25K in emergency cash, and that customers would pay in about 40 days. In addition, the new plant would require 98 days of woodchip and sawdust inventory; and their suppliers would give them about 20 days to pay. Accruals would be less than a day of sales. The forecast assumed that MC would need to set aside the 25K for emergency cash immediately. For simplicity, Michelle assumed that the initial inventory requirements would be purchased in cash. By the end of the first year, the forecast assumed that the suppliers would offer MC payment terms. Based on these assumptions, Michelle prepared the following working capital forecast for the new plant; Year # Date Cash Accounts Receivable Working Capital Forecast 0 1 1/1/2018 12/31/2018 25,000 25,000 334,829 612,164 612,164 122,964 5,753 637,164 843,276 637,164 206,112 2 12/31/2019 25,000 334,829 612,164 122,964 5,753 3 12/31/2020 25,000 334,829 612, 164 122,964 5,753 843,276 Inventory Accounts Payable Accruals Working Capital D(WC) 843,276 4) To staff the new plant, MC would have to hire 4 production employees for a total wage bill of about $180K per year. In addition, if they built the new plant, MC would promote their most experienced production employee, Paul, to be the supervisor at the new plant. Paul's position at the original plant would have to be filled with a replacement hire. Paul's current compensation was about $45K per year. Upon taking over the new plant, Paul would receive a $9K per year raise. Michelle believed they could fill Paul's current position at approximately the same compensation. 5) MC would have to hire two additional salespeople for total additional salary expense of $114K per year. 6) Cal would have to build a new 'furnace' for the new plant for an immediate up-front cost of about $60K. Annual Depreciation Expense would be $4K per year. 7) The plant would need a truck to deliver shipments to customers. In March of 2017, MC had purchased a new truck for $50K. Luckily, Cal hadn't gotten around to selling the old truck, which was expected to bring in around $25K. Thus, the new truck could be used at the new plant. The old truck was fully depreciated, while the new truck generated 10K in depreciation per year. 8) MC would lease the site of the new facility for $45K per year. In addition, they would have to erect two new prefab buildings for an immediate up-front cost of $90K. Annual depreciation expense on the new buildings would be $6K per year. 9) MC would need to make about $10K per year in annual CAPEX in 2019 and beyond to keep the assets at the new plant in good working order. 10)In May of 2017, Cal had spent $50K on an industrial engineer to help tweak the furnace design in preparation for building a new one. In addition, he had spent $20K on a marketing consultant to help assess the market for Medechar. 11) Finally, there was the overhead at both plants to consider. In anticipation of starting the new plant, Elroy had implemented a change in their accounting system. The "corporate overhead" (Elroy's, Michelle's, and Cal's salaries) would be allocated between the plants based on total production. Elroy argued that this was necessary to ensure that the new facility 'covered the cost of MC's overhead. The total cost of corporate overhead charged to the new plant would be about $130K per year. Michelle thought that sounded reasonable, though she did not anticipate any change in corporate salaries (i.e., Michelle's, Elroy's, and Cal's) and thus no change in corporate overhead from the expansion project. As Michelle reviewed the list of potential costs and benefits, she wondered again how to make sense of all of them. Which were relevant and which were not? Foster Live Preparation For the purposes of this case, you may assume that the discount rate for MC's new plant is 10%. Please address the following with your using the ideas from Videos 2.1-2.3 in a Word document, Please also submit your diagrams and Excel spreadsheet with computations. Questions: 1) Use the provided blank schematic diagrams to display a list of the relevant costs and benefits if MC goes through with the expansion. 2) Use the provided blank schematic diagrams to display a list of the relevant costs and benefits if MC does not go through with the expansion. 3) Prepare a spreadsheet for the costs and benefits "with the project and "without the project. 4) Compute the NPV of the expansion project in the spreadsheet. 5) Does this project increase MC's Value Add? Does this make sense given their competitive environment? Please provide your response to these questions in the Word document you submit. In June 2017 Michelle McGregor was thinking back to her MBA program and trying to remember how to decide whether a project is Net Present Value positive or not. She knew she had to identify the relevant costs and benefits, but which was which? Textbook problems in school were one thing. Real life was something else. There were substantial sums of money at stake for her family, so she wanted to get it right. Expansion McGregor Carbon (MC) had been in business for several years and had enjoyed some success, achieving Sales of around $3.8M in 2017. MC produced and sold Medechar, a superior substitute to charcoal. Demand was high, and Michelle was seriously considering expanding their operations. Cal was adamant that they should expand capacity to meet demand, whatever it cost. Their accountant and bookkeeper Elroy disagreed. He felt that rapid expansion would cost more than it was worthand hurt the business. Michelle saw both points of view. On one hand, she was concerned that the expansion might over-extend the business financially and end up destroying value if not bankrupting the business outright. On the other hand, she did not want to overlook a profitable alternative either. To overcome her concerns, she had identified a list of costs and benefits that she thought might be relevant. She wasn't sure, however, how to decide which was which. Potential Costs and Benefits The proposed expansion that Michelle was considering would involve opening a new plant about 40 minutes away from their existing plant. The new location would be closer to some new customers and produce about 2000 additional tons of Medechar per year. Michelle was confident that they would be able to sell the additional 2000 tons as they already had more demand than MC could meet. At the current price of $1,500 per ton, this meant additional revenues of about $3M per year, nearly double their current revenue. There were numerous other factors to consider. Michelle had prepared a preliminary list of these potential additional costs and benefits: 1) Opening the new plant would take a great deal of Cal's time. Michelle estimated that it would reduce shipments at the existing plant by about 500 tons or $750K in revenue during the first year (2018). 2) As the new plant would have the same yield as the existing plant, Michelle expected Cost of Goods Sold (COGS) to run about $0.76 on a dollar of sales. 3) Michelle believed that the new plant would require about the same working capital as the existing one. That is, she believed that the new plant would require about $25K in emergency cash, and that customers would pay in about 40 days. In addition, the new plant would require 98 days of woodchip and sawdust inventory; and their suppliers would give them about 20 days to pay. Accruals would be less than a day of sales. The forecast assumed that MC would need to set aside the 25K for emergency cash immediately. For simplicity, Michelle assumed that the initial inventory requirements would be purchased in cash. By the end of the first year, the forecast assumed that the suppliers would offer MC payment terms. Based on these assumptions, Michelle prepared the following working capital forecast for the new plant; Year # Date Cash Accounts Receivable Working Capital Forecast 0 1 1/1/2018 12/31/2018 25,000 25,000 334,829 612,164 612,164 122,964 5,753 637,164 843,276 637,164 206,112 2 12/31/2019 25,000 334,829 612,164 122,964 5,753 3 12/31/2020 25,000 334,829 612, 164 122,964 5,753 843,276 Inventory Accounts Payable Accruals Working Capital D(WC) 843,276 4) To staff the new plant, MC would have to hire 4 production employees for a total wage bill of about $180K per year. In addition, if they built the new plant, MC would promote their most experienced production employee, Paul, to be the supervisor at the new plant. Paul's position at the original plant would have to be filled with a replacement hire. Paul's current compensation was about $45K per year. Upon taking over the new plant, Paul would receive a $9K per year raise. Michelle believed they could fill Paul's current position at approximately the same compensation. 5) MC would have to hire two additional salespeople for total additional salary expense of $114K per year. 6) Cal would have to build a new 'furnace' for the new plant for an immediate up-front cost of about $60K. Annual Depreciation Expense would be $4K per year. 7) The plant would need a truck to deliver shipments to customers. In March of 2017, MC had purchased a new truck for $50K. Luckily, Cal hadn't gotten around to selling the old truck, which was expected to bring in around $25K. Thus, the new truck could be used at the new plant. The old truck was fully depreciated, while the new truck generated 10K in depreciation per year. 8) MC would lease the site of the new facility for $45K per year. In addition, they would have to erect two new prefab buildings for an immediate up-front cost of $90K. Annual depreciation expense on the new buildings would be $6K per year. 9) MC would need to make about $10K per year in annual CAPEX in 2019 and beyond to keep the assets at the new plant in good working order. 10)In May of 2017, Cal had spent $50K on an industrial engineer to help tweak the furnace design in preparation for building a new one. In addition, he had spent $20K on a marketing consultant to help assess the market for Medechar. 11) Finally, there was the overhead at both plants to consider. In anticipation of starting the new plant, Elroy had implemented a change in their accounting system. The "corporate overhead" (Elroy's, Michelle's, and Cal's salaries) would be allocated between the plants based on total production. Elroy argued that this was necessary to ensure that the new facility 'covered the cost of MC's overhead. The total cost of corporate overhead charged to the new plant would be about $130K per year. Michelle thought that sounded reasonable, though she did not anticipate any change in corporate salaries (i.e., Michelle's, Elroy's, and Cal's) and thus no change in corporate overhead from the expansion project. As Michelle reviewed the list of potential costs and benefits, she wondered again how to make sense of all of them. Which were relevant and which were not? Foster Live Preparation For the purposes of this case, you may assume that the discount rate for MC's new plant is 10%. Please address the following with your using the ideas from Videos 2.1-2.3 in a Word document, Please also submit your diagrams and Excel spreadsheet with computations. Questions: 1) Use the provided blank schematic diagrams to display a list of the relevant costs and benefits if MC goes through with the expansion. 2) Use the provided blank schematic diagrams to display a list of the relevant costs and benefits if MC does not go through with the expansion. 3) Prepare a spreadsheet for the costs and benefits "with the project and "without the project. 4) Compute the NPV of the expansion project in the spreadsheet. 5) Does this project increase MC's Value Add? Does this make sense given their competitive environment? Please provide your response to these questions in the Word document you submit