cannot be posted as a table
You are analyzing a company that sells replacement windows, its only product, both direct to consumers and through retailers. The company has only one competitor. The company has been rapidly losing market share in the direct segment because of the competitor's successful e-commerce strategy and aggressive pricing. Neither company reports financial data by product line or distribution channel (direct versus through retailers). To understand what is happening, you need to develop a model of both companies' historical results based on the limited information you have. A research firm did market research in both 2016 and 2019 to develope estimates of the market size and channel share. Use the CAGR to calculate the interim years (each year should increase by 1 + CAGR). Based on interviews in the field, you have estimated the split between direct and retail sales and the company's share of both the direct and retail markets. Average prices by channel are available from a price data information service. You have estimated the company has a unit cost 10% lower than the competitor because of its historically greater scale and consequent higher level of automation. Enter Excel formula in the yellow cells to create a model. 2017 2018 Industry Unit Model Estimated Total Market Size in Units Estimated Percent Direct Direct Market Size in Units Retail Market Size in Units 2016 600 33% 2019 799 58% CAGR 10% 20% Competitor 2018 2016 2017 2019 CAGR 2016 50% 70% 2017 40% 75% Company 2018 30% 80% 2019 20% 85% CAGR -26% 7% Company Unit Models Estimated Direct Market Share Estimated Retail Market Share Direct Units Sold Retail Units Sold Total Units Sold 2016 200 125 2017 200 125 2018 200 125 2019 200 125 CAGR 0% 0% 2016 190 125 2017 190 125 2018 190 125 2019 190 125 CAGR 0% 0% Revenues Model Price to Direct Price to Retailers Direct Revenues Retail Revenues Total Revenues 0% 2016 2017 2018 2019 0% 2016 100 2017 100 2018 100 2019 100 0% Gross Profit Model Unit Cost Direct Cost of Revenues Retail Cost of Revenues Direct Gross Profit Retail Gross Profit Total Gross Profit 2016 10% 5% 2017 10% 5% 2018 10% 5% 2019 10% 5% 0% 0% 2016 10% 5% 2017 10% 5% 2018 10% 5% 2019 10% 5% 0% 0% 0% 0% Operating Profit Model Direct SG&A Percent Retail SG&A Percent Direct SG&A Retail SG&A Direct EBIT Retail EBIT Total EBIT You are analyzing a company that sells replacement windows, its only product, both direct to consumers and through retailers. The company has only one competitor. The company has been rapidly losing market share in the direct segment because of the competitor's successful e-commerce strategy and aggressive pricing. Neither company reports financial data by product line or distribution channel (direct versus through retailers). To understand what is happening, you need to develop a model of both companies' historical results based on the limited information you have. A research firm did market research in both 2016 and 2019 to develope estimates of the market size and channel share. Use the CAGR to calculate the interim years (each year should increase by 1 + CAGR). Based on interviews in the field, you have estimated the split between direct and retail sales and the company's share of both the direct and retail markets. Average prices by channel are available from a price data information service. You have estimated the company has a unit cost 10% lower than the competitor because of its historically greater scale and consequent higher level of automation. Enter Excel formula in the yellow cells to create a model. 2017 2018 Industry Unit Model Estimated Total Market Size in Units Estimated Percent Direct Direct Market Size in Units Retail Market Size in Units 2016 600 33% 2019 799 58% CAGR 10% 20% Competitor 2018 2016 2017 2019 CAGR 2016 50% 70% 2017 40% 75% Company 2018 30% 80% 2019 20% 85% CAGR -26% 7% Company Unit Models Estimated Direct Market Share Estimated Retail Market Share Direct Units Sold Retail Units Sold Total Units Sold 2016 200 125 2017 200 125 2018 200 125 2019 200 125 CAGR 0% 0% 2016 190 125 2017 190 125 2018 190 125 2019 190 125 CAGR 0% 0% Revenues Model Price to Direct Price to Retailers Direct Revenues Retail Revenues Total Revenues 0% 2016 2017 2018 2019 0% 2016 100 2017 100 2018 100 2019 100 0% Gross Profit Model Unit Cost Direct Cost of Revenues Retail Cost of Revenues Direct Gross Profit Retail Gross Profit Total Gross Profit 2016 10% 5% 2017 10% 5% 2018 10% 5% 2019 10% 5% 0% 0% 2016 10% 5% 2017 10% 5% 2018 10% 5% 2019 10% 5% 0% 0% 0% 0% Operating Profit Model Direct SG&A Percent Retail SG&A Percent Direct SG&A Retail SG&A Direct EBIT Retail EBIT Total EBIT
