ulie Williams had a lot on her mind when she left the con- ference room at Specialty Packaging Corporation (SPC) Her divisional manager had informed her that she would be assigned to a team consisting of SPC's marketing vice president and staff members from their key customers Year Quarter Demand (000 lb) Demand ('000 lb) The goal of this team was to improve supply chain per formance, as SPC had been unable to meet demand effec- 2005 tively over the previous several years. This often left SPCS customers scrambling to meet new client demands. Julie had little contact with SPC's customers and wondered how she would add value to this process. She was told by her division manager that the team's first task was to establish a collaborative fore cast using data from both SPC and its customers, This forecast would serve as the basis for improving the firm's performance, as managers could use this more accurate forecast for their production planning.2007 Improved forecasts would allow SPC to improve delivery TABLE 7-4 Quarterly Historical Demand for Clear and Black Plastic Containers Black Plastic Clear Plastic 3,200 7,658 4,420 2.384 3,654 8,680 5,695 1,953 4,742 3,673 6,640 2,737 3,486 13,186 5,448 3,485 7,728 16,591 8,236 3.316 2,250 1,737 7.269 3,514 2,143 3,459 7,056 4,120 2,766 2,556 8,253 5,491 4,382 4,315 12,035 5,648 3,696 4,843 13,097 2006 IV SPC SPC tums polystyrene resin into recyclable/disposable con- tainers for the food industry. Polystyrene is purchased as a commodity in the form of resin pellets. The resin is unloaded from bulk rail containers or overland trailers into storage silos Making the food containers is a two-step 2009 process. First, resin is conveyed to an extruder, which con- verts it into polystyrene sheet wound into rolls. The plastic comes in two forms-clear and black. The rolls are either used immediately to make containers or are put into stor- age. Second, the rolls are loaded onto thermoforming presses, which form the sheet into containers and trim the containers from the sheet. The two manufacturing steps demand for sheets during the peak seasons. As a result are shown in Figure 7-9 2008 the plant is forced to build inventory of each type of sheet in anticipation of future demand. Table 7-4 and Over the past five years, the plastic packaging busi ness has grown steadily Demand for containers made Figure 7-10 display historical quarterly demand for from clear plastic comes from grocery stores, bakeries, each of the two types of containers (clear and black). The and restaurants Caterers and grocery stores use the black team modified SPC's sales data by accounting for lost plastic trays as packaging and serving trays. Demand for sales to obtain true demand data. Without the customers dlear plastic containers peaks in the summer months, involved in this team, SPC would never have known this whereas demand for black plastic containers peaks in the information, as the company did not keep track of lost fall Capacity on the extruders is not sufficient to cover orders Step 1 Step 2 forming Extruder Roll Storage Press FIGURE 7-9 Manufacturing Storage Process at SPC WHAT TO DO: As a first step in the team's decision making, you should forecast the quarterly demand for each of the tmo tupes of containers for the yeas 2010 to 2012. Based on historical trends, demand is expected to continue to grow until 2012, after which it is expected to plateau. Julie must select the appropriate forecasting method and estimate the likely forecast error. 0 Which method should she choose? Why? Ealain veuz ANTS n deails by oning in he EXCEL Ae suhmitted Hint Piot of the quartr demand (erur tine) for ciear and biack plazrtic containera wil heip you to determine on the forecating method Use the following methods to forecast quarterly demand for the years 2010 to 2012 in EXCEL YHAT TO SUB IT: One oftheteam memben should submit a SINGLE EXCEL file (0R xisx only) including all the TEAM MEMBERS' NAMESIDs in the file through the "IE341-Project2-Spring2019- EXCEL File Submission available under "Assignments" in Blackboard. Failure of submission will result in a 0 mark for the project. You ca use the Weekos-Forecasting xlx" (under "Course Material" n Blackboara as a tempiate for your anwer. Note that for parts (a), (b), (c), (a), (e) and C), you should have separate workskeets in the same e. No need to submit your anrwers az a printout (azI willony mark your EXCEL fle oniy) (5) Exponentia smoothing method: Assume that = 0.2 and Fi-Ai. Also after computing the forecast for 2010 Quarter I, assume that it becomes the actual demand, eg,AF This would allow you to compute the forecast for 2010 Quarter II. Similarly, for subsequent quarters assume that the actual demand is equal to the forecast for the previous quarter and compute the forecasts this way until 2012 quarter IV Hint: Iou should have the s forecast aue for all quarters of 2010 to 2012) Assume that =0.2, = 0.3, Si*Ai and Ti-o Also after computing the forecast for 2010 Quarter I, assume that it becomes the actual demand, eg, A-F This would allow you to compute the forecast for 2010 Quarter II. Similarly, for subsequent quarters assume that the actual demand is equal to the forecast for the previous quarter and compute the forecasts this way until 2012 quarter IV. Hot: You shovid have the same trend value for all quarters of 2010 to 2012) (use multiplicative model, e.g.,F-T+ S) (e) Given the 2010-2012 actual demand values below (use them for only error measures, not forecasting) Black Plastic Clear Plastic Year 2010 uarter Demand C000 Ib Demand 3873 5366 11823 17362 8136 3543 18272 8547 3746 3962 7976 13462 IV 2012 4675 5084 14033 19145 9036 3978 compute the: i. forecasting errors (MAD, MSE, MAPE) ii. standard error of forecast (Sr), Bias" and Tracking Signal", ii. also 90% confidence r range for the 2010-2012 forecasts (ie, using z90%-1.28). iv. Finally, optimize the value (for parts (b)and (c) onl by minimizing the standard error of forecast (%) and report your optimizedevalu in separate sheets (simply create copies of your worksheet for part (b) and (c), and use EXCEL solrer for optimizing a value) Note that 0.01s a s0.3). ulie Williams had a lot on her mind when she left the con- ference room at Specialty Packaging Corporation (SPC) Her divisional manager had informed her that she would be assigned to a team consisting of SPC's marketing vice president and staff members from their key customers Year Quarter Demand (000 lb) Demand ('000 lb) The goal of this team was to improve supply chain per formance, as SPC had been unable to meet demand effec- 2005 tively over the previous several years. This often left SPCS customers scrambling to meet new client demands. Julie had little contact with SPC's customers and wondered how she would add value to this process. She was told by her division manager that the team's first task was to establish a collaborative fore cast using data from both SPC and its customers, This forecast would serve as the basis for improving the firm's performance, as managers could use this more accurate forecast for their production planning.2007 Improved forecasts would allow SPC to improve delivery TABLE 7-4 Quarterly Historical Demand for Clear and Black Plastic Containers Black Plastic Clear Plastic 3,200 7,658 4,420 2.384 3,654 8,680 5,695 1,953 4,742 3,673 6,640 2,737 3,486 13,186 5,448 3,485 7,728 16,591 8,236 3.316 2,250 1,737 7.269 3,514 2,143 3,459 7,056 4,120 2,766 2,556 8,253 5,491 4,382 4,315 12,035 5,648 3,696 4,843 13,097 2006 IV SPC SPC tums polystyrene resin into recyclable/disposable con- tainers for the food industry. Polystyrene is purchased as a commodity in the form of resin pellets. The resin is unloaded from bulk rail containers or overland trailers into storage silos Making the food containers is a two-step 2009 process. First, resin is conveyed to an extruder, which con- verts it into polystyrene sheet wound into rolls. The plastic comes in two forms-clear and black. The rolls are either used immediately to make containers or are put into stor- age. Second, the rolls are loaded onto thermoforming presses, which form the sheet into containers and trim the containers from the sheet. The two manufacturing steps demand for sheets during the peak seasons. As a result are shown in Figure 7-9 2008 the plant is forced to build inventory of each type of sheet in anticipation of future demand. Table 7-4 and Over the past five years, the plastic packaging busi ness has grown steadily Demand for containers made Figure 7-10 display historical quarterly demand for from clear plastic comes from grocery stores, bakeries, each of the two types of containers (clear and black). The and restaurants Caterers and grocery stores use the black team modified SPC's sales data by accounting for lost plastic trays as packaging and serving trays. Demand for sales to obtain true demand data. Without the customers dlear plastic containers peaks in the summer months, involved in this team, SPC would never have known this whereas demand for black plastic containers peaks in the information, as the company did not keep track of lost fall Capacity on the extruders is not sufficient to cover orders Step 1 Step 2 forming Extruder Roll Storage Press FIGURE 7-9 Manufacturing Storage Process at SPC WHAT TO DO: As a first step in the team's decision making, you should forecast the quarterly demand for each of the tmo tupes of containers for the yeas 2010 to 2012. Based on historical trends, demand is expected to continue to grow until 2012, after which it is expected to plateau. Julie must select the appropriate forecasting method and estimate the likely forecast error. 0 Which method should she choose? Why? Ealain veuz ANTS n deails by oning in he EXCEL Ae suhmitted Hint Piot of the quartr demand (erur tine) for ciear and biack plazrtic containera wil heip you to determine on the forecating method Use the following methods to forecast quarterly demand for the years 2010 to 2012 in EXCEL YHAT TO SUB IT: One oftheteam memben should submit a SINGLE EXCEL file (0R xisx only) including all the TEAM MEMBERS' NAMESIDs in the file through the "IE341-Project2-Spring2019- EXCEL File Submission available under "Assignments" in Blackboard. Failure of submission will result in a 0 mark for the project. You ca use the Weekos-Forecasting xlx" (under "Course Material" n Blackboara as a tempiate for your anwer. Note that for parts (a), (b), (c), (a), (e) and C), you should have separate workskeets in the same e. No need to submit your anrwers az a printout (azI willony mark your EXCEL fle oniy) (5) Exponentia smoothing method: Assume that = 0.2 and Fi-Ai. Also after computing the forecast for 2010 Quarter I, assume that it becomes the actual demand, eg,AF This would allow you to compute the forecast for 2010 Quarter II. Similarly, for subsequent quarters assume that the actual demand is equal to the forecast for the previous quarter and compute the forecasts this way until 2012 quarter IV Hint: Iou should have the s forecast aue for all quarters of 2010 to 2012) Assume that =0.2, = 0.3, Si*Ai and Ti-o Also after computing the forecast for 2010 Quarter I, assume that it becomes the actual demand, eg, A-F This would allow you to compute the forecast for 2010 Quarter II. Similarly, for subsequent quarters assume that the actual demand is equal to the forecast for the previous quarter and compute the forecasts this way until 2012 quarter IV. Hot: You shovid have the same trend value for all quarters of 2010 to 2012) (use multiplicative model, e.g.,F-T+ S) (e) Given the 2010-2012 actual demand values below (use them for only error measures, not forecasting) Black Plastic Clear Plastic Year 2010 uarter Demand C000 Ib Demand 3873 5366 11823 17362 8136 3543 18272 8547 3746 3962 7976 13462 IV 2012 4675 5084 14033 19145 9036 3978 compute the: i. forecasting errors (MAD, MSE, MAPE) ii. standard error of forecast (Sr), Bias" and Tracking Signal", ii. also 90% confidence r range for the 2010-2012 forecasts (ie, using z90%-1.28). iv. Finally, optimize the value (for parts (b)and (c) onl by minimizing the standard error of forecast (%) and report your optimizedevalu in separate sheets (simply create copies of your worksheet for part (b) and (c), and use EXCEL solrer for optimizing a value) Note that 0.01s a s0.3)