MGT5100 Chamberlain Demand Forecasting in a Supply Chain

H9_a.Exercise 1, p. 200 (continued from last week). Referring to H8_a.xlsx Download H8_a.xlsx
, tab Exercise 1, evaluate the bias, TS, MAD, MAPE, MSE (already evaluated in H8_a.xlsx) Download H8_a.xlsx). Use section 7.6 and 7.7 as a guide to evaluate the quality of the forecast. Be sure to reference the various charts in H8_a.xlsx Download H8_a.xlsxthat discuss Normality of the forecast errors, the tracking signal, MAD, MSE, etc. Do NOT simply state “the forecast looks good” or some other trivial statement lacking reference to all the important material provided in H8_a.xlsx Download H8_a.xlsx.

H9_b.Exercise 2, p. 200 (continued from last week). Review H8_b.xlsx Download H8_b.xlsx,and, for the two cases in part a) and b) below that were performed in H8_b last week, answer part c). a) 4-week moving average (MA) b) exponential moving average with a=0.1 (EMA) c) evaluate the MAD, MSE, bias, for case (a) and (b) above. Which method (MA or EMA) do you prefer? Why? Hint: compare MSE and MAD for each method and select the one with minimum MSE and MAD. This problem is discussed on slides 49 and 50 of Chapter 7 PP, Part B.

Excercise 7-2: Hot Pizza
Week
1
2
3
4
Demand
108
116
118
124
Week
5
6
7
8
Demand
96
119
96
102
Week
9
10
11
12
Demand
112
102
92
91
1. Reformat
data
Moving Average
MOVING AVERAGE
Calculate
Level Forecast Errors
Et
At
bias
MSE
MAD
%
Error MAPE
TS
Graph
Using
Moving
Average
Forecasted Demand
130
125
120
115
106
117
114
114
109
103
107
103
102
106
117
114
114
109
103
107
103
102
102
102
102
10
-3
18
12
-3
1
15
12
10
3
18
12
3
1
15
12
10
8
25
38
34
36
51
63
107
57
140
142
116
97
116
120
10
6
10
11
9
8
9
9
Estimate of standard deviation of forecast error: 11.61
11
2
18
12
3
1
17
13
11
6
10
11
9
8
9
10
Unit Demand
Period Demand
1
108
2
116
3
118
4
124
5
96
6
119
7
96
8
102
9
112
10
102
11
92
12
91
13
14
15
16
Actual Demand
ERRORS
1.00
1.22
2.51
3.53
3.75
4.53
5.71
6.76
110
105
100
95
90
85
80
1
2
3
4
5
6
7
8
9
10 11 12 13 14 15 16
Periods
2. Start with
reformatted
data again.
Period Demand
0
1
108
2
116
3
118
4
124
5
96
6
119
7
96
8
102
9
112
10
102
11
92
12
91
13
14
15
16
CR – 7/13/2023 6:18 AM
ERRORS
bias
MSE MAD
Level Forecast Calculate
Et
At
Errors
106
107
106
-2
2
-2
3
2
113
107
-9
9
-10
39
5
116
113
-5
5
-16
36
5
121
116
-8
8
-24
44
6
106
121
25
25
1
157
10
114
106
-13
13
-12
160
10
103
114
18
18
5
182
11
102
103
1
1
6
159
10
108
102
-10
10
-3
152
10
104
108
6
6
3
140
10
97
104
12
12
16
142
10
93
97
6
6
22
133
10
93
Estimate of standard deviation of forecast error: 11.97
93
93
93
%
Error MAPE
2
7
5
7
26
11
18
1
9
6
14
7
2
5
5
5
9
10
11
10
9
9
10
9
EXPONENTIAL SMOOTHING
TS
-1.00
-2.00
-3.00
-4.00
0.08
-1.20
0.47
0.64
-0.31
0.32
1.57
2.25
Graph
Using
Exponent
ial
Actual Demand
Forecasted Demand
130
125
120
115
Unit Demand
EXPONENTIAL SMOOTHING
Alpha =
0.6
110
105
100
95
90
85
80
1
2
3
4
5
6
7
8
9
10 11 12 13 14 15 16
Periods
5f7ce805fdae5a160577986ba3b603fb – Exercise 2
Excersise 7-1: ABC Corporation
Monthly Demand ABC Corporation
p = 12
Year 1
2000
3000
3000
3000
4000
6000
7000
6000
10000
12000
14000
8000
78000
Year 2
3000
4000
3000
5000
5000
8000
3000
8000
12000
12000
16000
10000
89000
Year 3
2000
5000
5000
3000
4000
6000
7000
10000
15000
15000
18000
8000
98000
Year 4
5000
4000
4000
2000
5000
7000
10000
14000
16000
16000
20000
12000
115000
Year 5
5000
2000
3000
2000
7000
6000
8000
10000
20000
20000
22000
8000
113000
25000
Deseasonalized Demand
12000
y = 70.246x + 5,997.261
R² = 0.950
20000
10000
15000
Sales
Sales
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
Total
8000
10000
6000
(even)
5000
4000
0
2000
Month
0
Static Method for Forecasting
0
Dt (linear
trend)
Year
Month
Period
Et
At
bias
MSE
MAD
Percent
Error
MAPE
TS
Year 1
JAN
FEB
MAR
1
2
3
2000
3000
3000
6068
6138
6208
0.33
0.49
0.48
2588
2913
2872
588
-87
-128
588
87
128
588
501
373
346273
176952
123433
588
338
268
29
3
4
29
16
12
1.00
1.48
1.39
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
JAN
FEB
MAR
4
5
6
7
8
9
10
11
12
13
14
15
3000
4000
6000
7000
6000
10000
12000
14000
8000
3000
4000
3000
6542
6625
6667
6750
6875
7000
6917
6833
7000
6278
6348
6419
6489
6559
6629
6700
6770
6840
6910
6981
7051
0.48
0.63
0.93
1.08
0.91
1.51
1.79
2.07
1.17
0.43
0.57
0.43
2500
3944
5356
5534
7551
11489
11913
14377
7488
2948
3313
3262
-500
-56
-644
-1466
1551
1489
-87
377
-512
-52
-687
262
500
56
644
1466
1551
1489
87
377
512
52
687
262
-127
-183
-827
-2292
-742
747
660
1037
525
473
-214
48
155075
124678
173086
455240
698911
867560
781567
723454
685002
632517
621082
584250
326
272
334
496
628
723
660
634
624
580
587
566
17
1
11
21
26
15
1
3
6
2
17
9
13
11
11
12
14
14
13
12
11
11
11
11
-0.39
-0.67
-2.48
-4.63
-1.18
1.03
1.00
1.64
0.84
0.82
-0.36
0.08
Year 2
Year 3
Year 4
Year 5
Year 6
Demand Deseasonalized
Dt
Demand Dt
Seasonal
Factor St Forecast
APR
16
5000
7083
7121
0.70
2836
-2164
2164
-2117
840507
666
43
13
-3.18
MAY
17
5000
7167
7191
0.70
4468
-532
532
-2648
807704
658
11
13
-4.03
JUN
JUL
AUG
SEP
OCT
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
8000
3000
8000
12000
12000
16000
10000
2000
5000
5000
3000
4000
6000
7000
10000
15000
15000
18000
8000
5000
4000
4000
2000
5000
7000
10000
14000
16000
16000
20000
12000
5000
2000
3000
2000
7000
6000
8000
10000
20000
20000
22000
8000
7333
7375
7375
7500
7500
7375
7250
7333
7583
7792
8042
8250
8250
8292
8375
8292
8208
8208
8292
8458
8750
8958
9042
9167
9417
9583
9500
9375
9333
9417
9458
9333
9083
9083
9417
9667
9583
7262
7332
7402
7472
7543
7613
7683
7753
7824
7894
7964
8034
8105
8175
8245
8315
8386
8456
8526
8596
8667
8737
8807
8877
8948
9018
9088
9158
9229
9299
9369
9439
9510
9580
9650
9720
9791
9861
9931
10001
10072
10142
10212
Forecasts
1.10
0.41
1.08
1.61
1.59
2.10
1.30
0.26
0.64
0.63
0.38
0.50
0.74
0.86
1.21
1.80
1.79
2.13
0.94
0.58
0.46
0.46
0.23
0.56
0.78
1.11
1.54
1.75
1.73
2.15
1.28
0.53
0.21
0.31
0.21
0.72
0.61
0.81
1.01
2.00
1.99
2.17
0.78
6059
6253
8521
12950
13411
16167
8411
3308
3713
3652
3171
4992
6762
6972
9491
14411
14910
17958
9334
3667
4113
4042
3507
5516
7466
7691
10462
15871
16409
19748
10256
4027
4513
4432
3843
6039
8169
8410
11432
17332
17908
21538
11179
4386
4913
4822
4178
6563
8872
9129
12403
18793
19407
23328
12102
-1941
3253
521
950
1411
167
-1589
1308
-1287
-1348
171
992
762
-28
-509
-589
-90
-42
1334
-1333
113
42
1507
516
466
-2309
-3538
-129
409
-252
-1744
-973
2513
1432
1843
-961
2169
410
1432
-2668
-2092
-462
3179
1941
3253
521
950
1411
167
1589
1308
1287
1348
171
992
762
28
509
589
90
42
1334
1333
113
42
1507
516
466
2309
3538
129
409
252
1744
973
2513
1432
1843
961
2169
410
1432
2668
2092
462
3179
-4589
-1336
-815
135
1546
1713
124
1432
145
-1203
-1032
-40
722
694
186
-404
-493
-536
798
-535
-422
-380
1127
1642
2108
-201
-3739
-3868
-3459
-3711
-5454
-6427
-3915
-2483
-640
-1601
568
978
2410
-257
-2349
-2812
368
972128
1478005
1417679
1393120
1420351
1359815
1408374
1420438
1429544
1443909
1393389
1379266
1352665
1309056
1276231
1248087
1211615
1177049
1193763
1209503
1178008
1147848
1175927
1153731
1131425
1229085
1485675
1453028
1425079
1396112
1430356
1420490
1518356
1528783
1564679
1552568
1610944
1584712
1593039
1689953
1736276
1710467
1850424
729
862
845
850
875
845
876
893
908
924
898
901
896
868
857
849
826
804
819
833
814
794
812
805
796
832
893
876
866
853
872
874
906
917
934
935
958
948
957
987
1006
996
1033
1291
24
108
7
8
12
1
16
65
26
27
6
25
13
0
5
4
1
0
17
27
3
1
75
10
7
23
25
1
3
1
15
19
126
48
92
14
36
5
14
13
10
2
40
13
18
18
17
17
16
16
18
19
19
19
19
19
18
18
17
17
16
16
16
16
16
17
17
17
17
17
17
16
16
16
16
18
19
20
20
21
20
20
20
20
20
20
-6.29
-1.55
-0.96
0.16
1.77
2.03
0.14
1.60
0.16
-1.30
-1.15
-0.04
0.81
0.80
0.22
-0.48
-0.60
-0.67
0.97
-0.64
-0.52
-0.48
1.39
2.04
2.65
-0.24
-4.19
-4.41
-3.99
-4.35
-6.26
-7.36
-4.32
-2.71
-0.69
-1.71
0.59
1.03
2.52
-0.26
-2.34
-2.82
0.36
Estimate of std. dev. of forecast error:
10
20
30
40
50
60
Forecast Error Statistics with histogram, boxplot, and normal probability plot shown below.
Note Anderson-Darling normality test implies forecast errors are normally distributed
Deasonalized Demand Linear Fit (see above figure)
Coefficients
Intercept
5997.2605
X Variable 1
70.246
Seasonal Factors
Average of Seasonal Factor St
Month
Total
JAN
0.427
FEB
0.475
MAR
0.463
APR
0.398
MAY
0.621
JUN
0.834
JUL
0.853
AUG
1.151
SEP
1.733
OCT
1.778
NOV
2.124
DEC
1.095
Estimate is 1291 from 1.25*MAD
Estimate from data 1372
% error = (1372 – 1291)/1372
5.9
percent
Avg of Seasonal Factors
Matches pivot table
0.427
0.475
0.463
0.398
0.621
0.834
0.853
1.151
1.733
1.778
2.124
1.095
Tracking Signal TS
Bias
4.00
3000
2000
2.00
1000
0
-1000 1
0.00
4
7
10 13 16 19 22 25 28 31 34 37 40 43 46 49 52 55 58
bias
bias
1
-2.00
4
7
10 13 16 19 22 25 28 31 34 37 40 43 46 49 52 55 58
-2000
-3000
-4.00
-4000
-5000
-6.00
-6000
-7000
-8.00
Period
CR – 7/13/2023 6:17 AM
Period
d52a3e38b99f1a7b0c99408a8e5f4969 – Exercise 1
Excersise 7-1: ABC Corporation
Monthly Demand ABC Corporation
p = 12
Year 1
2000
3000
3000
3000
4000
6000
7000
6000
10000
12000
14000
8000
78000
Year 2
3000
4000
3000
5000
5000
8000
3000
8000
12000
12000
16000
10000
89000
Year 3
2000
5000
5000
3000
4000
6000
7000
10000
15000
15000
18000
8000
98000
Year 4
5000
4000
4000
2000
5000
7000
10000
14000
16000
16000
20000
12000
115000
Year 5
5000
2000
3000
2000
7000
6000
8000
10000
20000
20000
22000
8000
113000
25000
Deseasonalized Demand
12000
y = 70.246x + 5,997.261
R² = 0.950
20000
10000
15000
Sales
Sales
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
Total
8000
10000
6000
(even)
5000
4000
0
2000
Month
0
Static Method for Forecasting
0
Dt (linear
trend)
Year
Month
Period
Et
At
bias
MSE
MAD
Percent
Error
MAPE
TS
Year 1
JAN
FEB
MAR
1
2
3
2000
3000
3000
6068
6138
6208
0.33
0.49
0.48
2588
2913
2872
588
-87
-128
588
87
128
588
501
373
346273
176952
123433
588
338
268
29
3
4
29
16
12
1.00
1.48
1.39
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
JAN
FEB
MAR
4
5
6
7
8
9
10
11
12
13
14
15
3000
4000
6000
7000
6000
10000
12000
14000
8000
3000
4000
3000
6542
6625
6667
6750
6875
7000
6917
6833
7000
6278
6348
6419
6489
6559
6629
6700
6770
6840
6910
6981
7051
0.48
0.63
0.93
1.08
0.91
1.51
1.79
2.07
1.17
0.43
0.57
0.43
2500
3944
5356
5534
7551
11489
11913
14377
7488
2948
3313
3262
-500
-56
-644
-1466
1551
1489
-87
377
-512
-52
-687
262
500
56
644
1466
1551
1489
87
377
512
52
687
262
-127
-183
-827
-2292
-742
747
660
1037
525
473
-214
48
155075
124678
173086
455240
698911
867560
781567
723454
685002
632517
621082
584250
326
272
334
496
628
723
660
634
624
580
587
566
17
1
11
21
26
15
1
3
6
2
17
9
13
11
11
12
14
14
13
12
11
11
11
11
-0.39
-0.67
-2.48
-4.63
-1.18
1.03
1.00
1.64
0.84
0.82
-0.36
0.08
Year 2
Year 3
Year 4
Year 5
Year 6
Demand Deseasonalized
Dt
Demand Dt
Seasonal
Factor St Forecast
APR
16
5000
7083
7121
0.70
2836
-2164
2164
-2117
840507
666
43
13
-3.18
MAY
17
5000
7167
7191
0.70
4468
-532
532
-2648
807704
658
11
13
-4.03
JUN
JUL
AUG
SEP
OCT
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
8000
3000
8000
12000
12000
16000
10000
2000
5000
5000
3000
4000
6000
7000
10000
15000
15000
18000
8000
5000
4000
4000
2000
5000
7000
10000
14000
16000
16000
20000
12000
5000
2000
3000
2000
7000
6000
8000
10000
20000
20000
22000
8000
7333
7375
7375
7500
7500
7375
7250
7333
7583
7792
8042
8250
8250
8292
8375
8292
8208
8208
8292
8458
8750
8958
9042
9167
9417
9583
9500
9375
9333
9417
9458
9333
9083
9083
9417
9667
9583
7262
7332
7402
7472
7543
7613
7683
7753
7824
7894
7964
8034
8105
8175
8245
8315
8386
8456
8526
8596
8667
8737
8807
8877
8948
9018
9088
9158
9229
9299
9369
9439
9510
9580
9650
9720
9791
9861
9931
10001
10072
10142
10212
Forecasts
1.10
0.41
1.08
1.61
1.59
2.10
1.30
0.26
0.64
0.63
0.38
0.50
0.74
0.86
1.21
1.80
1.79
2.13
0.94
0.58
0.46
0.46
0.23
0.56
0.78
1.11
1.54
1.75
1.73
2.15
1.28
0.53
0.21
0.31
0.21
0.72
0.61
0.81
1.01
2.00
1.99
2.17
0.78
6059
6253
8521
12950
13411
16167
8411
3308
3713
3652
3171
4992
6762
6972
9491
14411
14910
17958
9334
3667
4113
4042
3507
5516
7466
7691
10462
15871
16409
19748
10256
4027
4513
4432
3843
6039
8169
8410
11432
17332
17908
21538
11179
4386
4913
4822
4178
6563
8872
9129
12403
18793
19407
23328
12102
-1941
3253
521
950
1411
167
-1589
1308
-1287
-1348
171
992
762
-28
-509
-589
-90
-42
1334
-1333
113
42
1507
516
466
-2309
-3538
-129
409
-252
-1744
-973
2513
1432
1843
-961
2169
410
1432
-2668
-2092
-462
3179
1941
3253
521
950
1411
167
1589
1308
1287
1348
171
992
762
28
509
589
90
42
1334
1333
113
42
1507
516
466
2309
3538
129
409
252
1744
973
2513
1432
1843
961
2169
410
1432
2668
2092
462
3179
-4589
-1336
-815
135
1546
1713
124
1432
145
-1203
-1032
-40
722
694
186
-404
-493
-536
798
-535
-422
-380
1127
1642
2108
-201
-3739
-3868
-3459
-3711
-5454
-6427
-3915
-2483
-640
-1601
568
978
2410
-257
-2349
-2812
368
972128
1478005
1417679
1393120
1420351
1359815
1408374
1420438
1429544
1443909
1393389
1379266
1352665
1309056
1276231
1248087
1211615
1177049
1193763
1209503
1178008
1147848
1175927
1153731
1131425
1229085
1485675
1453028
1425079
1396112
1430356
1420490
1518356
1528783
1564679
1552568
1610944
1584712
1593039
1689953
1736276
1710467
1850424
729
862
845
850
875
845
876
893
908
924
898
901
896
868
857
849
826
804
819
833
814
794
812
805
796
832
893
876
866
853
872
874
906
917
934
935
958
948
957
987
1006
996
1033
1291
24
108
7
8
12
1
16
65
26
27
6
25
13
0
5
4
1
0
17
27
3
1
75
10
7
23
25
1
3
1
15
19
126
48
92
14
36
5
14
13
10
2
40
13
18
18
17
17
16
16
18
19
19
19
19
19
18
18
17
17
16
16
16
16
16
17
17
17
17
17
17
16
16
16
16
18
19
20
20
21
20
20
20
20
20
20
-6.29
-1.55
-0.96
0.16
1.77
2.03
0.14
1.60
0.16
-1.30
-1.15
-0.04
0.81
0.80
0.22
-0.48
-0.60
-0.67
0.97
-0.64
-0.52
-0.48
1.39
2.04
2.65
-0.24
-4.19
-4.41
-3.99
-4.35
-6.26
-7.36
-4.32
-2.71
-0.69
-1.71
0.59
1.03
2.52
-0.26
-2.34
-2.82
0.36
Estimate of std. dev. of forecast error:
10
20
30
40
50
60
Forecast Error Statistics with histogram, boxplot, and normal probability plot shown below.
Note Anderson-Darling normality test implies forecast errors are normally distributed
Deasonalized Demand Linear Fit (see above figure)
Coefficients
Intercept
5997.2605
X Variable 1
70.246
Seasonal Factors
Average of Seasonal Factor St
Month
Total
JAN
0.427
FEB
0.475
MAR
0.463
APR
0.398
MAY
0.621
JUN
0.834
JUL
0.853
AUG
1.151
SEP
1.733
OCT
1.778
NOV
2.124
DEC
1.095
Estimate is 1291 from 1.25*MAD
Estimate from data 1372
% error = (1372 – 1291)/1372
5.9
percent
Avg of Seasonal Factors
Matches pivot table
0.427
0.475
0.463
0.398
0.621
0.834
0.853
1.151
1.733
1.778
2.124
1.095
Tracking Signal TS
Bias
4.00
3000
2000
2.00
1000
0
-1000 1
0.00
4
7
10 13 16 19 22 25 28 31 34 37 40 43 46 49 52 55 58
bias
bias
1
-2.00
4
7
10 13 16 19 22 25 28 31 34 37 40 43 46 49 52 55 58
-2000
-3000
-4.00
-4000
-5000
-6.00
-6000
-7000
-8.00
Period
CR – 7/13/2023 6:17 AM
Period
ccf1b48cf0ef79886f9b8dd803762432 – Exercise 1