SPC wishes to develop short-term demand forecasts for each of its many different products with a simple forecasting procedure. As a result, only simple exponential smoothing and trend-adjusted exponential soothing are being considered for use. The question that naturally arises is “Which of these exponential smoothing models should be used, and what smoothing parameters should be used with these models to obtain the best expected overall accuracy from forecasts?”  How well the different forecasting techniques would have performed if they had been in use over the given five year interval. As mentioned above, there is an obvious seasonality that must be accounted for in this case, and neither of these exponential smoothing models should be used directly with data sets that contain seasonal effects. This leads to a need for seasonal indexes. SPC management wants to have a forecasting model that updates seasonal indexes at the end of each given year to account for demand values that were observed during the given year. However, the parameters for the “most accurate” forecasting model in this situation should be determined on the basis of trying to minimize the total overall cost of forecasting error.*   please discuss the results obtained. ASAP

Practical Management Science
6th Edition
ISBN:9781337406659
Author:WINSTON, Wayne L.
Publisher:WINSTON, Wayne L.
Chapter2: Introduction To Spreadsheet Modeling
Section: Chapter Questions
Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...
icon
Related questions
Question

SPC wishes to develop short-term demand forecasts for each of its many different
products with a simple forecasting procedure. As a result, only simple exponential
smoothing and trend-adjusted exponential soothing are being considered for use. The
question that naturally arises is “Which of these exponential smoothing models should
be used, and what smoothing parameters should be used with these models to obtain the
best expected overall accuracy from forecasts?” 
How well the different forecasting techniques would have performed if they had been in
use over the given five year interval.
As mentioned above, there is an obvious seasonality that must be accounted for in
this case, and neither of these exponential smoothing models should be used directly with
data sets that contain seasonal effects. This leads to a need for seasonal indexes. SPC
management wants to have a forecasting model that updates seasonal indexes at the end
of each given year to account for demand values that were observed during the given
year. However, the parameters for the “most accurate” forecasting model in
this situation should be determined on the basis of trying to minimize the total overall
cost of forecasting error.*

 

please discuss the results obtained. ASAP

F10
2
3
4
5
6
7
8
9
10
12
13
14
15
16
17
1
18
19
20
21
22
23
24
25
26
27
28
29
30
16
17
14
15
18
19
20
13
21
22
23
24
25
26
12
27
28
30
29
MP
10
A
7
8
9
6
5
4
slope(y,x)
Slope 1437.443609
Intercept 130856.8421 intercept(y,x)
3
20
21
22
23
24
25
19
26
2
J18
27
9
14
15
16
17
18
28
1
сл
6
7
8
13
29
Year
5
Simple Exponential Smoothing
10
11
12
4
30
5
3
1
2
3
4
2
B
1
+
5
2
1
Year
2
3
75%
4
5
СП
sonal Factor
6
850
a
6
6
a
5
4
0.5
1
173,200.00
145,400.00
82,000.00
128,200.00
528,800.00
132,200.00
C
Seasonal
Index
Year
3
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
10
2
$% 0.00 123-
Quarter
1
D
Linear Regression (Trend)
Period
▼
|fx
1.27
1.08
0.65
1.00
B
Quarter
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
75%
0.45
0.02
1
1234
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
4
st
1
2
3
4
1
2
3
4
1
SI
1.31
1.10
0.62
0.97
B
trend-adjusted exponential soot
File Edit View Insert Format Data Tools Extensions Help
D
Quarter
1
2
3
4
1
2
3
4
1
2
3
75% ▼
fx =(F9+($B$27*(E10-G9)))
C
Period
1
2
3
4
LO
5
LD
6
7
8
CO
9
10
11
12
55
13
S
14
15
16
17
18
19
20
21
Demand
4
E
173,200
145,400
82,000
128,200
166,000
147,000
86,000
138,000
181,400
151,800
95,000
141,800
193,600
165,800
106,200
155,000
211,800
175,200
109,800
165,800
Default (Ari...
E
2
166,000.00
147,000.00
86,000.00
138,000.00
537,000.00
134,250.00
Quarter
C
7
8
Period
1
2
3
4
5
6
9
10
11
12
13
14
15
16
17
18
19
20
21
Forecasted
Demand
132294.2857
133731.7293
135169.1729
136606.6165
138044.0602
139481.5038
140918.9474
142356.391
143793.8346
145231.2782
146668.7218
148106.1654
149543.609
150981.0526
152418.4962
153855.9398
155293.3835
156730.8271
158168.2707
159605.7143
Simple Exponential Smoothing
$ %0.00 123
F
% 0.00 123-
D
*
Demand
173,200
145,400
82,000
128,200
166,000
147,000
86,000
138,000
181,400
151,800
95,000
141,800
193,600
165,800
106,200
155,000
211,800
175,200
109,800
165,800
SI
1.24
1.09
0.64
1.03
1
136,463.01
2 134,970.08
3 125,195.54
4 128,387.39
F
1
D
10
G
E
Demand
173,200
145,400
82,000
128,200
166,000
147,000
86,000
138,000
181,400
151,800
95,000
141,800
193,600
165,800
106,200
155,000
211,800
175,200
109,800
165,800
▼
Simple Exponential Smoothing
G
BISA
250000
200000
2
H
150000
100000
50000
E
3
181,400.00
151,800.00
95,000.00
141,800.00
570,000.00
142,500.00
Seasonal Factor
0
Default (Ari...
Year
и
Deseasonal Trend
Year
F
Default (Ari... Y
3
130790.1875 142,923.73
136455.3105 140,910.99
131302.6372 145,043.61
138201.7134 142,007.27
H
SI
1.27
1.07
0.67
1.00
128387.3888
147193.6944
147096.8472
116548.4236
127274.2118
154337.1059
153068.553
124034.2765
132917.1382
163258.5691
164529.2846
135364.6423
145182.3211
178491.1606
176845.5803
143322.7901
154561.3951
10
Simple Exponential Smoothing
Forecast
F
G
Trend Adjusted Exponential Smoothing
Ft
FITT
136463.0149
134970.0826
125195.5378
0
58843.8
109182.2958
128658.8278
-111536.991
Tt
136463.0149
134970.0826
125195.5378
0
57690
1153.8
107064.09
2118.2058
126200.2627 2458.565138
2074.635687
109462.3553
125758.1478
123445.345 2312.802768
2813.579438 153610.5607
150796.9813
155593.0928
152795.8084 2797.284391
130578.1476
128326.201 2251.946556
137980.9244
2352.943228
135627.9812
165863.0233
2853.514908
163009.5084
168687.6105
2852.947698
165834.6628
2290.559204 142858.745
140568.1858
2399.830499 150722.1402
148322.3097
181156.7084
2949.531236
178207.1771
181372.1105
2895.920861
178476.1896
151416.4326
2251.771867
149164.6608
157889.0379 2381.223973 160270.2619
5
Error
37,613
-194
-61,097
21,452
54,126
-2,537
-58,069
17,766
60,683
2,541
-58,329
19,635
66,618
-3,291
-67,046
22,477
88 53 = P
10
1.513296965
TS
Demand - Forecasted Demand
Linear Regression (Trend)
4
193,600.00
165,800.00
106,200.00
155,000.00
620,600.00
155,150.00
4
Last edit was seconds ago
B I S A
T
H
Error
tren-adjusted exponential soot ▾
10
152536.0259 166,875.67
153906.7379 162,632.45
162143.4892 167,639.88
155226.5621 166,042.35
107,156
37,818
-42,659
26,463
55,642
-1,811
-60,593
11,222
55,619
-63
-62,488
12,141
61,078
-5,957
-71,572
14,384
A
A
BISA
G
Period
3.482066922
TS
[Error]
SI
1.25
1.07
0.68
1.00
5
| Error |
5
211,800.00
175,200.00
109,800.00
165,800.00
662,600.00
165,650.00
H
15
11482451198
107156.2
37817.7042 1430178751
42658.82783 1819775592
26463.00901 700290845.7
3096015715
55641.85219
1810.560736 3278130.179
60593.0928 3671522895
11221.85241 125929971.4
55619.0756 3093481570
63.02333093 3971.940242
62487.61053 3904701470
147410073.1
12141.255
61077.85975 3730504952
35482374.63
5956.708372
5122566996
71572.11047
206887010.5
14383.56738
Error^2
39166.51935 2410655095
MAD
MSE
37612.6112 1414708521
193.6944011 37517.52102
61096.8472 3732824738
-21451.5764 460170130
54125.7882 2929600948
2537.1059 6436906.348
58068.55295 3371956842
17765.72352 315620932.4
60682.86176 3682409712
2541.430881 6458870.924
58329.28456 3402305437
19635.35772 385547272.8
66617.67886 4437915137
3291.16057 10831737.9
67045.58028 4495109836
22477.20986 505224963
34592.02902 1822322469
MAD
MSE
trend-adjusted exponential cont
Error²
Seasonal Index
SI
1.28
1.06
0.66
1.00
4.18 2:3 - =- pl
M
%Error
A
22.66%
0.13%
71.04%
15.54%
29.84%
1.67%
61.12%
12.53%
31.34%
1.53%
54.92%
12.67%
31.45%
1.88%
61.06%
13.56%
26.43%
MAPE
20
K
il.
%Error
V
M
M
Linear Regression (Trend) -
f
64.55%
25.73%
49.60%
19.18%
30.67%
1.19%
63.78%
7.91%
28.73%
0.04%
58.84%
7.83%
28.84%
3.40%
65.18%
8.68%
N
29.01%
MAPE
Linear Regression
Transcribed Image Text:F10 2 3 4 5 6 7 8 9 10 12 13 14 15 16 17 1 18 19 20 21 22 23 24 25 26 27 28 29 30 16 17 14 15 18 19 20 13 21 22 23 24 25 26 12 27 28 30 29 MP 10 A 7 8 9 6 5 4 slope(y,x) Slope 1437.443609 Intercept 130856.8421 intercept(y,x) 3 20 21 22 23 24 25 19 26 2 J18 27 9 14 15 16 17 18 28 1 сл 6 7 8 13 29 Year 5 Simple Exponential Smoothing 10 11 12 4 30 5 3 1 2 3 4 2 B 1 + 5 2 1 Year 2 3 75% 4 5 СП sonal Factor 6 850 a 6 6 a 5 4 0.5 1 173,200.00 145,400.00 82,000.00 128,200.00 528,800.00 132,200.00 C Seasonal Index Year 3 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 10 2 $% 0.00 123- Quarter 1 D Linear Regression (Trend) Period ▼ |fx 1.27 1.08 0.65 1.00 B Quarter 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 75% 0.45 0.02 1 1234 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 4 st 1 2 3 4 1 2 3 4 1 SI 1.31 1.10 0.62 0.97 B trend-adjusted exponential soot File Edit View Insert Format Data Tools Extensions Help D Quarter 1 2 3 4 1 2 3 4 1 2 3 75% ▼ fx =(F9+($B$27*(E10-G9))) C Period 1 2 3 4 LO 5 LD 6 7 8 CO 9 10 11 12 55 13 S 14 15 16 17 18 19 20 21 Demand 4 E 173,200 145,400 82,000 128,200 166,000 147,000 86,000 138,000 181,400 151,800 95,000 141,800 193,600 165,800 106,200 155,000 211,800 175,200 109,800 165,800 Default (Ari... E 2 166,000.00 147,000.00 86,000.00 138,000.00 537,000.00 134,250.00 Quarter C 7 8 Period 1 2 3 4 5 6 9 10 11 12 13 14 15 16 17 18 19 20 21 Forecasted Demand 132294.2857 133731.7293 135169.1729 136606.6165 138044.0602 139481.5038 140918.9474 142356.391 143793.8346 145231.2782 146668.7218 148106.1654 149543.609 150981.0526 152418.4962 153855.9398 155293.3835 156730.8271 158168.2707 159605.7143 Simple Exponential Smoothing $ %0.00 123 F % 0.00 123- D * Demand 173,200 145,400 82,000 128,200 166,000 147,000 86,000 138,000 181,400 151,800 95,000 141,800 193,600 165,800 106,200 155,000 211,800 175,200 109,800 165,800 SI 1.24 1.09 0.64 1.03 1 136,463.01 2 134,970.08 3 125,195.54 4 128,387.39 F 1 D 10 G E Demand 173,200 145,400 82,000 128,200 166,000 147,000 86,000 138,000 181,400 151,800 95,000 141,800 193,600 165,800 106,200 155,000 211,800 175,200 109,800 165,800 ▼ Simple Exponential Smoothing G BISA 250000 200000 2 H 150000 100000 50000 E 3 181,400.00 151,800.00 95,000.00 141,800.00 570,000.00 142,500.00 Seasonal Factor 0 Default (Ari... Year и Deseasonal Trend Year F Default (Ari... Y 3 130790.1875 142,923.73 136455.3105 140,910.99 131302.6372 145,043.61 138201.7134 142,007.27 H SI 1.27 1.07 0.67 1.00 128387.3888 147193.6944 147096.8472 116548.4236 127274.2118 154337.1059 153068.553 124034.2765 132917.1382 163258.5691 164529.2846 135364.6423 145182.3211 178491.1606 176845.5803 143322.7901 154561.3951 10 Simple Exponential Smoothing Forecast F G Trend Adjusted Exponential Smoothing Ft FITT 136463.0149 134970.0826 125195.5378 0 58843.8 109182.2958 128658.8278 -111536.991 Tt 136463.0149 134970.0826 125195.5378 0 57690 1153.8 107064.09 2118.2058 126200.2627 2458.565138 2074.635687 109462.3553 125758.1478 123445.345 2312.802768 2813.579438 153610.5607 150796.9813 155593.0928 152795.8084 2797.284391 130578.1476 128326.201 2251.946556 137980.9244 2352.943228 135627.9812 165863.0233 2853.514908 163009.5084 168687.6105 2852.947698 165834.6628 2290.559204 142858.745 140568.1858 2399.830499 150722.1402 148322.3097 181156.7084 2949.531236 178207.1771 181372.1105 2895.920861 178476.1896 151416.4326 2251.771867 149164.6608 157889.0379 2381.223973 160270.2619 5 Error 37,613 -194 -61,097 21,452 54,126 -2,537 -58,069 17,766 60,683 2,541 -58,329 19,635 66,618 -3,291 -67,046 22,477 88 53 = P 10 1.513296965 TS Demand - Forecasted Demand Linear Regression (Trend) 4 193,600.00 165,800.00 106,200.00 155,000.00 620,600.00 155,150.00 4 Last edit was seconds ago B I S A T H Error tren-adjusted exponential soot ▾ 10 152536.0259 166,875.67 153906.7379 162,632.45 162143.4892 167,639.88 155226.5621 166,042.35 107,156 37,818 -42,659 26,463 55,642 -1,811 -60,593 11,222 55,619 -63 -62,488 12,141 61,078 -5,957 -71,572 14,384 A A BISA G Period 3.482066922 TS [Error] SI 1.25 1.07 0.68 1.00 5 | Error | 5 211,800.00 175,200.00 109,800.00 165,800.00 662,600.00 165,650.00 H 15 11482451198 107156.2 37817.7042 1430178751 42658.82783 1819775592 26463.00901 700290845.7 3096015715 55641.85219 1810.560736 3278130.179 60593.0928 3671522895 11221.85241 125929971.4 55619.0756 3093481570 63.02333093 3971.940242 62487.61053 3904701470 147410073.1 12141.255 61077.85975 3730504952 35482374.63 5956.708372 5122566996 71572.11047 206887010.5 14383.56738 Error^2 39166.51935 2410655095 MAD MSE 37612.6112 1414708521 193.6944011 37517.52102 61096.8472 3732824738 -21451.5764 460170130 54125.7882 2929600948 2537.1059 6436906.348 58068.55295 3371956842 17765.72352 315620932.4 60682.86176 3682409712 2541.430881 6458870.924 58329.28456 3402305437 19635.35772 385547272.8 66617.67886 4437915137 3291.16057 10831737.9 67045.58028 4495109836 22477.20986 505224963 34592.02902 1822322469 MAD MSE trend-adjusted exponential cont Error² Seasonal Index SI 1.28 1.06 0.66 1.00 4.18 2:3 - =- pl M %Error A 22.66% 0.13% 71.04% 15.54% 29.84% 1.67% 61.12% 12.53% 31.34% 1.53% 54.92% 12.67% 31.45% 1.88% 61.06% 13.56% 26.43% MAPE 20 K il. %Error V M M Linear Regression (Trend) - f 64.55% 25.73% 49.60% 19.18% 30.67% 1.19% 63.78% 7.91% 28.73% 0.04% 58.84% 7.83% 28.84% 3.40% 65.18% 8.68% N 29.01% MAPE Linear Regression
Expert Solution
steps

Step by step

Solved in 5 steps with 10 images

Blurred answer
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Practical Management Science
Practical Management Science
Operations Management
ISBN:
9781337406659
Author:
WINSTON, Wayne L.
Publisher:
Cengage,
Operations Management
Operations Management
Operations Management
ISBN:
9781259667473
Author:
William J Stevenson
Publisher:
McGraw-Hill Education
Operations and Supply Chain Management (Mcgraw-hi…
Operations and Supply Chain Management (Mcgraw-hi…
Operations Management
ISBN:
9781259666100
Author:
F. Robert Jacobs, Richard B Chase
Publisher:
McGraw-Hill Education
Business in Action
Business in Action
Operations Management
ISBN:
9780135198100
Author:
BOVEE
Publisher:
PEARSON CO
Purchasing and Supply Chain Management
Purchasing and Supply Chain Management
Operations Management
ISBN:
9781285869681
Author:
Robert M. Monczka, Robert B. Handfield, Larry C. Giunipero, James L. Patterson
Publisher:
Cengage Learning
Production and Operations Analysis, Seventh Editi…
Production and Operations Analysis, Seventh Editi…
Operations Management
ISBN:
9781478623069
Author:
Steven Nahmias, Tava Lennon Olsen
Publisher:
Waveland Press, Inc.