• Author / Uploaded
• Najam Hassan

Citation preview

REVISED M05_REND6289_10_IM_C05.QXD

5/7/08

4:42 PM

Page 52

5

C H 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. 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) 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.4: Management Input to the 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. 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

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

Alternative Example 5.2: Weighted moving average ∑ (weight for period n)(demand in period n)) ⫽ ∑ 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

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. Ft⫹1 ⫽ Ft ⫹ α(At ⫺ Ft) ⫽ 500 ⫹ 0.1(450 ⫺ 500) ⫽ 495 units

REVISED M05_REND6289_10_IM_C05.QXD

5/7/08

4:42 PM

Page 53

CHAPTER 5

53

FORECASTING MODELS

Alternative Example 5.4: 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.

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. Alternative Example 5.5: Use the sales data given below to determine: (a) the least squares trend line, (b) the predicted value for 2000 sales. Year

Sales (Units)

1993 1994 1995 1996 1997 1998 1999

100 110 122 130 139 152 164

1993 1994 1995 1996 1997 1998 1999

Alternative Example 5.6: The rated power capacity (in hours/ week) over the past 6 years has been:

Year

Rated Capacity (hrs/wk)

1 2 3 4 5 6

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 (time) to simpler numbers. In this case, designate 1993 as year 1, 1994 as year 2, and so on.

Year

2.75

Time Period

Sales (Units)

1 2 3 4 5 6 17 兺x  28

100 110 122 130 139 152 164 兺y  917

x2 1 4 9 16 25 36 149 兺x 2  140

xy 100 220 366 520 695 912 1,148 兺 xy  3,961

∑ y 917 ∑ x 28 y= = = 131 = =4 n 7 n 7 ∑ xy − nxy 3, 961 − (7)( 4 )(131) 293 = = 10.464 b= = 28 140 − (7)( 4 2 ) ∑ x 2 − nx 2

x=

a = y − bx = 131 − 10.46( 4 ) = 89.14 Therefore, the least squares trend equation is, yˆ = a + bx = 89.14 + 10.464 x To project demand in 2000, we denote the year 2000 as x  8, Sales in 2000  89.14  10.464(8)  172.85

Year 1 2 3 4 5 6

b=

a=

Renumbered Year (x)

Capacity (y)

x2

xy

2.5 1.5 .5 .5 1.5 2.5 兺X  0

115 120 118 124 123 130 兺Y  730

6.25 2.25 0.25 0.25 2.25 6.25 兺X2  17.5

287.5 180 59 62 184.5 325 兺XY  45

∑ XY ∑X2

=

45 = 2.57 17.5

∑ Y 730 = = 121.67 n 6 y ⫽ 121.67 ⫹ 2.57X

Year 7 ⫽ 121.67 ⫹ (2.57)(3.5) ⫽131 Alternative Example 5.7: 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 = = = 11.7 n 6 RSFE −24 Tracking Signal = = = −2.1 MADs MAD 11.7

REVISED M05_REND6289_10_IM_C05.QXD

54

5/7/08

CHAPTER 5

4:42 PM

Page 54

FORECASTING MODELS

Table for Alternate Example 5.7 Year 1 2 3 4 5 6

Forecast Demand

Actual Demand

Error

RSFE

Forecast Error

Cumulative Error

MAD

Tracking Signal

78 75 83 84 88 85

71 80 101 84 60 73

7 5 18 0 28 12

7 2 16 16 12 24

7 5 18 0 28 12

7 12 30 30 58 70

7.0 6.0 10.0 7.5 11.6 11.7

1.0 0.3 1.6 2.1 1.0 2.1

SOLUTIONS TO DISCUSSION QUESTIONS AND PROBLEMS

MAD is important because it can be used to help increase forecasting accuracy.

5-1. are:

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 1, that season tends to be higher than average.

The steps that are used to develop any forecasting system 1. Determine the use of the forecast. 2. Select the items or quantities that are to be forecasted.

5-10.

3. Determine the time horizon of the forecast. 4. Select the forecasting model. 5. Gather the necessary data.

Ft1  Ft  0(At  Ft)  Ft This means that the forecast never changes. If the smoothing constant equals 1, then Ft1  Ft  1(At  Ft)  At

6. Validate the forecasting model. 7. Make the forecast. 8. Implement the results. 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. 5-4. 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-5. 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-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. 5-8. MAD is a technique for determining the accuracy of a forecasting model by taking the average of the absolute deviations.

If the smoothing constant equals 0, then

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 than a CMA, variations due to trend will be interpreted as variations due to seasonal factors. Thus, the seasonal indices will not be accurate. 5-12. Month Jan. Feb. Mar. Apr. May June July Aug. Sept. Oct. Nov. Dec.

Actual Shed Sales 10 12 13 16 19 23 26 30 28 18 16 14

Four-Month Moving Average

(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 MODELS

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. Feb. Mar. Apr. May June July Aug. Sept. Oct. Nov. Dec.

Three-month MAD =

58.35 = 6.48 9

Four-month MAD =

62.25 = 7.78 8

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.

5-14.

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

 42⁄3 5  61⁄3  72⁄3  81⁄3 8  91⁄3  112⁄3

Weighted Three-Year Moving Averages

sum of the weights [(2  4)  6  4]/4  41⁄2 [(2  5)  4  6]/4  50 [(2  10)  5  4]/4  71⁄4 [(2  8)  10  5]/4  73⁄4 [(2  7)  8  10]/4  80 [(2  9)  7  8]/4  81⁄4 [(2  12)  9  7]/4  10 [(2  14)  12  9]/4  121⁄4 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.05X Where X  time period (1, 2, . . .) Y  demand

a

Year

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

Demand

3-Yr MA |deviation|

3-Yr Wt. MA |deviation|

Trend line |deviation|

— — — 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) ⫽ 4,700 The calculations are: 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) ⫽ 5,000 ⫺ 300

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

5-17.   0.3. New forecast for year 2 is last period’s forecast  (last period’s actual demand  last period’s forecast):

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 MODELS

5-20. Year 1 2 3 4 5 6

Actual Sales

␣  0.3 Forecast

Absolute Deviation

␣  0.6 Forecast

Absolute Deviation

␣  0.9 Forecast

Absolute Deviation

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

40.0 49.0 27.9 47.8 25.8 — 190.5

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

MAD0.3 ⫽ 372.8/5 ⫽ 74.56 MAD0.6 ⫽ 259/5 ⫽ 51.8 MAD0.9 ⫽ 190.5/5 ⫽ 38.1 Because it has the lowest MAD, the smoothing constant   0.9 gives the most accurate forecast. 5-21. Year 1 2 3 4 5 6

Sales

Three-Year Moving Average

450 495 518 563 584 ?

(450  495  518)/3  487.667 (495  518  563)/3  525.333 (518  563  584)/3  555

5-22.

Year

Time Period X

1 2 3 4 5

1 2 3 4 5

Sales Y

X2

XY

450 495 518 563 2,584 2,610

1 4 9 16 125 55

450 990 1554 2252 2920 8166

b ⫽ 33.6 a ⫽ 421.2 Y ⫽ 421.2 ⫹ 33.6X Projected sales in year 6, Y ⫽ 421.2 ⫹ (33.6)(6) ⫽ 622.8 5-23. Year 1 2 3 4 5 6

Actual Sales

Three-Year Moving Average Forecast

450 — 495 — 518 — 563 487.7 584 525.3 ? 555.0 Total absolute deviation

Absolute Deviation — — — 75.3 58.7 — 134.0

Time-Series Forecast 454.8 488.4 522.0 555.6 589.2 622.8

Absolute Deviation 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

CHAPTER 5

MAD0.3 ⫽ 74.56

Page 58

FORECASTING MODELS

(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. Period

Month

Demand

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

Average

Weighted Average

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 moving average is 2.2. MAD for weighted average 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 are many other factors to consider, including seasonality and any underlying causal variables such as advertising budget. 5-25.

a.

Week

Actual Miles

Forecast (Ft)

Error

RSFE

Sum of Absolute Forecast Errors

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

— 4.00 1.20 4.96 1.03 2.83 1.74 0.61 3.51 0.81 4.35 3.52

— 4.00 5.20 10.16 9.13 6.30 8.04 7.43 10.94 11.75 7.40 10.92

— 4.00 5.20 10.16 11.19 14.02 15.76 16.37 19.88 20.69 25.04 28.56

MAD

Track Signal

— 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 MODELS

b. The total MAD is 2.60. c. RSFE is consistently positive. Tracking signal exceeds 5 MADs at week 10. This could indicate a problem. 5-26. a, b. See the accompanying table for a comparison of the calculations for the exponentially smoothed forecasts using constants of 0.1 and 0.6. c. Students should note how stable the smoothed values 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, 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

Actual Value, At

Smoothed Value, Ft (␣  0.1)

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 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 — 15 23 6 0 10 24 12 6 20 23 4 19 3 13 18 16 0 5 20 33 5 5 20

Smoothed Value, Ft (␣  0.6) 50 41 31 37 42 38 27 29 32 25 19 32 46 39 31 45 51 44 39 51 66 56 46 58

Forecast Error — 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.

Week 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

5/7/08

4:42 PM

CHAPTER 5

Page 60

FORECASTING MODELS

Using data from Problem 5-26, with   0.9 Actual Value At

Smoothed Value Ft

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

Forecast Error — 15 11 14 6 9 16 8 6 14 6 24 17 18 12 29 3 15 6 24 17 23 12 24

MAD  14.48

Note that in this problem, the initial forecast (for the first period) was not used in computing the MAD. Either approach is considered valid. 5-28.

Exponential smoothing with   0.1

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. Quarter 1 index ⫽ 0.8825; Quarter 2 index ⫽ 0.9816; Quarter 3 index ⫽ 0.9712; Quarter 4 index ⫽ 1.1569 b. The trendline is Y ⫽ 237.7478 ⫹ 3.6658X c. Quarter 1: Y ⫽ 237.7478 ⫹ 3.6658(17) ⫽ 300.0662 Quarter 2: Y ⫽ 237.7478 ⫹ 3.6658(18) ⫽ 303.7320 Quarter 3: Y ⫽ 237.7478 ⫹ 3.6658(19) ⫽ 307.3978 Quarter 4: Y ⫽ 237.7478 ⫹ 3.6658(20) ⫽ 311.0636 d. Quarter 1: 300.0662(0.8825) ⫽ 264.7938 Quarter 2: 303.7320(0.9816) ⫽ 298.1579 Quarter 3: 307.3978(0.9712) ⫽ 298.5336 Quarter 4: 311.0636(1.1569) ⫽ 359.8719 5-31. Letting t ⫽ time period (1, 2, 3, . . . , 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 Y ⫽ 281.6 ⫹ 3.7t ⫺ 75.7Q1 ⫺ 48.9Q2 ⫺ 52.1Q3 The forecasts for the next 4 quarters are: Y ⫽ 281.6 ⫹ 3.7(17) ⫺ 75.7(1) ⫺ 48.9(0) ⫺ 52.1(0) ⫽ 268.7 Y ⫽ 281.6 ⫹ 3.7(18) ⫺ 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 5-32. For a smoothing constant of 0.2, the forecast for year 11 is 6.489.

Month

Income

Forecast

Error

Year

Rate

Feb. 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

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. 5-29.

Exponential smoothing with   0.3

Forecast

|Error|

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 = 0.722

For a smoothing constant of 0.4, the forecast for year 11 is 6.458. Month

Income

Forecast

Error

Feb. 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,   0.3 produces a better forecast than   0.1 (of Problem 5-28). 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

CHAPTER 5

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 MODELS

5-33. To compute a seasonalized or adjusted sales forecast, we just multiply each seasonal index by the appropriate trend forecast. Yˆ  seasonal index  Yˆ trend forecast

Hence for: Quarter I: YˆI  (1.30)($100,000)  $130,000 Quarter II: Yˆ  (0.90)($120,000)  $108,000 II

Quarter III: YˆIII  (0.70)($140,000)  $98,000 Quarter IV: Yˆ  (1.10)($160,000)  $176,000 IV

5-34. (Average demand (year 1 demand) + (year 2 demand) ⫽ for season) 2

MAD = 0.604

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 =

(average for season) overall average demand

new annual demand 4 1, 200 = × season index 4

Year 3 demand =

MAD = 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 constant of 0.3, the forecast 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 ⫹ 0.0095X.

For January of 2007, X ⫽ 13; Y ⫽ 1.1940 ⫹ 0.0095(13) ⫽ 1.318. For February of 2007, X ⫽ 14; Y ⫽ 1.1940 ⫹ 0.0095(14) ⫽ 1.327. 5-38.

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 (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

CHAPTER 5

4:42 PM

Page 62

FORECASTING MODELS

5-42. The trend line found using Excel is: Crime Rate  51.98 ⫹ 6.09(time). Note these coefficients are rounded. For the next 3 years (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-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