REVISED M05_REND6289_10_IM_C05.QXD
5/7/08
4:42 PM
Page 52
C
H
5
A
P
T
E
R
Forecasting Models
TEACHING SUGGESTIONS Teaching Suggestion 5.1: Wide Use of Forecasting.
Forecasting is one of the most important tools a student can master because every firm needs to conduct forecasts. It’s useful to motivate students with the idea that obscure sounding techniques such as exponential smoothing are actually widely used in business, and a good manager is expected to understand forecasting. Regression is commonly accepted as a tool in economic and legal cases.
Week
Actual Bicycle Sales
Three-Week Moving Average
1 2 3 4 5 6 7
8 10 9 11 10 13 —
(8 ϩ 10 ϩ 9)/3 ϭ 9 (10 ϩ 9 ϩ 11)/3 ϭ 10 (9 ϩ 11 ϩ 10)/3 ϭ 10 (11 ϩ 10 ϩ 13)/3 ϭ 11Z\c
Teaching Suggestion 5.2: Forecasting as an Art and a Science.
Forecasting is as much an art as a science. Students should understand that qualitative analysis ( judgmental modeling) modeling) plays an important role in predicting the future since not every factor can be quantified. Sometimes the best forecast is done by seat-of-thepants methods. Teaching Suggestion 5.3: Use of Simple Models.
Many managers want to know what goes on behind the forecast. They may feel uncomfortable with complex statistical models with too many variables. They also need to feel a part of the process.
ϭ
Teaching Suggestion 5.5: Wide Use of Adaptive Models.
With today’s dominant use of computers in forecasting, it is possible for a program to constantly track the accuracy of a model’s forecast. It’s important to understand that a program can automatically select the best alpha and beta weights in exponential smoothing. Even if a firm has 10,000 products, the constants can be selected very quickly and easily without human intervention.
ALTERNATIVE EXAMPLES Alternative Example 5.1: ∑ demand in previous n periods Moving average = n
Bicycle sales at Bower’s Bikes are shown in the middle column of the following table. A 3-week moving average appears on the right.
52
)(demand in period n) ∑ (weight for period n)(demand ∑ weights
Bower’s Bikes decides to forecast bicycle sales by weighting the past 3 weeks as follows: Weights Applied
Period
3 2 1 6
Last week Two weeks ago Three weeks ago Sum of weights
Teaching Suggestion 5.4: Management 5.4: Management Input to the Exponential Exponential Smoothing Model.
One of the strengths of exponential smoothing is that it allows decision makers to input constants that give weight to recent data. Most managers want to feel a part of the modeling process and appreciate the opportunity to provide input.
Weighted moving average
Alternative Example 5.2:
A 3-week weighted moving average appears below.
Week
Actual Bicycle Sales
1 2 3 4 5 6 7
8 10 9 11 10 13 —
Three-Week Moving Average
[(3 ϫ 9) ϩ (2 ϫ 10) ϩ (1 ϫ 8)]/6 ϭ 9Z\n [(3 ϫ 11) ϩ (2 ϫ 9) ϩ (1 ϫ 10)]/6 ϭ 10Z\n [(3 ϫ 10) ϩ (2 ϫ 11) ϩ (1 ϫ 9)]/6 ϭ 10Z\n [(3 ϫ 13) ϩ (2 ϫ 10) ϩ (1 ϫ 11)]/6 ϭ 11X\c
Alternative Example 5.3: A firm uses simple exponential smoothing with a ϭ 0.1 to forecast demand. The forecast for the week of January 1 was 500 units, whereas actual demand turned out to be 450 units. The demand forecasted for the week of January 8 is calculated as follows. F t ϩ1 ϭ F t ϩ α ( A At Ϫ F t ) ϭ
500 ϩ 0.1(450
Ϫ
500)
ϭ
495 units
REVISED M05_REND6289_10_IM_C05.QXD
5/7/08
4:42 PM
Page 53
53
FORECASTING MO D E L S
CHAPTER 5
Exponential smoothing is used to forecast automobile battery sales. Two values of ␣ are examined, ␣ ϭ 0.8 and ␣ ϭ 0.5. To evaluate the accuracy of each smoothing constant, we can compute the absolute deviations and MADs. Assume that the forecast for January was 22 batteries. Alternative Example 5.4:
Actual Battery Sales
Month January February March April May June
20 21 15 14 13 16
Absolute Deviation with ␣ ϭ 0.8
Forecast with ␣ ϭ 0.8
Absolute Deviation with ␣ ϭ 0.5
Forecast with ␣ ϭ 0.5
22 2 20.40 0.6 20.880 5.88 16.176 2.176 14.435 1.435 13.287 2.713 Sum of absolute deviations: 15
22 21 21 18 16 14.5
2 0 6 4 3 31.5 16.5
MAD: 2.46
On the basis of this analysis, a smoothing constant of ␣ ϭ 0.8 is preferred to ␣ ϭ 0.5 because it has a smaller MAD. Use the sales data given below to determine: (a) the least squares trend line, (b) the predicted value for 2000 sales.
2.75
The rated power capacity (in hours/ week) over the past 6 years has been: Alternative Example 5.6:
Alternative Example 5.5:
Year
Sales (Units)
1993 1994 1995 1996 1997 1998 1999
100 110 122 130 139 152 164
1 2 3 4 5 6
1993 1994 1995 1996 1997 1998 1999
Time Period
Sales (Units)
1 2 3 4 5 6 17 ͚ x ϭ 28
100 110 122 130 139 152 164 ͚ y ϭ 917
x 2
1 4 9 16 25 36 149 2 ͚ x ϭ 140
xy
100 220 366 520 695 912 1,148 ͚ xy ϭ 3,961
∑ y 917 28 y = =4 = = 131 n n 7 7 3, 96 961 − (7 )(4 )(131) 293 ∑ xy − nxy b= 464 = = = 10.464 2 2 28 x nx 140 − (7 )(4 2 ) ∑ −
x =
∑x
115 120 118 124 123 130
Here is an alternative way to recode years which simplifies the math since ͚ X ϭ 0.
To minimize computations, transform the value of x x (time) to simpler numbers. In this case, designate 1993 as year 1, 1994 as year 2, and so on.
Year
Rated Capacity (hrs/wk)
Year
=
a = y − bx = 131 − 10.46 46 (4 ) = 89 .14 14
Therefore, the least squares trend equation is, yˆ = a + bx = 89.14 1 4 + 10 .4 64 64 x To project demand in 2000, we denote the year 2000 as x ϭ 8, Sales in 2000 ϭ 89.14 ϩ 10.464(8)
ϭ
172.85
Renumb Renumbere ered d Year ( x )
Year 1 2 3 4 5 6
Capaci Capacity ty ( y )
Ϫ2.5
ϩ2.5
115 120 118 124 123 130
0
͚Y ϭ
Ϫ1.5 Ϫ.5 ϩ.5 ϩ1.5
͚ X ϭ
b
=
a=
∑ XY ∑ X ∑ Y
n
2
=
=
45 17.5
730
x 2
6.25 2.25 0.25 0.25 2.25 6.25 2 ͚ X ϭ 17.5
xy Ϫ287.5 Ϫ180 Ϫ59 ϩ62 ϩ184.5 ϩ325
͚ XY ϭ
45
= 2.57
730 121.67 = 121 6
y ϭ 121.67
ϩ
2.57 X
Year 7 ϭ 121.67
ϩ
(2.57)(3.5)
ϭ131
The forecast demand and actual demand for 10-foot fishing boats are shown below. We compute the tracking signal and MAD. ∑ Forecast errors 70 MAD MAD = = = 11.7 n 6 RSFE −24 racking Signal = = = − 2.1 MADs MAD MAD 11.7 Alternative Example 5.7:
REVISED M05_REND6289_10_IM_C05.QXD
54
5/7/08
4:42 PM
CHAPTER 5
Page 54
FORECASTING MODELS
Table for Alternate Example 5.7 Year 1 2 3 4 5 6
Forecast Demand
Actual Demand
78 75 83 84 88 85
71 80 101 84 60 73
Error
RSFE
Ϫ7
Ϫ7
5 18 0 Ϫ28 Ϫ12
Ϫ2
Cumulative Error
MAD
7 5 18 0 28 12
7 12 30 30 58 70
7.0 6.0 10.0 7.5 11.6 11.7
16 16 Ϫ12 Ϫ24
SOLUTIONS TO DISCUSSION QUESTIONS AND PROBLEMS 5-1.
Forecast Error
The steps that are used to develop any forecasting system
are: 1. Determine Determine the use of the the foreca forecast. st. 2. Select Select the items items or quantitie quantitiess that are to be be forecasted forecasted..
5. Gather Gather the necess necessary ary data. data.
8. Implem Implement ent the result results. s. 5-2. A time-series forecasting model uses historical data to predict future trends. 5-3. The only difference between causal models and timeseries models is that causal models take into account any factors that may influence the quantity being forecasted. Causal models use historical data as well. Time-series models use only historical data.
Qualitative models incorporate subjective factors into the forecasting model. Judgmental models are useful when subjective factors are important. When quantitative data are difficult to obtain, qualitative models are appropriate. 5-4.
The disadvantages of the moving average forecasting model are that the averages always stay within past levels, and the moving averages do not consider seasonal variations. 5-5.
5-6. When the smoothing value, ␣, is high, more weight is given to recent data. When ␣ is low, more weight is given to past data. 5-7. The Delphi technique involves analyzing the predictions that a group of experts have made, then allowing the experts to review the data again. This process may be repeated several times. After the final analysis, the forecast is developed. The group of experts may be geographically dispersed. dispersed.
MAD is a technique for determining the accuracy of a forecasting model by taking the average of the absolute deviations. 5-8.
Ϫ0.3 ϩ1.6 ϩ2.1 Ϫ1.0 Ϫ2.1
5-9. If a seasonal index equals 1, that season is just an average season. If the index is less than 1, that season tends to be lower than average. If the index is greater than than 1, that season tends to be higher than average. 5-10.
If the smoothing constant equals 0, then At Ϫ F t ) ϭ F t F t ϩ1 ϭ F t ϩ 0( A
This means that the forecast never changes. If the smoothing constant equals 1, then
6. Validate Validate the forecastin forecasting g model. model. 7. Make Make the the for forec ecas ast. t.
Ϫ1.0
MAD is important because it can be used to help increase forecasting accuracy.
3. Determine Determine the the time time horizon horizon of the the forecast. forecast. 4. Select Select the the forecas forecastin ting g model. model.
Tracking Signal
F t ϩ1 ϭ F t ϩ 1( A At Ϫ F t ) ϭ At
This means that the forecast is always equal to the actual value in the prior period. 5-11. A centered moving average (CMA) should be used if trend is present in data. If an overall average is used rather rather than a CMA, variations due to trend will be interpreted as variations due to seasonal factors. Thus, the seasonal seasonal indices will not be accurate. 5-12. Mont Month h Jan. Feb. Mar. Apr. May June July Aug. Sept. O c t. Nov. Dec.
Actual Shed Shed Sale Saless
Four Four-M -Mon onth th Movi Moving ng Aver Averag age e
10 12 13 16 19 23 26 30 28 18 16 14
(10 ϩ 12 ϩ 13 ϩ 16)/4 ϭ 51/4 ϭ 12.75 (12 ϩ 13 ϩ 16 ϩ 19)/4 ϭ 60/4 ϭ 15 (13 ϩ 16 ϩ 19 ϩ 23)/4 ϭ 70/4 ϭ 17.75 (16 ϩ 19 ϩ 23 ϩ 26)/4 ϭ 84/4 ϭ 21 (19 ϩ 23 ϩ 26 ϩ 30)/4 ϭ 98/4 ϭ 24.5 (23 ϩ 26 ϩ 30 ϩ 28)/4 ϭ 107/4 ϭ 26.75 (26 ϩ 30 ϩ 28 ϩ 18)/4 ϭ 102/4 ϭ 25.5 (30 ϩ 28 ϩ 18 ϩ 16)/4 ϭ 92/4 ϭ 23
The MAD ϭ 7.78 See solution to 5-13 for calculations.
REVISED M05_REND6289_10_IM_C05.QXD
5/7/08
4:42 PM
Page 55
CHAPTER 5
55
FORECASTING MO D E L S
5-13.
Month
Actual Shed Sales
ThreeMonth Forecast
10 12 13 16 19 23 26 30 28 18 16 14
11.66 13.66 16 19.33 22.66 26.33 28 25.33 20.66
Jan. F eb . Mar. Apr. May June July Aug. Sept. Oct. Nov. Dec.
Three-month MAD = Four-month MAD =
5835 . 9
ThreeMonth Absolute Deviation
4.34 5.34 7 6.67 7.34 1.67 10 9.33 56.66 58.35
FourMonth Forecast
12.75 15 17.75 21 24.5 26.75 25.5 23
FourMonth Absolute Deviation
6.25 8 8.25 9 3.5 8.75 9.5 69.25 62.25
The 3-month moving average appears to be more accurate. However, if weighted moving averages had been used, the results might be different.
= 6.48
6225 . = 7.78 8
5-14. Year 1 2 3 4 5 6 7 8 9 10 11
Demand 4 6 4 5 10 8 7 9 12 14 15
Three-Year Moving Averages
(4 ϩ 6 ϩ 4)/3 (6 ϩ 4 ϩ 5)/3 (4 ϩ 5 ϩ 10)/3 (5 ϩ 10 ϩ 8)/3 (10 ϩ 8 ϩ 7)/3 (8 ϩ 7 ϩ 9)/3 (7 ϩ 9 ϩ 12)/3 (9 ϩ 12 ϩ 14)/3
2 4 ⁄ 3 ϭ5 1 3 ϭ 6 ⁄ 2 3 ϭ 7 ⁄ 1 3 ϭ 8 ⁄ ϭ8 1 3 ϭ 9 ⁄ 2 3 ϭ 11 ⁄
ϭ
Weighted Three-Year Moving Averages
sum of the weigh ts a 1 [(2 ϫ 4) ϩ 6 ϩ 4]/4 ϭ 4 ⁄ 2 [(2 ϫ 5) ϩ 4 ϩ 6]/4 ϭ 50 1 4 [(2 ϫ 10) ϩ 5 ϩ 4]/4 ϭ 7 ⁄ 3 4 [(2 ϫ 8) ϩ 10 ϩ 5]/4 ϭ 7 ⁄ [(2 ϫ 7) ϩ 8 ϩ 10]/4 ϭ 80 1 4 [(2 ϫ 9) ϩ 7 ϩ 8]/4 ϭ 8 ⁄ [(2 ϫ 12) ϩ 9 ϩ 7]/4 ϭ 10 1 4 [(2 ϫ 14) ϩ 12 ϩ 9]/4 ϭ 12 ⁄ Total absolute deviations:
MAD for 3-year average ϭ 2.54 MAD for weighted 3-year average
ϭ
2.32
The weighted moving average appears to be slightly more accurate in its annual forecasts. 5-15.
Using Excel or QM for Windows, the trend line is Y ϭ 2.22 ϩ 1.05 X
Where X ϭ time period (1, 2, . . .) Y ϭ demand
Three-Year Absolute Deviation
0.34 5.55 1.67 0.67 0.67 4.55 4.67 3.34 20.36
Three-Year Weighted Absolute Deviation
0.55 5.55 0.75 0.75 1.55 3.75 4.55 2.75 18.5
REVISED M05_REND6289_10_IM_C05.QXD
56
5/7/08
CHAPTER 5
4:42 PM
Page 56
FORECASTING MODELS
5-16. Using the forecasts in the previous problems we obtain the absolute deviations given in the table below.
Year Year
Dema Demand nd
3-Yr MA |dev |devia iati tion on||
3-Yr Wt. MA |dev |devia iati tion on||
Trend line |dev |devia iati tion on||
— — — 0.33 5.00 1.67 0.67 0.67 4.00 4.67 3.33
— — — 0.50 5.00 0.75 0.75 1.00 3.75 4.00 2.75
0.73 1.67 1.38 1.44 2.51 0.55 2.60 1.65 0.29 1.24 1.18
20.33
18.50
15.24
11 14 12 16 13 14 14 15 15 10 16 18 17 17 18 19 19 12 10 14 11 15 Total absolute deviations ϭ
Year 1 2 3 4 5 6 7 8 9 10 11
Demand 4,000 6,000 4,000 5,000 10,000 8,000 7,000 9,000 12,000 14,000 15,000
ϭ
5,000 ϩ (0.3)(Ϫ 1,000)
ϭ
5,000 Ϫ 300
ϭ
4,700
Year
Demand
New Forecast
2 3 4 5 6 7 8 9 10 11
6,000 4,000 5,000 10,000 8,000 7,000 9,000 12,000 14,000 15,000
4,700 ϭ 5,000 ϩ (0.3)(4,000 Ϫ 5,000) 5,090 ϭ 4,700 ϩ (0.3)(6,000 Ϫ 4,700) 4,763 ϭ 5,090 ϩ (0.3)(4,000 Ϫ 5,090) 4,834 ϭ 4,763 ϩ (0.3)(5,000 Ϫ 4,763) 6,384 ϭ 4,834 ϩ (0.3)(10,000 Ϫ 4,834) 6,869 ϭ 6,384 ϩ (0.3)(8,000 Ϫ 6,384) 6,908 ϭ 6,869 ϩ (0.3)(7,000 Ϫ 6,869) 7,536 ϭ 6,908 ϩ (0.3)(9,000 Ϫ 6,908) 8,875 ϭ 7,536 ϩ (0.3)(12,000 Ϫ 7,536) 10,412 ϭ 8,875 ϩ (0.3)(14,000 Ϫ 8,875)
The mean absolute deviation (MAD) can be used to determine which forecasting method is more accurate.
Absolute Deviation
4,500 5,000 7,250 7,750 8,000 8,250 10,000 12,250 Total: Mean:
new forecast for year 2 ϭ 5,000 ϩ (0.3)(4,000 Ϫ 5,000)
The calculations are:
MAD (3-year moving average) ϭ 2.54 MAD (3-year weighted moving average) ϭ 2.31 MAD (trend line) ϭ 1.39 The trend line is best because the MAD is lowest.
Weighted Moving Average
␣ ϭ 0.3. New forecast for year 2 is last period’s forecast ϩ period’s actual demand Ϫ last period’s forecast):
5-17. ␣(last
500 5,000 750 750 1,000 3,750 4,000 12,750 18,500 2,312.5
Exp. Sm. 5,000 4,700 5,090 4,763 4,834 6,384 6,869 6,908 7,536 8,875 10,412
Absolute Deviation 1,000 1,300 1,090 237 5,166 1,616 131 2,092 4,464 5,125 14,588 26,808 2,437
Thus, the 3-year weighted moving average model appears to be more accurate.
5-18.
Year
1
2
3
4
5
6
Forecast
410.0
422.0
443.9
466.1
495.2
521.8
5-19. Year 1 2 3 4 5 6
Sales
Forecast Using ␣ ϭ 0.6
450 495 518 563 584 ?
410 ϩ (0.6) (450 Ϫ 410) ϭ 434 434 ϩ (0.6) (495 Ϫ 434) ϭ 470.6 470.6 ϩ (0.6)(518 Ϫ 470.6) ϭ 499.0 499 ϩ (0.6) (563 Ϫ 499) ϭ 537.4 537.4 ϩ (0.6)(584 Ϫ 537) ϭ 565.6
Forecast Using ␣ ϭ 0.9 410 ϩ (0.9)(450 Ϫ 410) ϭ 446 446 ϩ (0.9)(495 Ϫ 446) ϭ 490.1 490.1 ϩ (0.9)(518 Ϫ 490.1) ϭ 515.21 515.21 ϩ (0.9)(563 Ϫ 515.21) ϭ 558.2 558.221 ϩ (0.9)(584 Ϫ 558.2) ϭ 581.4
REVISED M05_REND6289_10_IM_C05.QXD
5/7/08
4:42 PM
Page 57
CHAPTER 5
FORECASTING MO D E L S
5-20. Actual Sales
Year 1 2 3 4 5 6
␣ ϭ 0.3 Forecast
Absolute Deviation
␣ ϭ 0.6 Forecast
Absolute Deviation
␣ ϭ 0.9 Forecast
410.0 434.0 470.6 499.0 537.4 565.8
40.0 61.0 47.4 64.0 46.6 — 259.0
410.0 446.0 490.1 515.2 558.2 581.4
450 410.0 40.0 495 422.0 73.0 518 443.9 74.1 563 466.1 96.9 584 495.2 88.8 ? 521.8 — Total absolute deviation 372.8
Absolute Deviation 40.0 49.0 27.9 47.8 25.8 — 190.5
MAD␣ϭ0.3 ϭ 372.8/5 ϭ 74.56 MAD␣ϭ0.6 ϭ 259/5
ϭ
51.8
MAD␣ϭ0.9 ϭ 190.5/5 ϭ 38.1 Because it has the lowest MAD, the smoothing constant gives the most accurate forecast.
␣ ϭ
0.9
5-21. Year Year 1 2 3 4 5 6
Sale Saless
Thr Three-Y ee-Yea earr Movi Moving ng Aver Avera age
450 495 518 563 584 ?
(450 ϩ 495 ϩ 518)/3 ϭ 487.667 (495 ϩ 518 ϩ 563)/3 ϭ 525.333 (518 ϩ 563 ϩ 584)/3 ϭ 555
5-22. Time Period
Sales
Year
X
Y
1 2 3 4 5
1 2 3 4 5
450 495 518 563 2,584 2, 584 2,610
X 2
XY
1 4 9 16 125 55
450 990 1554 2252 2920 8166
b ϭ 33.6 a ϭ 421.2 Y ϭ 421.2
ϩ
33.6 X
Projected sales in year 6, Y ϭ 421.2 ϩ (33.6)(6) ϭ
622.8
5-23. Year Year 1 2 3 4 5 6
Actu Actual al Sale Saless
Three-Year Moving Aver Averag age e Fore Foreca cast st
450 — 495 — 518 — 563 487.7 584 525.3 ? 555.0 Total absolute deviation
Abso Absolu lute te Devi Deviat atio ion n — — — 75.3 58.7 — 134.0
Time-Series Fore Foreca cast st 454.8 488.4 522.0 555.6 589.2 622.8
Abso Absolu lute te Devi Deviat atio ion n 4.8 6.6 4.0 7.4 5.2 — 28.0
57
REVISED M05_REND6289_10_IM_C05.QXD
58
5/7/08
4:42 PM
Page 58
FORECASTING MODELS
CHAPTER 5
MAD␣ϭ0.3 ϭ 74.56
(see Problem 5-20)
MADmoving average ϭ 134/2
ϭ
67
MADregression ϭ 28/5 ϭ 5.6 Regression (trend line) is obviously the preferred method because of its low MAD. 5-24. To answer the discussion questions, two forecasting models are required: a three-period moving average and a three-period weighted moving average. Once the actual forecasts have been made, their accuracy can be compared using the mean average differences (MAD).
a, b. Peri Period od
Mont Month h
Dema Demand nd
Apr. May June July Aug. Sept. Oct. Nov. Dec. Jan. Feb.
10 15 17 11 14 17 12 14 16 11 –
4 5 6 7 8 9 10 11 12 13 14
Aver Averag age e
Weig Weight hted ed Aver Averag age e
13.67 13.33 13.67 14 14.33 14 14 14.33 14.33 14 13.67
14.5 12.67 13.5 15.17 13.67 13.50 15 14 13.83 14.67 13.17
c. MAD for for moving moving average average is is 2.2. MAD MAD for weight weighted ed averaverage is 2.72. Moving average forecast for February is 13.6667. Weighted moving average forecast for February is 13.1667. Because a three-period average forecasting method is used, forecasts start for period 4. As can be seen, the MAD for the moving average is 2.2, and the MAD for the weighted moving average is 2.7. Thus, based on this analysis, the moving average appears to be more accurate. The forecast for February is about 14. d. There There are many other other factors factors to consid consider, er, includin including g seasonality and any underlying causal variables such as advertising budget. 5-25.
a.
Week
Actual Miles
Forecast (F t )
1 2 3 4 5 6 7 8 9 10 11 12
17 21 19 23 18 16 20 18 22 20 15 22
17.00 17.00 17.80 18.04 19.03 18.83 18.26 18.61 18.49 19.19 19.35 18.48
Error
RSFE
—
—
ϩ4.00
ϩ4.00
ϩ1.20
ϩ5.20
ϩ4.96
ϩ10.16
Ϫ1.03
ϩ9.13
Ϫ2.83
ϩ6.30
ϩ1.74
ϩ8.04
Ϫ0.61
ϩ7.43
ϩ3.51
ϩ10.94
ϩ0.81
ϩ11.75
Ϫ4.35
ϩ7.40
ϩ3.52
ϩ10.92
Sum of Absolute Forecast Errors
MAD
Tr Track Signal
— 4.00 5.20 10.16 11.19 14.02 15.76 16.37 19.88 20.69 25.04 28.56
— 4.00 2.60 3.39 2.80 2.80 2.63 2.34 2.49 2.30 2.50 2.60
— 1 2 3 3.3 2.25 3.05 3.17 4.21 5.11 2.96 4.20
REVISED M05_REND6289_10_IM_C05.QXD
5/7/08
4:42 PM
Page 59
CHAPTER 5
FORECASTING MO D E L S
b. The The tota totall MAD MAD is 2.60 2.60.. c. RSFE is is consistent consistently ly positive positive.. Tracking Tracking signal signal exceeds exceeds 5 MADs at week 10. This could indicate a problem. 5-26. a, b. See the the accompany accompanying ing table table for for a compariso comparison n of the calculations for the exponentially smoothed forecasts using constants of 0.1 and 0.6. c. Students Students should should note note how how stable stable the smoothed smoothed values values for for the 0.1 smoothing constant are. When compared to actual week 25 calls of 85, the 0.6 smoothing constant appears to do a better job. On the basis of the forecast error, the 0.6 constant is better also. However, other smoothing constants need to be examined.
Week,
Actual Value,
t
At
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
50 35 25 40 45 35 20 30 35 20 15 40 55 35 25 55 55 40 35 60 75 50 40 65
Smoothed Value, F t (␣ ϭ 0.1) 50 50 48 46 45 45 44 42 41 40 38 36 36 38 38 37 38 40 40 40 42 45 45 45 47
Forecast Error
Smoothed Value, F t (␣ ϭ 0.6)
— Ϫ15 Ϫ23 Ϫ6
0 Ϫ10 Ϫ24 Ϫ12 Ϫ6 Ϫ20 Ϫ23 ϩ4 ϩ19 Ϫ3 Ϫ13 ϩ18 ϩ16 0 Ϫ5 ϩ20 ϩ33 ϩ5 Ϫ5 ϩ20
Forecast Error —
50 41 31 37 42 38 27 29 32 25 19 32 46 39 31 45 51 44 39 51 66 56 46 58
Ϫ15 Ϫ16 ϩ8 ϩ9 Ϫ7 Ϫ18 ϩ3 ϩ6 Ϫ12 Ϫ10 ϩ21 ϩ23 Ϫ11 Ϫ14 ϩ24 ϩ10 Ϫ12 Ϫ10 ϩ21 ϩ23 Ϫ16 Ϫ16 ϩ18
59
REVISED M05_REND6289_10_IM_C05.QXD
60
5-27.
5/7/08
4:42 PM
Page 60
FORECASTING MODELS
CHAPTER 5
Using data from Problem 5-26, with Actu Actual al Value
Smoo Smooth thed ed Value
Week
At
F t
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
50 35 25 40 45 35 20 30 35 20 15 40 55 35 25 55 55 40 35 60 75 50 40 65
50 50 36 26 39 44 36 22 29 34 21 16 38 53 37 26 52 55 41 36 58 73 52 41 62
␣ϭ
5-30. Using QM for Windows, we select Forecasting - Time Series and multiplicative decomposition. Then specify Centered Moving Average and we have the following results: a. Quar Quarte terr 1 ind index ex ϭ 0.8825; Quarter 2 index ϭ 0.9816; Quarter 3 index ϭ 0.9712; Quarter 4 index ϭ 1.1569
0.9
Forecast Error —
b. The The tre trend ndli line ne is Y ϭ 237.7478 ϩ 3.6658 X c. Quar Quarte terr 1: Y ϭ 237.7478 ϩ 3.6658(17) ϭ 300.0662
Ϫ15 Ϫ11
Quarter 2: Y ϭ 237.7478 Quarter 3: Y ϭ 237.7478 Quarter 4: Y ϭ 237.7478
14 6 Ϫ9 Ϫ16 8 6 Ϫ14 Ϫ6 24 17 Ϫ18 Ϫ12 29 3 Ϫ15 Ϫ6 24 17 Ϫ23 Ϫ12 24
␣ϭ
ϭ
Quarter 3: 307.3978(0.9712) Quarter 4: 311.0636(1.1569) 5-31.
ϭ ϭ
298.5336 359.8719
Letting
t ϭ time time perio period d (1, (1, 2, 3, . . . , 16) 16) Q1 ϭ 1 if quarter 1, 0 otherwise Q2 ϭ 1 if quarter 2, 0 otherwise Q3 ϭ 1 if quarter 3, 0 otherwise Note: if Q1 ϭ Q2 ϭ Q3 ϭ 0, then it is quarter 4.
Using computer software we get ϩ 3.7t Ϫ 75.7 Q1 Ϫ 48.9 Q2 Ϫ 52.1Q3
Y ϭ 281.6
The forecasts for the next 4 quarters are: Y ϭ 281.6 ϩ 3.7(17) Ϫ 75.7(1) Ϫ 48.9(0) Y ϭ 281.6 ϩ 3.7(18)
Note that in this problem, the initial forecast (for the first period) was not used in computing the MAD. Either approach is considered valid. Exponential smoothing with
0.1
5-32.
For a smoothing constant of 0.2, the forecast for year 11
Forecast
Error
Year
Rate
Forecast
|Error|
F eb . March April May June July Aug.
70.0 68.5 64.8 71.7 71.3 72.8
65.0 65.0 ϩ 0.1 (70 Ϫ 65) ϭ 65.5 65.5 ϩ 0.1(68.5 Ϫ 65.5) ϭ 65.8 65.8 ϩ 0.1(64.8 Ϫ 65.8) ϭ 65.7 65.7 ϩ 0.1(71.7 Ϫ 65.7) ϭ 66.3 66.3 ϩ 0.1(71.3 Ϫ 66.3) ϭ 66.8 66.8 ϩ 0.1(72.8 Ϫ 66.8) ϭ 67.4
— 3.0 Ϫ1.0 6.0 5.0 6.0
1 2 3 4 5 6 7 8 9 10 11
7.2 7 6.2 5.5 5.3 5.5 6.7 7.4 6.8 6.1
7.2 7.2 7.16 6.968 6.674 6.400 6.220 6.316 6.533 6.586 6.489
0 0.2 0.96 1.468 1.374 0.900 0.480 1.084 0.267 0.486
MAD ϭ 4.20
Note that in this problem, the initial forecast (for the first period) was not used in computing the MAD. Either approach is considered valid. ␣ϭ
52.1(0) ϭ 268.7
is 6.489.
Income
Exponential smoothing with
Ϫ
75.7(0) Ϫ 48.9(1) Ϫ 52.1(0) ϭ 299.2 Y ϭ 281.6 ϩ 3.7(19) Ϫ 75.7(0) Ϫ 48.9(0) Ϫ 52.1(1) ϭ 299.7 Y ϭ 281.6 ϩ 3.7(20) Ϫ 75.7(0) Ϫ 48.9(0) Ϫ 52.1(0) ϭ 355.4 Ϫ
Month
5-29.
303.7320 307.3978 ϭ 311.0636 ϭ
ϩ
d. Quarte Quarterr 1: 1: 300.0 300.066 662(0 2(0.88 .8825 25)) ϭ 264.7938 Quarter 2: 303.7320(0.9816) ϭ 298.1579
MAD ϭ 14.48
5-28.
3.6658(18) 3.6658(19) ϩ 3.6658(20) ϩ
0.3
MAD MAD = 0.72 0.722 2
For a smoothing constant of 0.4, the forecast for year 11 is 6.458. Mont Month h
Inco Incom me
For Forecas ecastt
Erro Errorr
F eb . March April May June July Aug.
70.0 68.5 64.8 71.7 71.3 72.8
65.0 66.5 67.1 66.4 68.0 69.0 70.1
— 2.0 Ϫ2.3 5.3 3.3 3.8
MAD ϭ 3.34
Based on MAD, ␣ (of Problem 5-28).
ϭ
0.3 produces a better forecast than
␣ ϭ
0.1
Note that in this problem, the initial forecast (for the first period) was not used in computing the MAD. Either approach is considered valid.
Year
Rate
Forecast
|Error|
1 2 3 4 5 6 7 8 9 10 11
7.2 7 6.2 5.5 5.3 5.5 6.7 7.4 6.8 6.1
7.2 7.2 7.12 6.752 6.251 5.871 5.722 6.113 6.628 6.697 6.458
0 0.2 0.92 1.252 0.951 0.371 0.978 1.287 0.172 0.597
MAD = 0.673
REVISED M05_REND6289_10_IM_C05.QXD
5/7/08
4:42 PM
Page 61
For a smoothing constant of 0.6, the forecast for year 11 is 6.401. Year
Rate
Forecast
|Error|
1 2 3 4 5 6 7 8 9 10 11
7.2 7 6.2 5.5 5.3 5.5 6.7 7.4 6.8 6.1
7.2 7.2 7.08 6.552 5.921 5.548 5.519 6.228 6.931 6.852 6.401
0 0.2 0.88 1.052 0.621 0.048 1.181 1.172 0.131 0.752
61
FORECASTING MO D E L S
CHAPTER 5
5-33. To compute a seasonalized or adjusted sales forecast, we just multiply each seasonal index index by the appropriate appropriate trend trend forecast. ˆ ϭ seasonal index ϫ Y ˆ Y trend forecast forecast
Hence for: ˆ ϭ (1.30)($100,000) Quarter I: Y I ˆ Quarter II: Y II ˆ Quarter III: Y III ˆ Quarter IV: Y
ϭ
$130,000
ϭ
(0.90)($120,000)
ϭ
$108,000
ϭ
(0.70)($140,000)
ϭ
$98,000
IV ϭ
(1.10)($160,000)
ϭ
$176,000
5-34.
(Average demand for season)
ϭ
MAD = 0.604 0.604
(yearr 1 demand)+ (yea demand)+ (year2 (year2 dema demand) nd) 2
For a smoothing constant of 0.8, the forecast for year 11 is 6.256. Year 1 2 3 4 5 6 7 8 9 10 11
Rate
Forecast
|Error|
7.2 7 6.2 5.5 5.3 5.5 6.7 7.4 6.8 6.1
7.2 7.2 7.04 6.368 5.674 5.375 5.475 6.455 7.211 6.882 6.256
0 0.2 0.84 0.868 0.374 0.125 1.225 0.945 0.411 0.782
Overall average (sum of all values ) = demand 8 Season index =
Year 3 demand = =
(average for season) overall average erage demand new annual demand 4 1, 200 200 4
× season index
MAD = 0.577 0.577
The lowest MAD is 0.577 for a smoothing constant of 0.8.
Solution Table for Problem 5-34
Season
Year 1 Demand
Year 2 Demand
(Average Year 1Year 2 Demand)
Average Season Demand
Season Index
Year 3 Demand
Fall Winter Spring Summer
200 350 150 300
250 300 165 285
225.0 325.0 157.5 292.5
250 250 250 250
0.90 1.30 0.63 1.17
270 390 189 351
5-35. Using Excel, the trend equation is Y ϭ 1582.61 ϩ 612.37X.
For 2008, X ϭ 19; Y ϭ 1582.61
ϩ
612.37(19)
ϭ
13217.6
For 2009, X ϭ 20; Y ϭ 1582.61
ϩ
612.37(20)
ϭ
13830.0
For 2010, X ϭ 21; Y ϭ 1582.61
ϩ
612.37(21)
ϭ
14442.4
The MSE from the Excel output is 1654334.7. 5-36. a. With a smoothing smoothing constant of of 0.3, the forecast for for 2008
is 11211.2 with MSE ϭ 3246841. b. Using QM for Windows, the best smoothing constant is 1.0. This gives the lowest MSE of 1443842. 5-37.
Using Excel, the trend equation is Y ϭ 1.1940
For January of 2007, X ϭ 13; Y ϭ 1.1940
ϩ
For February of 2007, X ϭ 14; Y ϭ 1.1940 5-38.
ϩ
0.0095(13)
ϩ
0.0095(14)
0.0095X. ϭ
1.318.
ϭ
1.327.
The forecast for January 2007 would be 1.286.
The MSE with the trend equation is 0.0003. The MSE with this exponential smoothing model is 0.0010.
SOLUTIONS TO INTERNET HOMEWORK PROBLEMS 5-39. With a ϭ 0.4, forecast for 2004 ϭ 10,339 and MAD ϭ 837. With a ϭ 0.6, forecast for 2004 ϭ 10,698 and MAD ϭ 612. 5-40. Using Excel, the trend line is: GDP ϭ 6142.7 ϩ 441.4(time). For 2004 (time ϭ 12) the forecast is GDP ϭ 6142.7 ϩ 441.4(12) ϭ 11,439.5. 5-41. The trend line found using Excel is: Patients ϭ 29.73 ϩ 3.28(time). Note these coefficients are rounded. For the next 3 years years (time (time ϭ 11, 12, and 13) the forecasts for the number of patients are: Patients ϭ 29.73 ϩ 3.28(11) ϭ 65.8 Patients ϭ 29.73 ϩ 3.28(12) ϭ 69.1 Patients ϭ 29.73 ϩ 3.28(13) ϭ 72.4 The coefficient of determination is 0.85, so the model is a fair model.
REVISED M05_REND6289_10_IM_C05.QXD
62
5/7/08
4:42 PM
Page 62
FORECASTING MODELS
CHAPTER 5
The trend line found using Excel is: Crime Rate ϭ 51.98 6.09(time). Note these coefficients are rounded. For the next next 3 year yearss (time (time ϭ 11, 12, and 13) the forecasts for the crime rates are: Crime Rate ϭ 51.98 ϩ 6.09(11) ϭ 118.97 Crime Rate ϭ 51.98 ϩ 6.09(12) ϭ 125.06 Crime Rate ϭ 51.98 ϩ 6.09(13) ϭ 131.15 The coefficient of determination is 0.96, so this is a very good model. 5-42.
ϩ
5-43. The regression equation (from Excel) is: Patients ϭ 1.23 ϩ 0.54(crime rate). Note these coefficients are rounded. If the crime rate is 131.2, the forecast number of patients is:
Patients ϭ 1.23
ϩ
0.54(131.2)
ϭ
72.1
If the crime rate is 90.6, the forecast number of patients is: Patients ϭ 1.23
ϩ
0.54(90.6)
ϭ
50.2
The coefficient of determination is 0.90, so this is a good model. 5-44. With a ϭ 0.6, forecast for 2003 ϭ 86.2 and MAD ϭ 3.42. With a ϭ 0.2, forecast for 2003 ϭ 63.87 and MAD ϭ 7.23. The model with a ϭ 0.6 is better since it has a lower MAD.
5-46. The trend line (coefficients from Excel are rounded) for deposits is:
Deposits ϭ Ϫ18.968 ϩ 1.638(time) For 2003, 2004, and 2005, time ϭ 45, 46, and 47 respectively. The forecasts are: Deposits ϭ Ϫ18.968 ϩ 1.638(45) ϭ 54.7 Deposits ϭ Ϫ18.968 ϩ 1.638(46) ϭ 56.4 Deposits ϭ Ϫ18.968 ϩ 1.638(47) ϭ 58.0 The trend line (coefficients from Excel are rounded) for GSP is: GSP ϭ 0.090 ϩ 0.112(time). The forecasts are: GSP ϭ 0.090 ϩ 0.112(45) ϭ 5.1 GSP ϭ 0.090 ϩ 0.112(46) ϭ 5.2 GSP ϭ 0.090 ϩ 0.112(47) ϭ 5.4 5-47. The regression equation from Excel is Deposits ϭ Ϫ17.64 ϩ 13.59(GSP) In the scatterplot of this data that follows, the pattern appears to change around 1985. There are definitely different relationships before 1985 and after 1985, so perhaps the model should be developed with 1985 as the first year of data.
5-45. With a ϭ 0.6, forecast for 2003 ϭ 4.86 and MAD ϭ 0.23. With a ϭ 0.2, forecast for 2003 ϭ 4.52 and MAD ϭ 0.48. The model with a ϭ 0.6 is better since it has a lower MAD.
Deposits and GSP over Time 100 80 60
DEPOSITS
40
GSP
20 0 1950
1960
1970
1980
Time
1990
2000
2010