B C D E F G H J L M N 0 P Q 12 Unit forecast of demand by product based on market growth and changes in market share 13 Cost structure 14 Economies of scale in plant construction 15 16 The following model supporting the decision of when and how large a plant to construct should incorporate the following: 17 Product level analysis including units, price, unit costs 18 Plant capacity and capital expenditure (Capex) analysis explicitly recognizing economies of scale. 19 Strategic analysis including market sizing and market share analysis. . 20 Investment analysis of new plant construction 21 Sensitivity analysis of the results 22 23 This represents a simplified example of analyzing a business strategy that requires plant constructions. 24 We have simplified the problem by focusing only on Gross Margin and Capex 25 We have ignored depreciation, indirect operating expenses, non-aperating expenses, financing and taxes. 26 Market Size Model 28 The first step is to model the entire market. The company manufacturers two products (A and B) so each market must be modeled. . (A 29 Modeling the market requires establishing growth rates for each of the products. 30 Complete the model by entering formulas in the green cells below. 31 The exercise is designed such that an error early in the assignment will not adversely impact later grading 32 33 The market size of each product can be forecast based on grawth expectations. 34 Product A's growth will decelerate, declining linearly by .5% every year. 35 Product B's growth will be stable at 10% annually. 36 Hint: When financial analysts adjust a growth rate or market share you do not compound the adjustment. If the growth rate is 10% and it dedines by 3%, the adjusted growth rate is 7%. Please use this darification to understand how to forecast throughout this Sheet 37 38 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 39 Market Size Units 1.045 1,045 1,045 1,045 1,045 1.045 1,045 1,045 1.045 1,045 1,045 40 Product A 523 523 S23 523 523 523 523 523 523 523 523 41 Product B 523 523 523 523 523 523 523 523 523 523 523 42 43 Market Unit Growth Rate 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 44 Product A 10.0% 45 Products 10.0X 46 47 Company Sales Model 48 The sales of the company for each product can be calculated based on an outlook for its market share of the product. 49 The company's market share of Product A is expected to be flat. 50 The company's market share of Product B is expected to dedine by 5% this year because of a new entrant but will then remain at thereafter. 51 52 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 53 Market Share 54 Product A 55 Product B 60% 56 57 Company's Unit Sales 470 0 58 Product A 59 Product B 3141 60 30% O 0 0 0 O 0 O 0 0 157 2031 Cost 61 Capacity Expansion Model 62 Product A and B require the same amount of capacity to manufacture. That simplifies the analysis of capacity as we can measure capacity in 'units' of Product A and B as a common denominator. 63 The company will choose to deploy scarce capacity on the most profitable products. 64 The price of Product A is $110 and the price of Product Bis $100. 65 Therefore, the company will manufacture Product A before Product B. 66 The company currently has 500 units of capacity. 67 The Unit Sales Given Capacity Constraints forecast of sales is based on the scenario that no capacity is added. 68 Write formulas in the green boxes below to calculate how many units of each product would be sold. 69 Hint: You will need to use a MIN/MAX/IF function. 70 71 2022 2023 2024 2025 2026 2027 2028 2029 2030 72 Unit Sales Given 500 units of Capacity 73 Product A 74 Product B 75 76 The company will have to add capacity over the next decade to satisfy demand. 77 78 Economies of Scale in Plant Construction Capital Operating Capacity Expenditure Capex Scale Unit Operating Scale 79 Expansion (Units) (CapEx) CapEx/Unit Economies Economies 80 $35,000 $350 $50 $45,000 $300 -14% $45 -10% 82 300 $75,000 $250 -29% $40 -20% 83 84 The above cost of Plant Construction demonstrates significant 'economies of scale'. 85 Economies of scale, enjoying a lower unit cost for a larger scale operation or investment, is common. 86 Notice how the Capex/Unit declines from $350 to $250 as the number of units of capacity added increases. 87 Furthermore the Unit Operating cost declines from $50 to $40 as the number of units of capacity added increases. 88 Software development affords the ultimate level of economies of scale as once software is developed there is absolutely no additional cost for additional use. 89 This is true for all intellectual property including entertainment content and is almost valid for pharmaceutical and other medical technologies. 90 The model permits users to select the size of Capacity Expansion in Units 91 Capacity Expansion (Units) 150 92 Write a formula using an IF (conditional) function in the green box below that selects the unit cost based on the Capacity Expansion above. 93 Unit Operating cost 94 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. 95 Write formulas in row 102 such that in every year prior to a year in which adequate capacity would not otherwise exist capacity is expanded by the quantity selected in 191. 96 Note : Additional Capacity becomes available in the year after the investment is made 97 Calculate the Incremental Unit Sales as the Company's Unit Sales minus the Capacity Constrained Unit Sales 98 100 81 150 $45,000 78 Economies of Scale in Plant Construction Capital Operating Capacity Expenditure CapEx Scale Unit Operating Scale 79 Expansion (Units) (Capex) CapEx/ Unit Economies Cost Economies 80 100 $35,000 $350 $50 81 150 $300 -14% $45 -10% 82 300 $75,000 $250 -29% $40 -20% 83 84 The above Cost of Plant Construction demonstrates significant 'economies of scale'. 85 Economies of scale, enjoying a lower unit cost for a larger scale operation or investment is common. 86 Notice how the Capex/Unit declines from $350 to $250 as the number of units of capacity added increases. 87 Furthermore the Unit Operating Cost declines from $50 to $40 as the number of units of capacity added increases. 88 Software development affords the ultimate level of economies of scale as once software is developed there is absolutely no additional cost for additional use. 89 This is true for all intellectual property including entertainment content and is almost valid for pharmaceutical and other medical technologies. 90 The model permits users to select the size of Capacity Expansion in Units 91 Capacity Expansion (Units) ( 150 92 Write a formula using an IF (conditional) function in the green box below that selects the unit cost based on the Capacity Expansion above. 93 Unit Operating Cost 94 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. 95 Write formulas in row 102 such that in every year prior to a year in which adequate capacity would not otherwise exist capacity is expanded by the quantity selected in D91. a 96 Note: Additional Capacity becomes available in the year after the investment is made. 97 Calculate the incremental Unit Sales as the Company's Unit Sales minus the Capacity Constrained Unit Sales. 98 99 2021 2022 2023 2024 2025 2026 2027 2028 100 Capacity 500 500 500 500 500 500 500 500 101 102 Capacity Expansion (Units) 103 104 Cumulative Additional Capacity 0 0 0 0 0 0 0 105 106 Incremental Unit Sales 107 Product A 108 Product B 109 2029 500 2030 500 2031 500 0 0 0 B C D E F G H J L M N 0 P Q 12 Unit forecast of demand by product based on market growth and changes in market share 13 Cost structure 14 Economies of scale in plant construction 15 16 The following model supporting the decision of when and how large a plant to construct should incorporate the following: 17 Product level analysis including units, price, unit costs 18 Plant capacity and capital expenditure (Capex) analysis explicitly recognizing economies of scale. 19 Strategic analysis including market sizing and market share analysis. . 20 Investment analysis of new plant construction 21 Sensitivity analysis of the results 22 23 This represents a simplified example of analyzing a business strategy that requires plant constructions. 24 We have simplified the problem by focusing only on Gross Margin and Capex 25 We have ignored depreciation, indirect operating expenses, non-aperating expenses, financing and taxes. 26 Market Size Model 28 The first step is to model the entire market. The company manufacturers two products (A and B) so each market must be modeled. . (A 29 Modeling the market requires establishing growth rates for each of the products. 30 Complete the model by entering formulas in the green cells below. 31 The exercise is designed such that an error early in the assignment will not adversely impact later grading 32 33 The market size of each product can be forecast based on grawth expectations. 34 Product A's growth will decelerate, declining linearly by .5% every year. 35 Product B's growth will be stable at 10% annually. 36 Hint: When financial analysts adjust a growth rate or market share you do not compound the adjustment. If the growth rate is 10% and it dedines by 3%, the adjusted growth rate is 7%. Please use this darification to understand how to forecast throughout this Sheet 37 38 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 39 Market Size Units 1.045 1,045 1,045 1,045 1,045 1.045 1,045 1,045 1.045 1,045 1,045 40 Product A 523 523 S23 523 523 523 523 523 523 523 523 41 Product B 523 523 523 523 523 523 523 523 523 523 523 42 43 Market Unit Growth Rate 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 44 Product A 10.0% 45 Products 10.0X 46 47 Company Sales Model 48 The sales of the company for each product can be calculated based on an outlook for its market share of the product. 49 The company's market share of Product A is expected to be flat. 50 The company's market share of Product B is expected to dedine by 5% this year because of a new entrant but will then remain at thereafter. 51 52 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 53 Market Share 54 Product A 55 Product B 60% 56 57 Company's Unit Sales 470 0 58 Product A 59 Product B 3141 60 30% O 0 0 0 O 0 O 0 0 157 2031 Cost 61 Capacity Expansion Model 62 Product A and B require the same amount of capacity to manufacture. That simplifies the analysis of capacity as we can measure capacity in 'units' of Product A and B as a common denominator. 63 The company will choose to deploy scarce capacity on the most profitable products. 64 The price of Product A is $110 and the price of Product Bis $100. 65 Therefore, the company will manufacture Product A before Product B. 66 The company currently has 500 units of capacity. 67 The Unit Sales Given Capacity Constraints forecast of sales is based on the scenario that no capacity is added. 68 Write formulas in the green boxes below to calculate how many units of each product would be sold. 69 Hint: You will need to use a MIN/MAX/IF function. 70 71 2022 2023 2024 2025 2026 2027 2028 2029 2030 72 Unit Sales Given 500 units of Capacity 73 Product A 74 Product B 75 76 The company will have to add capacity over the next decade to satisfy demand. 77 78 Economies of Scale in Plant Construction Capital Operating Capacity Expenditure Capex Scale Unit Operating Scale 79 Expansion (Units) (CapEx) CapEx/Unit Economies Economies 80 $35,000 $350 $50 $45,000 $300 -14% $45 -10% 82 300 $75,000 $250 -29% $40 -20% 83 84 The above cost of Plant Construction demonstrates significant 'economies of scale'. 85 Economies of scale, enjoying a lower unit cost for a larger scale operation or investment, is common. 86 Notice how the Capex/Unit declines from $350 to $250 as the number of units of capacity added increases. 87 Furthermore the Unit Operating cost declines from $50 to $40 as the number of units of capacity added increases. 88 Software development affords the ultimate level of economies of scale as once software is developed there is absolutely no additional cost for additional use. 89 This is true for all intellectual property including entertainment content and is almost valid for pharmaceutical and other medical technologies. 90 The model permits users to select the size of Capacity Expansion in Units 91 Capacity Expansion (Units) 150 92 Write a formula using an IF (conditional) function in the green box below that selects the unit cost based on the Capacity Expansion above. 93 Unit Operating cost 94 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. 95 Write formulas in row 102 such that in every year prior to a year in which adequate capacity would not otherwise exist capacity is expanded by the quantity selected in 191. 96 Note : Additional Capacity becomes available in the year after the investment is made 97 Calculate the Incremental Unit Sales as the Company's Unit Sales minus the Capacity Constrained Unit Sales 98 100 81 150 $45,000 78 Economies of Scale in Plant Construction Capital Operating Capacity Expenditure CapEx Scale Unit Operating Scale 79 Expansion (Units) (Capex) CapEx/ Unit Economies Cost Economies 80 100 $35,000 $350 $50 81 150 $300 -14% $45 -10% 82 300 $75,000 $250 -29% $40 -20% 83 84 The above Cost of Plant Construction demonstrates significant 'economies of scale'. 85 Economies of scale, enjoying a lower unit cost for a larger scale operation or investment is common. 86 Notice how the Capex/Unit declines from $350 to $250 as the number of units of capacity added increases. 87 Furthermore the Unit Operating Cost declines from $50 to $40 as the number of units of capacity added increases. 88 Software development affords the ultimate level of economies of scale as once software is developed there is absolutely no additional cost for additional use. 89 This is true for all intellectual property including entertainment content and is almost valid for pharmaceutical and other medical technologies. 90 The model permits users to select the size of Capacity Expansion in Units 91 Capacity Expansion (Units) ( 150 92 Write a formula using an IF (conditional) function in the green box below that selects the unit cost based on the Capacity Expansion above. 93 Unit Operating Cost 94 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. 95 Write formulas in row 102 such that in every year prior to a year in which adequate capacity would not otherwise exist capacity is expanded by the quantity selected in D91. a 96 Note: Additional Capacity becomes available in the year after the investment is made. 97 Calculate the incremental Unit Sales as the Company's Unit Sales minus the Capacity Constrained Unit Sales. 98 99 2021 2022 2023 2024 2025 2026 2027 2028 100 Capacity 500 500 500 500 500 500 500 500 101 102 Capacity Expansion (Units) 103 104 Cumulative Additional Capacity 0 0 0 0 0 0 0 105 106 Incremental Unit Sales 107 Product A 108 Product B 109 2029 500 2030 500 2031 500 0 0 0