Solve the following problems. 1. The ABC distribution center sold the following number for pallets during the last two weeks. Day 1 2 3 4 5 6 7 Demand 107 121 117 111 94 99 104 Day 8 9 10 11 12 13 14 Demand 116 123 129 92 95 104 102 Develop a spreadsheet to do the following task: Compare the accuracy of the forecasts, for days 15 to 28, using alpha = 0.2 and alpha = 0.6. Explain which method is best. You need to show all the calculations and metrics as explained in class. 2. The famous manufacturing Shelvesthru company often uses exponential smoothing to forecast the demand for its best selling drivethru shelves. The company is now considering whether to use exponential smoothing forecast with alpha = 0.4 or causal forecasting (regression). Using the following data for daily sales to recommend which method is more accurate. You need to show all the calculations and metrics as explained in class. Day 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Demand 200 134 147 165 183 125 146 154 182 197 132 163 157 169 Simple Moving average Current Demand Moth 1 2 3 4 5 6 7 8 9 10 11 12 Forecasted Demand 1600 2200 2000 1600 2500 3500 3300 3200 3900 4700 4300 4400 5000 4500 4000 3500 3000 2500 2000 1500 1000 500 0 1850 2075 2400 2725 3125 3475 3775 4025 Column E Column F 1 2 3 4 5 6 7 8 9 10 11 12 Weighted Moving Average Moth 1 2 3 4 5 6 7 8 9 10 11 12 Current Forecasted Demand Demand 1600 2200 2000 1600 2500 1840 3500 2100 3300 2670 3200 3030 3900 3220 4700 3530 4300 4020 4400 4230 0.1 0.2 0.3 0.4 0.1 0.2 0.3 0.4 5000 4500 4000 3500 3000 2500 2000 1500 1000 500 0 0.1 0.2 0.3 0.4 Column E Column F 1 2 3 4 5 6 7 8 9 10 11 12 Linear Regression Moth 1 2 3 4 5 6 7 8 9 10 11 12 Slope Intercept ME MAE MAPE MSE U-Stat 1 255 491 0.17 338182 1 Forecasted Demand 1523.07692 1809.79021 2096.5035 2383.21678 2669.93007 2956.64336 3243.35664 3530.06993 3816.78322 4103.4965 4390.20979 4676.92308 5000 4500 4000 3500 3000 2500 2000 1500 1000 500 0 f(x) = 286.7132867133x + 1236.3636363636 Current Demand Forecasted Demand Linear (Forecasted Demand) 1 2 3 4 5 6 7 8 9 10 11 12 286.713287 1236.36364 Period Alpha Current Demand 1600 2200 2000 1600 2500 3500 3300 3200 3900 4700 4300 4400 Exponential Smoothing E-F ABS(V) H/E Demand Forecast Error ABS Error % Error 1 1600 1600 2 2200 1600 600 600 3 2000 2200 -200 200 4 1600 2000 -400 400 5 2500 1600 900 900 6 3500 2500 1000 1000 7 3300 3500 -200 200 8 3200 3300 -100 100 9 3900 3200 700 700 10 4700 3900 800 800 11 4300 4700 -400 400 12 4400 4300 100 100 G^2 Sq. Error 0.27 0.10 0.25 0.36 0.29 0.06 0.03 0.18 0.17 0.09 0.02 360000 40000 160000 810000 1000000 40000 10000 490000 640000 160000 10000 U-Stat 360000 40000 160000 810000 1000000 40000 10000 490000 640000 160000 10000 Running sum MAD 600 400 0 900 1900 1700 1600 2300 3100 2700 2800 600 400 400 525 620 550 486 513 544 530 491 1 1 0 2 3 3 3 4 6 5 6 https://www.youtube.com/watch?v=6P-qGU8EYmI 5000 4500 4000 3500 3000 2500 2000 1500 1000 500 0 Column E Column F 1 2 3 4 5 6 7 8 9 10 11 12 Forecast 100 104 104 104 100 103 100 100 103 102 103 104 101 99 98 101 98 98 96 95 100 100 101 99 Error ABS Err % Err Sq Err -1 1 -20 14 -13 0 13 -4 6 6 -16 -10 -3 15 -17 0 -11 -5 24 -1 6 -12 8 1 1 20 14 13 0 13 4 6 6 16 10 3 15 17 0 11 5 24 1 6 12 8 0.010 0.010 0.238 0.123 0.144 0.000 0.115 0.040 0.056 0.055 0.182 0.110 0.031 0.133 0.202 0.000 0.126 0.055 0.202 0.010 0.057 0.135 0.075 U-Stat 1 1 400 196 169 0 169 16 36 36 256 100 9 225 289 0 121 25 576 1 36 144 64 289 4 441 900 576 100 169 196 81 1 441 9 25 289 841 196 121 16 784 400 49 289 324 running sum -1 0 -20 -6 -19 -19 -6 -10 -4 2 -14 -24 -27 -12 -29 -29 -40 -45 -21 -22 -16 -28 -20 140 120 100 80 Column D Column E 60 40 20 oct dec nov sep aug 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 feb 8 dec jul jun apr mar jan oct 7 nov 6 may 5 sep 4 jul 3 aug 2 jun apr 1 may 0 mar 0.2 -1 9.0 0.09 125 0.66239779 Sales 120 103 105 84 114 90 100 113 99 108 109 88 91 96 113 84 98 87 91 119 99 106 89 107 jan Alpha ME MAE MAPE MSE U-Stat Month 1 jan 2 feb 3 mar 4 apr 5 may 6 jun 7 jul 8 aug 9 sep 10 oct 11 nov 12 dec 13 jan 14 feb 15 mar 16 apr 17 may 18 jun 19 jul 20 aug 21 sep 22 oct 23 nov 24 dec feb Period MAD 1 -1 1 0 7 -2.85714286 9 -0.66666667 5 -3.8 2 -9.5 2 -3 2 -5 1 -4 1 2 2 -7 2 -12 1 -27 1 -12 2 -14.5 1 -29 1 -40 1 -45 2 -10.5 1 -22 0 #DIV/0! 1 -28 1 -20