how would this question be modeled in excel using @risk?

1. VidoCo Demand Forecast Information 1 Sharing Duygu Dagli, Turnkey Vacation Rentals zalp zer, University of Texas at Dallas Yanchong Zheng, Massachusetts Institute of Technology BACKGROUND Demand forecasting and forecast information affect key operational and strategic decisions in managing global supply chains. Companies often face long lead times in procuring materials and production. Hence, they rely on demand forecasts to determine production capacity and inventory stocking levels. As a result, obtaining accurate demand forecasts is critical to meet the final market demand. Typically, when a company is closer to the end customer (e.g., consumer-facing companies such as Apple and Nike, and retailers such as Walmart and Best Buy), the company can gain more information about the market demand and hence is able to generate more accurate demand forecasts. Therefore, an upstream supplier usually relies on demand forecasts provided by a downstream company to plan its production Realizing the importance of demand forecast sharing in a supply chain, companies such as General Motors, Procter & Gamble, and Neiman Mar- cus have invested heavily in deploying information management systems within their global supply chains to better coordinate with upstream sup- pliers. The development of initiatives such as electronic data interchange (EDI) and collaborative planning, forecasting, and replenishment (CPFR) has offered companies technological solutions for sharing demand fore- cast information. However, the effectiveness of such initiatives remains uncertain because these technologies facilitate information sharing but do not necessarily ensure the shared information to be credible or accurate. Companies continue to face critical challenges to enable effective forecast information sharing with their supply chain partners. VIDOCO AND TECHNOMART In June 2012, VidoCo, a medium-sized company specializing in webcams, reached a distribution/sales agreement with TechnoMart, one of the largest consumer electronics retailers in the United States, to sell high- definition (HD) webcams. The retail sales would commence in July 2012. Five months after the contract was signed, VidoCo started to face a dra- matic challenge of determining the correct production volume to fulfill the retail demand in upcoming months. VidoCo was established in China in 2002 as a medium-sized company that supplies webcams for local laptop computer manufacturers. VidoCo's focus on research and development and high-quality production, coupled with the rapid growth of the Internet during the mid-2000s (Exhibit 1.1), helped it to become a major supplier of webcams in China. In 2009, VidoCo announced its strategic plan to introduce stand-alone HD Bluetooth webcams into its product lines and expand to the global mar- ket, in particular North America. The plan had the vision of gaining higher profit margins and reaching a wider customer base. VidoCo intro- duced its first HD Bluetooth webcam models in March 2012. Its HD product line has 12 different webcam models that differ from each other in terms of the product design. Apart from design, the same components and a similar bill of materials are used to produce these webcams. VidoCo's research and development (R&D) team follows the technological developments closely and recommends upgrades. Hence, VidoCo has to change the lens or the electronic components of all its products once a new technology emerges. On average, the product life cycle of a particular model is 18 months. VidoCo's product samples were demonstrated in various technology fairs, including the one that led to the contract with TechnoMart. TechnoMart is a consumer electronics retailer with more than 640 stores in the United States. Its brick-and-mortar retail stores and online store generated an annual profit of $5.23 billion in 2012. Its merchandise in- the procurement process. Under this single-price contract, TechnoMart will pay VidoCo a fixed wholesale price for each unit of webcams it pur- chases regardless of the total purchase quantity. To start production quickly and catch the upcoming holiday season, the Sales Department at VidoCo accepted the contract promptly and requested a manufacturer's suggested retail price (MSRP) to be $60 because the company targets the high-end market with its high-quality products. Eventually, both firms agreed that TechnoMart would sell the webcam at a per-unit retail price of $75, and pay VidoCo a wholesale price of $45 for each unit in the final or- der. This outcome was a profitable deal for VidoCo because its production cost was $28 per unit. TechnoMart's representatives requested that they first conduct a market analysis for HD webcams before committing to the initial order quantity. The U.S. consumer electronics retail industry is highly competitive. Even though specialized retailers, such as TechnoMart, dominate the consumer electronics sales (69%), grocery retailers and Internet retailers capture 20% of the market. The majority of customers have low loyalty to brands or stores, and they are interested in innovative and attractively priced products. As a result, the retailers have been competing through deep discounts, bundling popular items with complementary products or services (such as installations or extended warranties), introducing new products into their portfolios, and matching their prices with the competitors'. The Marketing and Sales (M&S) Department of TechnoMart estimated that the demand for the new webcams could be any number between 5,500 and 8,600 units per month, equally likely. Because TechnoMart cannot predict its competitors' pricing and advertising strategies for the upcoming months, the M&S Department estimated that there would be an unpredictable demand variation added to the estimate. This additional demand variation is likely to be uniformly distributed between -850 and 850. Given these estimates, the lowest and highest possible demand for the webcam would be 4,650 and 9,450 per month. Jason McDonell and Sheng Fang, the brand manager of VidoCo, met at TechnoMart's headquarters in Dallas, Texas, in June 2012. During the meeting, Jason presented their first demand estimate and explained TechnoMart's sales strategy for VidoCo's webcams. Each month, Techno- Mart offers special discounts for particular desktops, laptops, and net- books. Which computers will be discounted is determined based on TechnoMart's inventory levels for the particular products and its agree- ments with suppliers. Because demand shifts to the computers on dis- count, TechnoMart would offer complementary products, such as web- cams, speakers, keyboards, and mouses with the computers. TechnoMart would select the webcam that matches the discounted computer and in- form VidoCo in advance of the selected webcam that it would order. Given the wide variety of HD webcam models and their short life cycles, Jason commented that a particular model would be selected only once, Jason also mentioned that TechnoMart is particularly careful about supply lead times and inventory levels. The company maintains high product availab- ility and avoids stockouts. To help its suppliers to better plan for produc- tion and shipments, TechnoMart's policy with its suppliers has been to communicate the company's demand forecasts for the upcoming three months on a monthly and rolling horizon basis. TechnoMart places a final order at the beginning of each month, and this final order must be ful- filled within 20 days. Jason pointed out that the company would only pur- chase the quantity indicated in the final order. TechnoMart also could not guarantee that the quantity in the final order would be the same as the forecasts sent earlier because market demand is variable. Being optimistic about the relationship and eager to catch the holiday season, Sheng, on behalf of VidoCo, accepted these terms. Shortly after Sheng returned to China, TechnoMart sent its first set of demand forecasts and the final or- der for July 2012. VidoCo fulfilled the first order from its inventory and started production for the coming months. 3 Five months later, Assistant Financial Manager Bo-Liu Ran of VidoCo no- ticed that inventory costs were rising. To resolve the issue, he organized a meeting with Brand Manager Sheng Fang, Assistant Supply Chain Man- ager Tao Li, and Assistant Production Manager Shi Dong. Bo-Liu started the meeting by voicing his concerns about inventory costs. Shi commented that VidoCo was accumulating raw materials and invent- Bo-Liu started the meeting by voicing his concerns about inventory costs. Shi commented that VidoCo was accumulating raw materials and invent- ories of webcams because of frequent changes in the orders placed by the Sales Department. Sheng Fang, the only representative from the Sales De- partment, presented the demand forecasts and orders received from TechnoMart (Exhibits 1.2 and 1.3). After investigating the data, all four managers noticed that the forecasts received have been substantially higher than the quantities in the final orders. Shi came up with a suggestion: The forecasts received from TechnoMart were obviously misleading, so it might be better if VidoCo generated its own forecasts. Bo-Liu then suggested delaying the production until after receiving the final order. To respond, Tao provided the list of suppliers and the current supply lead times for all of the components (Exhibit 1.4). Shi added that VidoCo's assembly, quality control, and packaging pro- cesses took 5 hours and the plant had the capacity to produce at most 300 units per day. Sheng wondered whether VidoCo could change its suppliers to shorten the component supply lead times. Tao stated that the current suppliers satisfied the quality requirements at very reasonable prices. To do so, the suppliers have substantially reduced production and replenish- ment lead times. Requesting a further reduction in lead time may lead to higher procurement prices or lower production quality due to rush orders. EXHIBITS Exhibit 1.1 Internet Users as Percentage of Population 90 80 70 60 50 Percentage of Population 40 30 20 10 0 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 China 0 0 0 0 0 0 0 0 0.2 0.7 1.8 2.7 4.66.2 7.3 8.6 11 16 23 29 34 38 -US 0.8 1.2 1.72.3 4.9 9.2 16 22 30 36 43 49 59 | 62 65 68 69 75 74 71 74 78 Data source: http://data.worldbank.org (http://data.worldbank.org) Exhibit 1.2 Forecasts and Orders Received from TechnoMart February 2013 July August September October November December January 2012 2012 2012 2012 2012 2012 2013 TID 127-43 HID 190- BHD 192-BS HD 121-LP HD 410-F TD 115.SORD 1-3 6.100 5,750 6400 5.700 8.000 7450 7,200 Mole Palonder Para recebe HD S-112 Exhibit 1.2 Forecasts and Orders Received from TechnoMart February 2013 Model July August September October November December January 2019 2012 2012 2012 2019 2012 2013 HD 127.43 FD 190-HD 192-18 TID 181-LP HD 410-F HD 118-50 ID 197-43 6.100 6.790 6,400 3.700 8.00 7.200 7450 7,200 HD 08-112 7.000 7.350 5.400 Final order Forecasts received to July Forecast Teed In August Forecasts received in September Forecasts received in October Forecasts received in November 6.300 8.150 7.300 8.200 7,700 6,400 7.650 6,500 7.100 Exhibit 1.3 Forecasts and Orders Received from TechnoMart 9000 8500 8000 7500 Quantity 7000 6500 6000 5500 5000 8000 7500 Quantity 7000 6500 6000 5500 5000 07, 2012 08, 2012 09, 2012 10, 2012 11, 2012 12, 2012 01, 2013 02, 2013 Final Order Forecasts Received in September Forecasts Received in October -Forecasts Received in July Forecasts Received in August - Forecasts Received in November Exhibit 1.4 List of Suppliers for HD Webcams Number of items Required per Product 1 Average Lead Time (Days) 38 A144 lens OCD Matic frame 11-ochi Supplier Cost (per Unit) Kunto C., Inc $5.00 Qualibit Co. $2.00 Plasty Plastrons Co 31.00 Pape Co. 0.10 1 1 4 14 8 LED 1 30.30 Shy Thing Digitals Vedon Co. Bluetooth 1 30.00 Miame 1 1 01 Nolypeco Ihana 16 Alphaber Co. Inc OL TechnoMart's demand in February 2013. You first need to come up with a likely demand distribution based on the information provided in the case. There are many possible distributions that are reasonable here, so you should justify your selection in the memo accompanying your analysis. To make things easier for your optimization, you can feel free to consider only possible order quantities that are divisible by 100 (i.e., 4600, 4700, etc.). I would like you to define "optimality" here as the order quantity that maximizes the expected monthly profit, which makes sense because conceivably VidoCo would be using this same methodology each month. 1. VidoCo Demand Forecast Information 1 Sharing Duygu Dagli, Turnkey Vacation Rentals zalp zer, University of Texas at Dallas Yanchong Zheng, Massachusetts Institute of Technology BACKGROUND Demand forecasting and forecast information affect key operational and strategic decisions in managing global supply chains. Companies often face long lead times in procuring materials and production. Hence, they rely on demand forecasts to determine production capacity and inventory stocking levels. As a result, obtaining accurate demand forecasts is critical to meet the final market demand. Typically, when a company is closer to the end customer (e.g., consumer-facing companies such as Apple and Nike, and retailers such as Walmart and Best Buy), the company can gain more information about the market demand and hence is able to generate more accurate demand forecasts. Therefore, an upstream supplier usually relies on demand forecasts provided by a downstream company to plan its production Realizing the importance of demand forecast sharing in a supply chain, companies such as General Motors, Procter & Gamble, and Neiman Mar- cus have invested heavily in deploying information management systems within their global supply chains to better coordinate with upstream sup- pliers. The development of initiatives such as electronic data interchange (EDI) and collaborative planning, forecasting, and replenishment (CPFR) has offered companies technological solutions for sharing demand fore- cast information. However, the effectiveness of such initiatives remains uncertain because these technologies facilitate information sharing but do not necessarily ensure the shared information to be credible or accurate. Companies continue to face critical challenges to enable effective forecast information sharing with their supply chain partners. VIDOCO AND TECHNOMART In June 2012, VidoCo, a medium-sized company specializing in webcams, reached a distribution/sales agreement with TechnoMart, one of the largest consumer electronics retailers in the United States, to sell high- definition (HD) webcams. The retail sales would commence in July 2012. Five months after the contract was signed, VidoCo started to face a dra- matic challenge of determining the correct production volume to fulfill the retail demand in upcoming months. VidoCo was established in China in 2002 as a medium-sized company that supplies webcams for local laptop computer manufacturers. VidoCo's focus on research and development and high-quality production, coupled with the rapid growth of the Internet during the mid-2000s (Exhibit 1.1), helped it to become a major supplier of webcams in China. In 2009, VidoCo announced its strategic plan to introduce stand-alone HD Bluetooth webcams into its product lines and expand to the global mar- ket, in particular North America. The plan had the vision of gaining higher profit margins and reaching a wider customer base. VidoCo intro- duced its first HD Bluetooth webcam models in March 2012. Its HD product line has 12 different webcam models that differ from each other in terms of the product design. Apart from design, the same components and a similar bill of materials are used to produce these webcams. VidoCo's research and development (R&D) team follows the technological developments closely and recommends upgrades. Hence, VidoCo has to change the lens or the electronic components of all its products once a new technology emerges. On average, the product life cycle of a particular model is 18 months. VidoCo's product samples were demonstrated in various technology fairs, including the one that led to the contract with TechnoMart. TechnoMart is a consumer electronics retailer with more than 640 stores in the United States. Its brick-and-mortar retail stores and online store generated an annual profit of $5.23 billion in 2012. Its merchandise in- the procurement process. Under this single-price contract, TechnoMart will pay VidoCo a fixed wholesale price for each unit of webcams it pur- chases regardless of the total purchase quantity. To start production quickly and catch the upcoming holiday season, the Sales Department at VidoCo accepted the contract promptly and requested a manufacturer's suggested retail price (MSRP) to be $60 because the company targets the high-end market with its high-quality products. Eventually, both firms agreed that TechnoMart would sell the webcam at a per-unit retail price of $75, and pay VidoCo a wholesale price of $45 for each unit in the final or- der. This outcome was a profitable deal for VidoCo because its production cost was $28 per unit. TechnoMart's representatives requested that they first conduct a market analysis for HD webcams before committing to the initial order quantity. The U.S. consumer electronics retail industry is highly competitive. Even though specialized retailers, such as TechnoMart, dominate the consumer electronics sales (69%), grocery retailers and Internet retailers capture 20% of the market. The majority of customers have low loyalty to brands or stores, and they are interested in innovative and attractively priced products. As a result, the retailers have been competing through deep discounts, bundling popular items with complementary products or services (such as installations or extended warranties), introducing new products into their portfolios, and matching their prices with the competitors'. The Marketing and Sales (M&S) Department of TechnoMart estimated that the demand for the new webcams could be any number between 5,500 and 8,600 units per month, equally likely. Because TechnoMart cannot predict its competitors' pricing and advertising strategies for the upcoming months, the M&S Department estimated that there would be an unpredictable demand variation added to the estimate. This additional demand variation is likely to be uniformly distributed between -850 and 850. Given these estimates, the lowest and highest possible demand for the webcam would be 4,650 and 9,450 per month. Jason McDonell and Sheng Fang, the brand manager of VidoCo, met at TechnoMart's headquarters in Dallas, Texas, in June 2012. During the meeting, Jason presented their first demand estimate and explained TechnoMart's sales strategy for VidoCo's webcams. Each month, Techno- Mart offers special discounts for particular desktops, laptops, and net- books. Which computers will be discounted is determined based on TechnoMart's inventory levels for the particular products and its agree- ments with suppliers. Because demand shifts to the computers on dis- count, TechnoMart would offer complementary products, such as web- cams, speakers, keyboards, and mouses with the computers. TechnoMart would select the webcam that matches the discounted computer and in- form VidoCo in advance of the selected webcam that it would order. Given the wide variety of HD webcam models and their short life cycles, Jason commented that a particular model would be selected only once, Jason also mentioned that TechnoMart is particularly careful about supply lead times and inventory levels. The company maintains high product availab- ility and avoids stockouts. To help its suppliers to better plan for produc- tion and shipments, TechnoMart's policy with its suppliers has been to communicate the company's demand forecasts for the upcoming three months on a monthly and rolling horizon basis. TechnoMart places a final order at the beginning of each month, and this final order must be ful- filled within 20 days. Jason pointed out that the company would only pur- chase the quantity indicated in the final order. TechnoMart also could not guarantee that the quantity in the final order would be the same as the forecasts sent earlier because market demand is variable. Being optimistic about the relationship and eager to catch the holiday season, Sheng, on behalf of VidoCo, accepted these terms. Shortly after Sheng returned to China, TechnoMart sent its first set of demand forecasts and the final or- der for July 2012. VidoCo fulfilled the first order from its inventory and started production for the coming months. 3 Five months later, Assistant Financial Manager Bo-Liu Ran of VidoCo no- ticed that inventory costs were rising. To resolve the issue, he organized a meeting with Brand Manager Sheng Fang, Assistant Supply Chain Man- ager Tao Li, and Assistant Production Manager Shi Dong. Bo-Liu started the meeting by voicing his concerns about inventory costs. Shi commented that VidoCo was accumulating raw materials and invent- Bo-Liu started the meeting by voicing his concerns about inventory costs. Shi commented that VidoCo was accumulating raw materials and invent- ories of webcams because of frequent changes in the orders placed by the Sales Department. Sheng Fang, the only representative from the Sales De- partment, presented the demand forecasts and orders received from TechnoMart (Exhibits 1.2 and 1.3). After investigating the data, all four managers noticed that the forecasts received have been substantially higher than the quantities in the final orders. Shi came up with a suggestion: The forecasts received from TechnoMart were obviously misleading, so it might be better if VidoCo generated its own forecasts. Bo-Liu then suggested delaying the production until after receiving the final order. To respond, Tao provided the list of suppliers and the current supply lead times for all of the components (Exhibit 1.4). Shi added that VidoCo's assembly, quality control, and packaging pro- cesses took 5 hours and the plant had the capacity to produce at most 300 units per day. Sheng wondered whether VidoCo could change its suppliers to shorten the component supply lead times. Tao stated that the current suppliers satisfied the quality requirements at very reasonable prices. To do so, the suppliers have substantially reduced production and replenish- ment lead times. Requesting a further reduction in lead time may lead to higher procurement prices or lower production quality due to rush orders. EXHIBITS Exhibit 1.1 Internet Users as Percentage of Population 90 80 70 60 50 Percentage of Population 40 30 20 10 0 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 China 0 0 0 0 0 0 0 0 0.2 0.7 1.8 2.7 4.66.2 7.3 8.6 11 16 23 29 34 38 -US 0.8 1.2 1.72.3 4.9 9.2 16 22 30 36 43 49 59 | 62 65 68 69 75 74 71 74 78 Data source: http://data.worldbank.org (http://data.worldbank.org) Exhibit 1.2 Forecasts and Orders Received from TechnoMart February 2013 July August September October November December January 2012 2012 2012 2012 2012 2012 2013 TID 127-43 HID 190- BHD 192-BS HD 121-LP HD 410-F TD 115.SORD 1-3 6.100 5,750 6400 5.700 8.000 7450 7,200 Mole Palonder Para recebe HD S-112 Exhibit 1.2 Forecasts and Orders Received from TechnoMart February 2013 Model July August September October November December January 2019 2012 2012 2012 2019 2012 2013 HD 127.43 FD 190-HD 192-18 TID 181-LP HD 410-F HD 118-50 ID 197-43 6.100 6.790 6,400 3.700 8.00 7.200 7450 7,200 HD 08-112 7.000 7.350 5.400 Final order Forecasts received to July Forecast Teed In August Forecasts received in September Forecasts received in October Forecasts received in November 6.300 8.150 7.300 8.200 7,700 6,400 7.650 6,500 7.100 Exhibit 1.3 Forecasts and Orders Received from TechnoMart 9000 8500 8000 7500 Quantity 7000 6500 6000 5500 5000 8000 7500 Quantity 7000 6500 6000 5500 5000 07, 2012 08, 2012 09, 2012 10, 2012 11, 2012 12, 2012 01, 2013 02, 2013 Final Order Forecasts Received in September Forecasts Received in October -Forecasts Received in July Forecasts Received in August - Forecasts Received in November Exhibit 1.4 List of Suppliers for HD Webcams Number of items Required per Product 1 Average Lead Time (Days) 38 A144 lens OCD Matic frame 11-ochi Supplier Cost (per Unit) Kunto C., Inc $5.00 Qualibit Co. $2.00 Plasty Plastrons Co 31.00 Pape Co. 0.10 1 1 4 14 8 LED 1 30.30 Shy Thing Digitals Vedon Co. Bluetooth 1 30.00 Miame 1 1 01 Nolypeco Ihana 16 Alphaber Co. Inc OL TechnoMart's demand in February 2013. You first need to come up with a likely demand distribution based on the information provided in the case. There are many possible distributions that are reasonable here, so you should justify your selection in the memo accompanying your analysis. To make things easier for your optimization, you can feel free to consider only possible order quantities that are divisible by 100 (i.e., 4600, 4700, etc.). I would like you to define "optimality" here as the order quantity that maximizes the expected monthly profit, which makes sense because conceivably VidoCo would be using this same methodology each month