please solve using the tahoe salt model provided. use excel and show ALL STEPS/ EQUATIONS. I have posted this question multiple times and no one has answered would be nice to actually get some help. thanks.

CASE STUDY Specialty Packaging Corporation Julie Williams had a lot on her mind when she left the form the sheet into containers and trim the containers conference room at Specialty Packaging Corporation from the sheet. The two manufacturing steps are shown (SPC). Her divisional manager had informed her that in Figure 7-11. she would be assigned to a team consisting of SPC's Over the past five years, the plastic packaging marketing vice president and staff members from their business has grown steadily. Demand for containers key customers. The goal of this team was to improve made from clear plastic comes from grocery stores, bak- supply chain performance, as SPC had been unable to eries, and restaurants. Caterers and grocery stores use meet demand effectively over the previous several the black plastic trays as packaging and serving trays. years. This often left SPC's customers scrambling to Demand for clear plastic containers peaks in the summer meet new client demands. Julie had little contact with months, whereas demand for black plastic containers SPC's customers and wondered how she would add peaks in the fall. Capacity on the extruders is not suffi- value to this process. She was told by her division man- cient to cover demand for sheets during the peak sea- ager that the team's first task was to establish a collab- sons. As a result, the plant is forced to build inventory of orative forecast using data from both SPC and its each type of sheet in anticipation of future demand. customers. This forecast would serve as the basis for Table 7-4 and Figure 7-12 display historical quarterly improving the firm's performance, as managers could demand for each of the two types of containers (clear use this more accurate forecast for their production and black). The team modified SPC's sales data by planning. Improved forecasts would allow SPC to accounting for lost sales to obtain true demand data. improve delivery performance. Without the customers involved in this team, SPC would never have known this information, as the company did not keep track of lost orders. SPC SPC turns polystyrene resin into recyclable/disposable Forecasting containers for the food industry. Polystyrene is pur- chased as a commodity in the form of resin pellets. The As a first step in the team's decision making, it wants to resin is unloaded from bulk rail containers or overland forecast quarterly demand for each of the two types of trailers into storage silos. Making the food containers containers for years 6 to 8. Based on historical trends, is a two-step process. First, resin is conveyed to an demand is expected to continue to grow until year 8. extruder, which converts it into a polystyrene sheet that after which it is expected to plateau. Julie must select the is wound into rolls. The plastic comes in two forms appropriate forecasting method and estimate the likely clear and black. The rolls are either used immediately forecast error. Which method should she choose? Why? to make containers or put into storage. Second, the Using the method selected, forecast demand for years 6 rolls are loaded onto thermoforming presses, which to 8. Step 1 Step 2 Thermo- forming Press Resin Extruder Roll Storage Storage FIGURE 7-11 Manufacturing Process at SPC Table 7.1 Quarterly Demand for Tahoe Salt Period Demand Year, Qtr 06,2 1 8,000 06,3 2 13,000 06,4 3 23,000 07,1 4 34,000 5 10,000 07.3 6 18,000 07.4 7 23,000 08.1 8 38,000 08,2 9 12,000 08,3 10 13,000 08,4 11 32,000 09.1 12 41,000 07,2 Figure 7.1 Quarterly Demand at Tahoe Salt Demand 45,000 40,000 35,000 30,000 25,000 20.000 15,000 10,000 5,000 0 062 06,3 06,4 071 072 073 07.4 08.1 082 083 08.4 091 Year, Quarter M Figure 7.3 Deseasonalized Demand for Tahoe Salt 65.000 10.000 1 Figure 72 Deseasonalized Demand for Tahoe Salt Period Demand Deseasonalized 2 + D Demand 1 8.000 4 2 13,000 5 3 23.000 19.750 6 4 34.000 20.025 7 5 10,000 21,250 6 18,000 21.750 7 23,000 22.500 10 38.000 22,125 11 12,000 22,625 12 10 13,000 24 125 13 11 32 000 14 12 41 000 15 10 17 18 19 20 Equation 72 Deseasonalizing Demand 25 30 000 25,000 20 000 puwag AA 1000 5000 0 Period, Desenlied Demand ---Demand View Datain Chart Figure 73) 23 24 D Dar+ Dura+ "22D)/(2p) for peven SD/p for podd 25 2) pa 27 28 where 20 D Demand 30 D periodicity ST 1 period 32 D E G H 1 REGRESSION SUMMARY OUTPUT 2 3 Regression Statistics 4 Multiple R 0.95806524 5 R Square 0.917889 6 Adjusted R Square 0.90420383 7 Standard Error 414.503312 Observations B 9 10 ANOVA 11 dr 12 Regression 1 13 Residual 6 14 Total 7 15 18 Coefficients 17 Intercept 18,439 18 X Variable 1 524 SS MS F 11523809.52 11523810 67 07182 1030877.976 171813 12554687.5 Significance F 0.000178609 Standard Error I Star P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% 440.8087079 41.82991 1.25E-08 17360.36726 19517.609 17360.3673 19517.8089 63.95924968 8.189738 0.000179367 3067633 680.31228367306753 580.312284 20 21 22 23 Initial Level, L Trend, T En 7.4 D En.) S % Error 11 8 24 MAPE, 11.8 71 54 1 Historical Data Deseasonalized Period Demand Demand 2 Di 3 1 8.000 18.963 2 13,000 19.487 5 3 23.000 20.011 4 34,000 20.535 7 5 10.000 21.059 B 6 18.000 21,583 7 23,000 22.107 10 8 38 000 22.631 9 12.000 23555 12 10 13.000 23079 13 11 92.000 24 203 14 12 41,000 24,722 15 16 Forecasted Data Forecasted Period Demand 17 Year Our 1 032 13 12010 19 033 17.614 20 034 15 20.767 21 04.1 44.642 Seasonal Factor Estimate En 7.5)S 0.42 0:47 0.267 1.15 1.17 1.56 1.66 0.47 0.83 1.04 1168 0,52 0.55 1.32 1:12 Absolute Error Forecast Error Er A 8.944 944 944 13.317 317 317 23.426 426 426 34.77 177 327 9933 -67 67 14,749 -3,251 3.251 25.679 2.879 2079 37 365 -335 335 10,921 -1.070 1.079 16.12 3,182 3.182 28.333 3,667 3.607 41153 153 155 Squared Error MSE 891,269 495,827 390,937 301.000 241.707 1.962,727 2866,661 2-522 358 2.371 392 34140403 4082.906 3,744,624 MAD, 944 630 562 466 388 864 1.152 1049 1053 1.266 1.484 1,373 0.5 0.7 18.1 12.5 09 9.0 24.5 11.5 0.4 BIAS, 944.1 1,260.9 1.686.5 1.8631 1.7958 -1455.0 14243 1.089.3 10.5 341920 475.0 TS, 1 2 3 4 5 -2 1 1 0 3.5 5.9 58 6.1 04 82 A5 78 0 0 F 22 23 24 25 26 27 28 29 310 31 32 33 34 45,000 40,000 35.000 30,000 25.000 20,000 15,000 10,000 Demand Di 45.000 40,000 35,000 30.000 25.000 20.000 15,000 10,000 5.000 N " Demand DI Did En 14) 0 O 36 37 10 11 12 NA D E F G H K Tahoe Salt Forecasts Using Four-Period Moving Average Absolute Mean Squared Period Demand Level Forecast Error Error Error t D. 4 F E A MSE, MAD, % Error MAPE, TS 1 8,000 2 13,000 3 23,000 4 34,000 19.500 5 10,000 20,000 19,500 9,500 9.500 90,250.000 9,500 95 95 1.00 6 18,000 21.250 20.000 2.000 2,000 47,125.000 5,750 11 53 7 23,000 2.00 21,250 21,250 -1.750 1,750 32.437,500 4,417 8 38 2.21 8 38.000 22,250 21,250 -16.750 16,750 94.468.750 7,500 39 -0.93 9 12,000 22.750 22,250 10.250 10.250 96,587,500 8,050 85 49 10 13,000 0.40 21,500 22,750 9.750 9,750 96,333,333 8,333 75 53 11 32,000 23,750 1.56 21.500 -10,500 10,500 98,321,429 8,643 33 50 12 41,000 0.29 24,500 23,750 -17250 17,250 123,226,563 9,719 42 +49 13 -1.52 24,500 5 24.500 27 15 24,500 B 16 24,500 9 0 1 45,000 2 40,000 3 35,000 30,000 Demand 25 25,000 DI 26 20,000 27 15,000 Forecast 28 10,000 FE 5,000 29 0 30 0 31 10 15 14 Tahoe SilIt Forecasts Using Simple Exponential Smoothing Absolute Error Periodt Demand D, Level L Forecast F Error E A Mean Squared Error MSEMAD % Error MAPE, TS, 3 0 22,083 1 8,000 19,267 22,083 14,083 14,083 198,340.278 14,083 176 5 176 1.00 2 13,000 18,013 19,267 6,267 6.267 118,805 694 10 175 48 112 3 2.00 23,000 19,011 18,013 4.987 4,987 87,492.744 8.446 22 4 34.000 1.82 22,009 19.011 -14,989 14,989 121.789,587 44 8 10.082 5 0.04 73 10,000 19,607 22,009 12,009 12.009 126,272,844 10,467 120 9 6 82 1.18 18,000 19.285 19,607 1,607 1,607 105,657,519 8,990 In 9 10 7 70 1.56 23,000 20.028 19,285 3.715 3.715 92,534,701 1 8.237 16 8 1.25 38.000 23,623 20,028 -17,972 17,972 121340,303 9 9,453 12 47 60 -0.81 12.000 21,298 23,623 11,623 11,623 122,867,719 9.694 97 13 10 13,000 19.639 21,298 8,298 0.40 8,298 117,466,887 54 9,555 64 11 64 1.28 32,000 22.111 19,639 -12.361 12,361 120,679,539 9,810 15 39 12 62 0.01 41,000 25,889 22.111 -18,889 18,889 140.356, 338 10,567 46 16 61 -1.80 25,889 11.847 13208 17 25,889 18 25,889 19 25,889 21 alpha 0.2 22 45,000 23 40,000 24 35,000 25 30,000 26 25,000 Demand 27 20,000 28 15,000 Forec 10,000 29 5,000 30 0 31 1 2 3 7 8 9 10 11 12 32 b t 5 6 FINO K Forecasts Using Trend-Corrected Exponential Smoothing (Holt's Model) Trend Absolute Error Period t Demand D Level L T Forecast Error E A Mean Squared Error MSEMAD, % Error MAPE, TS 0 12,015 1.549 1 8,000 13,008 1,438 13,564 5,564 5.564 30,958,096 5,564 70 70 2 13,000 14 301 1,409 14,445 1,445 1.445 16,523,523 3,505 11 40 3 23,000 16,439 1,555 15,710 -7,290 7,290 28.732,318 4,767 32 37 4 34,000 19,594 1.875 17,993 -16.007 16,007 85,603,146 7,577 47 5 20,322 39.86 10,000 -2.15 1,645 21,469 11,469 11,469 94,788,701 8,355 115 6 18,000 54.83 -0.58 21.570 1.566 21,967 3,967 3,967 81,613,705 7,624 22 49.36 -0.11 7 23,000 23,123 1,563 23,137 137 137 69,957.267 6,554 1 8 42.39 38,000 26,018 -0.11 1,830 24,686 -13,314 13,314 83 369,836 7,399 35 9 41.48 -1.90 12.000 26,262 1,513 27 847 15,847 15,847 102,010,079 8.338 132 51.54 0.22 10 13,000 26.29B 1,217 27,775 14,775 14.775 113,639,348 8,981 114 57.75 11 32.000 27,963 1,307 1.85 27,515 4,485 4,485 105,137 395 8.573 14 41,000 53.78 12 1.41 30,443 1,541 29.270 -11,730 11.730 107.841.864 8,836 29 51.68 0.04 31,985 33,526 35,067 36,609 alpha 0.1 Beta 0.2 45,000 40,000 35,000 30,000 25,000 Demand 20,000 15,000 Forecast 10,000 5,000 0 1 2 3 4 5 6 7 8 9 10 11 12 ELE - I Table 7.1 Fiona HOLTS MIDDEL REGRESSION SUMMARY OUTPUT Regression Statistics Multiple R 0.4813272 R Square 0.2316759 Adjusted R 0.1548435 Standard E 10666.883 Observatio 12 ANOVA dr Regression Residual Total SS MS 343092657.3 343092657.3 1137824009 113782400.9 1480916667 1 10 11 F fignificance F 3.01534 0.113127 Intercept X Variable Coefficients Standard Error 12.015 6565.012894 1,549 892.0095994 Stat P-value Lower 95% Upper 95% .ower 95.0%Jpper 95.0% 1.830179424 0.097147 -2612.611 26642.91 2612.611 26642.91 1.73647352 0.113127 438.5705 3536.473 438.5705 3536.473 b. estimate of demand and lovel at t=0 a, estimate of trend at to Forecasts Using Trend and Seasonality Corrected Exponential Smoothing) Deseasonalizing Demand Deseasonalized Deseasonalized Period Demand Demand Demand Seasonal Factor Estimate + (En 7.2) DI (Eqn 7.3) D. (Egn 7.5) S. (Egn 7.6) S 1 8,000 18,963 0.42 0.47 2 13,000 19,487 0.67 0.68 3 23,000 19,750 20.010 1.15 4 34,000 1.17 20,625 20,534 1.66 1.66 5 10,000 21,250 21.058 0.47 6 18,000 21,750 21,582 0.83 7 23,000 22,500 22,106 1.04 8 38,000 22.125 22,629 1.68 12.000 22,625 23,153 0.52 3 10 13,000 24,125 23,677 0.55 11 32,000 24,201 1.32 12 41,000 24,725 1.66 8 7 8 45,000 40,000 CO 35,000 1 30,000 22 25,000 Demand 23 20,000 24 15,000 -Deseasonized 25 10,000 Demand 26 5,000 0 27 28 5 7 9 11 29 30 31 5 9 3 9 WINTER'S MODEL REGRESSION SUMMARY OUTPUT E F G H Regression Statistics Multiple R 0.9580652 R Square 0.917889 Adjusted R Squar 0.9042038 Standard Error 414.50331 Observations 8 ANOVA df Regression Residual Total SS MS F iignificance F 1 11523809.5 11523809.5 67.07182 0.000179 6 1030877.98 171812.996 7 12554687.5 Intercept X Variable 1 Coefficients Standard Erdi Staf P-value Lower 95% Upper 95%-ower 95.0%ppor 95.0% 18,439 440.808708 41.8299089 1.25E-08 17360.37 195 17.61 17360.37 19517.61 524 63.9592497 8.18973841 0.000179 367.3068 680.3123 367.3068 680.3123 Initial estimate of level Initial estimate of trend M MAD, % Error MAPE, TS Forecasts Using Trend and Seasonality Corrected Exponential Smoothing Winter's Model) Demand Absolute Error 2 Period Mean Squared Level L Trend T. Seasonal Factor Forecast Error E Error MSE 0 18,439 524 4 1 8,000 18 866 514 0.47 8.913 912 913 5 2 832 857 13.000 19.367 513 0.68 13.379 179 179 LE 3 23.000 1969 512 432367 23260 260 7. 4 34.000 260 20,380 512 310.720 1.67 34030 36 36 8 5 10.000 20.921 233364 515 0.47 9.722 277 227 19.000 20.689 540 202036 0.68 14558 10 3442 2442 23.00022102 7 2143,255 527 25,081 38.000 22.636 2.981 528 3.106.500 1.67 37 TNT 213 12 12 000 23 291 2723756 0467 10,819 13 1190 2.571653 10 12.000 23577 515 0.69 16.544 3544 3644 11 32 000 3578.94 24 275 533 22049 4.151 4.151 16 4.618.250 12 41.000 24291 522 41 442 442 4.432,307 16 13 0.47 11.40 17 14 06 11 17070 15 1:17 30030 TD 16 44020 20 21 h 0 22 Beta 0.1 50,000 2 Cara 90 45.000 24 40.000 35.000 20 30 000 25.000 Cerrand 20.000 Forecast 20 15,000 30 10,000 5,000 0 913 11 546 1 450 1 347 O 333 3 851 19 1.155 7.037 1 1,054 10 1303 27 1.502 1 1403 1 11.41 1.00 6.39 2.00 4.64 3.00 3.50 4.00 3.30 3.34 5.38 -2.74 08 0.50 6.18 042 -0.72 8.66 22.10 -0.87 . un Taha 1 Forecast Errors for Tahoe Salt Forecasting 2 3 4 Forecasting Method MAD MAPE(%) 5 Four-period moving average 9,719 49 6 Simple exponential smoothing 10,567 61 7 Holt's model 8,836 52 8 Winter's model 1,469 8 9 10 TS Range Min Max -1.52 2.21 -1.80 2.00 -2.15 2.00 -2.74 4.00 This is a challenging case study where you have to select an appropriate forecasting method and use the method to forecast the demand for quarters I, II, III and IV for years 6 to 8. Instead of starting from scratch, I recommend that you modify the Tahoe Salt excel model. The text explains in detail how different forecasting methods were applied to the Tahoe Salt. Read the text before modifying the Tahoe Salt file. For your convenience, a copy of the Tahoe Salt excel file has been uploaded in this module