# 数据、模型与决策(运筹学)课后习题和案例答案013

CHAPTER 13 FORECASTING
Review Questions
13.1-1 Substantially underestimating demand is likely to lead to many lost sales, unhappy customers, and perhaps allowing the competition to gain the upper hand in the marketplace. Significantly overestimating the demand is very costly due to excessive inventory costs, forced price reductions, unneeded production or storage capacity, and lost opportunity to market more profitable goods. 13.1-2 A forecast of the demand for spare parts is needed to provide good maintenance service. 13.1-3 In cases where the yield of a production process is less than 100%, it is useful to forecast the production yield in order to determine an appropriate value of reject allowance and, consequently, the appropriate size of the production run. 13.1-4 Statistical models to forecast economic trends are commonly called econometric models. 13.1-5 Providing too few agents leads to unhappy customers, lost calls, and perhaps lost business. Too many agents cause excessive personnel costs. 13.2-1 The company mails catalogs to its customers and prospective customers several times per year, as well as publishing mini-catalogs in computer magazines. They then take orders for products over the phone at the company’s call center. 13.2-2 Customers who receive a busy signal or are on hold too long may not call back and business may be lost. If too many agents are on duty there may be idle time, which wastes money because of labor costs. 13.2-3 The manager of the call center is Lydia Weigelt. Her current major frustration is that each time she has used her procedure for setting staffing levels for the upcoming quarter, based on her forecast of the call volume, the forecast usually has turned out to be considerably off. 13.2-4 Assume that each quarter’s call volume will be the same as for the preceding quarter, except for adding 25% for quarter 4. 13.2-5 The average forecasting error is commonly called MAD, which stands for Mean Absolute Deviation. Its formula is MAD = (Sum of forecasting errors) / (Number of forecasts) 13.2-6 MSE is the mean square error. Its formula is (Sum of square of forecasting errors) / (Number of forecasts). 13.2-7 A time series is a series of observations over time of some quantity of interest. 13.3-1 In general, the seasonal factor for any period of a year measures how that period compares to the overall average for an entire year. 13.3-2 Seasonally adjusted call volume = (Actual call volume) / (Seasonal factor).
13-1

13.3-3 Actual forecast = (Seasonal factor)(Seasonally adjusted forecast) 13.3-4 The last-value forecasting method sometimes is called the naive method because statisticians consider it naive to use just a sample size of one when additional relevant data are available. 13.3-5 Conditions affecting the CCW call volume were changing significantly over the past three years. 13.3-6 Rather than using old data that may no longer be relevant, this method averages the data for only the most recent periods. 13.3-7 This method modifies the moving-average method by placing the greatest weight on the last value in the time series and then progressively smaller weights on the older values. 13.3-8 A small value is appropriate if conditions are remaining relatively stable. A larger value is needed if significant changes in the conditions are occurring relatively frequently. 13.3-9 Forecast = α(Last Value) + (1 – α)(Last forecast). Estimated trend is added to this formula when using exponential smoothing with trend. 13.3-10 The one big factor that drives total sales up or down is whether there are any hot new products being offered. 13.4-1 CB Predictor uses the raw data to provide the best fit for all these inputs as well as the forecasts. 13.4-2 Each piece of data should have only a 5% chance of falling below the lower line and a 5% chance of rising above the upper line. 13.5-1 The next value that will occur in a time series is a random variable. 13.5-2 The goal of time series forecasting methods is to estimate the mean of the underlying probability distribution of the next value of the time series as closely as possible. 13.5-3 No, the probability distribution is not the same for every quarter. 13.5-4 Each of the forecasting methods, except for the last-value method, placed at least some weight on the observations from Year 1 to estimate the mean for each quarter in Year 2. These observations, however, provide a poor basis for estimating the mean of the Year 2 distribution. 13.5-5 A time series is said to be stable if its underlying probability distribution usually remains the same from one time period to the next. A time series is unstable if both frequent and sizable shifts in the distribution tend to occur. 13.5-6 Since sales drive call volume, the forecasting process should begin by forecasting sales. 13.5-7 The major components are the relatively stable market base of numerous small-niche products and each of a few major new products. 13.6-1 Causal forecasting obtains a forecast of the quantity of interest by relating it directly to one or more other quantities that drive the quantity of interest.

13-2

13.6-2 The dependent variable is call volume and the independent variable is sales. 13.6-3 When doing causal forecasting with a single independent variable, linear regression involves approximating the relationship between the dependent variable and the independent variable by a straight line. 13.6-4 In general, the equation for the linear regression line has the form y = a + bx. If there is more than one independent variable, then this regression equation has a term, a constant times the variable, added on the right-hand side for each of these variables. 13.6-5 The procedure used to obtain a and b is called the method of least squares. 13.6-6 The new procedure gives a MAD value of only 120 compared with the old MAD value of 400 with the 25% rule. 13.7-1 Statistical forecasting methods cannot be used if no data are available, or if the data are not representative of current conditions. 13.7-2 Even when good data are available, some managers prefer a judgmental method instead of a formal statistical method. In many other cases, a combination of the two may be used. 13.7-3 The jury of executive opinion method involves a small group of high-level managers who pool their best judgment to collectively make a forecast rather than just the opinion of a single manager. 13.7-4 The sales force composite method begins with each salesperson providing an estimate of what sales will be in his or her region. 13.7-5 A consumer market survey is helpful for designing new products and then in developing the initial forecasts of their sales. It is also helpful for planning a marketing campaign. 13.7-6 The Delphi method normally is used only at the highest levels of a corporation or government to develop long-range forecasts of broad trends. 13.8-1 Generally speaking, judgmental forecasting methods are somewhat more widely used than statistical methods. 13.8-2 Among the judgmental methods, the most popular is a jury of executive opinion. Manager’s opinion is a close second. 13.8-3 The survey indicates that the moving-average method and linear regression are the most widely used statistical forecasting methods.

Problems
13.1 a) Forecast = last value = 39 b) Forecast = average of all data to date = (5 + 17 + 29 + 41 + 39) / 5 = 131 / 5 = 26 c) Forecast = average of last 3 values = (29 + 41 + 39) / 3 = 109 / 3 = 36 d) It appears as if demand is rising so the average forecasting method seems inappropriate because it uses older, out-of-date data.

13-3

13.2

a) Forecast = last value = 13 b) Forecast = average of all data to date = (15 + 18 + 12 + 17 + 13) / 5 = 75 / 5 = 15 c) Forecast = average of last 3 values = (12 + 17 + 13) / 3 = 42 / 3 = 14 d) The averaging method seems best since all five months of data are relevant in determining the forecast of sales for next month and the data appears relatively stable.

13.3 Quarter 1 2 3 4 Forecast 327 332 328 330 True Value 345 317 336 311 Error 18 15 8 19

MAD = (Sum of forecasting errors) / (Number of forecasts) = (18 + 15 + 8 + 19) / 4 = 60 / 4 = 15 MSE = (Sum of squares of forecasting errors) / (Number of forecasts) = (182 + 152 + 82 + 192) / 4 = 974 / 4 = 243.5 13.4 a) Method 1 MAD = (258 + 499 + 560 + 809 + 609) / 5 = 2,735 / 5 = 547 Method 2 MAD = (374 + 471 + 293 + 906 + 396) / 5 = 2,440 / 5 = 488 Method 1 MSE = (2582 + 4992 + 5602 + 8092 + 6092) / 5 = 1,654,527 / 5 = 330,905 Method 2 MSE = (3742 + 4712 + 2932 + 9062 + 3962) / 5 = 1,425,218 / 5 = 285,044 Method 2 gives a lower MAD and MSE. b) She can use the older data to calculate more forecasting errors and compare MAD for a longer time span. She can also use the older data to forecast the previous five months to see how the methods compare. This may make her feel more comfortable with her decision. 13.5 a) Quarter 1 2 3 4 Call Volume 6809 6465 6569 8266 Seasonal Factor 6,089 / 7,027 = 0.97 6,465 / 7,027 = 0.92 6,569 / 7,027 = 0.93 8,266 / 7,027 = 1.18

13-4

b) Quarter Seasonal Factor 0.97 0.92 0.93 1.18 Actual Call Volume 7,257 7,064 7,784 8,724 Seasonally Adjusted Call Volume 7,257 / 0.97 = 7,481 7,064 / 0.92 = 7,678 7,784 / 0.93 = 8,370 8,724 / 1.18 = 7,393

1 2 3 4 c) Quarter 1 2 3 4 d) Quarter

Two-Year Average 7,033 6,765 7,177 8,495

Seasonal Factor 7,033 / 7,367 = 0.95 6,765 / 7,367 = 0.92 7,177 / 7,367 = 0.97 8,495 / 7,367 = 1.15

Seasonal Factor 0.95 0.92 0.97 1.15

1 2 3 4 13.6 a) Quarter 1 2 3 4

Actual Call Volume 6992 6822 7949 9650

Seasonally Adjusted Call Volume 6,992 / 0.95 = 7,360 6,822 / 0.92 = 7,415 7,949 / 0.97 = 8,195 9,650 / 1.15 = 8,391

Unemployment Rate 6.2% 6.0% 7.5% 5.5%

Seasonal Factor 6.2% / 6.3% = 0.98 6.0% / 6.3% = 0.95 7.5% / 6.3% = 1.19 5.5% / 6.3% = 0.87

13-5

b) Quarter 1 2 3 4 Seasonal Factor 0.98 0.95 1.19 0.87 Actual Unemployment Rate 7.8% 7.4% 8.7% 6.1% Seasonally Adjusted Call Volume 7.8% / 0.98 = 8.0% 7.4% / 0.95 = 7.8% 8.7% / 1.19 = 7.3% 6.1% / 0.87 = 7.0%

This progression indicates that the state’s economy is improving with the unemployment rate decreasing from 8% to 7% (seasonally adjusted) over the four quarters. 13.7 a) Quarter 1 2 3 4 Three-Year Average 21 23 30 26 Seasonal Factor 21 /25 = 0.84 23 /25 = 0.92 30 / 25 = 1.20 26 / 25 = 1.04

b) Seasonally adjusted value for Y3(Q4) = 28 / 1.04 = 27, Actual forecast for Y4(Q1) = (27)(0.84) = 23. c) Y4(Q1) = 23 as shown in part b Seasonally adjusted value for Y4(Q1) = 23 / 0.84 = 27 Actual forecast for Y4(Q2) = (27)(0.92) = 25 Seasonally adjusted value for Y4(Q2) = 25 / 0.92 = 27 Actual forecast for Y4(Q3) = (27)(1.20) = 33 Seasonally adjusted value for Y4(Q3) = 33/1.20 = 27 Actual forecast for Y4(Q4) = (27)(1.04) = 28 d) Quarter 1 2 3 4 13.8 13.9 13.10 Seasonal Factor 0.84 0.92 1.20 1.04 Average House Sales 25 25 25 25 Seasonally Adjusted Forecast (25)(0.84) = 21 (25)(0.92) = 23 (25)(1.20) = 30 (25)(1.04) = 26

Forecast = 2,083 – (1,945 / 4) + (1,977 / 4) = 2,091 Forecast = 782 – (805 / 3) + (793 / 3) = 778 Forecast = 1,551 – (1,632 / 10) + (1,532 / 10) = 1,541
13-6

13.11

Forecast(α) = α(last value) + (1 – α)(last forecast) Forecast(0.1) = (0.1)(792) + (1 – 0.1)(782) = 783 Forecast(0.3) = (0.3)(792) + (1 – 0.3)(782) = 785 Forecast(0.5) = (0.5)(792) + (1 – 0.5)(782) = 787 Forecast(α) = α(last value) + (1 – α)(last forecast) Forecast(0.1) = (0.1)(1,973) + (1 – 0.1)(2,083) = 2,072 Forecast(0.3) = (0.3)(1,973) + (1 – 0.3)(2,083) = 2,050 Forecast(0.5) = (0.5)(1,973) + (1 – 0.5)(2,083) = 2,028 = initial estimate = 5000 = α(last value) + (1 – α)(last forecast) = (0.25)(4,600) + (1 – 0.25)(5,000) = 4,900 = (0.25)(5,300) + (1 – 0.25)(4,900) = 5,000

13.12

13.13 a) Forecast(year 1) Forecast(year 2) Forecast(year 3)

b) MAD = (400 + 400 + 1,000) / 3 = 600 MSE = (4002 + 4002 + 1,0002) / 3 = 440,000 c) Forecast(next year) = (0.25)(6,000) + (1 – 0.25)(5,000) = 5,250 13.14 Forecast = α(last value) + (1 – α)(last forecast) + Estimated trend Estimated trend = β (Latest trend) + (1 – β)(Latest estimate of trend) Latest trend = α(Last value – Next-to-last value) + (1 – α)(Last forecast – Next-to-last forecast) Forecast(year 1) = Initial average + Initial trend = 3,900 + 700 = 4,600 Forecast (year 2) = (0.25)(4,600) + (1 – 0.25)(4,600)+(0.25)[(0.25)(4,600 – 3900) + (1 – 0.25)(4,600 – 3,900)] + (1 – 0.25)(700) = 5,300 Forecast (year 3) = (0.25)(5,300) + (1 – 0.25)(5,300) + (0.25)[(0.25)(5,300 – 4,600) + (1 – 0.25)(5,300 – 4,600)]+(1 – 0.25)(700) = 6,000 13.15 Forecast = α(last value) + (1 – α)(last forecast) + Estimated trend Estimated trend = β (Latest trend) + (1 – β)(Latest estimate of trend) Latest trend = α(Last value – Next-to-last value) + (1 – α)(Last forecast – Next-to-last forecast) Forecast = (0.2)(550) + (1 – 0.2)(540) + (0.3)[(0.2)(550 – 535) + (1 – 0.2)(540 – 530)] + (1 – 0.3)(10) = 552

13-7

13.16

Forecast = α(last value) + (1 – α)(last forecast) + Estimated trend Estimated trend = β (Latest trend) + (1 – β)(Latest estimate of trend) Latest trend = α(Last value – Next-to-last value) + (1 – α)(Last forecast – Next-to-last forecast) Forecast = (0.1)(4,935) + (1 – 0.1)(4,975) + (0.2)[(0.1)(4,935 – 4,655) + (1 – 0.1) (4,975 – 4720)] + (1 – 0.2)(240) = 5,215

13.17 a) Since sales are relatively stable, the averaging method would be appropriate for forecasting future sales. This method uses a larger sample size than the last-value method, which should make it more accurate and since the older data is still relevant, it should not be excluded, as would be the case in the moving-average method. b) Last-Value Method:
3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 B Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 C True Value 23 24 22 28 22 27 20 26 21 29 23 28 D Last-Value Forecast 23 24 22 28 22 27 20 26 21 29 23 28 E Forecasting Error 1 2 6 6 5 7 6 5 8 6 5 F G H Mean Absolute Deviation MAD = 5.2 Mean Square Error MSE = 30.6

c) Averaging Method:
3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 B Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 C True Value 23 24 22 28 22 27 20 26 21 29 23 28 D Averaging Forecast 23 24 23 24 24 24 24 24 24 24 24 24 E Forecasting Error 1 2 5 2 3 4 2 3 5 1 4 F G H Mean Absolute Deviation MAD = 3.0 Mean Square Error MSE = 11.1

13-8

d) Moving Average Method (n = 3):
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 C True Value 23 24 22 28 22 27 20 26 21 29 23 28 D Moving Average Forecast #N/A #N/A 23 25 24 26 23 24 22 25 24 27 E Forecasting Error F G H Number of previous periods to consider n= 3 Mean Absolute Deviation MAD = 3.9 Mean Square Error MSE = 17.4

5 3 3 6 3 3 7 2 4

e) Considering the MAD values (5.2, 3.0, and 3.9, respectively), the averaging method is the best one to use. f) Considering the MSE values (30.6, 11.1, and 17.4, respectively), the averaging method is the best one to use. g) Unless there is reason to believe that sales will not continue to be relatively stable, the averaging method should be the most accurate in the future as well. 13.18 Using the template for exponential smoothing, with an initial estimate of 24, the following forecast errors were obtained for various values of the smoothing constant α: Smoothing Constant MAD MSE 0.1 2.7 9.4 0.2 2.8 10.2 0.3 3.0 11.2 0.4 3.1 12.4 0.5 3.3 13.8 Considering both MAD and MSE values, α = 0.1 is the best smoothing constant to use. 13.19 a) Answers will vary. Averaging or Moving Average appear to do a better job than Last Value. b) For Last Value, a change in April will only affect the May forecast. For Averaging, a change in April will affect all forecasts after April. For Moving Average, a change in April will affect the May, June, and July forecast. c) Answers will vary. Averaging or Moving Average appear to do a slightly better job than Last Value. d) Answers will vary. Averaging or Moving Average appear to do a slightly better job than Last Value.

13-9

13.20 a) Since the sales level is shifting significantly from month to month, and there is no consistent trend, the last-value method seems like it will perform well. The averaging method will not do as well because it places too much weight on old data. The movingaverage method will be better than the averaging method but will lag any short-term trends. The exponential smoothing method will also lag trends by placing too much weight on old data. Exponential smoothing with trend will likely not do well because the trend is not consistent. b) Last-value method:
3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 B Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 C True Value 126 137 142 150 153 154 148 145 147 151 159 166 D Last-Value Forecast 126 137 142 150 153 154 148 145 147 151 159 166 E Forecasting Error 11 5 8 3 1 6 3 2 4 8 7 F G H Mean Absolute Deviation MAD = 5.3 Mean Square Error MSE = 36.2

13-10

Averaging method:
3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 B Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 C True Value 126 137 142 150 153 154 148 145 147 151 159 166 D Averaging Forecast 126 132 135 139 142 144 144 144 145 145 147 148 E Forecasting Error 11 11 15 14 12 4 1 3 6 14 19 F G H Mean Absolute Deviation MAD = 10.0 Mean Square Error MSE = 131.4

Moving-average method:
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 C True Value 126 137 142 150 153 154 148 145 147 151 159 166 D Moving Average Forecast #N/A #N/A 135 143 148 152 152 149 147 148 152 159 E Forecasting Error F G H Number of previous periods to consider n= 3 Mean Absolute Deviation MAD = 8.1 Mean Square Error MSE = 84.3

15 10 6 4 7 2 4 11 14

Comparing MAD values (5.3, 10.0, and 8.1, respectively), the last-value method is the best to use of these three options. Comparing MSE values (36.2, 131.4, and 84.3, respectively), the last-value method is the best to use of these three options.

13-11

c) Using the template for exponential smoothing, with an initial estimate of 120, the following forecast errors were obtained for various values of the smoothing constant α: Smoothing Constant MAD MSE 0.1 18.5 382.7 0.2 13.0 210.2 0.3 10.1 139.7 0.4 8.7 104.2 0.5 8.0 82.9 Considering both MAD and MSE, it appears that a high value for the smoothing constant is appropriate. d) Using the template for exponential smoothing with trend, using initial estimates of 120 for the average value and 10 for the trend, the following forecast errors were obtained for various values of the smoothing constants α and β : MAD MSE 0.1 0.1 25.4 919.6 0.1 0.3 21.2 634.1 0.1 0.5 17.7 450.6 0.3 0.1 13.5 261.9 0.3 0.3 9.8 144.1 0.3 0.5 8.8 111.5 0.5 0.1 8.4 116.1 0.5 0.3 7.0 72.2 0.5 0.5 6.5 61.1 Considering both MAD and MSE, it appears that a high value for both smoothing constants is appropriate. e) Management should use the last-value method to forecast sales. Using this method the forecast for January of the new year will be 166. Exponential smoothing with trend with high smoothing constants (e.g., α = 0.5 and β = 0.5) also works well. With this method, the forecast for January of the new year will be 165. 13.21 a) Shift in total sales may be due to the release of new products on top of a stable product base, as was seen in the CCW case study. b) Forecasting might be improved by breaking down total sales into stable and new products. Exponential smoothing with a relatively small smoothing constant can be used for the stable product base. Exponential smoothing with trend, with a relatively large smoothing constant, can be used for forecasting sales of each new product. c) Managerial judgment is needed to provide the initial estimate of anticipated sales in the first month for new products. In addition, a manger should check the exponential smoothing forecasts and make any adjustments that may be necessary based on knowledge of the marketplace.

α

β

13-12

13.22 a) Answers will vary. Last value seems to do the best, with exponential smoothing with trend a close second. b) For last value, a change in April will only affect the May forecast. For averaging, a change in April will affect all forecasts after April. For moving average, a change in April will affect the May, June, and July forecast. For exponential smoothing, a change in April will affect all forecasts after April. For exponential smoothing with trend, a change in April will affect all forecasts after April. c) Answers will vary. last value or exponential smoothing seem to do better than the averaging or moving average. d) Answers will vary. last value or exponential smoothing seem to do better than the averaging or moving average. 13.23 a) Using the template for exponential smoothing, with an initial estimate of 50, the following MAD values were obtained for various values of the smoothing constant α: Smoothing Constant 0.1 0.2 0.3 0.4 0.5 Choose α = 0.1 b) Using the template for exponential smoothing, with an initial estimate of 50, the following MAD values were obtained for various values of the smoothing constant α: Smoothing Constant 0.1 0.2 0.3 0.4 0.5 Choose α = 0.2 MAD 1.69 1.66 1.71 1.82 1.93 MAD 1.49 1.58 1.67 1.76 1.86

13-13

c) Using the template for exponential smoothing, with an initial estimate of 50, the following MAD values were obtained for various values of the smoothing constant α: Smoothing Constant 0.1 0.2 0.3 0.4 0.5 Choose α = 0.5 13.24 a) Best α = 0.25. Forecast = 50.
1 2 3 4 5 6 7 8 9 10 11 12 13 A Time Period 1 2 3 4 5 6 7 8 9 10 11
A 1 2 3 4 5 6 7 8 9 10 11 12 13 Time Period 1 2 3 4 5 6 7 8 9 10 11

MAD 2.18 1.73 1.59 1.49 1.44

B True Value 51 48 52 49 53 49 48 51 50 49 50
B True Value 52 50 53 51 52 48 52 53 49 52 51

C

D

Best α 0.25

b) Best α = 0.114. Forecast = 51.
C D

Best α 0.114

13-14

c) Best α = 0.268. Forecast = 54.
A 1 2 3 4 5 6 7 8 9 10 11 12 13 Time Period 1 2 3 4 5 6 7 8 9 10 11 B True Value 50 52 51 55 53 56 52 55 54 53 54 C D

Best α 0.268

13.25 a) Using the template for exponential smoothing with trend, with an initial estimates of 50 for the average and 2 for the trend and α = 0.2, the following MAD values were obtained for various values of the smoothing constant β : Smoothing Constant 0.1 0.2 0.3 0.4 0.5 Choose β = 0.1 MAD 0.74 0.75 0.76 0.77 0.78

b) Using the template for exponential smoothing with trend, with an initial estimates of 50 for the average and 2 for the trend and α = 0.2, the following MAD values were obtained for various values of the smoothing constant β : Smoothing Constant 0.1 0.2 0.3 0.4 0.5 Choose β = 0.1 MAD 2.61 2.76 2.87 2.99 3.05

13-15

c) Using the template for exponential smoothing with trend, with an initial estimates of 50 for the average and 2 for the trend and α = 0.2, the following MAD values were obtained for various values of the smoothing constant β : Smoothing Constant 0.1 0.2 0.3 0.4 0.5 Choose β = 0.1
A Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 A Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 B True Value 52 55 55 58 59 63 64 66 67 72 73 74 77 B True Value 52 55 59 61 66 69 71 72 73 74 73 74 74

MAD 5.66 6.02 6.23 6.36 6.54

13.26 a) Best α = 0.637. Best β = 0.488. Forecast = 77.
C D 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Best α 0.637 Best β 0.488

b) Best α = 0.84. Best β = 0.582. Forecast = 74.
C D 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Best α 0.84 Best β 0.582

13-16

c) Best α = 0.904. Best β = 0.999. Forecast = 79.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 A Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 B True Value 52 53 51 50 48 47 49 52 57 62 69 74 79 C D

Best α 0.904 Best β 0.999

13.27 a) The time series is not stable enough for the moving-average method. There appears to be an upward trend. b) Moving Average (n = 3):
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Time Period 1 2 3 4 5 6 7 8 9 10 11 C True Value 382 405 398 421 426 415 443 451 446 464 D Moving Average Forecast #N/A #N/A 395 408 415 421 428 436 447 454 E Forecasting Error F G H Number of previous periods to consider n= 3 Mean Absolute Deviation MAD = 16.6 Mean Square Error MSE = 346.0

26 18 0 22 23 10 17

c) Exponential smoothing:
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Time Period 1 2 3 4 5 6 7 8 9 10 11 C True Value 382 405 398 421 426 415 443 451 446 464 D Exponential Smoothing Forecast 380 381 393 396 408 417 416 430 440 443 454 E Forecasting Error 2 24 5 26 18 2 27 21 6 21 F G H

Smoothing Constant α= Initial Estimate Average =

0.5

380

Mean Absolute Deviation MAD = 15.14 Mean Square Error MSE =

322.97

13-17

d) Exponential smoothing with trend:
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Time Period 1 2 3 4 5 6 7 8 9 10 11 12 C True Value 382 405 398 421 426 415 443 451 446 464 D Latest Trend 10.50 13.72 9.21 12.32 10.82 5.33 9.94 9.53 5.87 8.20 E Estimated Trend 10.00 10.13 11.02 10.57 11.01 10.96 9.55 9.65 9.62 8.68 8.56 F Exponential Smoothing Forecast 380 391 405 414 427 438 441 451 461 466 474 #N/A G Forecasting Error 2 14 7 7 1 23 2 0 15 2 H I J Smoothing Constants α= 0.25 β= 0.25 Initial Estimates Average = Trend = 370 10

Mean Absolute Deviation MAD = 7.28 Mean Square Error MSE = 105.11

e) Based on the MAD and MSE values, exponential smoothing with trend should be used in the future. f) Best method is exponential smoothing with trend, using α = 0.317 and β = 0.999.
1 2 3 4 5 6 7 8 9 10 11 12 13 A Time Period 1 2 3 4 5 6 7 8 9 10 11 B True Value 382 405 398 421 426 415 443 451 446 464 473 C D

Best Method Exp. Smoothing with Trend Best α 0.317 Best β 0.999

13.28

For moving average, the forecast typically lie below the demands. For exponential smoothing, the forecasts typically lie below the demands. For exponential smoothing with trend, the forecasts are at about the same level as demand (perhaps slightly above). This would indicate that exponential smoothing with trend is the best method to use hereafter.

13-18

13.29

Forecast for next production yield = 62%.
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Time Period 1 2 3 4 5 6 7 8 9 10 11 12 C True Value 15 21 24 32 37 41 40 47 51 53 D Latest Trend 5.00 5.20 4.79 5.39 5.38 5.15 3.93 4.35 4.19 3.66 E Estimated Trend 5.00 5.00 5.04 4.99 5.07 5.13 5.14 4.89 4.79 4.67 4.46 F Exponential Smoothing Forecast 15 20 25 30 35 41 46 50 54 58 62 #N/A G Forecasting Error 0 1 1 2 2 0 6 3 3 5 H I J

Smoothing Constants α= 0.2 β= 0.2 Initial Estimates Average = Trend =

10 5

Mean Absolute Deviation MAD = 2.27 Mean Square Error MSE = 8.75

13.30 a) Seasonal factors:
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

C Quarter 1 2 3 4 1 2 3 4 1 2 3 4
D True Value 25 47 68 42 27 46 72 39 24 49 70 44

Year 1 1 1 1 2 2 2 2 3 3 3 3
C

D True Value 25 47 68 42 27 46 72 39 24 49 70 44
E Seasonally Adjusted Value 45 46 45 46 49 45 47 43 44 48 46 49 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A

E

F Type of Seasonality Quarterly

G

Quarter 1 2 3 4

Estimate for Seasonal Factor 0.550 1.027 1.519 0.904

b) Last-value method with seasonality forecast = 27 acre-feet.
Year 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5 6 Quarter 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 F Seasonally Adjusted Forecast 45 46 45 46 49 45 47 43 44 48 46 49 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A G Actual Forecast 47 70 40 26 50 68 43 24 45 72 42 27 H Forecasting Error 0 2 2 1 4 4 4 0 4 2 2 I J K Type of Seasonality Quarterly Quarter 1 2 3 4 Seasonal Factor 0.550 1.027 1.519 0.904 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000

Mean Absolute Deviation MAD = 2.39 Mean Square Error MSE =

7.82

13-19

c) Winter = (49)(0.550) = 27 Spring = (49)(1.027) = 50 Summer = (49)(1.519) = 74 Fall = (49)(0.904) = 44 d) Averaging method with seasonality forecast = 25 acre-feet.
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 C D True Value 25 47 68 42 27 46 72 39 24 49 70 44 Year 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5 6 Quarter 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 E Seasonally Adjusted Value 45 46 45 46 49 45 47 43 44 48 46 49 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A F Seasonally Adjusted Forecast 45 46 45 46 46 46 46 46 46 46 46 46 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A G Actual Forecast 47 69 41 25 48 70 42 25 47 70 41 25 H Forecasting Error 0 1 1 2 2 2 3 1 2 0 3 I J K Type of Seasonality Quarterly Quarter 1 2 3 4 Seasonal Factor 0.550 1.027 1.519 0.904 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000

Mean Absolute Deviation MAD = 1.57 Mean Square Error MSE =

3.07

e) Moving-average method with seasonality forecast = 26 acre-feet.
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 C D True Value 25 47 68 42 27 46 72 39 24 49 70 44 Year 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5 6 6 6 6 Quarter 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 E Seasonally Adjusted Value 45 46 45 46 49 45 47 43 44 48 46 49 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A F Seasonally Adjusted Forecast #N/A #N/A #N/A 46 47 46 47 46 45 45 45 47 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A G Actual Forecast H Forecasting Error I J Number of previous periods to consider n= K

4

Type of Seasonality Quarterly 25 48 70 42 25 46 69 41 26 2 2 2 3 1 3 1 3 Quarter 1 2 3 4 Seasonal Factor 0.550 1.027 1.519 0.904 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000

Mean Absolute Deviation MAD = 2.17 Mean Square Error MSE =

5.46

13-20

f) Exponential smoothing method with seasonality forecast = 25 acre-feet.
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 C D True Value 25 47 68 42 27 46 72 39 24 49 70 44 Year 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5 6 6 6 6 7 7 Quarter 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 E Seasonally Adjusted Value 45 46 45 46 49 45 47 43 44 48 46 49 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A F Seasonally Adjusted Forecast 46 46 46 46 46 46 46 46 46 46 46 46 46 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A G Actual Forecast 25 47 70 41 25 47 70 42 25 47 70 41 25 H Forecasting Error 0 0 2 1 2 1 2 3 1 2 0 3 I J Smoothing Constant α= Initial Estimate Average = K 0.1

46 Type of Seasonality Quarterly

Quarter 1 2 3 4

Seasonal Factor 0.550 1.027 1.519 0.904 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000

Mean Absolute Deviation MAD = 1.42 Mean Square Error MSE =

2.75

g) The exponential smoothing method results in the lowest MAD value (1.42) and the lowest MSE value (2.75). 13.31 a)
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Year 1 1 1 1 2 2 2 2 3 3 3 3 C Quarter 1 2 3 4 1 2 3 4 1 2 3 4 D True Value 23 22 31 26 19 21 27 24 21 26 32 28 E F Type of Seasonality Quarterly G

Quarter 1 2 3 4

Estimate for Seasonal Factor 0.84 0.92 1.20 1.04

13-21

b) Last-value method with seasonality forecast = 23.
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 C D True Value 23 22 31 26 19 21 27 24 21 26 32 28 Year 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5 6 Quarter 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 E Seasonally Adjusted Value 27 24 26 25 23 23 23 23 25 28 27 27 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A F Seasonally Adjusted Forecast 27 24 26 25 23 23 23 23 25 28 27 27 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A G Actual Forecast 25 29 27 21 21 27 23 19 23 34 28 23 H Forecasting Error 3 2 1 2 0 0 1 2 3 2 0 I J K Type of Seasonality Quarterly Quarter 1 2 3 4 Seasonal Factor 0.84 0.92 1.20 1.04 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000

Mean Absolute Deviation MAD = 1.49 Mean Square Error MSE =

3.28

c) Averaging method with seasonality forecast = 21.
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 C D True Value 23 22 31 26 19 21 27 24 21 26 32 28 Year 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5 6 Quarter 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 E Seasonally Adjusted Value 27 24 26 25 23 23 23 23 25 28 27 27 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A F Seasonally Adjusted Forecast 27 26 26 26 25 25 24 24 24 25 25 25 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A G Actual Forecast 25 31 27 21 23 30 25 20 22 30 26 21 H Forecasting Error 3 0 1 2 2 3 1 1 4 2 2 I J K Type of Seasonality Quarterly Quarter 1 2 3 4 Seasonal Factor 0.84 0.92 1.20 1.04 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000

Mean Absolute Deviation MAD = 1.94 Mean Square Error MSE =

4.85

13-22

d) Moving-average method with seasonality forecast = 22.
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 C D True Value 23 22 31 26 19 21 27 24 21 26 32 28 Year 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5 6 6 6 6 Quarter 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 E Seasonally Adjusted Value 27 24 26 25 23 23 23 23 25 28 27 27 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A F Seasonally Adjusted Forecast #N/A #N/A #N/A 26 24 24 23 23 23 25 26 27 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A G Actual Forecast H Forecasting Error I J Number of previous periods to consider n= K

4

Type of Seasonality Quarterly 21 22 29 24 19 21 30 27 22 2 1 2 0 2 5 2 1 Quarter 1 2 3 4 Seasonal Factor 0.84 0.92 1.20 1.04 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000

Mean Absolute Deviation MAD = 1.98 Mean Square Error MSE =

5.31

e) Exponential-smoothing method with seasonality forecast = 22.
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 C D True Value 23 22 31 26 19 21 27 24 21 26 32 28 Year 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5 6 6 6 6 7 7 Quarter 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 E Seasonally Adjusted Value 27 24 26 25 23 23 23 23 25 28 27 27 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A F Seasonally Adjusted Forecast 25 26 25 25 25 25 24 24 24 24 25 25 26 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A G Actual Forecast 21 24 30 26 21 23 29 25 20 22 30 26 22 H Forecasting Error 2 2 1 0 2 2 2 1 1 4 2 2 I J Smoothing Constant α= Initial Estimate Average = K 0.25

25 Type of Seasonality Quarterly

Quarter 1 2 3 4

Seasonal Factor 0.84 0.92 1.20 1.04 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000

Mean Absolute Deviation MAD = 1.66 Mean Square Error MSE =

3.56

13-23

f) Exponential-smoothing method with trend and seasonality forecast = 22.
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 C D True Value 23 22 31 26 19 21 27 24 21 26 32 28 Year 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5 6 6 6 6 7 7 7 7 Quarter 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 E Seasonally Adjusted Value 27 24 26 25 23 23 23 23 25 28 27 27 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A F Latest Trend 1 0 0 0 -1 -1 -1 0 0 1 1 1 G Estimated Trend 0 0 0 0 0 0 0 0 0 0 0 0 0 H Seasonally Adjusted Forecast 25 26 25 26 25 25 24 23 23 23 25 25 26 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A I Actual Forecast 21 24 30 27 21 23 29 24 19 21 29 26 22 J Forecasting Error 2 2 1 1 2 2 2 0 2 5 3 2 K L Smoothing Constant α= β= Initial Estimate Average = Trend = M 0.25 0.25

25 0 Type of Seasonality Quarterly

Quarter 1 2 3 4

Seasonal Factor 0.84 0.92 1.20 1.04 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00

Mean Absolute Deviation MAD = 1.78 Mean Square Error MSE =

4.42

g) The last-value method with seasonality has the lowest MAD and MSE value. Using this method, the forecast for Q1 is 23 houses. h) Forecast(Q2) = (27)(0.92) = 25 Forecast(Q3) = (27)(1.2) = 32 Forecast(Q4) = (27)(1.04) = 28 13.32 a) Method Last-value Averaging Moving-average Exponential smoothing MAD 3.07 3.12 2.18 2.34 MSE 12.89 13.07 5.79 9.31

13-24

b) The moving-average method with seasonality has the lowest MAD value. Using this method, the forecast for January is 73 passengers.
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 C D True Value 68 71 66 72 77 85 94 96 80 73 84 89 Year 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec E Seasonally Adjusted Value 76 81 73 77 80 78 80 83 82 80 80 82 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A F Seasonally Adjusted Forecast #N/A #N/A 76 77 77 79 80 81 82 82 81 81 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A G Actual Forecast H Forecasting Error I J Number of previous periods to consider n= K

3

71 74 84 92 91 78 75 86 87 73

1 3 1 2 5 2 2 2 2

Type of Seasonality Monthly Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Seasonal Factor 0.90 0.88 0.91 0.93 0.96 1.09 1.17 1.15 0.97 0.91 1.05 1.08

Mean Absolute Deviation MAD = 2.18 Mean Square Error MSE =

5.79

13.33 a) Method Last-value Averaging Moving-average Exponential smoothing MAD 2.46 7.06 2.79 4.28 MSE 8.34 74.73 9.68 25.87

b) Exponential smoothing with trend and seasonality forecast = 94.
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 C D True Value 75 76 81 84 85 99 107 108 94 90 106 110 Year 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr E Seasonally Adjusted Value 83 86 89 90 89 91 91 94 97 99 101 102 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A F Latest Trend 2 2 3 2 2 2 1 1 2 2 2 2 G Estimated Trend 2 2 2 2 2 2 2 2 2 2 2 2 2 H Seasonally Adjusted Forecast 82 84 87 90 92 93 95 96 97 99 101 103 104 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A I Actual Forecast 74 74 79 83 88 102 111 110 95 90 106 111 94 J Forecasting Error 1 2 2 1 3 3 4 2 1 0 0 1 K L Smoothing Constant α= β= Initial Estimate Average = Trend = M 0.2 0.2

80 2 Type of Seasonality Monthly

Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec

Seasonal Factor 0.90 0.88 0.91 0.93 0.96 1.09 1.17 1.15 0.97 0.91 1.05 1.08

Mean Absolute Deviation MAD = 1.66 Mean Square Error MSE =

4.21

MAD and MSE is lower here than all those found in part a.

13-25

c)
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 C D True Value 68 71 66 72 77 85 94 96 80 73 84 89 75 76 81 84 85 99 107 108 94 90 106 110 Year 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr E Seasonally Adjusted Value 76 81 73 77 80 78 80 83 82 80 80 82 83 86 89 90 89 91 91 94 97 99 101 102 #N/A #N/A #N/A #N/A F Latest Trend -1 0 -1 0 0 0 0 1 1 0 0 1 1 1 2 2 1 1 1 2 2 2 2 2 G Estimated Trend 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 2 2 2 H Seasonally Adjusted Forecast 80 79 79 77 77 77 77 78 79 80 80 80 81 82 83 85 87 89 90 92 93 96 98 100 102 #N/A #N/A #N/A I Actual Forecast 72 69 72 72 74 84 90 89 77 73 84 87 73 72 76 79 84 97 106 105 91 87 103 108 92 J Forecasting Error 4 2 6 0 3 1 4 7 3 0 0 2 2 4 5 5 1 2 1 3 3 3 3 2 K L Smoothing Constant α= β= Initial Estimate Average = Trend = M 0.2 0.2

80 0 Type of Seasonality Monthly

Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec

Seasonal Factor 0.90 0.88 0.91 0.93 0.96 1.09 1.17 1.15 0.97 0.91 1.05 1.08

Mean Absolute Deviation MAD = 2.74 Mean Square Error MSE =

10.44

d) Exponential smoothing with trend should be used.

13-26

e) The best values for the smoothing constants are α = 0.3, β = 0.3, and γ = 0.001. The forecasts for the coming year are shown in cells C28:C38 below.
A 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 26 27 28 29 30 31 32 33 34 35 36 37 38 Year 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 B Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec C True Value 68 71 66 72 77 85 94 96 80 73 84 89 75 76 81 84 85 99 107 108 94 90 106 110 97 98.4 98.5 105.3 110.3 125.7 138.1 141.0 120.7 113.4 132.6 139.7 D E

Best α 0.3 Best β 0.3 Best γ 0.001

13-27

13.34 a)
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Year 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 C Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec D True Value 352 329 365 358 412 446 420 471 355 312 567 533 317 331 344 386 423 472 415 492 340 301 629 505 338 346 383 404 431 459 433 518 309 335 594 527 E F Type of Seasonality Monthly G

Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec

Estimate for Seasonal Factor 0.81 0.81 0.88 0.92 1.02 1.11 1.02 1.19 0.81 0.76 1.44 1.26

13-28

b) Moving-average with seasonality:
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 C D True Value Year 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 3 Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan E Seasonally Adjusted Value #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 440 413 420 450 425 446 474 451 447 460 467 480 478 461 463 #N/A F Seasonally Adjusted Forecast #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 424 428 432 440 448 457 457 453 458 469 475 473 467 G Actual Forecast H Forecasting Error I J Number of previous periods to consider n= K

3

Type of Seasonality Monthly Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Seasonal Factor 0.81 0.81 0.88 0.92 1.02 1.11 1.02 1.19 0.81 0.76 1.44 1.26

335 594 527 364 343 391 437 458 494 468 555 387 364 662 581

343 345 378 406 456 505 465 538 369 357 683 594 378

21 2 13 31 2 11 3 17 18 7 21 13

Mean Absolute Deviation MAD = 13.30 Mean Square Error MSE = 249.09

c) Exponential smoothing with seasonality:
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 C D True Value 364 343 391 437 458 494 468 555 387 364 662 581 Year 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 3 3 Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb E Seasonally Adjusted Value 450 425 446 474 451 447 460 467 480 478 461 463 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A F Seasonally Adjusted Forecast 420 426 426 430 439 441 442 446 450 456 461 461 461 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A G Actual Forecast 339 344 373 396 446 488 450 530 363 347 662 579 373 H Forecasting Error 25 1 18 41 12 6 18 25 24 17 0 2 I J Smoothing Constant α= Initial Estimate Average = K 0.2

420 Type of Seasonality Monthly

Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec

Seasonal Factor 0.81 0.81 0.88 0.92 1.02 1.11 1.02 1.19 0.81 0.76 1.44 1.26

Mean Absolute Deviation MAD = 15.83 Mean Square Error MSE =

384.99

13-29

d) Exponential smoothing with trend and seasonality:
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 C D True Value 364 343 391 437 458 494 468 555 387 364 662 581 Year 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Apr E Seasonally Adjusted Value 450 425 446 474 451 447 460 467 480 478 461 463 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A F Latest Trend 6 1 5 10 5 3 5 6 7 6 2 1 G Estimated Trend 0 1 1 2 3 4 4 4 4 5 5 4 4 H Seasonally Adjusted Forecast 420 427 428 433 445 450 453 458 464 472 479 479 480 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A I Actual Forecast 339 345 375 399 452 497 461 545 374 359 688 602 388 J Forecasting Error 25 2 16 38 6 3 7 10 13 5 26 21 K L Smoothing Constant α= β= Initial Estimate Average = Trend = M 0.2 0.2

420 0 Type of Seasonality Monthly

Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec

Seasonal Factor 0.81 0.81 0.88 0.92 1.02 1.11 1.02 1.19 0.81 0.76 1.44 1.26

Mean Absolute Deviation MAD = 14.26 Mean Square Error MSE =

314.71

e) Moving average results in the best MAD value (13.30) and the best MSE value (249.09). f) Month January February March April May June July August September October November December MAD = 14.17 g) Moving average performed better than the average of all three so it should be used next year. Avg. Forecast
341 345 375 400 451 497 459 537 369 354 677 592

Forecasting Error 23 2 16 37 7 3 9 18 18 10 15 12

13-30

h) The best method is exponential smoothing with seasonality and trend, using α = 0.3, β = 0.3, and γ = 0.001.
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 49 50 51 52 53 54 55 56 57 58 59 60 61 62 Year 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 4 4 5 5 5 5 5 5 5 5 5 5 5 5 B Month Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec Jan Feb Mar Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec C True Value 352 329 365 358 412 446 420 471 355 312 567 533 317 331 344 662 581 389 394 430 453 502 548 507 594 405 383 726 637 D E

Best Method Exp. Smoothing With Trend & Seasonality Best α 0.3 Best β 0.3 Best γ 0.001

13-31

13.35 a)

600 500 400 Sales 300 200 100 0 0 1 2 3 4 5 6 Month
E Estimate 428 446 463 481 498 516 534 551 569 587

?

?

?

?

?

?

?

?

?

?

7

8

9

10

b) y = 410.33 + 17.63x
3 4 5 6 7 8 9 10 11 12 13 14 B Time Period 1 2 3 4 5 6 7 8 9 10 C Independent Variable 1 2 3 4 5 6 7 8 9 10 D Dependent Variable 430 446 464 480 498 514 532 548 570 591 F Estimation Error 2.04 0.41 0.78 0.85 0.48 2.12 1.75 3.38 0.99 4.36 G Square of Error 4 0 1 1 0 4 3 11 1 19 H I J Linear Regression Line y = a + bx a= 410.33 b= 17.63

Estimator If x = then y=

5,000 88,561.85

c)
600 500 400 Sales 300 200 100 0 0 1 2 3 4 5 6 Month 7 8 9 10 ? ? ? ? ? ? ? ? ? ?

d) y = 410.33 + (17.63)(11) = 604 e) y = 410.33 + (17.63)(20) = 763 f) The average growth in sales per month is 17.63.

13-32

13.36 a)

6000 5000 Applications 4000 3000 2000 1000 0 0
b)

? ? ?

1 Year

2

3

6000 5000 Applications 4000 3000 2000 1000 0 0
c) y = 3,900 + 700x
3 4 5 6 7 B Time Period 1 2 3 C Independent Variable 1 2 3 D Dependent Variable 4,600 5,300 6,000 E Estimate 4,600 5,300 6,000 F Estimation Error 0.00 0.00 0.00

? ? ?

1 Year

2

3

G Square of Error 0 0 0

H

I J Linear Regression Line y = a + bx a= 3,900.00 b= 700.00

d) y (year 4) = 3,900 + (700)(4) = 6,700 y (year 5) = 3,900 + (700)(5) = 7,400 y (year 6) = 3,900 + (700)(6) = 8,100 y (year 7) = 3,900 + (700)(7) = 8,800 y (year 8) = 3,900 + (700)(8) = 9,500 e) It does not make sense to use the forecast obtained earlier of 9,500. The relationship between the variable has changed and, thus, the linear regression that was used is no longer appropriate.

13-33

f)

7000 6000 Applications 5000 4000 3000 2000 1000 0 0 1 2 3 Year
y = 5,229 + 92.9x y = 5,229 + (92.9)(8) = 5,971
3 4 5 6 7 8 9 10 11 12 B Time Period 1 2 3 4 5 6 7 8 C Independent Variable 1 2 3 4 5 6 7 D Dependent Variable 4,600 5,300 6,000 6,300 6,200 5,600 5,200 E Estimate 5,321 5,414 5,507 5,600 5,693 5,786 5,879 F Estimation Error 721.43 114.29 492.86 700.00 507.14 185.71 678.57 G Square of Error 520,459 13,061 242,908 490,000 257,194 34,490 460,459 H I J Linear Regression Line y = a + bx a= 5,229 b= 92.9

?

?

?

?

?

?

?

4

5

6

7

Estimator If x = then y=

8 5,971

The linear regression line does not provide a close fit to the data. Consequently, the forecast that it provides for year 8 is not likely to be accurate. It does not make sense to continue to use a linear regression line when changing conditions cause a large shift in the underlying trend in the data. g)
B 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Time Period 1 2 3 4 5 6 7 8 9 10 11 12 C True Value 4,600 5,300 6,000 6,300 6,200 5,600 5,200 D Latest Trend 700.00 700.00 700.00 500.00 150.00 -337.50 -546.88 E Estimated Trend 700.00 700.00 700.00 700.00 600.00 375.00 18.75 -264.06 F Exponential Smoothing Forecast 4,600 5,300 6,000 6,700 7,100 7,025 6,331 5,502 #N/A #N/A #N/A #N/A G Forecasting Error 0 0 0 400 900 1,425 1,131 H I J Smoothing Constants α= 0.5 β= 0.5 Initial Estimates Average = Trend = 3,900 700

Mean Absolute Deviation MAD = 550.89 Mean Square Error MSE = 611,478.79

Causal forecasting takes all the data into account, even the data from before changing conditions cause a shift. Exponential smoothing with trend adjusts to shifts in the underlying trend by placing more emphasis on the recent data.

13-34

13.37 a)

500 450 400 350 300 250 200 150 100 50 0 0

Annual Demand

?

?

?

?

?

?

?

?

?

?

1

2

3

4

5 6 Year
E Estimate 388 397 405 413 421 429 437 445 454 462

7

8

9

10

b) y = 380 + 8.15x
3 4 5 6 7 8 9 10 11 12 13 14 B Time Period 1 2 3 4 5 6 7 8 9 10 C Independent Variable 1 2 3 4 5 6 7 8 9 10 D Dependent Variable 382 405 398 421 426 415 443 451 446 464 F Estimation Error 6.42 8.43 6.72 8.13 4.98 14.18 5.67 5.52 7.63 2.22 G Square of Error 41 71 45 66 25 201 32 30 58 5 H I J Linear Regression Line y = a + bx a= 380 b= 8.15

Estimator If x = then y=

11 470

c)
500 450 400 350 300 250 200 150 100 50 0 0 ? ? ? ? ? ? ? ? ?

Annual Demand

?

1

2

3

4

5 6 Year

7

8

9

10

d) y = 380 + (8.15)(11) = 470 e) y = 380 = (8.15)(15) = 503 f) The average growth per year is 8.15 tons.

13-35

13.38 a) The amount of advertising is the independent variable and sales is the dependent variable. b)
Sales (thousands of passengers) 25 20 15 10 5 0 0 100 200 300 400 Amount of Advertising (\$1,000s)
C Independent Variable 225 400 350 275 450 D Dependent Variable 16 21 20 17 23 E Estimate 16 21 20 17 23 F Estimation Error 0.21 0.29 0.29 0.36 0.14

? ? ?

?

?

500

c) y = 8.71 + 0.031x
3 4 5 6 7 8 9 10 11 12 B Time Period 1 2 3 4 5 6 7 8 G Square of Error 0 0 0 0 0 H I J Linear Regression Line y = a + bx a= 8.71 b= 0.031

Estimator If x = then y=

300 18

Sales (thousands of passengers)

25 20 15 10 5 0 0 100 200 300 400 Amount of Advertising (\$1,000s) ? ? ? ?

?

500

d) y = 8.71 + (0.031)(300) = 18,000 passengers e) 22 = 8.71 + (0.031)(x) x = \$429,000
13-36

f) An increase of 31 passengers can be attained. 13.39 a) If the sales change from 16 to 19 when the amount of advertising is 225, then the linear regression line shifts below this point (the line actually shifts up, but not as much as the data point has shifted up). b) If the sales change from 23 to 26 when the amount of advertising is 450, then the linear regression line shifts below this point (the line actually shifts up, but not as much as the data point has shifted up). c) If the sales change from 20 to 23 when the amount of advertising is 350, then the linear regression line shifts below this point (the line actually shifts up, but not as much as the data point has shifted up). 13.40 a) The number of flying hours is the independent variable and the number of wing flaps needed is the dependent variable. b)

14 Wing Flaps Needed 12 10 8 6 4 2 0 0
c) y = -3.38 + 0.093x
3 4 5 6 7 8 9 10 11 12 B Time Period 1 2 3 4 5 6 7 8 C Independent Variable 162 149 185 171 138 154 D Dependent Variable 12 9 13 14 10 11 E Estimate 12 10 14 13 9 11

? ? ? ? ?

?

100 Flying Hours (thousands)
F Estimation Error 0.30 1.49 0.84 1.46 0.53 0.04

200

G Square of Error 0 2 1 2 0 0

H

I J Linear Regression Line y = a + bx a= -3.38 b= 0.093

Estimator If x = then y=

150 11

13-37

d)

14 Wing Flaps Needed 12 10 8 6 4 2 0 0 100 Flying Hours (thousands) ? ? ? ?

?

?

200

e) y = -3.38 + (0.093)(150) = 11 f) y = -3.38 + (0.093)(200) = 15 13.41 Joe should use the linear regression line y = –9.95 + 0.10x to develop a forecast for jobs (y) in terms of the number of permits issued (x).
3 4 5 6 7 8 9 10 11 12 13 14 B Time Period 1 2 3 4 5 6 7 8 9 10 C Independent Variable 323 359 396 421 457 472 446 407 374 343 D Dependent Variable 24 23 28 32 34 37 33 30 27 22 E Estimate 22 25 29 31 35 36 34 30 26 23 F Estimation Error 2.48 2.02 0.63 0.93 0.57 0.97 0.50 0.30 0.51 1.47 G Square of Error 6 4 0 1 0 1 0 0 0 2 H I J Linear Regression Line y = a + bx a= -9.95 b= 0.10

Estimator If x = then y=

150 5

Case
13.1 a) We need to forecast the call volume for each day separately. 1) To obtain the seasonally adjusted call volume for the past 13 weeks, we first have to determine the seasonal factors. Because call volumes follow seasonal patterns within the week, we have to calculate a seasonal factor for Monday, Tuesday, Wednesday, Thursday, and Friday. We use the Template for Seasonal Factors. The 0 values for holidays should not factor into the average. Leaving them blank (rather than 0) accomplishes this. (A blank value does not factor into the AVERAGE function in Excel that is used to calculate the seasonal values.) Using this template (shown on the following page, the seasonal factors for Monday, Tuesday, Wednesday, Thursday, and Friday are 1.238, 1.131, 0.999, 0.850, and 0.762, respectively.

13-38

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

B

C

D

E

F

G

Template for Seasonal Factors
Week 44 44 44 44 44 45 45 45 45 45 46 46 46 46 46 47 47 47 47 47 48 48 48 48 48 49 49 49 49 49 50 50 50 50 50 51 51 51 51 51 52/1 52/1 52/1 52/1 52/1 2 2 2 2 2 3 3 3 3 3 4 4 4 4 4 5 5 5 5 5 Day Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri True Value 1,130 851 859 828 726 1,085 1,042 892 840 799 1,303 1,121 1,003 1,113 1,005 2,652 2,825 1,841 Type of Seasonality Daily

Day Mon Tue Wed Thur Fri

Estimate for Seasonal Factor 1.238 1.131 0.999 0.850 0.762

1,949 1,507 989 990 1,084 1,260 1,134 941 847 714 1,002 847 922 842 784 823

Average Call Volume 1,025

401 429 1,209 830 1,082 841 1,362 1,174 967 930 853 924 954 1,346 904 758 886 878 802 945 610 910 754 705 729 772

13-39

2) To forecast the call volume for the next week using the last-value forecasting method, we need to use the Last Value with Seasonality template. To forecast the next week, we need only start with the last Friday value since the Last Value method only looks at the previous day.
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 B C D E F G H I J K

Template for Last-Value Forecasting Method with Seasonality
True Value Seasonally Adjusted Value #N/A #N/A #N/A #N/A 1,013 1,013 1,013 1,013 1,012 1,012 Seasonally Adjusted Forecast #N/A #N/A #N/A #N/A 1,013 1,013 1,013 1,013 1,012 Actual Forecast Forecasting Error

Week 5 5 5 5 5 6 6 6 6 6

Day Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri

Type of Seasonality Daily Day Mon Tue Wed Thur Fri Seasonal Factor 1.238 1.131 0.999 0.850 0.762 1.000 1.000

772 1,254 1,146 1,012 860 771

1,254 1,146 1,012 860 771

0 0 0 0 0

The forecasted call volume for the next week is 5,045 calls: 1,254 calls are received on Monday, 1,148 calls are received on Tuesday, 1,012 calls are received on Wednesday, 860 calls are received on Thursday, and 771 calls are received on Friday.

13-40

3) To forecast the call volume for the next week using the averaging forecasting method, we need to use the Averaging with Seasonality template.
A 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 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 73 74 75 B C D E F G H I J K

Template for Averaging Forecasting Method with Seasonality
True Value 1,130 851 859 828 726 1,085 1,042 892 840 799 1,303 1,121 1,003 1,113 1,005 2,652 2,825 1,841 0 0 1,949 1,507 989 990 1,084 1,260 1,134 941 847 714 1,002 847 922 842 784 823 0 0 401 429 1,209 830 0 1,082 841 1,362 1,174 967 930 853 924 954 1,346 904 758 886 878 802 945 610 910 754 705 729 772 1171 1071 945 804 721 Seasonally Adjusted Value 913 752 860 975 953 877 921 893 989 1,048 1,053 991 1,004 1,310 1,319 2,143 2,497 1,842 0 0 1,575 1,332 990 1,165 1,422 1,018 1,002 942 997 937 810 749 923 991 1,029 665 0 0 472 563 977 734 0 1,274 1,103 1,100 1,038 968 1,095 1,119 746 843 1,347 1,064 995 716 776 803 1,112 800 735 666 706 858 1,013 946 947 946 946 946 Seasonally Adjusted Forecast 913 833 842 875 890 888 893 893 903 918 930 935 941 967 990 1,062 1,147 1,185 1,123 1,067 1,091 1,102 1,097 1,100 1,113 1,109 1,105 1,099 1,096 1,091 1,081 1,071 1,067 1,064 1,063 1,052 1,024 997 983 973 973 967 945 952 956 959 960 961 963 966 962 960 967 969 969 965 962 959 961 959 955 950 947 945 946 946 946 946 946 Actual Forecast 1,033 832 715 667 1,102 1,005 892 759 689 1,136 1,052 934 799 737 1,226 1,202 1,146 1,007 856 1,321 1,234 1,101 932 838 1,377 1,255 1,104 934 835 1,350 1,224 1,070 906 811 1,316 1,191 1,023 847 750 1,204 1,101 967 803 726 1,183 1,085 960 816 734 1,196 1,089 959 822 738 1,200 1,092 961 815 733 1,187 1,081 950 804 720 1,171 1,071 945 804 721 Forecasting Error 182 27 113 59 17 37 0 81 110 167 69 69 314 268 1,426 1,623 695 1,007 856 628 273 112 58 246 117 121 163 87 121 348 377 148 64 27 493 1,191 1,023 446 321 5 271 967 279 115 179 89 7 114 119 272 135 387 82 20 314 214 159 130 123 277 327 245 75 52 0 0 0 0 0

Week 44 44 44 44 44 45 45 45 45 45 46 46 46 46 46 47 47 47 47 47 48 48 48 48 48 49 49 49 49 49 50 50 50 50 50 51 51 51 51 51 52/1 52/1 52/1 52/1 52/1 2 2 2 2 2 3 3 3 3 3 4 4 4 4 4 5 5 5 5 5 6 6 6 6 6

Day Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri

Type of Seasonality Daily Day Mon Tue Wed Thur Fri Seasonal Factor 1.238 1.131 0.999 0.850 0.762 1.000 1.000 1.000 1.000 1.000 1.000 1.000

Mean Absolute Deviation MAD = 267.27 Mean Square Error MSE = 187,916.17

The forecasted call volume for the next week is 4,712 calls: 1,171 calls are received on Monday, 1,071 calls are received on Tuesday, 945 calls are received on Wednesday, 804 calls are received on Thursday, and 721 calls are received on Friday.

13-41

4) To forecast the call volume for the next week using the moving-average forecasting method, we need to use the Moving Averaging with Seasonality template. Since only the past 5 days are used in the forecast, we start with Monday of the last week to forecast through Friday of the next week.
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 B C D E F G H I J K

Template for Moving-Average Forecasting Method with Seasonality
True Value 910 754 705 729 772 985 914 835 732 658 Seasonally Adjusted Value 735 666 706 858 1,013 796 808 836 862 863 #N/A Seasonally Adjusted Forecast #N/A #N/A #N/A #N/A 796 808 836 862 863 833 Actual Forecast Forecasting Error Number of previous periods to consider n=

Week 5 5 5 5 5 6 6 6 6 6 7

Day Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon

5

Type of Seasonality Daily 985 914 835 732 658 1,031 0 0 0 0 0 Day Mon Tue Wed Thur Fri Seasonal Factor 1.238 1.131 0.999 0.850 0.762

The forecasted call volume for the next week is 4,124 calls: 985 calls are received on Monday, 914 calls are received on Tuesday, 835 calls are received on Wednesday, 732 calls are received on Thursday, and 658 calls are received on Friday. 5) To forecast the call volume for the next week using the exponential smoothing forecasting method, we need to use the Exponential with Seasonality template. We start with the initial estimate of 1,125 calls (the average number of calls on non-holidays during the previous 13 weeks).

13-42

A 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 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 73 74 75

B

C

D

E

F

G

H

I

J

K

Template for Exponential Smoothing Forecasting Method with Seasonality
True Value 1,130 851 859 828 726 1,085 1,042 892 840 799 1,303 1,121 1,003 1,113 1,005 2,652 2,825 1,841 0 0 1,949 1,507 989 990 1,084 1,260 1,134 941 847 714 1,002 847 922 842 784 823 0 0 401 429 1,209 830 0 1,082 841 1,362 1,174 967 930 853 924 954 1,346 904 758 886 878 802 945 610 910 754 705 729 772 1074 982 867 737 661 Seasonally Adjusted Value 913 752 860 975 953 877 921 893 989 1,048 1,053 991 1,004 1,310 1,319 2,143 2,497 1,842 0 0 1,575 1,332 990 1,165 1,422 1,018 1,002 942 997 937 810 749 923 991 1,029 665 0 0 472 563 977 734 0 1,274 1,103 1,100 1,038 968 1,095 1,119 746 843 1,347 1,064 995 716 776 803 1,112 800 735 666 706 858 1,013 868 868 868 867 867 Seasonally Adjusted Forecast 1,025 1,014 988 975 975 973 963 959 952 956 965 974 976 978 1,012 1,042 1,152 1,287 1,342 1,208 1,087 1,136 1,156 1,139 1,142 1,170 1,155 1,139 1,120 1,107 1,090 1,062 1,031 1,020 1,017 1,018 983 885 796 764 744 767 764 687 746 782 814 836 849 874 898 883 879 926 940 945 922 908 897 919 907 890 867 851 852 868 868 868 868 868 Actual Forecast 1,269 1,147 987 828 743 1,204 1,089 958 809 728 1,195 1,102 975 831 771 1,290 1,304 1,286 1,140 921 1,346 1,285 1,155 968 870 1,448 1,306 1,138 951 844 1,350 1,202 1,030 867 775 1,260 1,112 884 676 582 921 868 763 584 568 968 920 835 721 666 1,112 999 878 787 716 1,170 1,043 907 762 700 1,122 1,007 867 723 649 1,074 982 867 737 661 Forecasting Error 139 296 128 0 17 119 47 66 31 71 108 19 28 282 234 1,362 1,521 555 1,140 921 603 222 166 22 214 188 172 197 104 130 348 355 108 25 9 437 1,112 884 275 153 288 38 763 498 273 394 254 132 209 187 188 45 468 117 42 284 165 105 183 90 212 253 162 6 123 0 0 0 0 0 Smoothing Constant α= Initial Estimate Average =

Week 44 44 44 44 44 45 45 45 45 45 46 46 46 46 46 47 47 47 47 47 48 48 48 48 48 49 49 49 49 49 50 50 50 50 50 51 51 51 51 51 52/1 52/1 52/1 52/1 52/1 2 2 2 2 2 3 3 3 3 3 4 4 4 4 4 5 5 5 5 5 6 6 6 6 6

Day Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri

0.1

1,025 Type of Seasonality Daily

Day Mon Tue Wed Thur Fri

Seasonal Factor 1.238 1.131 0.999 0.850 0.762 1.000 1.000 1.000 1.000 1.000 1.000 1.000

Mean Absolute Deviation MAD = 261.3 Mean Square Error MSE =

171,377.0

The forecasted call volume for the next week is 4,322 calls: 1,074 calls are received on Monday, 982 calls are received on Tuesday, 867 calls are received on Wednesday, 737 calls are received on Thursday, and 661 calls are received on Friday.
13-43

b) To obtain the mean absolute deviation for each forecasting method, we simply need to subtract the true call volume from the forecasted call volume for each day in the sixth week. We then need to take the absolute value of the five differences. Finally, we need to take the average of these five absolute values to obtain the mean absolute deviation. 1) The spreadsheet for the calculation of the mean absolute deviation for the last-value forecasting method follows.
A 1 2 3 4 5 6 7 8 9 B C D E F G H

Last Value
Week 6 6 6 6 6 Day Monday Tuesday Wednesday Thursday Friday True Value 723 677 521 571 498 Actual Forecast 1,254 1,146 1,012 860 771 Forecast Error 531 469 491 289 273

Mean Absolute Deviation MAD = 410.6

This method is the least effective of the four methods because this method depends heavily upon the average seasonality factors. If the average seasonality factors are not the true seasonality factors for week 6, a large error will appear because the average seasonality factors are used to transform the Friday call volume in week 5 to forecasts for all call volumes in week 6. We calculated in part (a) that the call volume for Friday is 0.762 times lower than the overall average call volume. In week 6, however, the call volume for Friday is only 0.83 times lower than the average call volume over the week. Also, we calculated that the call volume for Monday is 1.34 times higher than the overall average call volume. In Week 6, however, the call volume for Monday is only 1.21 times higher than the average call volume over the week. These differences introduce a large error.

13-44

2) The spreadsheet for the calculation of the mean absolute deviation for the averaging forecasting method appears below.
A 1 2 3 4 5 6 7 8 9 B C D E F G H

Averaging
Week 6 6 6 6 6 Day Monday Tuesday Wednesday Thursday Friday True Value 723 677 521 571 498 Actual Forecast 1,171 1,071 945 804 721 Forecast Error 448 394 424 233 223

Mean Absolute Deviation MAD = 344.4

This method is the second-most effective of the four methods. Again, the reason lies in the average seasonality factors. Applying the average seasonality factors to an average call volume yields a much more accurate result than applying average seasonality factors to only one call volume. This method is not the most effective method, however, because the centralized call center experiences not only daily seasonality, but also weekly seasonality. For example, the call volumes in weeks 45 and 46 are much greater than the call volumes in week 6. Therefore, these larger call volumes inflate the average call volume, which in turn inflates the forecasts for Week 6. 3)The spreadsheet for the calculation of the mean absolute deviation for the movingaverage forecasting method appears below.
A 1 2 3 4 5 6 7 8 9 B C D E F G H

Moving Average
Week 6 6 6 6 6 Day Monday Tuesday Wednesday Thursday Friday True Value 723 677 521 571 498 Actual Forecast 985 914 835 732 658 Forecast Error 262 237 314 161 160

Mean Absolute Deviation MAD = 226.8

This method is the most effective of the four methods because this method only uses the average week 5 call volume to forecast the call volumes for week 6. Again, applying the average seasonality factors to an average call volume yields a much more accurate result than applying average seasonality factors to only one call volume. Also, the average call volume used in this method is not overly inflated since it is an average of the week 5 call volumes, which are closer to the week 6 call volumes than any other of the 13 weeks.

13-45

4) The spreadsheet for the calculation of the mean absolute deviation for exponential forecasting method follows.
A 1 2 3 4 5 6 7 8 9 B C D E F G H

Exponential Smoothing
Week 6 6 6 6 6 Day Monday Tuesday Wednesday Thursday Friday True Value 723 677 521 571 498 Actual Forecast 1,074 982 867 737 661 Forecast Error 351 305 346 166 163

Mean Absolute Deviation MAD = 266.2

This method is nearly as effective as the moving average. This method is a little more effective than the averaging forecasting method because the smoothing constant causes less weight to be placed on the call volumes in the earlier weeks. c) This problem is simply a linear regression problem. 1) To find a mathematical relationship, we use the Linear Regression template. The decentralized case volumes are the independent variables, and the centralized case volumes are the dependent variables. Substituting the case volume data, we obtain the following spreadsheet. The relationship is y = 1576 + 0.756x, where x is the decentralized case volume, and y is the estimated centralized case volume.
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 B C D E F G H I J

Template for Linear Regression
Week 44 45 46 47 48 49 50 51 52/1 2 3 4 5 Independent Variable 612 721 693 540 1,386 577 405 441 655 572 475 530 595 Dependent Variable 2,052 2,170 2,779 2,334 2,514 1,713 1,927 1,167 1,549 2,126 2,337 1,916 2,098 Estimate 2,038 2,121 2,099 1,984 2,623 2,012 1,882 1,909 2,071 2,008 1,935 1,976 2,025 Estimation Error 13.84 49.45 679.61 350.27 109.26 298.70 45.32 741.89 521.66 118.08 402.41 60.17 72.69 Square of Error 192 2,445 461,872 122,690 11,938 89,221 2,054 550,400 272,132 13,943 161,932 3,620 5,284 Linear Regression Line y = a + bx a= 1,576 b= 0.76

Estimator If x = then y=

613 2,038.9

13-46

2) To forecast the week 6 call volume for the centralized call center, we simply input the week 6 decentralized case volume for the value of x in the Estimator section of the Linear Regression Spreadsheet (as shown in part 1 above). The value of y then represents the week 6 centralized case volume. We multiply this value of y by 1.5 to obtain the week 6 centralized call volume. Thus, the forecasted number of calls is 1.5 * 2,038.9 = 3,058. We then break this weekly call volume into daily call volume. We do this conversion by dividing the weekly call volume by the sum of the seasonal factors calculated in part (a) and then multiplying this weekly call volume by the appropriate seasonal factor to find the call volume for each of the five days of the week. The spreadsheet showing these calculations follows:
A 1 2 3 4 5 6 7 8 9 10 B C

Week 6 Call Volume Daily Call Volume

3058 611.6 Seasonal Factor 1.238 1.131 0.999 0.850 0.762 Forecasted Call Volume 757 692 611 520 466

Day Monday Tuesday Wednesday Thursday Friday

The forecasted call volume for week 6 is 3,046 calls: 757 calls are received on Monday, 692 calls are received on Tuesday, 611 calls are received on Wednesday, 520 calls are received on Thursday, and 466 calls are received on Friday.

13-47

3) To calculate the mean absolute deviation, we need to subtract the true call volume from the forecasted call volume for each day in the sixth week. We then need to take the absolute value of the five differences. Finally, we need to take the average of these five absolute values to obtain the mean absolute deviation. The spreadsheet for the calculation of the mean absolute deviation follows.
A 1 2 3 4 5 6 7 8 9 B C D E F G H

Causal Forecasting
Week 6 6 6 6 6 Day Monday Tuesday Wednesday Thursday Friday True Value 723 677 521 571 498 Actual Forecast 757 692 611 520 466 Forecast Error 34 15 90 51 32

Mean Absolute Deviation MAD = 44.4

This forecasting method is by far the most effective method. The centralized center performs the same services and serves the same population as the decentralized center. Therefore, the call volume trends are the same. Once we have a factor to scale the decentralized call volumes to the centralized call volumes, we have a very effective forecasting method. d) We would definitely recommend using the causal forecasting method implemented in part (c) because it yields the lowest error. The causal method shows us that the call volume trends remain relatively the same year after year. We had to convert between case volumes and call volumes in part (c), however, and such a conversion introduces error. For example, what if a case generates a higher or lower number of calls? We therefore recommend that call volume data be meticulously recorded as the centralized center continues its operation. Once one year’s worth of call volumes have been collected, the causal forecasting model should be updated. The model should be updated to use the historical centralized call volume data instead of the historical decentralized case volume data.

13-48

《数据模型与决策》复习题及参考答案

《数据模型与决策》复习题及参考答案_图文