Question

Please answer each question and highlight each answer or the table.

Starbucks has a Isrge, global supply chain that must eficienty supply over 17,000 stores. Although the stores might appesr to be very similse, they are actualy very difierent. Depending on the locaion of the store, its size, and the prolile of the oustomers served, Starbucks management oonfigures the store offerings to take maimum advantage of the space available and customer preferenoes. Starbucks' actual distribution system is much more complex, but for the purpose of our exercise lef's focus on a single hem that is currently distributed through five distribution centers in the United States. Our item is s logo branded coffeemaker that is sold at some of the lasger retail stores. The cofleemaker has been a stesdy seller aver the years due to its relisbility and nugged construction. Starbucks does not cpnsider this a seasonal product, but there is scme variablity in demand. Demand for the product over the past 18 weeks is shown in the folowing table (week-1 is the week belore week 1 in the tsble,-2 is two weeks before week 1, etc). Management would like you to experiment with some forecasting models to determine what shoud be used in a new system to be implemented. The new system is programmed to use one of two forecasting models simple moving average or exponentisl smoothing WEEK -3 2 -1 2 30 12 3 5 6 10 11 24 36 13 41 45 35 20 46 Adanta 45 36 30 56 36 30 54 28 58 21 27 Chcago Dalas LA 71 45 47 43 45 33 53 4P 38 50 36 42 64 48 40 40 44 29 42 34 40 44 62 40 46 42 34 36 34 196 167 36 174 14 52 42 196 72 246 40 4 48 48 150 180 251 243 247 20e 243 282 203 227 256 247 230 Total a. Consider using a simple exponential smoothing model In your analysis, test bwo sipha values, 02 and 0.4. When using an sipha value of 0.2, assume that the forecast for week 1 is the past three-week aversge the average demand for periods-3-2, and-1) For the model using an alpha of 0.4, assume that the forecast for week 1 is the past five-week average. (Round your answers to 2 decimal plsces) ES, -0.2 Week ATL BOS CHI DAL LA Total 11 13. ES, 04 DAL CHI Woek ATL BOS LA Tolal 10 11 12 13 b. Evaluate the forecasts that would have been made over the 13 weeks using the overal (at the end of the 13 weeks) mean sbsolute devistion, mean sbeckute percent error, and tracking signsl ss criteris. (Round your answers to 2 decimal plsces. Negative values should be indicated by a minus sign.) ATL BOS CHI DAL LA Avg of DC ES, 0.21 MAD MAPE TS ES, 04 MAD MAPE TS

Answer 1

a) Calculation of ES forecast and Error analysis (MAD, MAPE, TS) is done in the following spreadsheet

ES, alpha = 0.2

fx =N10(G10- N10 )*0.2 N11 B C WEEK ATL BOs CHI DAL LA TOTAL H I к Y AB AC AD AE A DEF G M N O R T X AA AF AG AH AL AJ 1 40 30 50 40 52 64 45 44 36 4 73 043 Absolute Percent Deviation 55 43 247 ES,a 0.2 CHI Absolute Deviation Forecast Erron 35 35 30 30 ATL BOS CHI 10.00 11.578.00 21.33 10.33 61.33 DAL LA TOTAL 33.33 38.89 17.78 76.19 30.39 36.73 ATL BOS CHI DAL -10.00 -1167 -8.00 -21.33 -10.33 |ТОTAL DAL LA TOTAL 228.33 6 1 4 195 ATL BOS DAL 1A ТОTAL ATL BOS CHI 1A 40.00 41.67 53.00 49.33 44. 7 1 4 28 34 167 -61.3 5.33 18.40 7.30 42 42 45.07 42.27 216.0 10.24 38.00 39.35 1.40 U2. U.bs U 9.36 15.69 55. 3.9 30 41 54 45 28 48 38.52 38.61 36.82 40.25 |37.80 41.82 50.45 47.21 |50.12 227.40 42.36 43.77 206, .82 206.45 5.91 052 16.22 8.72 37.42 29.82 | 37.98 24.20 30.57 25.31 -8.52 2,39 17.18 5.91 -12.25 7.75 20.22 2.36 0 94 2.36 023 40 10 4 208 42,78 8.52 239 0.23 96 28.40 5.82 19.29 0.2 1 96 17.18 5.91 31.82 13.15 243 11 4t 50 39.09 44.82 41.89 .18 1 4.12 36.52 2.56 15.0 .1 4.12 6.57 1.1 12.25 7.73 26.94 18.49 18 13.5 27 35 45.06 43.51 44,65 213.75 43.76 16.10 89.01 22.56 21.18 27.35 12 7 62 72 282 40.27 68.25 6.94 18.4 68.25 20 54 17.8 20.7 10 1 -17.80 12 18 13.55 20.7 -10.13 13 6r 68 41 246 18.60 7 56 18.60 25 26.16 12.b 40.96 121.27 41.46 53.16 96.87 203 23.75 28.12 9.74 28.12 04 b4.24 44.25 437 48.10 231.12 7o 24.25 -26.16 1 43 3А 5 39 12.21 46 73 64.65 4.39 2.48 30 20 36 46 24 30 56 46 41 52 40.39 43 39.52 352 45 02225 48 52.66 13.5 2 66 -13 5 33.79 18.4 16 10 40 38 256 4.39 702 30.20 54 85 11 8 7.0 15.52 19.59 4.79 58 45 3.62 231.84 12.8 -15.52-14.02-19.87-12.8 19.59 4.79 9.83 17 11 34 174 44.02 53.87 0 81 1 02 33.72 9.13 -57.84 38 48 9.8 1 3 57.84 33.24 31 45 10.51 55 247 36.41 40.33 42.17 48 7.22 19.10 13.33 2.93 26.73 49. 90 49 220,27 560 225 62 4,90 3.25 10.51 18 12 41 21 8.25 26.73 34 98 10.40 10.88 10.82 -490 -3.2 -5.60 47 60 1.63 18.90 230 19 3 4 42 067 9 83 91 4.38 4,08 1.91 0.67 1 9 4 4.38 20 MAPE (% ) МАГ Cumulative Forecast Error (CFE) LA TOTAL 22 ТОTAL LA TOTAL ATL BOS 2.32 12 35 -22.33 -14.27 12.72 CHI DAL 23 ATI BOS CH ПА LA ATL BOS CHI DAL 32.62 27.20 44.49 23.71 16.39 14.12 11.39 9.45 17.80 10.33 7.61 24 29.66 9.21 25 The above numbers are percentage 26 Trackine Sienal (TS)- CFE/MAD | воS LA TOTAL 28 ATI CH DA 0.20 131 -1 25 -1.3 29 1,67 131 30 31

EXCEL FORMULAS:

Cell	Formula	Copy to
I7	=AVERAGE(B4:B6)	I7:N7
I8	=I7+(B7-I7)*0.2	I8:N19
P7	=ABS(B7-I7)	P7:U19
W7	=P7/B7*100	W7:AB19
AD7	=B7-I7	AD7:AI19
P24	=AVERAGE(P7:P19)	P24:U24, W24:AB24
AD24	=SUM(AD7:AD19)	AD24:AI24
AD29	=AD24/P24	AD29:AI29

-----------------------------

ES, alpha = 0.4

G HI J K L N OP Y Z AB AC AD C D E F M R S T V W X AA AE AF AG AH Al A U AJ WEEK ATL BOS CHI DAL LA TOTAL 40 44 45 60 251 5 62 55 40 35 35 64 45 43 247 Absolute Deviation Absolute Percent Deviation 5 ES, a-0.4 Forecast Error LA TOTAL 4.33 228.33 DAL TOTAL 3t 195 LA TOTAL TOTAL 4 ATL BOS CH DAL ATL BOS CHI LA ATL BOS CHI DAI ATL BOS CHI DAI 6 44 LA 33.33 38 89 17.78 76.19 30.39 20.00 40.00 41.6753.00 49.33 36.00 37.00 49.80 40.80 33 -61.33 10.00 1167 6.7" -10.00 -11.67 -800 7 4. 28 34 167 35 10.33 61 33 35 42 42 0.20 203.80 8.82 50.91 8 40 4 3 196 9.00 0 80 80 86 4 2 ,98 9.00 .0 6 8 2: 80 -7.80 20.08 7.28 9.08 10.25 4.20 4.20 37 76 37 45 RE 34.66 38.89 42.39 41.33 43.74 36.64 29.98 6533.8 14 70 14 79 A 0F 4 33 199 12 50 48 35.82 13.58 8.20 51.41 13.89 39.25 83.18 18.52 14.00 25.22 48.31 140.00117.13 9.83 19.34 6.11 -14.39 6.67 -16.64 10 00 43.88 43.88 11 45 243 42.23 90 19.34 6.11 77 10.98 3.67 1.9 7.64 18.0 377 10.98 3.67 48 5.80 216.6 14.39 6.67 28.26 18.59 28.26 12 62 72 282 13.41 26.20 65.33 29.98 3 23.1t 18.59 26.20 65.33 8 96 44,00 55.04 50,85 48.00 58.63 57.71 49.77 244,08 0 7 6.28 242.80 10 13 6 68 46 16.64 17.15 16.28 3.20 1.30 17.15 -16.28 3.20 58 20 28.00 31.6 6.29 46.38 20.24 28.02 -28.00 -31.63 6.2 14 2 64 34 203 28.02 15.77 41.08 -15.77 41.08 8 23 20 20 19 76 0.1760 RE 3.3 67.78 53.49 79.27 39.71 13.85 11.49 4.04 18.89 2.35 43 11 46 08 37 59 40.27 33.76 39.63 5.65 10 594 28.61 16 28.61 16.21 22.24 6.37 24 30 56 46 41 52 46.05 60.95 0.16 41.41 238.83 15.30 44,04 212.9 16.05 26.95 7 " -16.27 -16.05 -26.95-12.16 22.24 6.37 64.83 17 11 34 38 48 174 12.16 659 2.0 13.74 659 64,83 19.92 13.81 18 12 45 45 5 247 0.17 17 0.30 10.96 34.10 0.6 51 -0.30 10.96 34.10 42. 848.10 45.18 8.43 226.5 0.89 1.50 9.82 110 4. 4P 3.46 7.57 3.46 19 3 230 42,66 1.66 9.82 10 1 0.43 -1.66 -3.18 -0.43 20 22 MAPE (%) DAL 34.83 29.07 46.64 22.30 18.15 14.22 Cumulative Forecast Error (CFE) BOS CHI 498 11 10 -13.35-13 57 9.81-1.02 MAD LA TOTAL 23 ATL BOS CH DAL IA TOTAL ATL BOS CHI ATL DA IA TOTAL 12.45 9.90 19.08 24 963 8.30 29.83 The above numbers are 25 percentage 26 F LA BOS CHI 28 ATI OTAL 0.40 1.12-0.70-1.41 1.18 -0.03 29 30 31

EXCEL FORMULAS:

Cell	Formula	Copy to
I7	=AVERAGE(B2:B6)	I7:N7
I8	=I7+(B7-I7)*0.4	I8:N19
P7	=ABS(B7-I7)	P7:U19
W7	=P7/B7*100	W7:AB19
AD7	=B7-I7	AD7:AI19
P24	=AVERAGE(P7:P19)	P24:U24, W24:AB24
AD24	=SUM(AD7:AD19)	AD24:AI24
AD29	=AD24/P24	AD29:AI29

---------------------------

---------------------------

Tables for (a) are following:

ES, α = 0.2
Week	ATL	BOS	CHI	DAL	LA	TOTAL
1	40.00	41.67	53.00	49.33	44.33	228.33
2	38.00	39.33	51.40	45.07	42.27	216.07
3	39.40	38.27	47.72	44.45	42.21	212.05
4	38.52	38.61	42.78	42.36	43.77	206.04
5	36.82	39.09	44.82	41.89	43.82	206.43
6	40.25	40.27	45.06	43.51	44.65	213.75
7	37.80	41.82	50.45	47.21	50.12	227.40
8	34.24	44.25	53.16	51.37	48.10	231.12
9	38.99	39.40	47.92	53.89	45.28	225.49
10	40.39	43.52	43.34	53.52	45.02	225.80
11	39.52	44.02	53.87	50.81	43.62	231.84
12	36.41	41.21	49.90	48.25	44.49	220.27
13	40.33	42.17	48.92	47.60	46.60	225.62

---

ES, α = 0.4
Week	ATL	BOS	CHI	DAL	LA	TOTAL
1	40.00	41.67	53.00	49.33	44.33	228.33
2	36.00	37.00	49.80	40.80	40.20	203.80
3	39.60	35.80	43.08	41.28	40.92	200.68
4	37.76	37.48	35.05	38.37	44.55	193.21
5	34.66	38.89	42.23	39.02	44.33	199.12
6	42.39	41.33	43.74	43.41	45.80	216.67
7	36.64	44.00	55.04	50.85	56.28	242.80
8	29.98	48.00	58.63	57.71	49.77	244.08
9	41.19	36.80	45.98	60.23	43.46	227.65
10	43.11	46.08	37.59	56.94	43.68	227.39
11	40.27	46.05	60.95	50.16	41.41	238.83
12	33.76	39.63	50.17	45.30	44.04	212.90
13	42.66	42.18	48.10	45.18	48.43	226.54

b) Summary of Error analysis is following:

		ATL	BOS	CHI	DAL	LA	Avg of DCs
ES, α = 0.2	MAD	11.39	9.45	17.80	10.33	7.61	11.32
	MAPE	32.62	27.20	44.49	23.71	16.39	28.88
	TS	0.20	1.31	-1.25	-1.38	1.67	0.11
ES, α = 0.4	MAD	12.45	9.90	19.08	9.63	8.30	11.87
	MAPE	34.83	29.07	46.64	22.30	18.15	30.20
	TS	0.40	1.12	-0.70	-1.41	1.18	0.12