Company Financial Model The existing plant has a Unit Operating cost of $60. The plan is to fully load any new plant because of its lower operating cost and to utilize the existing plant to manufacture the balance. The price of Product A is $110 and the price of Product B is $100. The Incremental Gross Profit is the Incremental Unit Sales multiplied by the Unit Gross Profit The average Unit Cost will depend on the Size of plant constructed Mix of manufacturing done at the lower cost new plant rather than the higher cost existing plant We will first measure Gross Margin based on the $60 Unit Cost of the existing plant. However, new capacity will operate at a lower unit cost and therefore the Operating Cost Reduction is the product of: $60 Unit Cost of the existing plant minus the Unit Cost of the new plant dependent on the size of new plant constructed. The Cumulative Additional Capacity because all of that capacity will be utilized before loading the higher cost existing plant. The Incremental Cash Flow is the surn of the Incremental Gross Profit plus the Operating Cost Reduction minus the Capital Expenditures Terminal Value 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 Incremental Gross Profit at $60 $0 $0 SO $0 $0 SO $0 $0 Product A $0 $0 $0 $0 $0 $0 $0 0 $0 SO $0 $0 so $0 so $0 $0 $O SO $0 Product B SO SO SO Operating Cost Reduction (below $60) SO $0 $0 $0 $0 $0 $0 $0 $o $0 Capital Expenditures (Capex Hint: This is based on the 'Capacity Expansion (Units) in row 101 above you should use a reference function to incorporate the data). Incremental Cash Flow Hint: For the calculation of the Terminal Value' in N134 the Discount Rate'is in D140. Investment Analysis (Valuation) Having developed a forecast of Cash Flows we can now analyze potential scenarios You should assume a discount rate of 12% % Discount Rate 12% In cell N134 enter a formula to calculate the terrninal value assume no additional growth beyond 2030. Enter a formula in the green cell below that calculates the New Present Value of the Incremental Cash Flows. Net Present Value (NPV) Enter a formula in the green cell below that calculates the Internal Rate of Retum of the Incremental Cash Flows. Internal Rate of Retum (IRR) This IRR exceeds the investment threshold of the company so the construction of a new plant is a 'go' decision. In general, it is extremely difficult to justify a new plant for operational efficiencies alone. Typically generating incremental sales must contribute to the justification Notice that in the above analysis Incremental Gross Profit (from incremental sales) represents the vast majority of Incremental Cash Flows, Company Financial Model The existing plant has a Unit Operating cost of $60. The plan is to fully load any new plant because of its lower operating cost and to utilize the existing plant to manufacture the balance. The price of Product A is $110 and the price of Product B is $100. The Incremental Gross Profit is the Incremental Unit Sales multiplied by the Unit Gross Profit The average Unit Cost will depend on the Size of plant constructed Mix of manufacturing done at the lower cost new plant rather than the higher cost existing plant We will first measure Gross Margin based on the $60 Unit Cost of the existing plant. However, new capacity will operate at a lower unit cost and therefore the Operating Cost Reduction is the product of: $60 Unit Cost of the existing plant minus the Unit Cost of the new plant dependent on the size of new plant constructed. The Cumulative Additional Capacity because all of that capacity will be utilized before loading the higher cost existing plant. The Incremental Cash Flow is the surn of the Incremental Gross Profit plus the Operating Cost Reduction minus the Capital Expenditures Terminal Value 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 Incremental Gross Profit at $60 $0 $0 SO $0 $0 SO $0 $0 Product A $0 $0 $0 $0 $0 $0 $0 0 $0 SO $0 $0 so $0 so $0 $0 $O SO $0 Product B SO SO SO Operating Cost Reduction (below $60) SO $0 $0 $0 $0 $0 $0 $0 $o $0 Capital Expenditures (Capex Hint: This is based on the 'Capacity Expansion (Units) in row 101 above you should use a reference function to incorporate the data). Incremental Cash Flow Hint: For the calculation of the Terminal Value' in N134 the Discount Rate'is in D140. Investment Analysis (Valuation) Having developed a forecast of Cash Flows we can now analyze potential scenarios You should assume a discount rate of 12% % Discount Rate 12% In cell N134 enter a formula to calculate the terrninal value assume no additional growth beyond 2030. Enter a formula in the green cell below that calculates the New Present Value of the Incremental Cash Flows. Net Present Value (NPV) Enter a formula in the green cell below that calculates the Internal Rate of Retum of the Incremental Cash Flows. Internal Rate of Retum (IRR) This IRR exceeds the investment threshold of the company so the construction of a new plant is a 'go' decision. In general, it is extremely difficult to justify a new plant for operational efficiencies alone. Typically generating incremental sales must contribute to the justification Notice that in the above analysis Incremental Gross Profit (from incremental sales) represents the vast majority of Incremental Cash Flows
