Chapter 7
Problem Summary
Prob. # / Concepts Covered / Level of Difficulty / Notes7.1 / Graphing Time Series, Testing for Stationarity Using Regression, Forecasting Using Simple and Weighted Moving Averages, Evaluating Forecasting Techniques Using MSE / 2
7.2 / Exponential Smoothing, Evaluating Forecasting Techniques Using MSE / 1
7.3 / Using Regression to Forecast a Linear Trend Model / 1
7.4 / Using Holt's Method to Forecast a Linear Trend Model / 3
7.5 / Forecasting Using Classical Decomposition for a Multiplicative Model / 4
7.6 / Graphing Time Series, Testing for Stationarity Using Regression, Forecasting Using Simple Moving Averages / 1
7.7 / Forecasting Using Exponential Smoothing / 1
7.8 / Forecasting Using Multiple Regression for an Additive Model / 4
7.9 / Graphing Time Series, Testing for Stationarity Using Regression, Forecasting Using Simple Moving Averages and Exponential Smoothing / 2
7.10 / Determining the Optimal Smoothing Constant Using Based on the MSE Criterion Using Solver, Determining the Optimal Weightings for a Weighted Moving Average Using Solver, Selecting the Better Forecasting Technique / 5
7.11 / Forecasting Using Linear Regression / 1
7.12 / Forecasting Using Classical Decomposition for a Multiplicative Model / 4
7.13 / Forecasting Using Multiple Regression for an Additive Model / 4
7.14 / Forecasting Using Classical Decomposition for a Multiplicative Model / 4
7.15 / Graphing Time Series, Testing for Stationarity Using Regression, Forecasting Using Exponential Smoothing, Making a Decision Based on Forecast Results / 4
7.16 / Forecasting Using a Weighted Moving Average, Making a Decision Based on Forecast Results / 2
7.17 / Forecasting Using Linear Regression / 2
7.18 / Graphing Time Series, Forecasting Using Multiple Regression for an Additive Model / 4
7.19 / Graphing a Time Series, Using Regression to Forecast a Linear Trend Model / 4
7.20 / Forecasting Using Holt's Method / 4
7.21 / Graphing Time Series, Testing for Stationarity Using Regression, Forecasting Using Simple Moving Averages, Evaluating Forecasting Techniques Using MSE / 4
7.22 / Forecasting Using Exponential Smoothing, Evaluating Forecasting Methods in terms of MSE, MAD, MAPE, and LAD / 3
7.23 / Determining the Optimal Exponential Smoothing Constant Based on the MSE, MAD, and MAPE Criteria Using Solver / 5
7.24 / Graphing Time Series, Testing for Stationarity Using Regression, Forecasting Using Exponential Smoothing, Determining the Optimal Smoothing Constant Based on the MSE Criterion Using Solver / 5
7.25 / Forecasting Using Moving Averages, Evaluating Forecasting Techniques / 2
7.26 / Forecasting Using Exponential Smoothing / 1
7.27 / Forecasting Using Holt's Method and Linear Regression, Evaluating Forecasting Techniques in Terms of MAD / 4
7.28 / Forecasting Using Simple and Weighted Moving Averages, Evaluating Forecasting Techniques Using MAD / 3
7.29 / Forecasting Using Exponential Smoothing, Evaluating Forecasting Techniques Using MSE / 3
7.30 / Forecasting Using Linear Regression and Holt’s Method / 3
7.31 / Forecasting Using Exponential Smoothing, Selecting the Better Smoothing Constant / 3
7.32 / Determining the Best Smoothing Constant Using Solver / 4
7.33 / Forecasting Using a Weighted Moving Average, Choosing Between a Weighted and Simple Moving Average Based on the MAPE Criterion / 3
7.34 / Forecasting Using Linear Regression and Forecasting Using Multiple Regression for an Additive Model, Determining Autocorrelation and Testing for Significance / 6
7.35 / Forecasting Using Classical Decomposition for a Multiplicative Model / 4
7.36 / Forecasting a Transformed Time Series Using Exponential Smoothing / 5
7.37 / Forecasting Two Separate Time Series, One Using Exponential Smoothing, the Other Using Holt’s Method, Multiplying the Two Forecasts Together / 6
7.38 / Forecasting Using Multiple Regression for an Additive Model / 4
7.39 / Forecasting Using Classical Decomposition for a Multiplicative Model / 4
7.40 / Using Regression to Determine if a Stationary Model is Appropriate, Forecasting Using Exponential Smoothing / 3
7.41 / Forecasting Using Holt’s Method / 2
7.42 / Forecasting Using Linear Regression, Determining which Forecasting Technique is Better Using the MAPE Criterion / 3
7.43 / Using Regression to Determine if a Stationary Model is Appropriate, Forecasting Using the Exponential Smoothing and Weighted Moving Average Techniques / 4
7.44 / Forecasting Using Linear Regression and Holt’s Method, Selecting the Better Technique in Terms of the MSE Criterion / 4
7.45 / Determining the Appropriate Forecasting Model Based on the MAD and MSE Criteria, Forecasting Using Exponential Smoothing, Linear Regression, and Holt’s Method / 6
7.46 / Collecting Data and Performing a Forecast Using Exponential Smoothing with Solver Determining the Best Smoothing Constant in Terms of the MSE Criterion / 5
7.47 / Collecting Data and Performing a Forecast Using Classical Decomposition for a Multiplicative Model / 5
7.48 / Collecting Data and Performing a Forecast Using Exponential Smoothing with Solver Determining the Best Smoothing Constant in Terms of the MAPE Criterion / 5
7.49 / Collecting Data and Performing a Forecast Using Multiple Regression for an Additive Model / 5
7.50 / Collecting Data and Performing a Forecast Using Holt’s Method / 5
Problem Solutions
7.1 See file Ch7.1.xls
a.
Yes, a stationary model seems appropriate
b.
Coefficients / Standard Error / t Stat / P-value / Lower 95% / Upper 95% / Lower 95.0% / Upper 95.0%Intercept / 20.16667 / 1.373732 / 14.6802 / 4.3E-08 / 17.1058 / 23.22753 / 17.1058 / 23.22753
Period / -0.07692 / 0.186653 / -0.41212 / 0.688949 / -0.49281 / 0.338967 / -0.49281 / 0.338967
From regression output, t = -.412 and p = .689. A stationary model seems appropriate since the linear term, Period, is not significant.
7.1 c.
Forecast for January -- 19, for upcoming year – 12*19 = 228
7.1 d.
Forecast for January -- 20.4
e. 4 month moving average. MAD is 1.72
7.2 See files Ch7.2a.xls and Ch7.2b.xls
a.
Forecast for January -- 18.86
7.2 b. See file Ch7.2b.xls
Forecast for January -- 20.28
c. = .6 gives the lower MSE
7.3 See file Ch7.3.xls
a.
b.
Coefficients / Standard Error / t Stat / P-value / Lower 95% / Upper 95% / Lower 95.0% / Upper 95.0%Intercept / 406.6014 / 3.916368 / 103.8211 / 4.22E-31 / 398.4794 / 414.7235 / 398.4794 / 414.7235
Month / 10.17522 / 0.274089 / 37.12382 / 2.44E-21 / 9.606792 / 10.74364 / 9.606792 / 10.74364
From regression output, t = 37.1238, p = 0
7.3c.
From the above output we see that the forecast is as follows:
25 -- 660, 26 -- 671, 27 -- 681, 28 -- 692, 29 -- 702, 30 -- 712, 31 -- 722, 32 -- 732, 33 -- 742, 34 -- 753, 35 -- 763, 36 -- 773.
7.4See file Ch7.4a.xls and Ch7.4b.xls
Forecast for upcoming 12 months:
Period / 25 / 26 / 27 / 28 / 29 / 30 / 31 / 32 / 33 / 34 / 35 / 36Forecast / 665 / 675 / 685 / 695 / 706 / 716 / 726 / 736 / 746 / 756 / 766 / 776
7.4b See file Ch7.4b.xls
Forecast for upcoming 12 months:
Period / 25 / 26 / 27 / 28 / 29 / 30 / 31 / 32 / 33 / 34 / 35 / 36Forecast / 659 / 665 / 671 / 676 / 682 / 688 / 693 / 699 / 705 / 710 / 716 / 722
- = .5 and = .7
d. = .5 and = .7
7.5 See file Ch7.5.xls
Week 5: Su 318.89, M 371.72, Tu 353.72, W 397.54, Th 404.22, F 362.08, Sa 366.49,
Week 6: Su 326.62, M 380.70, Tu 362.24, W 407.08, Th 413.89, F 370.71, Sa 375.20.
7.6 See file Ch7.6.xls
a.
b.
Coefficients / Standard Error / t Stat / P-value / Lower 95% / Upper 95% / Lower 95.0% / Upper 95.0%Intercept / 67.175 / 3.120786 / 21.52503 / 3.96E-12 / 60.48157 / 73.86843 / 60.48157 / 73.86843
Week / 0.457353 / 0.322744 / 1.417077 / 0.178329 / -0.23486 / 1.149571 / -0.23486 / 1.149571
From the regression output, t = 1.4171, p = .1783 so a stationary model seems appropriate since Week is not significant.
7.6 c.
Forecast for upcoming year = 52*71*100 = 369,200 bottles of shampoo.
7.7 See file Ch7.7.xls
Forecast for upcoming year = 52*70.6296*100 = 367,274 bottles of shampoo.
7.8 See file Ch7.8.xls
Quarter 1 -- Forecast = 5.71 - .0775*21 + 1.9275 = 6.01
Quarter 2 -- Forecast = 5.71 - .0775*22 + 6.0050 = 10.01
Quarter 3 -- Forecast = 5.71 - .0775*23 + 3.5625 = 7.49
Quarter 4 -- Forecast = 5.71 - .0775*24 = 3.85
7.9 See file Ch7.9.xls,
a.
b.
Coefficients / Standard Error / t Stat / P-value / Lower 95% / Upper 95% / Lower 95.0% / Upper 95.0%Intercept / 152.5421 / 7.57903 / 20.12686 / 8.64E-14 / 136.6191 / 168.4651 / 136.6191 / 168.4651
Period / -0.27068 / 0.632685 / -0.42782 / 0.673855 / -1.5999 / 1.058547 / -1.5999 / 1.058547
From the regression output, t = -.428 and p = .674. Hence, one cannot conclude that linear trend is present.
7.9 c. See file Ch7.3.xls
Forecast for upcoming week is 143.8*5 = 719
7.9d. See file Ch7.9.xls
Forecast for upcoming week = 147.2*5 = 736.
7.10 See file Ch7.10.xls
The optimal smoothing constant is = .11.
7.10 b.
- The weighted moving average gives lower values for the MSE and hence it would be recommended.
7.11 See file Ch7.11.xls
The forecast is as follows:
year 6 -- 134,189, year 7 -- 139,288, year 8 -- 144,387
7.12 See file Ch7.12.xls
The forecast for the four quarters of the upcoming year is as follows;
Quarter 1 -- 5485
Quarter 2 -- 6524
Quarter 3 -- 6956
Quarter 4 -- 5194
7.13 See file Ch7.13.xls
The year 5 forecast is:
fall -- 118.1667 +4.9778*13 + 666.7060 = 850
spring -- 118.1667 +4.9778*14 + 595.7281 = 784
summer -- 118.1667 +4.9778*15 = 193
7.14See Ch7.14.xls
37 / jan / 455.20
38 / feb / 456.16
39 / mar / 365.16
40 / apr / 347.43
41 / may / 254.39
42 / jun / 226.24
43 / jul / 178.88
44 / aug / 189.56
45 / sep / 273.23
46 / oct / 316.73
47 / nov / 353.00
48 / dec / 387.26
7.15 See file Ch7.15.xls
a.
b.
Coefficients / Standard Error / t Stat / P-value / Lower 95% / Upper 95% / Lower 95.0% / Upper 95.0%Intercept / 0.442632 / 0.007661 / 57.7744 / 6.83E-22 / 0.426536 / 0.458728 / 0.426536 / 0.458728
Period / 0.001226 / 0.00064 / 1.916262 / 0.071351 / -0.00012 / 0.002569 / -0.00012 / 0.002569
From the regression analysis, since t = 1.916 and p = .071, a stationary model is appropriate. That is, the linear component, Period, is not significant.
c. See file Ch7.15.xls
Forecast of firm's cocoa purchase cost over next six months = $.45585*140,000 = $63,819
d. Yes, purchasing the futures is worthwhile.
7.16 See file Ch7.16.xls
As the forecast is now $.4480, the futures contract purchase is not recommended since it would cost more than $.01 per pound over the firm's forecast cost. Forecast of the firm’s purchase cost over the next six months = $0.448*140,000 = $62,720.
7.17 See file Ch7.17.xls
Letting time 1997 correspond to t =1 gives the following spreadsheet:
a. The year 2006 corresponds to year 10, hence the forecast for the book value is 8.12.
- The year 2035 corresponds to year 39, hence the forecast book value is: -2.96. As this value is negative, and firms do generally not survive with a negative book value, something is amiss. The model may be inappropriate since 2035 if too far into the future from the last observed time period. If the model is deemed appropriate, Kerf will have to something different in the future if it wishes to remain solvent.
7.18 See file Ch7.18.xls
a.
b.
The following are the forecasts for week 5:
Monday -- 44.3 +.436*21 - 2.256 = 51
Tuesday --44.3 +.436*22 - 10.192 = 44
Wednesday --44.3 +.436*23 - 3.378 = 51
Thursday --44.3 +.436*24 - 9.314 = 45
Friday -- 44.3 +.436*25 = 55
7.19 See file Ch7.19.xls
a.
b.
Coefficients / Standard Error / t Stat / P-value / Lower 95% / Upper 95% / Lower 95.0% / Upper 95.0%Intercept / 1165.475 / 35.94789 / 32.42123 / 4.56E-20 / 1090.923 / 1240.026 / 1090.923 / 1240.026
Period / 14.3287 / 2.515828 / 5.695419 / 9.96E-06 / 9.111182 / 19.54621 / 9.111182 / 19.54621
From the regression analysis we see that t = 5.695 and p = .0001.
7.19 c See file Ch7.19.xls
The forecast is as follows: Jan 1524, Feb 1538, Mar 1552, Apr 1567, May 1581, Jun 1595, Jul 1610, Aug 1624, Sep 1638, Oct 1653, Nov 1667, Dec 1681
7.20See file Ch7.20.xls
Forecast / 1538 / 1525 / 1512 / 1498 / 1485 / 1472 / 1459 / 1446 / 1432 / 1419 / 1406 / 1393
7.21 See files Ch7.21a.xls and Ch7.21d.xls
a.
b.
Coefficients / Standard Error / t Stat / P-value / Lower 95% / Upper 95% / Lower 95.0% / Upper 95.0%Intercept / 16452.38 / 527.348 / 31.19834 / 1.31E-13 / 15313.12 / 17591.65 / 15313.12 / 17591.65
Week / 9.285714 / 58.00055 / 0.160097 / 0.875266 / -116.017 / 134.5883 / -116.017 / 134.5883
From the regression analysis we see that since t = .16 and p = .8753 a stationary model is appropriate.
7.21 c. See file Ch7.21a.xls
c. The forecast for each week is 16,367
7.21d See file Ch7.21d.xls
d. The forecast for each week is 16,475
e. The 3-week moving average gives the lower MSE.
7.22 See files Ch7.22a.xls and Ch7.22b.xls
a.
a. The forecast is 16,509
7.22b See file Ch7.22b.xls
b. The forecast is 16,515
c. = .10, MSE is 964,750 versus 1034.893 for = .20
- = .10, MAD = 851.5475 versus 859.8929 for = .20
- = .10, MAPE = 5.1496 versus 5.2089 for = .20
f. = .10, LAD = 1890 versus 1980 for = .20
7.23 See files Ch7.23a.xls, Ch7.23b.xls, and Ch7.23c.xls
a.
a. = 0 minimizes MSE
7.23b See file Ch7,23b.xls
b. = 0 minimizes MAD
7.23 c. See file Ch7.23c.xls
c. = 0 minimizes MAPE
7.24 See files Ch7.24a.xls, Ch7.24d.xls
a.
b.
Coefficients / Standard Error / T Stat / P-value / Lower 95% / Upper 95% / Lower 95.0% / Upper 95.0%Intercept / 4.94 / 0.308545 / 16.01063 / 2.32E-07 / 4.228494 / 5.651506 / 4.228494 / 5.651506
Campaign / 0.02 / 0.049727 / 0.4022 / 0.698069 / -0.09467 / 0.13467 / -0.09467 / 0.13467
From regression analysis we see t = .402, p = .698, a stationary model is appropriate since slope, Campaign, is not significant.
7.24 c. See file Ch7.24c.xls
c. The forecast response rate is 4.9%
7.24 d. See file Ch7.24d.xls
d. The optimal value for the smoothing constant in terms of minimizing the mean square error is = .27.
7.25 See files Ch7.25a.xls, Ch7,25b.xls, Ch7.25c.xls, and Ch7.25d.xls
a.
a. With n = 1, forecast = 4.9%, MSE = .4922
7.25b See file Ch7.25b.xls
b. With n = 2, forecast = 4.8%, MSE = .2259
7.25 c. See file Ch7.25c.xls
c. With n = 3, forecast = 5.0%, MSE = .3338
7.25 d. See file Ch7.25d.xls
d. With n = 4, forecast = 5.05%, MSE =.2711
As a result of this analysis a two-period moving average forecast would be recommended since it gives the lowest MSE of the models evaluated.
7.26 See file ch7.26.xls
a.
a. The forecast is 17.0972 which would be rounded to 17.
b. Exponential smoothing assumes that there is no long-term trend and no seasonal factors.
7.27 See file Ch7.27.xls
a.
a. Using Holt's method gives the following forecast:
Week 13 -- 47.76757 = 48
Week 14 -- 50.1519 = 50
Week 15 -- 52.53624 = 53
7.27 b. Using linear regression gives
b. Using a regression approach the forecast is as follows:
Week 13 -- 47.970 = 48
Week 14 -- 50.37529 = 50
Week 15 -- 52.78089 = 53
c. Based on the MAD criterion, the regression approach is preferred.
7.28 See file Ch7.28.xls
a.
Using an eight-week simple moving average gives a forecast of 38.5 which would be rounded to 39.
7.28a continued
Using the AV8 forecasting system gives a forecast of 38.4 which would be rounded to 38.
b. The eight week simple moving average gives an MAD value of 5.375 while the eight week weighted moving average gives an MAD value of 5.25. Based on the MAD, Nature's Health should switch to the weighted moving average for forecasting.
7.29 See files Ch7.29A.xls and Ch7.29B.xls
7.29 a. continued
a. Using a = .2, the MSE is 95.16206. Using = .7, the MSE is 120.2956. Hence, the item should be classified as a Group A item.
b. The forecast for the upcoming weeks using = .2 is 37.04943 = 37.
7.30See file Ch7.30.xls
a.
a. The forecast for total sales over the upcoming quarter is 6480.534*$1000 = $6,480,534.
7.30 b.
b. The forecast for total sales over the upcoming quarter is 6332.364*$1000 = $6,332,364.
7.31See files Ch7.31a.xls and Ch7.31b.xls
a.
a. Forecast for week 53 is 473 cases
7.31b. See file Ch7.31b.xls
b. Forecast for week 53 is 461 cases.
c. Company should select the = .4 smoothing constant as it gives lower values for the MSE, MAD, MAPE, and LAD measures.
7.32See file Ch7.32.xls
The company should use a smoothing constant of = 1. The forecast in this case is 478 cases in week 53.
7.33See file Ch7.33.xls
a.
- The forecast for sales in week 53 is 479 cases.
7.33b.
- No, the five-period simple moving average should be used as it has the lower MAPE. This model will forecast 447 cases in week 53.
7.34 See file Ch7.34.xls
a.
a. The forecasts are as follows:
Next Year
Summer122.81 + 3.99*17 - 36.77 = 154
Fall122.81 + 3.99*18 - 1.76 = 193
Winter122.81 + 3.99*19 + 52.74 = 251
Spring122.81 + 3.99*20 = 203
Two Years from Now
Summer122.81 + 3.99*21 - 36.77 = 170
Fall122.81 + 3.99*22 - 1.76 = 209
Winter122.81 + 3.99*23 + 52.74 = 267
Spring122.81 + 3.99*24 = 219
7.34 b.
Next Year -- 801
Two Years from Now -- 864.9 = 865
7.34 c.
c. r4 = .500749. Since .500749 is greater than 2/16 = .5, we would conclude that there is significant autocorrelation of lag 4. We could also verify this by doing an F test to determine if a reduced model can be used.
7.35 See file Ch7.35.xls
Forecast / 6,573 / 11,201 / 10,816 / 9,850
7.36 See file Ch7.36.xls
Forecast of sales over the upcoming quarter is $803,329.
7.37See Ch7.37.xls
7.37 continued
Customer demand forecast is 846.1738. Multiplying this value by the forecast for the amount spent, $130.5277, gives a forecast for upcoming month’s revenue equal to $110,449.
7.38See file Ch7.38.xls
a.
49 / 2870.583
50 / 2857.583
51 / 2809.833
52 / 2700.833
53 / 2715.083
54 / 2826.333
55 / 2865.583
56 / 2897.583
57 / 2890.333
58 / 2911.333
59 / 2944.583
60 / 2951.333
b. MAD = 22.642
7.39See file Ch7.39.xls
a.
a.
Period / 49 / 50 / 51 / 52 / 53 / 54 / 55 / 56 / 57 / 58 / 59 / 60Forecast / 2877 / 2878 / 2818 / 2691 / 2700 / 2828 / 2865 / 2900 / 2891 / 2909 / 2956 / 2950
- MAD = 16.152
c. In terms of MAD criterion, classical decomposition gives better results.
7.40See file Ch7.40.xls
a.
Coefficients / Standard Error / t Stat / P-value / Lower 95% / Upper 95% / Lower 95.0% / Upper 95.0%Intercept / 3.6366842 / 0.02374201 / 153.18 / 1.7E-29 / 3.5868 / 3.68656 / 3.586804 / 3.686564
Period / -0.0013985 / 0.00198194 / -0.706 / 0.48946 / -0.00556 / 0.00277 / -0.00556 / 0.002765
The t statistic is -.706 and the p value is .489. Hence, it appears a stationary forecasting model is reasonable since the linear term, Period, is not significant.
b.
- $3.63
7.41 See file Ch7.41.xls
The forecast for the stock price over the next ten days is as follows:
Day / 61 / 62 / 63 / 64 / 65 / 66 / 67 / 68 / 69 / 70Forecast / 50.38 / 50.41 / 50.44 / 50.46 / 50.49 / 50.51 / 50.54 / 50.57 / 50.59 / 50.62
7.42 See file Ch7.42.xls
a.
Day / 61 / 62 / 63 / 64 / 65 / 66 / 67 / 68 / 69 / 70Forecast / 48.76 / 48.62 / 48.47 / 48.33 / 48.19 / 48.04 / 47.90 / 47.76 / 47.61 / 47.47
b. Linear regression performs better under the MAPE criterion.
7.43 See file Ch7.43.xls
a.
.
Coefficients / Standard Error / t Stat / P-value / Lower 95% / Upper 95% / Lower 95.0% / Upper 95.0%Intercept / 82.71579 / 18.00901 / 4.593022 / 0.000226 / 44.88023 / 120.5514 / 44.88023 / 120.5514
Period / -1.07293 / 1.503363 / -0.71369 / 0.484572 / -4.23138 / 2.085519 / -4.23138 / 2.085519
a. Since the t value is -.7137 and the p value is .485, a stationary model is appropriate for the time series. That is, the linear term, Period, is not significant.
- Sales are forecasted to be 113.7*10 = 1137 copies
7.43 c.
c. Sales are forecasted to be 103.6*10 = 1036 copies
7.44 See file Ch7.44.xls
a.
a. Sales are forecasted to be 554 copies
7.44 b.
b. Sales are forecasted to be 1997 copies
c. Holt’s method as it has the lower MSE value.
7.45 See file Ch7.45.xls
a.
a. The Regression Analysis on 1999 data indicates a stationary model is appropriate. For = 0.3, MAD = 0.019 and for = 0.7, MAD = 0.015. Hence, = 0.7 is used and the forecast of gasoline prices for all weeks 12, 13, and 14 would be $1.178 per gallon.
7.45b.
b.
b. The Regression Analysis on 2000 data indicates that trend is present. Regression yields MSE = 0.004 while Holt’s method yields MSE = 0.003. Hence, Holt’s method is used and the forecast for the three weeks is as follows:
Week / 12 / 13 / 14Forecast / 1.788 / 1.870 / 1.953
7-1