You dont need to do the data table part of the question at the end as i did not provided the data table for the last part. But please solve the parts at the top

L M B D E F H J 77 Economies of Scale in Plant Construction Capital Operating Capacity Expenditure CapEx Scale Unit Operating Scale 78 Expansion (Units) (CapEx) CapEx/ Unit / Economies Cost Economies 79 100 $35,000 $350 $50 80 150 $45,000 $300 -14% $45 -10% 81 300 $75,000 $250 -29% $40 -20% 82 83 The above Cost of Plant Construction demonstrates significant 'economies of scale'. 84 Economies of scale, enjoying a lower unit cost for a larger scale operation or investment is common. 85 Notice how the Capex/Unit declines from $350 to $250 as the number of units of capacity added increases. 86 Furthermore the Unit Operating cost declines from $50 to $40 as the number of units of capacity added increases. 87 Software development affords the ultimate level of economies of scale as once software is developed there is absolutely no additional cost for additional use. 88 This is true for all intellectual property including entertainment content and pharmaceutical and other medical technologies. 89 The model permits users to select the size of Capacity Expansion in Units 90 Capacity Expansion (Units) 150 91 Write a formula using an IF (conditional) function in the green box below that selects the unit cost based on the Capacity Expansion above. 92 Unit Operating Cost | 93 Hint: If you hard code the Unit Operating cost of $45 related to Capacity Expansion of 150 Units downstream questions will be graded as incorrect. 94 Write formulas in row 100 such that capacity is increased by the quantity selected in B90 in all years in which Capacity would not satisfy the Company's Unit Sales' in the following year. 95 Calculate the Incremental Unit Sales as the Company's Unit Sales minus the Capacity Constrained Unit Sales 96 97 2019 2020 2021 2022 2023 2024 2025 2026 2027 98 Capacity 500 500 500 500 500 500 500 500 500 99 100 Capacity Expansion (Units) 101 102 Cumulative Additional Capacity 0 0 0 0 0 0 0 0 0 103 104 Incremental Unit Sales 105 Product A 106 Product B 107 2028 500 2029 500 0 0 1 J K L M N A B D E F G H 108 Company Financial Model 109 The existing plant has a Unit Operating cost of $60. 110 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. 111 The price of Product A is $110 and the price of Product Bis $100. 112 The Incremental Gross Profit is the Incremental Unit Sales multiplied by the Unit Gross Profit 113 The average Unit Cost will depend on the 114 Size of plant constructed 115 Mix of manufacturing done at the lower cost new plant rather than the higher cost existing plant 116 We will first measure Gross Margin based on the $60 Unit Cost of the existing plant. 117 However, new capacity will operate at a lower unit cost and therefore the Operating Cost Reduction is the product of: 118 $60 Unit Cost of the existing plant minus the Unit Cost of the new plant dependent on the size of new plant constructed. 119 The Cumulative Additional Capacity because all of that capacity will be utilized before loading the higher cost existing plant. 120 The Incremental Cash Flow is the sum of the Incremental Gross Profit plus the Operating Cost Reduction minus the Capital Expenditures 121 Terminal Value 2027 2028 2029 $o $0 SO $0 $0 $0 $0 $0 $0 $0 $0 $0 122 2020 2021 2022 2023 2024 2025 2026 123 124 Incremental Gross Profit at $60 $0 $0 $0 SO $ $0 SO $0 125 Product A $0 $o $O $0 $0 $0 $0 126 Product B $0 $0 $0 $0 $0 $0 $0 127) 128 Operating cost Reduction (below $60) $0 $0 $0 $0 $0 $0 $0 129 130 Capital Expenditures (CapEx) 131 Hint: This is based on the 'Capacity Expansion (Units)'in row 100 above. 132 133 Incremental Cash Flow 134 Hint: The 'Discount Rate' is in D139. 135 136 Investment Analysis (Valuation) 137 Having developed a forecast of Cash Flows we can now analyze potential scenarios. 138 You should assume a discount rate of 12%. 139 Discount Rate 140 in cell N133 enter a formula to calculate the terminal value assume no additional growth beyond 2029. 141 Enter a formula in the green cell below that calculates the New Present Value of the Incremental Cash Flows. 142 Net Present Value (NPV) 143 Enter a formula in the green cell below that calculates the Internal Rate of Return of the Incremental Cash Flows. 144 Intemal Rate of Return (IRR)| 145 This IRR exceeds the investment threshold of the company so the construction of a new plant is a 'go' decision. 146 In general, it is extremely difficult to justify a new plant for operational efficiencies alone. Typically generating incremental sales must contribute to the justification. 147 Notice that in the above analysis Incremental Gross Profit (from incremental sales) represents the vast majority of Incremental Cash Flows. 148) 12% 136 Investment Analysis (Valuation) 137 Having developed a forecast of Cash Flows we can now analyze potential scenarios. 138 You should assume a discount rate of 12%. 139 Discount Rate 12% 140 In cell N133 enter a formula to calculate the terminal value assume no additional growth beyond 2029. 141 Enter a formula in the green cell below that calculates the New Present Value of the Incremental Cash Flows. 142 Net Present Value (NPV) 143 Enter a formula in the green cell below that calculates the Internal Rate of Return of the Incremental Cash Flows. 144 Internal Rate of Return (IRR) 145 This IRR exceeds the investment threshold of the company so the construction of a new plant is a 'go' decision. 146 In general, it is extremely difficult to justify a new plant for operational efficiencies alone. Typically generating incremental sales must contribute to the justification. 147 Notice that in the above analysis Incremental Gross Profit (from incremental sales) represents the vast majority of Incremental Cash Flows. 148 149 Risk Analysis (Data Table) 150 We cleared the hurdle and have justified the construction of a new plant 151 Now we have to drill down to determine what sized plant to construct. 152 153 You will use a Data Table to perform a sensitivity analysis of the results. 154 The last potential component of a model is a Risk Analysis. 155 In this case we will use Excel Data Tables to analyze the sensitivity of the decision to key factors. 156 157 You are going to want to complete this section with the 'DATA TABLES'Sheet arranged to the right. 158 Instructions on how to view multiple Sheets are on Row 137 of the 'EXCEL Financial Functions' Sheet of this Workbook. 159 The data table(s) related to this Sheet start on row 30 of the 'DATA TABLES'Sheet. 160 161 Create a Data Table in the template starting in row 36 of the 'DATA TABLES'Sheet. 162 What is the capacity Expansion that results in the highest NPV independent of what Discount Rate you use 163 164 L M B D E F H J 77 Economies of Scale in Plant Construction Capital Operating Capacity Expenditure CapEx Scale Unit Operating Scale 78 Expansion (Units) (CapEx) CapEx/ Unit / Economies Cost Economies 79 100 $35,000 $350 $50 80 150 $45,000 $300 -14% $45 -10% 81 300 $75,000 $250 -29% $40 -20% 82 83 The above Cost of Plant Construction demonstrates significant 'economies of scale'. 84 Economies of scale, enjoying a lower unit cost for a larger scale operation or investment is common. 85 Notice how the Capex/Unit declines from $350 to $250 as the number of units of capacity added increases. 86 Furthermore the Unit Operating cost declines from $50 to $40 as the number of units of capacity added increases. 87 Software development affords the ultimate level of economies of scale as once software is developed there is absolutely no additional cost for additional use. 88 This is true for all intellectual property including entertainment content and pharmaceutical and other medical technologies. 89 The model permits users to select the size of Capacity Expansion in Units 90 Capacity Expansion (Units) 150 91 Write a formula using an IF (conditional) function in the green box below that selects the unit cost based on the Capacity Expansion above. 92 Unit Operating Cost | 93 Hint: If you hard code the Unit Operating cost of $45 related to Capacity Expansion of 150 Units downstream questions will be graded as incorrect. 94 Write formulas in row 100 such that capacity is increased by the quantity selected in B90 in all years in which Capacity would not satisfy the Company's Unit Sales' in the following year. 95 Calculate the Incremental Unit Sales as the Company's Unit Sales minus the Capacity Constrained Unit Sales 96 97 2019 2020 2021 2022 2023 2024 2025 2026 2027 98 Capacity 500 500 500 500 500 500 500 500 500 99 100 Capacity Expansion (Units) 101 102 Cumulative Additional Capacity 0 0 0 0 0 0 0 0 0 103 104 Incremental Unit Sales 105 Product A 106 Product B 107 2028 500 2029 500 0 0 1 J K L M N A B D E F G H 108 Company Financial Model 109 The existing plant has a Unit Operating cost of $60. 110 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. 111 The price of Product A is $110 and the price of Product Bis $100. 112 The Incremental Gross Profit is the Incremental Unit Sales multiplied by the Unit Gross Profit 113 The average Unit Cost will depend on the 114 Size of plant constructed 115 Mix of manufacturing done at the lower cost new plant rather than the higher cost existing plant 116 We will first measure Gross Margin based on the $60 Unit Cost of the existing plant. 117 However, new capacity will operate at a lower unit cost and therefore the Operating Cost Reduction is the product of: 118 $60 Unit Cost of the existing plant minus the Unit Cost of the new plant dependent on the size of new plant constructed. 119 The Cumulative Additional Capacity because all of that capacity will be utilized before loading the higher cost existing plant. 120 The Incremental Cash Flow is the sum of the Incremental Gross Profit plus the Operating Cost Reduction minus the Capital Expenditures 121 Terminal Value 2027 2028 2029 $o $0 SO $0 $0 $0 $0 $0 $0 $0 $0 $0 122 2020 2021 2022 2023 2024 2025 2026 123 124 Incremental Gross Profit at $60 $0 $0 $0 SO $ $0 SO $0 125 Product A $0 $o $O $0 $0 $0 $0 126 Product B $0 $0 $0 $0 $0 $0 $0 127) 128 Operating cost Reduction (below $60) $0 $0 $0 $0 $0 $0 $0 129 130 Capital Expenditures (CapEx) 131 Hint: This is based on the 'Capacity Expansion (Units)'in row 100 above. 132 133 Incremental Cash Flow 134 Hint: The 'Discount Rate' is in D139. 135 136 Investment Analysis (Valuation) 137 Having developed a forecast of Cash Flows we can now analyze potential scenarios. 138 You should assume a discount rate of 12%. 139 Discount Rate 140 in cell N133 enter a formula to calculate the terminal value assume no additional growth beyond 2029. 141 Enter a formula in the green cell below that calculates the New Present Value of the Incremental Cash Flows. 142 Net Present Value (NPV) 143 Enter a formula in the green cell below that calculates the Internal Rate of Return of the Incremental Cash Flows. 144 Intemal Rate of Return (IRR)| 145 This IRR exceeds the investment threshold of the company so the construction of a new plant is a 'go' decision. 146 In general, it is extremely difficult to justify a new plant for operational efficiencies alone. Typically generating incremental sales must contribute to the justification. 147 Notice that in the above analysis Incremental Gross Profit (from incremental sales) represents the vast majority of Incremental Cash Flows. 148) 12% 136 Investment Analysis (Valuation) 137 Having developed a forecast of Cash Flows we can now analyze potential scenarios. 138 You should assume a discount rate of 12%. 139 Discount Rate 12% 140 In cell N133 enter a formula to calculate the terminal value assume no additional growth beyond 2029. 141 Enter a formula in the green cell below that calculates the New Present Value of the Incremental Cash Flows. 142 Net Present Value (NPV) 143 Enter a formula in the green cell below that calculates the Internal Rate of Return of the Incremental Cash Flows. 144 Internal Rate of Return (IRR) 145 This IRR exceeds the investment threshold of the company so the construction of a new plant is a 'go' decision. 146 In general, it is extremely difficult to justify a new plant for operational efficiencies alone. Typically generating incremental sales must contribute to the justification. 147 Notice that in the above analysis Incremental Gross Profit (from incremental sales) represents the vast majority of Incremental Cash Flows. 148 149 Risk Analysis (Data Table) 150 We cleared the hurdle and have justified the construction of a new plant 151 Now we have to drill down to determine what sized plant to construct. 152 153 You will use a Data Table to perform a sensitivity analysis of the results. 154 The last potential component of a model is a Risk Analysis. 155 In this case we will use Excel Data Tables to analyze the sensitivity of the decision to key factors. 156 157 You are going to want to complete this section with the 'DATA TABLES'Sheet arranged to the right. 158 Instructions on how to view multiple Sheets are on Row 137 of the 'EXCEL Financial Functions' Sheet of this Workbook. 159 The data table(s) related to this Sheet start on row 30 of the 'DATA TABLES'Sheet. 160 161 Create a Data Table in the template starting in row 36 of the 'DATA TABLES'Sheet. 162 What is the capacity Expansion that results in the highest NPV independent of what Discount Rate you use 163 164