Just to let you know the decline linearly for product A is 0.5% as told on D34. Don't mistake it for 5%. Cause someone did mistook it for 5% before. Thank you. 
A B D E F G H J K L M 2020 2028 2029 2026 538,200 159,600 378,600 2027 538,200 159,600 378,600 538,200 159,600 378,600 538,200 159,600 378,600 0% 0% 0% 0% 27 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. 29 Modeling the market requires establishing growth rates for each of the products. 30 Errors in the model will turn red. The exercise is designed such that an error early in the assignment will not adversely impact later grading. 31 Complete the model by entering formulas in the green cells below. 32 33 The market size of each product can be forecast based on growth expectations. 34 Product A's growth will decelerate, declining linearly by .5% every year. 35 Product B's growth will be stable at 5% annually. 36 37 2019 2021 2022 2023 2024 2025 38 Market Size Units 1,000 538,200 538,200 538,200 538,200 538,200 538,200 39 Product A 400 159,600 159,600 159,600 159,600 159,600 159,600 40 Product B 600 378,600 378,600 378,600 378,600 378,600 378,600 41 42 Market Growth in Units 53720% 0% 0% 0% 0% 0% 43 Product A 10.0% 39800.0% 44 Product B 5.0% 63000.0% 45 46 Company Sales Model 47 The sales of the company for each product can be calculated based on an outlook for its market share of the product. 48 The company's market share of Product A is expected to be flat. 49 The company's market share of Product B is expected to decline by 5% this year because of a new entrant but will then remain flat thereafter. 50 51 2019 2020 2021 2022 2023 2024 2025 52 Market Share 53 Product A 30% 54 Product B 60% 55 56 Company's Unit Sales 480 0 0 0 0 0 0 57 Product A 120 58 Product B 360 59 2026 2027 2028 2029 0 0 0 0 A B D E F G H J K L M 2025 2026 2027 2028 2029 46 Company Sales Model 47 The sales of the company for each product can be calculated based on an outlook for its market share of the product. 48 The company's market share of Product A is expected to be flat. 49 The company's market share of Product B is expected to decline by 5% this year because of a new entrant but will then remain flat thereafter. 50 51 2019 2020 2021 2022 2023 2024 52 Market Share 53 Product A 30% 54 Product B 60% 55 56 Company's Unit Sales 480 57 Product A 120 0 0 0 0 0 0 0 0 0 0 58 Product B 360 59 60 Capacity Expansion Model 61 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. 62 The company will choose to deploy scarce capacity on the most profitable products. 63 The price of Product A is $100 and the price of Product B is $90. 64 Therefore, the company will manufacture Product A before Product B. 65 The company currently has 500 units of capacity. 66 The Capacity Constrained forecast of sales is based on the scenario that no capacity is added. 67 Write formulas in the green boxes below to calculate how many units of each product would be sold. 68 Hint: You will need to use a MIN/MAX/IF function. 69 2020 2021 2022 70 2023 2024 2025 2026 2027 2028 71 Capacity Constrained Unit Sales 72 Product A 73 Product B 74 2029
