Essentials of Business Analytics 1st Edition by Jeffrey D. Camm - Test Bank

Essentials of Business Analytics 1st Edition by Jeffrey D. Camm – Test Bank

 

Instant Download – Complete Test Bank With Answers

 

 

Sample Questions Are Posted Below

 

Chapter 5: Time Series Analysis and Forecasting

1. A forecast is defined as a(n):
2. prediction of future values of a time series.
3. quantitative method used when historical data on the variable of interest are either unavailable or not applicable.
4. set of observations on a variable measured at successive points in time.
5. outcome of a random experiment.

Answer: A

Difficulty: Easy

LO: 5.1, Page 204

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: Forecast is defined as a prediction of future values of a time series.

 

2. Qualitative forecasting methods are used when:
3. historical data on the variable being forecast are available.
4. information on past values of the variable being measured is quantifiable.
5. historical data on the variable being forecast are either unavailable or are not applicable.
6. it is reasonable to assume that past is prologue.

Answer: C

Difficulty: Moderate

LO: 5.1, Page 204

Bloom’s: Comprehension

BUSPROG: Analytic

DISC: Time Series Data

Feedback: Qualitative forecasting methods are used when historical data on the variable being forecast are either unavailable or not applicable.

 

3. A set of observations on a variable measured at successive points in time or over successive periods of time constitute a _____.
4. geometric series
5. time invariant set
6. time series
7. logarithmic series

Answer: C

Difficulty: Easy

LO: 5.1, Page 205

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: A time series is a sequence of observations on a variable measured at successive points in time or over successive periods of time.

 

4. Which of the following states the objective of time series analysis?
5. To study the variation of time with respect to increase in the variable value
6. To analyze the time-dependent environmental factors that affected variable values in the past
7. To use present variable values to study what should have been the ideal past values
8. To uncover a pattern in the time series and then extrapolate the pattern into the future

Answer: D

Difficulty: Moderate

LO: 5.1, Page 204

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: The objective of time series analysis is to uncover a pattern in the time series and then extrapolate the pattern into the future.

 

5. Causal forecasting:
6. does not depend upon historical values of a variable.
7. assumes that the variable being forecast has a cause-effect relationship with one or more other variables.
8. uses present variable values to study what should have been the ideal past values.
9. uses time series plots to study if the variable values are centered around the mean.

Answer: B

Difficulty: Moderate

LO: 5.1, Page 204

Bloom’s: Comprehension

BUSPROG: Analytic

DISC: Time Series Data

Feedback: Causal or exploratory forecasting methods are based on the assumption that the variable being forecast has a cause-effect relationship with one or more other variables.

 

6. A _____ pattern exists when the data fluctuate randomly around a constant mean over time.
7. vertical
8. seasonal
9. cyclical
10. horizontal

Answer: D

Difficulty: Easy

LO: 5.1, Page 205

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: A horizontal pattern exists when the data fluctuate randomly around a constant mean over time.

 

7. _____ is the term used for a time series whose statistical properties are independent of time.
8. Cluster
9. Stationary time series
10. Trend
11. Constant time series

Answer: B

Difficulty: Easy

LO: 5.1, Page 205

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: Stationary time series is the term used for a time series whose statistical properties are independent of time.

 

8. Which of the following is true of a stationary time series?
9. The process generating the data has a variable mean.
10. The variability of the time series is constant over time.
11. The time series plot for this case is a straight line.
12. The fluctuations in values will always exhibit a cyclical pattern.

Answer: B

Difficulty: Moderate

LO: 5.1, Page 205

Bloom’s: Comprehension

BUSPROG: Analytic

DISC: Time Series Data

Feedback: For a stationary time series,

1. The process generating the data has a constant mean.
2. The variability of the time series is constant over time.

 

9. If a time series plot exhibits a horizontal pattern, then:
10. it is evident that the time series is stationary.
11. the data fluctuates randomly around a variable mean.
12. there is no relationship between time and the time series variable.
13. there is no sufficient evidence to conclude that the time series is stationary.

Answer: D

Difficulty: Moderate

LO: 5.1, Page 205

Bloom’s: Comprehension

BUSPROG: Analytic

DISC: Time Series Data

Feedback: A time series plot for a stationary time series will always exhibit a horizontal pattern with random fluctuations. However, simply observing a horizontal pattern is not sufficient evidence to conclude that the time series is stationary.

 

10. Trend refers to:
11. the long-run shift or movement in the time series observable over several periods of time.
12. the outcome of a random experiment.
13. the recurring patterns observed over successive periods of time.
14. the short-run shift or movement in the time series observable at some specific period of time.

Answer: A

Difficulty: Moderate

LO: 5.1, Page 207

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: Trend refers to the long-run shift or movement in the time series observable over several periods of time.

 

11. Trends result from:
12. rapidly-arising short-term factors.
13. rapidly-arising long-term factors.
14. slowly-varying short-term factors.
15. slowly-varying long-term factors.

Answer: D

Difficulty: Moderate

LO: 5.1, Page 207

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: A trend is usually the result of long-term factors such as population increases or decreases, shifting demographic characteristics of the population, improving technology, changes in the competitive landscape, and/or changes in consumer preferences.

 

12. Which of the following data patterns best describes the scenario shown in the below plot?
13. Time series with a linear trend pattern
14. Time series with a nonlinear trend pattern
15. Time series with no pattern
16. Time series with a horizontal pattern

Answer: D

Difficulty: Easy

LO: 5.1, Page 205

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: The given scenario shows a time series plot with a horizontal pattern.

 

13. Which of the following data patterns best describes the scenario shown in the given time series plot?
14. Linear trend pattern
15. Nonlinear trend pattern
16. Seasonal pattern
17. Cyclical pattern

Answer: A

Difficulty: Easy

LO: 5.1, Page 207

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: The given time series plot shows a linear trend.

 

14. Which of the following data patterns best describes the scenario shown in the given time series plot?
15. Linear trend pattern
16. Nonlinear trend pattern
17. Seasonal pattern
18. Cyclical pattern

Answer: B

Difficulty: Easy

LO: 5.1, Page 208

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: The given time series plot shows a nonlinear trend.

 

15. Which of the following data patterns best describes the scenario shown in the given time series plot?
16. Linear trend pattern
17. Logarithmic trend
18. Exponential trend
19. Seasonal pattern

Answer: D

Difficulty: Easy

LO: 5.1, Page 209

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: The given time series plot shows a seasonal trend.

 

16. Which of the following data patterns best describes the scenario shown in the given time series plot?
17. Linear trend and cyclical pattern
18. Linear trend and horizontal pattern
19. Seasonal and cyclical patterns
20. Seasonal pattern and linear trend

Answer: D

Difficulty: Easy

LO: 5.1, Page 209

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: The given time series plot exhibits both a seasonal pattern and a linear trend.

 

17. An exponential trend pattern is appropriate when:
18. the amount of increase between periods in the value of the variable is constant.
19. the percentage change between periods in the value of the variable is relatively constant.
20. there is a no relationship between the time series variable and time.
21. there are random fluctuations in the variable value with time.

Answer: B

Difficulty: Moderate

LO: 5.1, Page 209

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: An exponential trend pattern is appropriate when the percentage change of variable value from one period to the next is relatively constant.

 

18. A time series that shows a recurring pattern over one year or less is said to follow a _____.
19. horizontal pattern
20. stationary pattern
21. cyclical pattern
22. seasonal pattern

Answer: D

Difficulty: Easy

LO: 5.1, Page 209

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: A time series that shows a recurring pattern over one year or less is said to follow a seasonal pattern.

 

19. With reference to time series data patterns, a cyclical pattern is the component of the time series that:
20. shows a periodic pattern over one year or less.
21. does not vary with respect to time.
22. results in periodic above-trend and below-trend behavior of the time series lasting more than one year.
23. is characterized by a linear variation of the dependent variable with respect to time.

Answer: C

Difficulty: Moderate

LO: 5.1, Page 211

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: With reference to time series data patterns, a cyclical pattern is the component of the time series that results in periodic above-trend and below-trend behavior of the time series lasting more than one year.

 

20. _____ is the amount by which the predicted value differs from the observed value of the time series variable.
21. Mean forecast error
22. Mean absolute error
23. Smoothing constant
24. Forecast error

Answer: D

Difficulty: Easy

LO: 5.2, Page 213

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: Forecast error is the amount by which the forecasted value differs from the observed value.

 

21. If the forecasted value of the time series variable for period 2 is 22.5 and the actual value observed for period 2 is 25, what is the forecast error in period 2?
22. 3
23. 2
24. 5
25. –2.5

Answer: C

Difficulty: Easy

LO: 5.2, Page 213

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

Feedback: Forecast error is the amount by which the forecasted value differs from the observed value. For the given values, the forecast error in period 2 is computed as 25 – 22.5 = 2.5.

 

22. The measures of accuracy of the forecasts:
23. check how well a particular forecasting method is able to reproduce the time series data that are already available.
24. use the current value to estimate how well the model generates previous values correctly.
25. predict the future values and wait for a pre-defined time period to examine how accurate the predictions were.
26. check to see if the forecast error is negative.

Answer: A

Difficulty: Moderate

LO: 5.2, Page 213

Bloom’s: Comprehension

BUSPROG: Analytic

DISC: Time Series Data

Feedback: Measures of forecast accuracy are used to determine how well a particular forecasting method is able to reproduce the time series data that are already available. By selecting the method that is most accurate for the data already known, we hope to increase the likelihood that we will obtain more accurate forecasts for future time periods.

 

23. Forecast error:
24. takes a positive value when the forecast is too high.
25. cannot be negative.
26. cannot take a value of zero.
27. is associated with measuring forecast accuracy.

Answer: D

Difficulty: Moderate

LO: 5.1, 5.2, Page 204 and 213

Bloom’s: Comprehension

BUSPROG: Analytic

DISC: Time Series Data

Feedback: The forecast in time series analysis is based solely on past values of the variable and/or on past forecast errors.

 

24. Which of the following measures of forecast accuracy is susceptible to the problem of positive and negative forecast errors offsetting one another?
25. Mean absolute error
26. Mean forecast error
27. Mean squared error
28. Mean absolute percentage error

Answer: B

Difficulty: Moderate

LO: 5.2, Page 214

Bloom’s: Comprehension

BUSPROG: Analytic

DISC: Time Series Data

Feedback: Because positive and negative forecast errors tend to offset one another, the mean forecast error is not a very useful measure of forecast accuracy.

 

25. The moving averages method refers to a forecasting method that:
26. moves up the average of every subsequent forecast by one.
27. uses regression relationship based on past time series values to predict the future time series values.
28. relates a time series to other variables that are believed to explain or cause its behavior.
29. uses the average of the most recent data values in the time series as the forecast for the next period.

Answer: D

Difficulty: Easy

LO: 5.3, Page 217

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: The moving averages method refers to a forecasting method that uses the average of the most recent data values in the time series as the forecast for the next period.

 

26. The moving averages and exponential smoothing methods are appropriate for a time series exhibiting _____.
27. horizontal pattern
28. cyclical pattern
29. trends
30. seasonal effects

Answer: A

Difficulty: Easy

LO: 5.3, Page 217

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: The moving averages and exponential smoothing methods are appropriate for a time series exhibiting horizontal pattern.

 

27. Which of the following statements is the objective of the moving averages and exponential smoothing methods?
28. To characterize the variable fluctuations by a smooth curve
29. To smooth out random fluctuations in the time series
30. To characterize the variable fluctuations by an exponential equation
31. To transform a nonstationary time series into a stationary series

Answer: B

Difficulty: Moderate

LO: 5.3, Page 217

Bloom’s: Comprehension

BUSPROG: Analytic

DISC: Time Series Data

Feedback: The objective of the moving averages and exponential smoothing methods is to smooth out random fluctuations in the time series; they are also referred to as smoothing methods.

 

28. In the moving averages method, the order k determines the:
29. error tolerance
30. compensation for forecasting error
31. number of time series values under consideration
32. number of samples in each unit time period

Answer: C

Difficulty: Moderate

LO: 5.3, Page 218

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: To use moving averages to forecast a time series, we must first select the order k, or the number of time series values to be included in the moving average. If only the most recent values of the time series are considered relevant, a small value of k is preferred. If a greater number of past values are considered relevant, then we generally opt for a larger value of k.

 

29. Using a large value for order k in the moving averages method is effective in:
30. tracking changes in a time series more quickly.
31. smoothing out random fluctuations.
32. providing a more accurate forecast variable value.
33. eliminating the effect of seasonal variations in the time series.

Answer: B

Difficulty: Moderate

LO: 5.3, Page 218

Bloom’s: Comprehension

BUSPROG: Analytic

DISC: Time Series Data

Feedback: A moving average will adapt to the new level of the series and continue to provide good forecasts in k periods. Thus a smaller value of k will track shifts in a time series more quickly. On the other hand, larger values of k will be more effective in smoothing out random fluctuations.

 

30. _____ uses a weighted average of past time series values as the forecast.
31. The qualitative method
32. Exponential smoothing
33. Correlation analysis
34. The causal model

Answer: B

Difficulty: Easy

LO: 5.3, Page 221

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: Exponential smoothing uses a weighted average of past time series values as the forecast.

 

31. With reference to exponential forecasting models, a parameter that provides the weight given to the most recent time series value in the calculation of the forecast value is known as the _____.
32. moving average
33. regression coefficient
34. smoothing constant
35. mean forecast error

Answer: C

Difficulty: Easy

LO: 5.3, Page 221

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: With reference to exponential forecasting models, a parameter that provides the weight given to the most recent time series value in the calculation of the forecast value is known as the smoothing constant.

 

32. The exponential smoothing forecast for period t + 1 is a weighted average of the:
33. forecast value in period t with weight α and the actual value for period t with weight 1 – α.
34. actual value in period t + 1 with weight α and the forecast for period t with weight 1 – α.
35. forecast value in period t – 1 with weight α and the forecast for period t with weight 1 – α.
36. actual value in period t with weight α and the forecast for period t with weight 1 – α.

Answer: D

Difficulty: Moderate

LO: 5.3, Page 221

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: The exponential smoothing forecast for period t + 1 is a weighted average of the actual value in period t and the forecast for period t. The weight given to the actual value in period t is the smoothing constant α, and the weight given to the forecast in period t is 1 – α.

 

33. Which of the following is true of the exponential smoothing coefficient?
34. It is a randomly generated value between –1 and +1.
35. It is small for a time series that has relatively little random variability.
36. It is chosen as the value that minimizes a selected measure of forecast accuracy such as the mean squared error.
37. It is computed in relation with the order value, k, for the moving averages.

Answer: C

Difficulty: Easy

LO: 5.3, Page 225

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: The criterion we will use to determine a desirable value for the smoothing constant α is the same as that proposed for determining the order or number of periods of data to include in the moving averages calculation; that is, we choose the value of α that minimizes the MSE.

 

34. The process of _____ might be used to determine the value of smoothing constant that minimizes the mean squared error.
35. quantization
36. nonlinear optimization
37. clustering
38. curve fitting

Answer: B

Difficulty: Easy

LO: 5.3, Page 225

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: Trial and error is often used to determine whether a different smoothing constant α can provide more accurate forecasts, but we can avoid trial and error and determine the value of α that minimizes MSE through the use of nonlinear optimization.

 

35. Autoregressive models:
36. use the average of the most recent data values in the time series as the forecast for the next period.
37. are used to smooth out random fluctuations in time series.
38. relate a time series to other variables that are believed to explain or cause its behavior.
39. occur whenever all the independent variables are previous values of the same time series.

Answer: D

Difficulty: Easy

LO: 5.4, Page 228

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: Autoregressive models occur whenever all the independent variables are previous values of the same time series.

 

36. A time series with a seasonal pattern can be modeled by treating the season as a _____.
37. predictor variable
38. dependent variable
39. dummy variable
40. categorical variable

Answer: C

Difficulty: Easy

LO: 5.4, Page 228

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: A time series with a seasonal pattern can be modeled by treating the season as a dummy variable.

 

37. Causal models:
38. provide evidence of a causal relationship between an independent variable and the variable to be forecast.
39. use the average of the most recent data values in the time series as the forecast for the next period.
40. occur whenever all the independent variables are previous values of the same time series.
41. relate a time series to other variables that are believed to explain or cause its behavior.

Answer: D

Difficulty: Easy

LO: 5.4, Page 232

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: Causal models relate a time series to other variables that are believed to explain or cause its behavior.

 

38. A causal model provides evidence of _____ between an independent variable and the variable to be forecast.
39. a causal relationship
40. association
41. no relationship
42. a seasonal relationship

Answer: B

Difficulty: Easy

LO: 5.4, Page 232

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: The causal forecasting model provides evidence only of association between an independent variable and the variable to be forecast. The model does not provide evidence of a causal relationship between an independent variable and the variable to be forecast, and the conclusion that a causal relationship exists must be based on practical experience.

 

39. The value of an independent variable from the prior period is referred to as a _____.
40. lagged variable
41. dummy variable
42. predictor variable
43. categorical variable

Answer: A

Difficulty: Easy

LO: 5.4, Page 235

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: The value of an independent variable from the prior period is referred to as a lagged variable.

 

40. For causal modeling, _____ are used to detect linear or nonlinear relationships between the independent and dependent variables.
41. descriptive statistics on the data
42. scatter charts
43. contingency tables
44. pie charts

Answer: B

Difficulty: Easy

LO: 5.5, Page 236

Bloom’s: Knowledge

BUSPROG: Analytic

DISC: Time Series Data

Feedback: For causal modeling, scatter charts can indicate whether strong linear or nonlinear relationships exist between the independent and dependent variables.

 

 

Problems

1. Consider the following time series data:

Year	Value
1	234
2	287
3	255
4	310
5	298
6	250
7	456
8	412
9	525
10	436

 

Using the naïve method (most recent value) as the forecast for the next year, compute the following measures of forecast accuracy:

1. Mean absolute error
2. Mean squared error
3. Mean absolute percentage error
4. What is the forecast for year 11?

 

Answer:

The following table shows the calculations for parts (a), (b), and (c).

Year	Value	Forecast	Forecast Error	Absolute Value of Forecast Error	Squared Forecast Error	Percentage Error	Absolute Value of Percentage Error
1	234
2	287	234	53	53	2809	18.4669	18.4669
3	255	287	-32	32	1024	-12.5490	12.5490
4	310	255	55	55	3025	17.7419	17.7419
5	298	310	-12	12	144	-4.0268	4.0268
6	250	298	-48	48	2304	-19.2000	19.2000
7	456	250	206	206	42436	45.1754	45.1754
8	412	456	-44	44	1936	-10.6796	10.6796
9	525	412	113	113	12769	21.5238	21.5238
10	436	525	-89	89	7921	-20.4128	20.4128
Total				652	74368		169.7764

 

72. MAE = 652/9 = 72.44
73. MSE = 74368/9 = 8263.11
74. MAPE = 169.7764/9 = 18.86%
75. The forecast for year 11 is = 436.

Difficulty: Easy

LO: 5.2, Pages 212-217

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

 

2. Consider the following time series data:

Year	Value
1	234
2	287
3	255
4	310
5	298
6	250
7	456
8	412
9	525
10	436

 

Using the average of all the historical data as a forecast for the next year, compute the following measures of forecast accuracy:

1. Mean absolute error
2. Mean squared error
3. Mean absolute percentage error
4. What is the forecast for year 11?

Answer: The following table shows the calculations for parts (a), (b), and (c).

Year	Value	Forecast	Forecast Error	Absolute Value of Forecast Error	Squared Forecast Error	Percentage Error	Absolute Value of Percentage Error
1	234
2	287	234.00	53.00	53.00	2809.00	18.4669	18.4669
3	255	260.50	-5.50	5.50	30.25	-2.1569	2.1569
4	310	258.67	51.33	51.33	2635.11	16.5591	16.5591
5	298	271.50	26.50	26.50	702.25	8.8926	8.8926
6	250	276.80	-26.80	26.80	718.24	-10.7200	10.7200
7	456	272.33	183.67	183.67	33733.44	40.2778	40.2778
8	412	298.57	113.43	113.43	12866.04	27.5312	27.5312
9	525	312.75	212.25	212.25	45050.06	40.4286	40.4286
10	436	336.33	99.67	99.67	9933.44	22.8593	22.8593
Total				772.15	108477.84		187.8924

 

772. MAE = 772.15/9= 85.79
773. MSE = 108477.84/9 = 12053.09
774. MAPE = 187.8924/9 = 20.88%
775. The forecast for year 11 is = (y₁ + y₂ + y₃ + y₄ + y₅ + y₆ + y₇ + y₈ + y₉ + y₁₀) / 10 = (234 + 287 + 255 + 310 + 298 + 250 + 456 + 412 + 525 + 436) / 10 = 346.3.

Difficulty: Easy

LO: 5.2, Pages 212-217

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

 

3. The monthly sales revenue (in hundreds of dollars) of a company for one year is listed below.

Month	Sales ($100s)
January	12,354
February	13,657
March	14,536
April	13,478
May	16,590
June	19,790
July	17,987
August	18,657
September	19,765
October	18,678
November	20,678
December	23,675

 

1. Compute MSE using the most recent value as the forecast for the next period. What is the forecast for the next month?
2. Compute MSE using the average of all the data available as the forecast for the next period. What is the forecast for the next month?
3. Which method appears to provide the better forecast?

 

Answer:

Month	Sales ($100s)	Forecast	Forecast Error	Squared Forecast Error
January	12,354
February	13,657	12,354	1303	1697809
March	14,536	13,657	879	772641
April	13,478	14,536	-1058	1119364
May	16,590	13,478	3112	9684544
June	19,790	16,590	3200	10240000
July	17,987	19,790	-1803	3250809
August	18,657	17,987	670	448900
September	19,765	18,657	1108	1227664
October	18,678	19,765	-1087	1181569
November	20,678	18,678	2000	4000000
December	23,675	20,678	2997	8982009
Total				42605309

 

MSE = 42605309/11 = 3873209.91 ≈ 3873210

The forecast (in $100s) for the next month is  = y_dec = 23,675.

Month	Sales ($100s)	Forecast	Forecast Error	Squared Forecast Error
January	12,354
February	13,657	12,354.00	1303.00	1697809.00
March	14,536	13,005.50	1530.50	2342430.25
April	13,478	13,515.67	-37.67	1418.78
May	16,590	13,506.25	3083.75	9509514.06
June	19,790	14,123.00	5667.00	32114889.00
July	17,987	15,067.50	2919.50	8523480.25
August	18,657	15,484.57	3172.43	10064303.04
September	19,765	15,881.13	3883.88	15084485.02
October	18,678	16,312.67	2365.33	5594801.78
November	20,678	16,549.20	4128.80	17046989.44
December	23,675	16,924.55	6750.45	45568636.57
Total				147548757.1847

 

MSE = 147548757.1847/11 = 13413523.38

Forecast (in $100s) for next month is  = (y₁ + y₂ +… + y₁₁ + y₁₂) / 12 = (12,354 + 13,657 + … + 20,678 + 23,675) / 12 = 17,487.08.

 

1. The most recent value method in part (a) is better because MSE is smaller.

 

Difficulty: Moderate

LO: 5.2, Pages 212-217

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

 

4. Consider the following time series data:

Year	Value
1	234
2	287
3	255
4	310
5	298
6	250
7	302
8	267
9	225
10	336

 

1. Construct a time series plot. What type of pattern exists in the data?
2. Develop a three-year moving average for this time series. Compute MSE and a forecast for the year 11.

 

Answer:

 

The time series data appear to follow a horizontal pattern.

Year	Value	Forecast	Forecast Error	Squared Forecast Error
1	234
2	287
3	255
4	310	258.67	51.33	2635.11
5	298	284.00	14.00	196.00
6	250	287.67	-37.67	1418.78
7	302	286.00	16.00	256.00
8	267	283.33	-16.33	266.78
9	225	273.00	-48.00	2304.00
10	336	264.67	71.33	5088.44
Total				12165.11

 

MSE = 12165.11/7 = 1737.87

The forecast for year 11 is  = (y₈ + y₉ + y₁₀) / 3 = (267 + 225 + 336) / 3 = 276.00.

Difficulty: Easy

LO: 5.3, Pages 217-221

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

 

5. Consider the following time series data:

Year	Value
1	234
2	287
3	255
4	310
5	298
6	250
7	302
8	267
9	225
10	336

 

1. Use α = 0.2 to compute the exponential smoothing values for the time series. Compute MSE and a forecast for year 11.
2. Use trial and error to find a value of the exponential smoothing coefficient α that results in a smaller MSE than what you calculated for α = 0.2.
3. Compute the forecast for year 11 using the smoothing coefficient α selected using trial error.

 

Answer:

1. Smoothing constant α = 0.2

Year	Value	Forecast	Forecast Error	Squared Forecast Error
1	234
2	287	234.00	53.00	2809.00
3	255	244.60	10.40	108.16
4	310	246.68	63.32	4009.42
5	298	259.34	38.66	1494.29
6	250	267.08	-17.08	291.56
7	302	263.66	38.34	1469.94
8	267	271.33	-4.33	18.73
9	225	270.46	-45.46	2066.84
10	336	261.37	74.63	5569.64
Total				17837.58

 

MSE = 17837.58/9 = 1981.95

The forecast for year 11 is  = αy₁₀ + (1- α)  = (0.2)(336) + (1 – 0.2)(261.37) = 276.30.

1. Several values of α will yield an MSE smaller than the MSE associated with α = 0.2. The table below shows the resulting MSE from several different α.

 

α	MSE
0.1	2285.29
0.2	1981.95
0.3	1928.71
0.4	1978.37
0.5	2081.20
0.6	2219.57
0.7	2387.01
0.8	2580.70

 

The value of α that yields the minimum MSE is α = 0.29, which yields an MSE of 1928.08.

α	=   0.29
Year	Value	Forecast	Forecast Error	Squared Forecast Error
1	234
2	287	234.00	53.00	2809.00
3	255	249.37	5.63	31.70
4	310	251.00	59.00	3480.68
5	298	268.11	29.89	893.30
6	250	276.78	-26.78	717.14
7	302	269.01	32.99	1088.11
8	267	278.58	-11.58	134.09
9	225	275.22	-50.22	2522.20
10	336	260.66	75.34	5676.53
Total				17352.74

 

MSE = 17352.74/9 = 1928.08

The forecast for year 11 is  = αy₁₀ + (1- α)  = (0.29)(336) + (1 – 0.29)260.66 = 282.51.

Difficulty: Challenging

LO: 5.3, Pages 221-225

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

6. The monthly market shares of General Electric Company for 12 consecutive months follow.

21.51, 22.43, 23.02, 23.03, 22.1, 23.37, 23.21, 24.6, 23.31, 23.94, 26.05, 26.65

1. Construct a time series plot. What type of pattern exists in the data?
2. Develop three-month and four-month moving averages for this time series. Does the three-month or the four-month moving average provide the better forecasts based on MSE? Explain.
3. What is the moving average forecast for the next month?

Answer:

 

The data appear to follow a horizontal pattern.

Month	Market shares	3-Month Moving Average Forecast	Error	(Error)2	4-Month Moving Average Forecast	Error	(Error)2
1	21.51
2	22.43
3	23.02
4	23.03	22.32	0.71	0.50
5	22.1	22.83	-0.73	0.53	22.50	-0.40	0.16
6	23.37	22.72	0.65	0.43	22.65	0.72	0.53
7	23.21	22.83	0.38	0.14	22.88	0.33	0.11
8	24.6	22.89	1.71	2.91	22.93	1.67	2.80
9	23.31	23.73	-0.42	0.17	23.32	-0.01	0.00
10	23.94	23.71	0.23	0.05	23.62	0.32	0.10
11	26.05	23.95	2.10	4.41	23.77	2.29	5.22
12	26.65	24.43	2.22	4.91	24.48	2.18	4.73
Total				14.07	Total		13.64

 

MSE (3-Month) = 14.07/ 9 = 1.56

MSE (4-Month) = 13.64/ 8 = 1.71

The 3-Month moving average provides the better forecasts because the MSE for the 3-Month moving average is smaller.

23. Using the 3-Month moving average, the forecast for the next month is = (y₁₀ + y₁₁ + y₁₂) / 3 = (94 + 26.05 + 26.65) / 3 = 25.55.

Difficulty: Moderate

LO: 5.3, Pages 217-221

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

7. The following time series shows the sales of a particular commodity over the past 15 weeks.

Week	Sales
1	1123
2	1157
3	1138
4	1120
5	1130
6	1132
7	1188
8	1151
9	1129
10	1118
11	1125
12	1147
13	1162
14	1190
15	1137

 

1. Construct a time series plot. What type of pattern exists in the data?
2. Use α = 0.3 to develop the exponential smoothing values for the time series and compute the forecast of demand for the next week.
3. Use trial and error to find a value of the exponential smoothing coefficient α that results in a relatively small MSE.

 

Answer:

The time series plot shows a horizontal pattern.

α      =	0.3
Week	Sales	Forecast	Forecast Error	Squared Forecast Error
1	1123
2	1157	1123.00	34.00	1156.00
3	1138	1133.20	4.80	23.04
4	1120	1134.64	-14.64	214.33
5	1130	1130.25	-0.25	0.06
6	1132	1130.17	1.83	3.34
7	1188	1130.72	57.28	3280.82
8	1151	1147.91	3.09	9.58
9	1129	1148.83	-19.83	393.37
10	1118	1142.88	-24.88	619.19
11	1125	1135.42	-10.42	108.54
12	1147	1132.29	14.71	216.30
13	1162	1136.71	25.29	639.84
14	1190	1144.29	45.71	2089.08
15	1137	1158.01	-21.01	441.23
Total				9194.72

 

MSE = 9194.72/14 = 656.77

The forecast for week 16 is  = αy₁₅ + (1- α) = 0.3(1137) + 0.7(1158.01) = 1151.70

1. MSE values for exponential smoothing forecasts with several different values of α appear below.

α	MSE
0.05	767.11
0.1	686.51
0.2	646.06
0.21	645.92
0.3	656.77
0.4	679.49
0.5	703.75

 

The value of α that yields the smallest possible MSE is α = 0.21, which yields an MSE of 645.92.

α	=    0.21
Week	Sales	Forecast	Forecast Error	Squared Forecast Error
1	1123
2	1157	1123.00	34.00	1156.00
3	1138	1130.14	7.86	61.78
4	1120	1131.79	-11.79	139.02
5	1130	1129.31	0.69	0.47
6	1132	1129.46	2.54	6.46
7	1188	1129.99	58.01	3364.90
8	1151	1142.17	8.83	77.90
9	1129	1144.03	-15.03	225.82
10	1118	1140.87	-22.87	523.11
11	1125	1136.07	-11.07	122.51
12	1147	1133.74	13.26	175.72
13	1162	1136.53	25.47	648.83
14	1190	1141.88	48.12	2315.82
15	1137	1151.98	-14.98	224.49
Total				9042.83

 

MSE = 9042.83/14 = 645.92

Difficulty: Challenging

LO: 5.3, Pages 221-225

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

8. The following times series shows the demand for a particular product over the past 10 months.

Month	Demand
1	324
2	311
3	303
4	314
5	323
6	313
7	302
8	315
9	312
10	326

 

1. Construct a time series plot. What type of pattern exists in the data?
2. Develop a three-month moving average for this time series. Compute MSE and a forecast for month 11.

Answer:

 

The data appear to follow a horizontal pattern.

Month	Demand	Forecast	Forecast Error	Squared Forecast Error
1	324
2	311
3	303
4	314	312.67	1.33	1.78
5	323	309.33	13.67	186.78
6	313	313.33	-0.33	0.11
7	302	316.67	-14.67	215.11
8	315	312.67	2.33	5.44
9	312	310.00	2.00	4.00
10	326	309.67	16.33	266.78
Total				680.00

 

MSE = 680.00/7 = 97.14

The forecast for month 11 is  = (y₈ + y₉ + y₁₀) / 3 = (315 + 312 + 326) / 3 = 317.67.

Difficulty: Easy

LO: 5.3, Pages 217-221

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

 

9. The following times series shows the demand for a particular product over the past 10 months.

Month	Value
1	324
2	311
3	303
4	314
5	323
6	313
7	302
8	315
9	312
10	326

 

1. Use α = 0.2 to compute the exponential smoothing values for the time series. Compute MSE and a forecast for month 11.
2. Compare the three-month moving average forecast with the exponential smoothing forecast using α = 0.2. Which appears to provide the better forecast based on MSE?

Answer:

1. Smoothing constant α = 0.2

α	0.2
Month	Value	Forecast	Forecast Error	Squared Forecast Error
1	324
2	311	324.00	-13.00	169.00
3	303	321.40	-18.40	338.56
4	314	317.72	-3.72	13.84
5	323	316.98	6.02	36.29
6	313	318.18	-5.18	26.84
7	302	317.14	-15.14	229.36
8	315	314.12	0.88	0.78
9	312	314.29	-2.29	5.26
10	326	313.83	12.17	148.01
				Total = 967.94

 

MSE = 967.94/9 = 107.55

The forecast for month 11 is  = αy₁₀ + (1- α)  = 0.2(326) + (1 – 0.2)313.83 = 316.27.

1. Comparing the MSE for three-month moving average (calculated in the previous problem) and the MSE for exponential smoothing, the three-month moving average provides a better forecast as it has a smaller MSE.

Difficulty: Moderate

LO: 5.3, Pages 217-225

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

10. The following data shows the quarterly profit (in thousands of dollars) made by a particular company in the past 3 years.

Year	Quarter	Profit ($1000s)
1	1	45
1	2	51
1	3	72
1	4	50
2	1	49
2	2	45
2	3	79
2	4	54
3	1	42
3	2	58
3	3	70
3	4	56

 

1. Construct a time series plot. What type of pattern exists in the data?
2. Develop a three-period moving average for this time series. Compute MSE and a forecast of profit (in $1000s) for the next quarter.

Answer:

Rewrite the data as below:

t	Profit ($1000s)
1	45
2	51
3	72
4	50
5	49
6	45
7	79
8	54
9	42
10	58
11	70
12	56

 

 

 

The data appear to follow a horizontal pattern.

 

Year	Quarter	t	Profit ($1000s)	Forecast	Forecast Error	Squared Forecast Error
1	1	1	45
1	2	2	51
1	3	3	72
1	4	4	50	56.00	-6.00	36.00
2	1	5	49	57.67	-8.67	75.11
2	2	6	45	57.00	-12.00	144.00
2	3	7	79	48.00	31.00	961.00
2	4	8	54	57.67	-3.67	13.44
3	1	9	42	59.33	-17.33	300.44
3	2	10	58	58.33	-0.33	0.11
3	3	11	70	51.33	18.67	348.44
3	4	12	56	56.67	-0.67	0.44
						Total = 1879.00

 

MSE = 1879/9 = 208.78

The forecast of profit (in $1000s) for the next quarter is  = (y₁₀ + y₁₁ + y₁₂) / 3 = (58 + 70 + 56) / 3 = 61.33.

 

Difficulty: Easy

LO: 5.3, Pages 217-221

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

 

11. The following data shows the quarterly profit (in thousands of dollars) made by a particular company in the past 3 years.

Year	Quarter	Profit ($1000s)
1	1	45
1	2	51
1	3	72
1	4	50
2	1	49
2	2	45
2	3	79
2	4	54
3	1	42
3	2	58
3	3	70
3	4	56

 

1. Use α = 0.3 to compute the exponential smoothing values for the time series. Compute MSE and the forecast of profit (in $1000s) for the next quarter.
2. Compare the three-period moving average forecast with the exponential smoothing forecast using α = 0.3. Which appears to provide the better forecast based on MSE?

Answer:

1. Smoothing constant α = 0.3

Year	Quarter	t	Profit ($1000s)	Forecast	Forecast Error	Squared Forecast Error
1	1	1	45
1	2	2	51	45.000	6.000	36.000
1	3	3	72	46.800	25.200	635.040
1	4	4	50	54.360	-4.360	19.010
2	1	5	49	53.052	-4.052	16.419
2	2	6	45	51.836	-6.836	46.736
2	3	7	79	49.785	29.215	853.488
2	4	8	54	58.550	-4.550	20.701
3	1	9	42	57.185	-15.185	230.581
3	2	10	58	52.629	5.371	28.843
3	3	11	70	54.241	15.759	248.359
3	4	12	56	58.968	-2.968	8.811
					Total	2143.988

 

MSE = 2143.988/11 = 194.91

The forecast of profit (in $1000s) for quarter 13 is  = αy₁₂ + (1- α)  = 0.3(56) + (1 – 0.3)58.968 = 58.08.

1. Compared to the three-period moving average forecast (calculated in the previous problem), exponential smoothing forecast provides a better forecast because it has a smaller MSE.

 

Difficulty: Moderate

LO: 5.3, Pages 217-225

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

12. The below time series gives the indices of Industrial Production in U.S for 10 consecutive years.

Year	IP
1	79.62
2	86.54
3	88.14
4	89.23
5	93.45
6	97.4
7	99.34
8	96.98
9	100.22
10	103.56

 

1. Construct a time series plot. What type of pattern exists in the data?
2. Use simple linear regression analysis to find the parameters for the line that minimizes MSE for this time series.
3. What is the forecast for t = 11?

 

Answer:

The time series plot shows a linear trend.

 

1. Excel output:

 

 

From the above output, the regression estimates for the y-intercept and slope that minimize MSE for this time series are b₀ = 80.458 and b₁ = 2.36, which result in the following forecasts, errors, and MSE:

Year	IP	Forecast	Forecast Error	Squared Forecast Error
1	79.62	82.81981818	-3.200	10.239
2	86.54	85.18163636	1.358	1.845
3	88.14	87.54345455	0.597	0.356
4	89.23	89.90527273	-0.675	0.456
5	93.45	92.26709091	1.183	1.399
6	97.4	94.62890909	2.771	7.679
7	99.34	96.99072727	2.349	5.519
8	96.98	99.35254545	-2.373	5.629
9	100.22	101.71436364	-1.494	2.233
10	103.56	104.07618182	-0.516	0.266
Total				35.622

 

MSE = 35.622/10 = 3.56.

80. = b₀ + b₁t = 458 + 2.36(11) = 106.438.

 

Difficulty: Moderate

LO: 5.4, Pages 226-228

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

13. The monthly sales (in hundreds of dollars) of a company are listed below.

Month	Sales ($100s)
January	12,354
February	13,657
March	14,536
April	13,478
May	16,590
June	19,790
July	17,987
August	18,657
September	19,765
October	18,678
November	20,678
December	23,675

 

1. Construct a time series plot. What type of pattern exists in the data?
2. Use simple linear regression analysis to find the parameters for the line that minimizes MSE for this time series.
3. What is the sales forecast (in hundreds of dollars) for next month?

 

Answer:

The time series plot shows a linear trend.

1. Excel output:

 

From the above output, the regression estimates for the y-intercept and slope that minimize MSE for this time series are b₀ = 11747.38 and b₁ = 883.03, which result in the following forecasts, errors, and MSE:

Month	t	Sales ($100s)	Forecast	Forecast Error	Squared Forecast Error
January	1	12,354	12630.41026	-276.410	76402.630
February	2	13,657	13513.44172	143.558	20608.978
March	3	14536	14396.47319	139.527	19467.730
April	4	13478	15279.50466	-1801.505	3245419.047
May	5	16,590	16162.53613	427.464	182725.360
June	6	19,790	17045.56760	2744.432	7531909.203
July	7	17,987	17928.59907	58.401	3410.669
August	8	18,657	18811.63054	-154.631	23910.603
September	9	19,765	19694.66200	70.338	4947.434
October	10	18,678	20577.69347	-1899.693	3608835.292
November	11	20,678	21460.72494	-782.725	612658.334
December	12	23,675	22343.75641	1331.244	1772209.495
				Total =	17102504.775

 

MSE = 17102504.775/12 = 1425208.73.

11747. The forecast (in $100s) is = b₀ + b₁t = 38 + 883.03(13) = $23,226.79.

 

Difficulty: Moderate

LO: 5.4, Pages 226-228

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

14. Consider the following time series:

t	yt
1	1234
2	1201
3	1103
4	987
5	945
6	891
7	817
8	734

 

1. Construct a time series plot. What type of pattern exists in the data?
2. Use simple linear regression analysis to find the parameters for the line that minimizes MSE for this time series.
3. What is the forecast for t = 9?

 

Answer:

The data appear to show a linear trend with a decreasing pattern.

1. Excel output:

The regression estimates for the y-intercept and slope that minimize MSE for the given time series are b₀ = 1315.68 and b₁ = -72.60, which result in the following forecasts, errors, and MSE:

t	yt	Forecast	Forecast Error	Squared Forecast Error
1	1234	1243.083333	-9.083	82.507
2	1201	1170.488095	30.512	930.976
3	1103	1097.892857	5.107	26.083
4	987	1025.297619	-38.298	1466.708
5	945	952.702381	-7.702	59.327
6	891	880.1071429	10.893	118.654
7	817	807.5119048	9.488	90.024
8	734	734.9166667	-0.917	0.840
				Total = 2775.119

MSE = 2775.119/8 = 346.89.

1315. = b₀ + b₁t = 68 – 72.60(9) = 662.32

Difficulty: Moderate

LO: 5.4, Pages 226-228

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

15. The yearly sales (in millions of dollars) of an automobile manufacturing company during the period 2000-2011 are given below:

Year	Sales ($millions) y
2000	470
2001	485
2002	499
2003	515
2004	532
2005	532
2006	556
2007	576
2008	583
2009	587
2010	601
2011	605

 

1. Construct a time series plot. What type of pattern exists in the data?
2. Use simple linear regression analysis to find the parameters for the line that minimizes MSE for this time series.
3. What is the sales forecast (in millions of dollars) for the year 2012?

 

Answer:

a.

 

 

The time series plot shows a linear trend.

 

1. Excel output:

 

The regression estimates for the y-intercept and slope that minimize MSE for the given time series are b₀ = -24986.47 and b₁ = 12.73, which result in the following forecasts, errors, and MSE:

Year	Sales ($millions) y	Forecast	Forecast Error	Squared Forecast Error
2000	470	475.0641026	-5.064	25.645
2001	485	487.7948718	-2.795	7.811
2002	499	500.525641	-1.526	2.328
2003	515	513.2564103	1.744	3.040
2004	532	525.9871795	6.013	36.154
2005	532	538.7179487	-6.718	45.131
2006	556	551.4487179	4.551	20.714
2007	576	564.1794872	11.821	139.725
2008	583	576.9102564	6.090	37.085
2009	587	589.6410256	-2.641	6.975
2010	601	602.3717949	-1.372	1.882
2011	605	615.1025641	-10.103	102.062
				Total = 428.551

 

MSE = 428.551/12 = 35.713.

1. The sales forecast (in millions of dollars) for the year 2012:

= b₀ + b₁t = -24986.47 + 12.73(2012) = 627.83.

Difficulty: Moderate

LO: 5.4, Pages 226-228

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

16. Consider the following time series data:

t	yt
1	0.345
2	0.366
3	0.398
4	0.356
5	0.456
6	0.478
7	0.543
8	0.596
9	0.634
10	0.698

 

1. Construct a time series plot. What type of pattern exists in the data?
2. Use simple linear regression analysis to find the parameters for the line that minimizes MSE for this time series.
3. What is the forecast for t = 11?

 

Answer:

This time series plot shows an upward linear trend.

1. Excel output:

The regression estimates that minimize MSE for this time series are b₀ = 0.2661 and b₁ = 0.0402, which result in the following forecasts, errors, and MSE:

t	yt	Forecast	Forecast Error	Squared Forecast Error
1	0.345	0.306290909	0.039	0.001
2	0.366	0.346448485	0.020	0.000
3	0.398	0.386606061	0.011	0.000
4	0.356	0.426763636	-0.071	0.005
5	0.456	0.466921212	-0.011	0.000
6	0.478	0.507078788	-0.029	0.001
7	0.543	0.547236364	-0.004	0.000
8	0.596	0.587393939	0.009	0.000
9	0.634	0.627551515	0.006	0.000
10	0.698	0.667709091	0.030	0.001
				Total = 0.009

 

MSE = 0.009/10 = 0.0009.

1. The forecast for t = 11 is = b₀ + b₁t = 0.2661 + 0.0402(11) = 0.708.

Difficulty: Moderate

LO: 5.4, Pages 226-228

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

17. Consider the following quarterly time series:

 

Quarter	Year 1	Year 2	Year 3
1	923	1112	1243
2	1056	1156	1301
3	1124	1124	1254
4	992	1078	1198

 

1. Construct a time series plot. What type of pattern exists in the data?
2. Use a multiple regression model with dummy variables as follows to develop an equation to account for seasonal effects in the data. Qtr1 = 1 if quarter 1, 0 otherwise; Qtr2 = 1 if quarter 2, 0 otherwise; Qtr3 = 1 if quarter 3, 0 otherwise.
3. Compute the quarterly forecasts for next year based on the model developed in part b.

 

Answer:

 

The above time series plot reveals a horizontal pattern with a seasonal pattern in the data. For instance, in each year the value increases from quarter 1 to quarter 2 and drops from quarter 3 to quarter 4.

 

1. Rewrite the data using the dummy variables in the following format:

Year	Quarter	Qtr1	Qtr2	Qtr3	Time Series Value, yt
1	1	1	0	0	923
1	2	0	1	0	1056
1	3	0	0	1	1124
1	4	0	0	0	992
2	1	1	0	0	1112
2	2	0	1	0	1156
2	3	0	0	1	1124
2	4	0	0	0	1078
3	1	1	0	0	1243
3	2	0	1	0	1301
3	3	0	0	1	1254
3	4	0	0	0	1198

 

We can use Excel’s Regression tool to find the regression model that accounts for the seasonal effects in the data.

 

Excel output:

 

 

From the above output, the regression model that minimizes MSE for the given time series is:

= 1089.33 + 3.33Qtr1 + 81.67Qtr2 + 78Qtr3

1. Based on the model in part (b), the quarterly forecasts for next year are as follows:

Quarter 1 forecast = 1089.33 + 3.33(1) + 81.67(0) + 78(0) = 1092.67

Quarter 2 forecast = 1089.33 + 3.33(0) + 81.67(1) + 78(0) = 1171.00

Quarter 3 forecast = 1089.33 + 3.33(0) + 81.67(0) + 78(1) = 1167.33

Quarter 4 forecast = 1089.33 + 3.33(0) + 81.67(0) + 78(0) = 1089.33

Difficulty: Challenging

LO: 5.4, Pages 228-230

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

18. Consider the following time series:

Quarter	Year 1	Year 2	Year 3
1	923	1112	1243
2	1056	1156	1301
3	1124	1124	1254
4	992	1078	1198

 

3. Use a multiple regression model to develop an equation to account for linear trend and seasonal effects in the data. To capture seasonal effects, use the dummy variables Qtr1 = 1 if quarter 1, 0 otherwise; Qtr2 = 1 if quarter 2, 0 otherwise; Qtr3 = 1 if quarter 3, 0 otherwise; and create a variable t such that t = 1 for quarter 1 in year 1, t = 2 for quarter 2 in year 1, … ,t = 12 for quarter 4 in year 3.
4. Compute the quarterly forecasts for next year based on the model developed in part a.

Answer:

1. Rewrite the data using dummy variables and variable t in the following format:

Year	Quarter	Qtr1	Qtr2	Qtr3	t	yt
1	1	1	0	0	1	923
1	2	0	1	0	2	1056
1	3	0	0	1	3	1124
1	4	0	0	0	4	992
2	1	1	0	0	5	1112
2	2	0	1	0	6	1156
2	3	0	0	1	7	1124
2	4	0	0	0	8	1078
3	1	1	0	0	9	1243
3	2	0	1	0	10	1301
3	3	0	0	1	11	1254
3	4	0	0	0	12	1198

 

Use Excel’s Regression tool to find the regression model that accounts for both the trend and seasonal effects in the data.

 

Excel output:

 

The regression model that minimizes MSE for the given time series is:

= 864.08 + 87.80Qtr1 + 137.98Qtr2 + 106.16Qtr3 + 28.16t

 

1. Based on the model in part (a), the quarterly forecasts for next year are as follows:

 

Quarter 1 forecast = 864.08 + 87.80(1) + 137.98(0) + 106.16(0) + 28.16(13) = 1317.92

Quarter 2 forecast = 864.08 + 87.80(0) + 137.98(1) + 106.16(0) + 28.16(14) = 1396.25

Quarter 3 forecast = 864.08 + 87.80(0) + 137.98(0) + 106.16(1) + 28.16(15) = 1392.58

Quarter 4 forecast = 864.08 + 87.80(0) + 137.98(0) + 106.16(0) + 28.16(16) = 1314.58

Difficulty: Moderate

LO: 5.4, Pages 230-231

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

19. The following table shows the average monthly distance traveled (in billion miles) by vehicles on urban highways for five different years.

	Urban Highways – Average Monthly Distance Traveled by Vehicles (Billion Miles)
Years	Jan	Feb	Mar	Apr	May	Jun	July	Aug	Sep	Oct	Nov	Dec
Year 1	4.22	5.32	5.21	5.12	4.92	4.49	4.55	4.49	4.44	4.39	4.37	4.35
Year 2	4.31	5.44	5.34	5.24	4.98	4.59	4.68	4.65	4.61	4.68	4.74	4.79
Year 3	4.38	5.51	5.41	5.36	4.98	4.63	4.71	4.78	4.82	4.88	4.85	4.89
Year 4	4.45	5.59	5.5	5.41	5.01	4.72	4.78	4.79	4.82	4.92	5.06	5.11
Year 5	4.51	5.65	5.62	5.49	5.12	4.8	4.88	4.82	4.95	5.12	5.22	5.44

 

1. Construct a time series plot. What type of pattern exists in the data?
2. Use a multiple regression model with dummy variables as follows to develop an equation to account for seasonal effects in the data. Jan = 1 if Month is January, 0 otherwise; Feb = 1 if month is February, 0 otherwise; …; Nov = 1 if month is November, 0 otherwise.
3. Compute the forecast (in billion miles) for next three months based on the model developed in part b.

 

Answer:

This time series plot shows horizontal pattern in the data, however, there is seasonal pattern as well. For instance, the lowest value occurs in January and the highest in February.

1. Rewrite the data with dummy variables in the following format:

yt	Jan	Feb	Mar	Apr	May	June	July	Aug	Sep	Oct	Nov
4.22	1	0	0	0	0	0	0	0	0	0	0
5.32	0	1	0	0	0	0	0	0	0	0	0
5.21	0	0	1	0	0	0	0	0	0	0	0
5.12	0	0	0	1	0	0	0	0	0	0	0
4.92	0	0	0	0	1	0	0	0	0	0	0
4.49	0	0	0	0	0	1	0	0	0	0	0
4.55	0	0	0	0	0	0	1	0	0	0	0
4.49	0	0	0	0	0	0	0	1	0	0	0
4.44	0	0	0	0	0	0	0	0	1	0	0
4.39	0	0	0	0	0	0	0	0	0	1	0
4.37	0	0	0	0	0	0	0	0	0	0	1
4.35	0	0	0	0	0	0	0	0	0	0	0
4.31	1	0	0	0	0	0	0	0	0	0	0
5.44	0	1	0	0	0	0	0	0	0	0	0
5.34	0	0	1	0	0	0	0	0	0	0	0
5.24	0	0	0	1	0	0	0	0	0	0	0
4.98	0	0	0	0	1	0	0	0	0	0	0
4.59	0	0	0	0	0	1	0	0	0	0	0
4.68	0	0	0	0	0	0	1	0	0	0	0
4.65	0	0	0	0	0	0	0	1	0	0	0
4.61	0	0	0	0	0	0	0	0	1	0	0
4.68	0	0	0	0	0	0	0	0	0	1	0
4.74	0	0	0	0	0	0	0	0	0	0	1
4.79	0	0	0	0	0	0	0	0	0	0	0
4.38	1	0	0	0	0	0	0	0	0	0	0
5.51	0	1	0	0	0	0	0	0	0	0	0
5.41	0	0	1	0	0	0	0	0	0	0	0
5.36	0	0	0	1	0	0	0	0	0	0	0
4.98	0	0	0	0	1	0	0	0	0	0	0
4.63	0	0	0	0	0	1	0	0	0	0	0
4.71	0	0	0	0	0	0	1	0	0	0	0
4.78	0	0	0	0	0	0	0	1	0	0	0
4.82	0	0	0	0	0	0	0	0	1	0	0
4.88	0	0	0	0	0	0	0	0	0	1	0
4.85	0	0	0	0	0	0	0	0	0	0	1
4.89	0	0	0	0	0	0	0	0	0	0	0
4.45	1	0	0	0	0	0	0	0	0	0	0
5.59	0	1	0	0	0	0	0	0	0	0	0
5.5	0	0	1	0	0	0	0	0	0	0	0
5.41	0	0	0	1	0	0	0	0	0	0	0
5.01	0	0	0	0	1	0	0	0	0	0	0
4.72	0	0	0	0	0	1	0	0	0	0	0
4.78	0	0	0	0	0	0	1	0	0	0	0
4.79	0	0	0	0	0	0	0	1	0	0	0
4.82	0	0	0	0	0	0	0	0	1	0	0
4.92	0	0	0	0	0	0	0	0	0	1	0
5.06	0	0	0	0	0	0	0	0	0	0	1
5.11	0	0	0	0	0	0	0	0	0	0	0
4.51	1	0	0	0	0	0	0	0	0	0	0
5.65	0	1	0	0	0	0	0	0	0	0	0
5.62	0	0	1	0	0	0	0	0	0	0	0
5.49	0	0	0	1	0	0	0	0	0	0	0
5.12	0	0	0	0	1	0	0	0	0	0	0
4.8	0	0	0	0	0	1	0	0	0	0	0
4.88	0	0	0	0	0	0	1	0	0	0	0
4.82	0	0	0	0	0	0	0	1	0	0	0
4.95	0	0	0	0	0	0	0	0	1	0	0
5.12	0	0	0	0	0	0	0	0	0	1	0
5.22	0	0	0	0	0	0	0	0	0	0	1
5.44	0	0	0	0	0	0	0	0	0	0	0

 

Use Excel’s Regression tool to find the regression model that accounts for the seasonal effects in the data.

 

Excel output:

 

From the above output, the regression model that minimizes MSE for this time series is:

= 4.916 – 0.542Jan + 0.586Feb + 0.5Mar + 0.408Apr + 0.086May –0.27June – 0.196July – 0.21Aug – 0.188Sep – 0.118Oct – 0.068Nov

1. Based on the model in part (b), the quarterly forecasts for the next three months are as follows:

Year 6, January forecast (in billion miles) = 4.916 – 0.542(1) +0.586(0) +0.5(0) + 0.408(0) + 0.086(0) – 0.27(0) – 0.196(0) – 0.21(0) – 0.188(0) – 0.118(0) – 0.068(0) = 4.374.

Year 6, February forecast (in billion miles) = 4.916 – 0.542(0) +0.586(1) +0.5(0) + 0.408(0) + 0.086(0) – 0.27(0) – 0.196(0) – 0.21(0) – 0.188(0) – 0.118(0) – 0.068(0) = 5.502.

Year 6, March forecast (in billion miles) = 4.916 – 0.542(0) +0.586(0) +0.5(1) + 0.408(0) + 0.086(0) – 0.27(0) – 0.196(0) – 0.21(0) – 0.188(0) – 0.118(0) – 0.068(0) = 5.416.

Difficulty: Challenging

LO: 5.4, Pages 228-230

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

20. The following table shows the average monthly distance traveled (in billion miles) by vehicles on urban highways for five different years.

	Urban Highways – Average Monthly Distance Traveled by Vehicles (Billion Miles)
Years	Jan	Feb	Mar	Apr	May	Jun	July	Aug	Sep	Oct	Nov	Dec
Year 1	4.22	5.32	5.21	5.12	4.92	4.49	4.55	4.49	4.44	4.39	4.37	4.35
Year 2	4.31	5.44	5.34	5.24	4.98	4.59	4.68	4.65	4.61	4.68	4.74	4.79
Year 3	4.38	5.51	5.41	5.36	4.98	4.63	4.71	4.78	4.82	4.88	4.85	4.89
Year 4	4.45	5.59	5.5	5.41	5.01	4.72	4.78	4.79	4.82	4.92	5.06	5.11
Year 5	4.51	5.65	5.62	5.49	5.12	4.8	4.88	4.82	4.95	5.12	5.22	5.44

 

5. Use a multiple regression model to develop an equation to account for seasonal effects and any linear trend in the data. To capture seasonal effects, use the dummy variables Jan = 1 if month is January, 0 otherwise; Feb = 1 if month is February, 0 otherwise; …; Nov = 1 if month is November, 0 otherwise; and create a variable t such that t = 1 for January of year 1, t = 2 for February of year 1, …, t = 60 for December of year 5.
6. Compute the forecast (in billion miles) for next three months based on the model developed in part a.

Answer:

1. Rewrite the data using the dummy variables and the variable t in the following format:

yt	Jan	Feb	Mar	Apr	May	June	July	Aug	Sep	Oct	Nov	t
4.22	1	0	0	0	0	0	0	0	0	0	0	1
5.32	0	1	0	0	0	0	0	0	0	0	0	2
5.21	0	0	1	0	0	0	0	0	0	0	0	3
5.12	0	0	0	1	0	0	0	0	0	0	0	4
4.92	0	0	0	0	1	0	0	0	0	0	0	5
4.49	0	0	0	0	0	1	0	0	0	0	0	6
4.55	0	0	0	0	0	0	1	0	0	0	0	7
4.49	0	0	0	0	0	0	0	1	0	0	0	8
4.44	0	0	0	0	0	0	0	0	1	0	0	9
4.39	0	0	0	0	0	0	0	0	0	1	0	10
4.37	0	0	0	0	0	0	0	0	0	0	1	11
4.35	0	0	0	0	0	0	0	0	0	0	0	12
4.31	1	0	0	0	0	0	0	0	0	0	0	13
5.44	0	1	0	0	0	0	0	0	0	0	0	14
5.34	0	0	1	0	0	0	0	0	0	0	0	15
5.24	0	0	0	1	0	0	0	0	0	0	0	16
4.98	0	0	0	0	1	0	0	0	0	0	0	17
4.59	0	0	0	0	0	1	0	0	0	0	0	18
4.68	0	0	0	0	0	0	1	0	0	0	0	19
4.65	0	0	0	0	0	0	0	1	0	0	0	20
4.61	0	0	0	0	0	0	0	0	1	0	0	21
4.68	0	0	0	0	0	0	0	0	0	1	0	22
4.74	0	0	0	0	0	0	0	0	0	0	1	23
4.79	0	0	0	0	0	0	0	0	0	0	0	24
4.38	1	0	0	0	0	0	0	0	0	0	0	25
5.51	0	1	0	0	0	0	0	0	0	0	0	26
5.41	0	0	1	0	0	0	0	0	0	0	0	27
5.36	0	0	0	1	0	0	0	0	0	0	0	28
4.98	0	0	0	0	1	0	0	0	0	0	0	29
4.63	0	0	0	0	0	1	0	0	0	0	0	30
4.71	0	0	0	0	0	0	1	0	0	0	0	31
4.78	0	0	0	0	0	0	0	1	0	0	0	32
4.82	0	0	0	0	0	0	0	0	1	0	0	33
4.88	0	0	0	0	0	0	0	0	0	1	0	34
4.85	0	0	0	0	0	0	0	0	0	0	1	35
4.89	0	0	0	0	0	0	0	0	0	0	0	36
4.45	1	0	0	0	0	0	0	0	0	0	0	37
5.59	0	1	0	0	0	0	0	0	0	0	0	38
5.5	0	0	1	0	0	0	0	0	0	0	0	39
5.41	0	0	0	1	0	0	0	0	0	0	0	40
5.01	0	0	0	0	1	0	0	0	0	0	0	41
4.72	0	0	0	0	0	1	0	0	0	0	0	42
4.78	0	0	0	0	0	0	1	0	0	0	0	43
4.79	0	0	0	0	0	0	0	1	0	0	0	44
4.82	0	0	0	0	0	0	0	0	1	0	0	45
4.92	0	0	0	0	0	0	0	0	0	1	0	46
5.06	0	0	0	0	0	0	0	0	0	0	1	47
5.11	0	0	0	0	0	0	0	0	0	0	0	48
4.51	1	0	0	0	0	0	0	0	0	0	0	49
5.65	0	1	0	0	0	0	0	0	0	0	0	50
5.62	0	0	1	0	0	0	0	0	0	0	0	51
5.49	0	0	0	1	0	0	0	0	0	0	0	52
5.12	0	0	0	0	1	0	0	0	0	0	0	53
4.8	0	0	0	0	0	1	0	0	0	0	0	54
4.88	0	0	0	0	0	0	1	0	0	0	0	55
4.82	0	0	0	0	0	0	0	1	0	0	0	56
4.95	0	0	0	0	0	0	0	0	1	0	0	57
5.12	0	0	0	0	0	0	0	0	0	1	0	58
5.22	0	0	0	0	0	0	0	0	0	0	1	59
5.44	0	0	0	0	0	0	0	0	0	0	0	60

 

We can use Excel’s Regression tool to find the regression model that accounts for both the trend and seasonal effects in the data.

 

Excel output:

 

From the above output, the regression model that minimizes MSE for this time series is:

= 4.576 – 0.438Jan + 0.681Feb + 0.585Mar + 0.484Apr + 0.152May – 0.213June – 0.149July – 0.172Aug – 0.1608Sep – 0.099Oct – 0.059Nov + 0.0095t

1. Based on the model in part (a), the quarterly forecasts for the next three months are as follows:

Year 6, January forecast (in billion miles) = 4.576 – 0.438(1) + 0.681(0) + 0.585(0) + 0.484(0) + 0.152(0) – 0.213(0) – 0.149(0) – 0.172(0) – 0.1608(0) – 0.099(0) – 0.059(0) + 0.0095(61) = 4.714.

Year 6, February forecast (in billion miles) = 4.576 – 0.438(0) + 0.681(1) + 0.585(0) + 0.484(0) + 0.152(0) – 0.213(0) – 0.149(0) – 0.172(0) – 0.1608(0) – 0.099(0) – 0.059(0) + 0.0095(62) = 5.842.

Year 6, March forecast (in billion miles) = 4.576 – 0.438(0) + 0.681(0) + 0.585(1) + 0.484(0) + 0.152(0) – 0.213(0) – 0.149(0) – 0.172(0) – 0.1608(0) – 0.099(0) – 0.059(0) + 0.0095(63) = 5.756.

Difficulty: Challenging

LO: 5.4, Pages 228-231

Bloom’s: Application

BUSPROG: Analytic

DISC: Time Series Data

 

Chapter 5

Time Series Analysis and Forecasting

 

 

 

           Solutions:

 

1. The following table shows the calculations for parts (a), (b), and (c).

 

Week	Time Series Value	Forecast	Forecast Error	Absolute Value of Forecast Error	Squared Forecast Error	Percentage Error	Absolute Value of Percentage Error
1	18
2	13	18	-5	5	25	-38.46	38.46
3	16	13	3	3	9	18.75	18.75
4	11	16	-5	5	25	-45.45	45.45
5	17	11	6	6	36	35.29	35.29
6	14	17	-3	3	9	-21.43	21.43
			Totals	22	104	-51.30	159.38

 

4. MAE = 22/5 = 4.4

 

20. MSE = 104/5 = 20.8

 

159. MAPE = 159.38/5 = 31.88

 

14. The forecast for week 7 is = y₆ = 14.

 

2. The following table shows the calculations for parts (a), (b), and (c).

Week	Time Series Value	Forecast	Forecast Error	Absolute Value of Forecast Error	Squared Forecast Error	Percentage Error	Absolute Value of Percentage Error
1	18
2	13	18.00	-5.00	5.00	25.00	-38.46	38.46
3	16	15.50	0.50	0.50	0.25	3.13	3.13
4	11	15.67	-4.67	4.67	21.81	-42.45	42.45
5	17	14.50	2.50	2.50	6.25	14.71	14.71
6	14	15.00	-1.00	1.00	1.00	-7.14	7.14
			Totals	13.67	54.31	-70.21	105.86

 

13. MAE = 13.67/5 = 2.73

 

54. MSE = 54.31/5 = 10.86

 

105. MAPE = 105.89/5 = 21.18

 

14. The forecast for week 7 is = (y₁ + y₂ + y₃ + y₄ + y₅ + y₆) / 6 = (18 + 13 + 16 + 11 + 17 + 14) / 6 = 14.83.

 

3. The following table shows the measures of forecast error for both methods.

 

	Exercise 1	Exercise 2
MAE	4.40	2.73
MSE	20.80	10.86
MAPE	31.88	21.18

 

For each measure of forecast accuracy the average of all the historical data provided more accurate forecasts than simply using the most recent value.

 

4. a.

Month	Time Series Value	Forecast	Forecast Error	Squared Forecast Error
1	24
2	13	24	-11	121
3	20	13	7	49
4	12	20	-8	64
5	19	12	7	49
6	23	19	4	16
7	15	23	-8	64
			Total	363

 

MSE = 363/6 = 60.5

 

The forecast for month 8 is  = y₇ = 15.

 

b.

Week	Time Series Value	Forecast	Forecast Error	Squared Forecast Error
1	24
2	13	24.00	-11.00	121.00
3	20	18.50	1.50	2.25
4	12	19.00	-7.00	49.00
5	19	17.25	1.75	3.06
6	23	17.60	5.40	29.16
7	15	18.50	-3.50	12.25
			Total	216.72

 

MSE = 216.72/6 = 36.12

Forecast for month 8 is  = (y₁ + y₂ + y₃ + y₄ + y₅ + y₆ + y₇) / 7 = (24 + 13 + 20 + 12 + 19 + 23 + 15) / 7 = 18.

 

1. The average of all the previous values is better because MSE is smaller.

 

5. a.

 

The data appear to follow a horizontal pattern.

 

1. Three-week moving average.

Week	Time Series Value	Forecast	Forecast Error	Squared Forecast Error
1	18
2	13
3	16
4	11	15.67	-4.67	21.78
5	17	13.33	3.67	13.44
6	14	14.67	-0.67	0.44
			Total	35.67

 

MSE = 35.67/3 = 11.89

 

The forecast for week 7 is  = (y₄ + y₅ + y₆) / 3 = (11 + 17 + 14) / 3 = 14.

 

1. Smoothing constant a = 0.2

Week	Time Series Value	Forecast	Forecast Error	Squared Forecast Error
1	18
2	13	18.00	-5.00	25.00
3	16	17.00	-1.00	1.00
4	11	16.80	-5.80	33.64
5	17	15.64	1.36	1.85
6	14	15.91	-1.91	3.66
			Total	65.15

 

MSE = 65.15/5 = 13.03

 

The forecast for week 7 is  = ay₆ + (1-a)  = 0.2(14) + (1 – 0.2)15.91 = 15.53.

 

1. The three-week moving average provides a better forecast since it has a smaller MSE.

 

1. Several values of a will yield an MSE smaller than the MSE associated with a = 0.2. The table below shows the resulting MSE from several different a .that you select.

 

a	MSE
0.1	15.04
0.2	13.03
0.3	12.20
0.4	12.09
0.5	12.47
0.6	13.25
0.7	14.41

 

The value of a that yields the minimum MSE is a = 0.368, which yields an MSE of 12.06.

 

	a =	0.368
Week	Time Series Value	Forecast	Forecast Error	Squared Forecast Error
1	18
2	13	18	-5.00	25.00
3	16	16.16	-0.16	0.03
4	11	16.10	-5.10	26.03
5	17	14.23	2.77	7.69
6	14	15.25	-1.25	1.55
			Total	60.30
	MSE = 60.30/5=	12.06

 

6. a.

 

 

The data appear to follow a horizontal pattern.

 

1. Three-week moving average.

Week	Time Series Value	Forecast	Forecast Error	Squared Forecast Error
1	24
2	13
3	20
4	12	19.00	-7.00	49.00
5	19	15.00	4.00	16.00
6	23	17.00	6.00	36.00
7	15	18.00	-3.00	9.00
			Total	110.00

 

MSE = 110/4 = 27.5.

 

The forecast for week 8 is  = (y₅ + y₆ + y₇) / 3 = (19 + 23 + 15) / 3 = 19.

 

1. Smoothing constant a = 0.2

Week	Time Series Value	Forecast	Forecast Error	Squared Forecast Error
1	24
2	13	24.00	-11.00	121.00
3	20	21.80	-1.80	3.24
4	12	21.44	-9.44	89.11
5	19	19.55	-0.55	0.30
6	23	19.44	3.56	12.66
7	15	20.15	-5.15	26.56
			Total	252.87

 

MSE = 252.87/6 = 42.15

 

The forecast for week 8 is  = ay₇ + (1-a)  =0.2(15) + (1 – 0.2)20.15 = 19.12.

 

1. The three-week moving average provides a better forecast since it has a smaller MSE.

 

1. Several values of a will yield an MSE smaller than the MSE associated with a = 0.2. The table below shows the resulting MSE for several different a values.

 

a	MSE
0.1	48.86
0.2	42.15
0.3	39.85
0.4	39.79
0.5	41.02
0.6	43.18
0.7	46.15

 

The value of a that yields the minimum MSE is a = 0.351, which yields an MSE of 39.61.

 

	a =		0.351
		Month		Time Series Value		Forecast		Forecast Error		Squared Forecast Error
		1		24
		2		13		24		-11.00		121.00
		3		20		20.13		-0.13		0.02
		4		12		20.09		-8.09		65.40
		5		19		17.25		1.75		3.08
		6		23		17.86		5.14		26.40
		7		15		19.67		-4.67		21.79
								Total		237.69

 

MSE = 237.69/6 = 39.61428577

 

7. a. Four and Five -week moving averages.

Week	Sales	4 Period Moving Average	5 period Moving Average
1	17
2	21
3	19
4	23
5	18	20.00
6	16	20.25	19.60
7	20	19.00	19.40
8	18	19.25	19.20
9	22	18.00	19.00
10	20	19.00	18.80
11	15	20.00	19.20
12	22	18.75	19.00

 

1. The MSE for the four-week and five-week moving averages.

 

For the four-week moving average:

Week	Time Series Value	Forecast	Forecast Error		Squared Forecast Error
1	17
2	21
3	19
4	23
5	18	20.00	-2.00	4.0000
6	16	20.25	-4.25	18.0625
7	20	19.00	1.00	1.0000
8	18	19.25	-1.25	1.5625
9	22	18.00	4.00	16.0000
10	20	19.00	1.00	1.0000
11	15	20.00	-5.00	25.0000
12	22	18.75	3.25	10.5625
			Total	77.1875

 

MSE = 77.1875/8 = 9.648

 

For the five-week moving average:

Week	Time Series Value	Forecast	Forecast Error	Squared Forecast Error
1	17
2	21
3	19
4	23
5	18
6	16	19.60	-3.60	12.96
7	20	19.40	0.60	0.36
8	18	19.20	-1.20	1.44
9	22	19.00	3.00	9.00
10	20	18.80	1.20	1.44
11	15	19.20	-4.20	17.64
12	22	19.00	3.00	9.00
			Total	51.84

 

MSE = 51.84/7 = 7.406

 

1. The MSE for the moving average forecasts are:

 

three week	27.500
four week	9.648
five week	7.406

 

Using the MSE as our standard, the best number of weeks of past data to use in the moving average computation is five.

 

8. a. Exponential smoothing forecasts using α = 0.1:

 

Week	Time Series Value	Forecast	Forecast Error	Squared Forecast Error
1	17	17.00
2	21	17.00	4.00	16.00
3	19	17.40	1.60	2.56
4	23	17.56	5.44	29.59
5	18	18.10	-0.10	0.01
6	16	18.09	-2.09	4.38
7	20	17.88	2.12	4.48
8	18	18.10	-0.10	0.01
9	22	18.09	3.91	15.32
10	20	18.48	1.52	2.32
11	15	18.63	-3.63	13.18
12	22	18.27	3.73	13.94
			Total	101.78

 

MSE = 101.78/11 = 9.253

 

For a smoothing constant of α = 0.2:

 

Week	Time Series Value	Forecast	Forecast Error	Squared Forecast Error
1	17	17.00
2	21	17.00	4.00	16.00
3	19	17.80	1.20	1.44
4	23	18.04	4.96	24.60
5	18	19.03	-1.03	1.07
6	16	18.83	-2.83	7.98
7	20	18.26	1.74	3.03
8	18	18.61	-0.61	0.37
9	22	18.49	3.51	12.34
10	20	19.19	0.81	0.66
11	15	19.35	-4.35	18.94
12	22	18.48	3.52	12.38
			Total	98.80

 

MSE = 98.80 / 11 = 8.982

 

Applying the MSE measure of forecast accuracy, a smoothing constant of α = 0.2 produces a smaller MSE and so is preferred.

 

1. For a smoothing constant of α = 0.1:

 

Week	Time Series Value	Forecast	Forecast Error	Absolute Forecast Error
1	17	17.00
2	21	17.00	4.00	4.00
3	19	17.40	1.60	1.60
4	23	17.56	5.44	5.44
5	18	18.10	-0.10	0.10
6	16	18.09	-2.09	2.09
7	20	17.88	2.12	2.12
8	18	18.10	-0.10	0.10
9	22	18.09	3.91	3.91
10	20	18.48	1.52	1.52
11	15	18.63	-3.63	3.63
12	22	18.27	3.73	3.73
			Total	28.25

 

MAE = 28.25 / 11 = 2.568

 

For a smoothing constant of α = 0.2:

 

Week	Time Series Value	Forecast	Forecast Error	Absolute Forecast Error
1	17	17.00
2	21	17.00	4.00	4.00
3	19	17.80	1.20	1.20
4	23	18.04	4.96	4.96
5	18	19.03	-1.03	1.03
6	16	18.83	-2.83	2.83
7	20	18.26	1.74	1.74
8	18	18.61	-0.61	0.61
9	22	18.49	3.51	3.51
10	20	19.19	0.81	0.81
11	15	19.35	-4.35	4.35
12	22	18.48	3.52	3.52
			Total	28.56

 

MAE = 28.56 / 11 = 2.596

 

Applying the MAE measure of forecast accuracy, a smoothing constant of α = 0.1 produces a slightly smaller MAE and so is preferred.

 

1. For a smoothing constant of α = 0.1:

 

 

 

Week	Time Series Value	Forecast	Forecast Error	100*(Forecast Error/ Time Series Value)	Absolute Value of 100*(Forecast Error/ Time Series Value)
1	17	17.00
2	21	17.00	4.00	19.05	19.05
3	19	17.40	1.60	8.42	8.42
4	23	17.56	5.44	23.65	23.65
5	18	18.10	-0.10	-0.58	0.58
6	16	18.09	-2.09	-13.09	13.09
7	20	17.88	2.12	10.58	10.58
8	18	18.10	-0.10	-0.53	0.53
9	22	18.09	3.91	17.79	17.79
10	20	18.48	1.52	7.61	7.61
11	15	18.63	-3.63	-24.20	24.20
12	22	18.27	3.73	16.97	16.97
				Total	142.46

 

MAPE = 142.46 / 11 = 12.95

 

For a smoothing constant of α = 0.2:

 

Week	Time Series Value	Forecast	Forecast Error	100*(Forecast Error/ Time Series Value)	Absolute Value of 100*(Forecast Error/ Time Series Value)
1	17	17.00
2	21	17.00	4.00	19.05	19.05
3	19	17.80	1.20	6.32	6.32
4	23	18.04	4.96	21.57	21.57
5	18	19.03	-1.03	-5.73	5.73
6	16	18.83	-2.83	-17.66	17.66
7	20	18.26	1.74	8.70	8.70
8	18	18.61	-0.61	-3.38	3.38
9	22	18.49	3.51	15.97	15.97
10	20	19.19	0.81	4.05	4.05
11	15	19.35	-4.35	-29.01	29.01
12	22	18.48	3.52	15.99	15.99
				Total	147.43

 

MAPE = 147.43 / 11 = 13.40

 

Applying the MAPE measure of forecast accuracy, a smoothing constant of α = 0.1 produces a smaller MAPE and so is preferred.

 

9. a.   = 0.2y₁₂ + 0.16y₁₁ + 0.64(0.2y₁₀ + 0.8 )

= 0.2y₁₂ + 0.16y₁₁ + 0.128y₁₀ + 0.512

= 0.2y₁₂ + 0.16y₁₁ + 0.128y₁₀ + 0.512(0.2y₉ + 0.8 )

= 0.2y₁₂ + 0.16y₁₁ + 0.128y₁₀ + 0.1024y₉ + 0.4096

= 0.2y₁₂ + 0.16y₁₁ + 0.128y₁₀ + 0.1024y₉ + 0.4096(0.2y₈ + 0.8 )

= 0.2y₁₂ + 0.16y₁₁ + 0.128y₁₀ + 0.1024y₉ + 0.08192y₈ + 0.32768

 

1. The more recent data receive the greater weight or importance in determining the forecast. The moving averages method weights the last n data values equally in determining the forecast.

 

10. a.

The time series plot indicates a horizontal pattern.

 

b.

Week	Sales Volume	Forecast	Forecast Error	Squared Value of Forecast Error
1	2750
2	3100	2750.00	350.000	122,500.00
3	3250	2890.00	360.000	129,600.00
4	2800	3034.00	-234.000	54,756.00
5	2900	2940.40	-40.400	1,632.16
6	3050	2924.24	125.760	15,815.58
7	3300	2974.54	325.456	105,921.61
8	3100	3104.73	-4.726	22.34
9	2950	3102.84	-152.836	23,358.79
10	3000	3041.70	-41.702	1,739.02
11	3200	3025.02	174.979	30,617.68
12	3150	3095.01	54.987	3,023.62
			Total	488,986.80

 

Note: MSE = 488,986.80/11 = 44,453

 

Forecast for week 13 is  = ay₁₂ + (1-a)  = 0.4(3150) + 0.6(3095.01) = 3117.01 or 3117 half-gallons of milk.

 

11. a.

 

The data appear to follow a horizontal pattern.

 

1. For the three month moving average:

 

Month	Time Series Value	Forecast	Forecast Error	Square Forecast Error
1	80
2	82
3	84
4	83	82.00	1.00	1.00
5	83	83.00	0.00	0.00
6	84	83.33	0.67	0.44
7	85	83.33	1.67	2.78
8	84	84.00	0.00	0.00
9	82	84.33	-2.33	5.44
10	83	83.67	-0.67	0.44
11	84	83.00	1.00	1.00
12	83	83.00	0.00	0.00
			Total	11.11

 

MSE = 11.11 / 9 = 1.235

 

 

 

 

 

 

For the exponential smoothing forecast for α = 0.2:

 

Month	Time Series Value	Forecast	Forecast Error	Square Forecast Error
1	80	80.00
2	82	80.00	2.00	4.00
3	84	80.40	3.60	12.96
4	83	81.12	1.88	3.53
5	83	81.50	1.50	2.26
6	84	81.80	2.20	4.85
7	85	82.24	2.76	7.63
8	84	82.79	1.21	1.46
9	82	83.03	-1.03	1.06
10	83	82.83	0.17	0.03
11	84	82.86	1.14	1.30
12	83	83.09	-0.09	0.01
			15.35	39.11

 

MSE = 39.80 / 11 = 3.555

 

Applying the MSE measure of forecast accuracy, a three-month moving average produces a smaller MSE and so is preferred.

 

1. Using a three-month moving average, the forecast for next month (t = 13) is

= (y₁₀ + y₁₁ + y₁₂) / 3 = (83 + 84 + 83) / 3 = 83.33.

 

 

 

12. a.

 

The data appear to follow a horizontal pattern.

 

b.

Month	Time-Series Value	3-Month Moving Average Forecast	(Error)2	4-Month Moving Average Forecast	(Error)2
1	9.5
2	9.3
3	9.4
4	9.6	9.40	0.04
5	9.8	9.43	0.14	9.45	0.12
6	9.7	9.60	0.01	9.53	0.03
7	9.8	9.70	0.01	9.63	0.03
8	10.5	9.77	0.53	9.73	0.59
9	9.9	10.00	0.01	9.95	0.00
10	9.7	10.07	0.14	9.98	0.08
11	9.6	10.03	0.18	9.97	0.14
12	9.6	9.73	0.02	9.92	0.10
			1.08		1.09

 

MSE(3-Month) = 1.08 / 9 = 0.12

 

MSE(4-Month) = 1.09 / 8 = 0.14

 

The MSE for the 3-Month moving average is smaller, so use the 3-Month moving average.

 

9. The forecast for month 13 is = (y₁₀ + y₁₁ + y₁₂) / 3 = (9.7 + 9.6 + 9.6) / 3 = 9.63.

 

 

13. a.

 

 

The data appear to follow a horizontal pattern.

 

b.

Month	Time-Series Value	3-Month Moving Average Forecast	(Error)2	a = 0.2 Forecast	(Error)2
1	240
2	350			240.00	12100.00
3	230			262.00	1024.00
4	260	273.33	177.69	255.60	19.36
5	280	280.00	0.00	256.48	553.19
6	320	256.67	4010.69	261.18	3459.79
7	220	286.67	4444.89	272.95	2803.70
8	310	273.33	1344.69	262.36	2269.57
9	240	283.33	1877.49	271.89	1016.97
10	310	256.67	2844.09	265.51	1979.36
11	240	286.67	2178.09	274.41	1184.05
12	230	263.33	1110.89	267.53	1408.50
			17,988.52		27,818.49

MSE(3-Month) = 17,988.52 / 9 = 1998.72

 

MSE(α = 0.2) = 27,818.49 / 11 = 2528.95

 

Based on the above MSE values, the 3-month moving average appears better.  However, exponential smoothing was penalized by including month 2 which was difficult for any method to forecast. Using only the errors for months 4 to 12, the MSE for exponential smoothing is:

 

MSE(a = 0.2) = 14,694.49 / 9 = 1632.72

 

Thus, exponential smoothing was better considering months 4 to 12.

 

1. Using exponential smoothing with a = 0.2,

 

= ay₁₂ + (1 – a)   = 0.2(230) + 0.8(267.53) = 256.66

 

14. a.

 

 

The data appear to follow a horizontal pattern.

 

1. Smoothing constant a = 0.3.

Month t	Time-Series Value yt	Forecast	Forecast Error    yt –	Squared Error
1	105
2	135	105.00	30.00	900.00
3	120	114.00	6.00	36.00
4	105	115.80	-10.80	116.64
5	90	112.56	-22.56	508.95
6	120	105.79	14.21	201.92
7	145	110.05	34.95	1221.50
8	140	120.54	19.46	378.69
9	100	126.38	-26.38	695.90
10	80	118.46	-38.46	1479.17
11	100	106.92	-6.92	47.89
12	110	104.85	5.15	26.52
			Total	5613.18

 

MSE = 5613.18 / 11 = 510.29

 

The forecast for month 13 is  = ay₁₂ + (1-a)  = 0.3(110) + 0.7(104.85) = 106.4

 

 

 

 

1. The MSE values for exponential smoothing forecasts with several different values of a appear below.

 

a	MSE
0.01	461.45
0.05	460.48
0.1	468.11
0.2	489.82
0.3	510.28
0.4	527.61
0.5	540.57
0.6	547.63
0.7	547.73

 

The values of a that yields the smallest possible MSE is a = 0.033, which yields an MSE of 459.693

 

	a =	0.033
				Squared
	Time Series		Forecast	Forecast
Month	Value	Forecast	Error	Error
1	105
2	135	105.00	30.00	900.00
3	120	105.98	14.02	196.65
4	105	106.43	-1.43	2.06
5	90	106.39	-16.39	268.53
6	120	105.85	14.15	200.13
7	145	106.31	38.69	1496.61
8	140	107.57	32.43	1051.46
9	100	108.63	-8.63	74.47
10	80	108.35	-28.35	803.65
11	100	107.43	-7.43	55.14
12	110	107.18	2.82	7.93
			Total	5056.62

 

MSE = 5056.62 / 11 = 459.693

 

 

15. a.

 

 

The data appear to follow a horizontal pattern.

 

1. The MSE values for exponential smoothing forecasts with several different values of a appear below.

 

a	MSE
0.1	0.0266
0.2	0.0171
0.3	0.0127
0.4	0.0106
0.5	0.0095
0.6	0.0090
0.7	0.0087
0.8	0.0086
0.9	0.0085
0.95	0.0085
0.99	0.0085

 

The value of a that yields the minimum MSE is a = 0.9102, which yields an MSE of 0.0085. Values of a near 0.9102 will yield similar values for the MSE.

 

16. a.

 

 

1. The value of the MSE will vary depending on the ultimate value of a that you select. The resulting MSE values for several different a values appear below.

a	MSE
0.1	1.71
0.2	1.40
0.3	1.27
0.4	1.23
0.5	1.22
0.6	1.24
0.7	1.27

 

The value of a that yields the smallest possible MSE is a = 0.467, which yields an MSE of 1.22.

 

	a =	0.467
Period	Stock %	Forecast	Forecast Error	Squared Forecast Error
1st-2011	29.8
2nd-2011	31.0	29.8.0	1.20	1.44
3rd-2011	29.9	30.36	-0.46	0.21
4th-2011	30.1	30.15	-0.05	0.00
1st-2012	32.2	30.12	2.08	4.31
2nd-2012	31.5	31.09	0.41	0.16
3rd-2012	32.0	31.28	0.72	0.51
4th-2012	31.9	31.62	0.28	0.08
1st-2013	30.0	31.75	-1.75	3.06
2nd-2013		30.93	Total	9.78
MSE =	1.22

 

1. The forecast for second quarter 2013 will vary depending on the ultimate value of a that you selected in part b. Using an exponential smoothing model with a = 0.467, the forecast for second quarter of year 3 = 30.93.

 

17. a.

 

 

The time series plot shows a linear trend.

 

1. From the Excel output

 

 

the regression estimates for the slope and y-intercept that minimize MSE for this time series are are b₀ = 4.7 and b₁ = 2.1, which results in the following forecasts, errors, and MSE:

 

Year	Sales	Forecast	Forecast Error	Squared Forecast Error
1	6.00	6.80	-0.80	0.64
2	11.00	8.90	2.10	4.41
3	9.00	11.00	-2.00	4.00
4	14.00	13.10	0.90	0.81
5	15.00	15.20	-0.20	0.04
6		17.30	Total	9.9

 

 

MSE = 9.9/5 = 1.982.475.

 

4. = b₀ + b₁t = 4.7 + 2.1(6) = 17.3

 

18. a.

 

 

The data are following a downward trend.

 

1. From the Excel output

 

 

the regression estimates for the slope and y-intercept that minimize MSE for this time series are are b₀ = 119.714 and b₁ = -4.929, which results in the following forecasts, errors, and MSE:

 

Period	Time Series Value	Forecast	Forecast Error	Squared Forecast Error
1	120	114.7857	5.2143	27.1888
2	110	109.8571	0.1429	0.0204
3	100	104.9286	-4.9286	24.2908
4	96	100.0000	-4.0000	16.0000
5	94	95.0714	-1.0714	1.1480
6	92	90.1429	1.8571	3.4490
7	88	85.2143	2.7857	7.7602
			Total	79.8571

 

MSE = 79.8571 / 7 = 11.4082.

 

119. = b₀ + b₁t = 119.714 – 4.929(8) = 80.282

 

19. a.

 

 

The time series plot shows a linear trend

 

1. From the Excel output

 

 

the regression estimates for the slope and y-intercept that minimize MSE for this time series are b₀ = 4.717 and b₁ = 1.457, which results in the following forecasts, errors, and MSE:

 

Year		Enrollment		Forecast		Forecast Error	Squared Forecast Error
1		6.50		6.17		0.33	0.11
2		8.10		7.63		0.47	0.22
3		8.40		9.09		-0.69	0.47
4		10.20		10.54		-0.34	0.12
5		12.50		12.00		0.50	0.25
6		13.30		13.46		-0.16	0.02
7		13.70		14.91		-1.21	1.47
8		17.20		16.37		0.83	0.69
9		18.10		17.83		0.27	0.07
10				19.28		Total	3.42
MSE =		0.3808

4. = b₀ + b₁t = 4.717 + 1.457(10) = 19.29

 

20. a.

 

 

The data appear to follow a downward trend.

 

1. From the Excel output

 

 

the regression estimates for the slope and y-intercept that minimize MSE for this time series are are b₀ = 13.8 and b₁ = -0.7, which results in the following forecasts, errors, and MSE:

 

t	Time Series Value	Forecast	Forecast Error	Squared Forecast Error
1	13.9	13.10	0.80	0.64
2	12.2	12.40	-0.20	0.04
3	10.5	11.70	-1.20	1.44
4	10.4	11.00	-0.60	0.36
5	11.5	10.30	1.20	1.44
6	10.0	9.60	0.40	0.16
7	8.5	8.90	-0.40	0.16
8			Total	4.24

 

MSE = 4.24 / 7 = 0.606.

 

13. = b₀ + b₁t = 13.8 – 0.7(8) = 8.20

 

1. Using the forecast model for t = 9, 10, …15 gives us

t
9	7.5
10	6.8
11	6.1
12	5.4
13	4.7
14	4.0
15	3.3

 

Thus, we predict that SCC will achieve a level less than 5% in year 13 (6 years from now) at 4.7%.

21. a.

 

The time series plot shows an upward linear trend

 

1. From the Excel output

 

 

the regression estimates for the slope and y-intercept that minimize MSE for this time series are b₀ = 19.993 and b₁ = 1.774, which results in the following forecasts, errors, and MSE:

 

				Squared
			Forecast	Forecast
Year	Cost/Unit($)	Forecast	Error	Error
1	20.00	21.77	-1.77	3.12
2	24.50	23.54	0.96	0.92
3	28.20	25.31	2.89	8.33
4	27.50	27.09	0.41	0.17
5	26.60	28.86	-2.26	5.12
6	30.00	30.64	-0.64	0.40
7	31.00	32.41	-1.41	1.99
8	36.00	34.18	1.82	3.30
			Total	23.35
	MSE =	2.92

 

1. The average cost/unit has been increasing by approximately $1.77 per year.

 

19. = b₀ + b₁t = 19.993 + 1.774(9) = $35.96.

 

 

22. a.

 

The time series plot shows a horizontal pattern. But, there is a seasonal pattern in the data. For instance, in each year the lowest value occurs in quarter 2 and the highest value occurs in quarter 4.

 

1. After putting the data into the following format:

 

		Dummy Variables
Year	Quarter	Qtr1	Qtr2	Qtr3	yt
1	1	1	0	0	71
1	2	0	1	0	48
1	3	0	0	1	58
1	4	0	0	0	78
2	1	1	0	0	68
2	2	0	1	0	41
2	3	0	0	1	60
2	4	0	0	0	81
3	1	1	0	0	62
3	2	0	1	0	51
3	3	0	0	1	53
3	4	0	0	0	72

 

we can use the Excel Regression tool to find the regression model that to accounts for seasonal effects in the data. From the Excel output

 

 

the regression model that minimizes MSE for this time series is:

 

Value = 77 – 10 Qtr1 – 30.333 Qtr2 – 20 Qtr3

 

1. The quarterly forecasts for next year are as follows:

 

Quarter 1 forecast = 77 – 10(1) – 30.333(0) – 20(0) = 67

 

Quarter 2 forecast = 77 – 10(0) – 30.333(1) – 20(0) = 46.667

 

Quarter 3 forecast = 77 – 10(0) – 30.333(0) – 20(1) = 57

 

Quarter 4 forecast = 77 – 10(0) – 30.333(0) – 20(0) = 77

 

23. a.

 

 

Careful scrutiny of the time series plot reveals a horizontal pattern (i.e., a linear trend) with seasonality. For instance, in each year the value drops from quarter 1 to quarter 2 and the value increases from quarter 3 to quarter 4.

 

1. After putting the data into the following format:

 

		Dummy Variables
Year	Quarter	Qtr1	Qtr2	Qtr3	yt
1	1	1	0	0	4
1	2	0	1	0	2
1	3	0	0	1	3
1	4	0	0	0	5
2	1	1	0	0	6
2	2	0	1	0	3
2	3	0	0	1	5
2	4	0	0	0	7
3	1	1	0	0	7
3	2	0	1	0	6
3	3	0	0	1	6
3	4	0	0	0	8

 

We can use the Excel Regression tool to find the regression model that to accounts for trend and seasonal effects in the data. From the Excel output

 

 

The regression model that minimizes MSE for this time series is:

 

Value = 6.667 -1 Qtr1 – 3 Qtr2 – 2 Qtr3

 

1. Based on the model in part (b), the quarterly forecasts for next year are as follows:

 

Quarter 1 forecast = 6.667 – 1 (1) – 3 (0) – 2 (0) = 5.667

 

Quarter 2 forecast = 6.667 – 1 (0) – 3 (1) – 2 (0) = 3.667

 

Quarter 3 forecast = 6.667 – 1 (0) – 3 (0) – 2 (1) = 6.667

 

Quarter 4 forecast = 6.667 – 1 (0) – 3 (0) – 2 (0) = 6.667

 

1. After putting the data into the following format:

 

		Dummy Variables
Year	Quarter	Qtr1	Qtr2	Qtr3	t	yt
1	1	1	0	0	1	4
1	2	0	1	0	2	2
1	3	0	0	1	3	3
1	4	0	0	0	4	5
2	1	1	0	0	5	6
2	2	0	1	0	6	3
2	3	0	0	1	7	5
2	4	0	0	0	8	7
3	1	1	0	0	9	7
3	2	0	1	0	10	6
3	3	0	0	1	11	6
3	4	0	0	0	12	8

 

We can use the Excel Regression tool to find the regression model that to accounts for trend and seasonal effects in the data. From the Excel output

 

 

The regression model that minimizes MSE for this time series is:

 

Value = 3.4167 + 0.2188 Qtr1 – 2.1875 Qtr2 – 1.5938 Qtr3 + 0.4063 t

 

1. Based on the model in part (d), the quarterly forecasts for next year are as follows:

 

Quarter 1 forecast = 3.4167 + 0.2188(1) – 2.1875(0) – 1.5938(0) + 0.4063(13) = 8.91678

 

Quarter 2 forecast = 3.4167 + 0.2188(0) – 2.1875(1) – 1.5938(0) + 0.4063(14) = 6.9167

 

Quarter 3 forecast = 3.4167 + 0.2188(0) – 2.1875(0) – 1.5938(1) + 0.4063(15) = 7.9167

 

Quarter 4 forecast = 3.4167 + 0.2188(0) – 2.1875(0) – 1.5938(0) + 0.4063(16) = 9.9167

 

1. For the model from part (b) that only includes seasonal effects:

 

Year	Quarter	yt	Forecast	Forecast Error	Squared Forecast Error
1	1	4	5.6667	-5.6667	32.1111
1	2	2	3.6667	-3.6667	13.4444
1	3	3	4.6667	-4.6667	21.7778
1	4	5	6.6667	-6.6667	44.4444
2	1	6	5.6667	-5.6667	32.1111
2	2	3	3.6667	-3.6667	13.4444
2	3	5	4.6667	-4.6667	21.7778
2	4	7	6.6667	-6.6667	44.4444
3	1	7	5.6667	-5.6667	32.1111
3	2	6	3.6667	-3.6667	13.4444
3	3	6	4.6667	-4.6667	21.7778
3	4	8	6.6667	-6.6667	44.4444
				Total	335.3333

 

MSE = 335.3333 / 12 = 27.9444.

 

For the model from part (d) that includes both trend and seasonal effects:

 

Year	Quarter	t	yt	Forecast	Forecast Error	Squared Forecast Error
1	1	1	4	4.0417	-0.0417	0.0017
1	2	2	2	2.0417	-0.0417	0.0017
1	3	3	3	3.0417	-0.0417	0.0017
1	4	4	5	5.0417	-0.0417	0.0017
2	1	5	6	5.6667	0.3333	0.1111
2	2	6	3	3.6667	-0.6667	0.4444
2	3	7	5	4.6667	0.3333	0.1111
2	4	8	7	6.6667	0.3333	0.1111
3	1	9	7	7.2917	-0.2917	0.0851
3	2	10	6	5.2917	0.7083	0.5017
3	3	11	6	6.2917	-0.2917	0.0851
3	4	12	8	8.2917	-0.2917	0.0851
					Total	1.5417

 

MSE = 1.5417 / 12 = 0.1285.

 

The mean squared error for the model from part (d) that includes both trend and seasonal effects is much smaller than the mean squared error for the model from part (b) that includes only seasonal effects. This supports our preliminary conclusions reached in review of the time series plot constructed in part (a) – these data show a linear trend with seasonality.

 

 

24. a.

 

There appears to be a seasonal pattern in the data and perhaps a moderate upward linear trend.

 

1. After putting the data into the following format:

 

		Dummy Variables
Year	Quarter	Qtr1	Qtr2	Qtr3	yt
1	1	1	0	0	1690
1	2	0	1	0	940
1	3	0	0	1	2625
1	4	0	0	0	2500
2	1	1	0	0	1800
2	2	0	1	0	900
2	3	0	0	1	2900
2	4	0	0	0	2360
3	1	1	0	0	1850
3	2	0	1	0	1100
3	3	0	0	1	2930
3	4	0	0	0	2615

 

We can use the Excel Regression tool to find the regression model that to accounts for seasonal effects in the data. From the Excel output

 

 

The regression model that minimizes MSE for this time series is:

 

Value = 2491.6667 – 711.6667 Qtr1 – 1511.6667 Qtr2 + 326.6667 Qtr3

 

1. Based on the model in part (b), the quarterly forecasts for next year are as follows:

 

Quarter 1 forecast = 2491.6667 – 711.6667(1) – 1511.6667(0) + 326.6667(0) = 1780.00

 

Quarter 2 forecast = 2491.6667 – 711.6667(0) – 1511.6667(1) + 326.6667(0) = 980.00

 

Quarter 3 forecast = 2491.6667 – 711.6667(0) – 1511.6667(0) + 326.6667(1) = 2818.3333

 

Quarter 4 forecast = 2491.6667 – 711.6667(0) – 1511.6667(0) + 326.6667(0) = 2491.6667

 

 

1. After putting the data into the following format:

 

		Dummy Variables
Year	Quarter	Qtr1	Qtr2	Qtr3	t	yt
1	1	1	0	0	1	1690
1	2	0	1	0	2	940
1	3	0	0	1	3	2625
1	4	0	0	0	4	2500
2	1	1	0	0	5	1800
2	2	0	1	0	6	900
2	3	0	0	1	7	2900
2	4	0	0	0	8	2360
3	1	1	0	0	9	1850
3	2	0	1	0	10	1100
3	3	0	0	1	11	2930
3	4	0	0	0	12	2615

 

we can use the Excel Regression tool to find the regression model that to accounts for seasonal effects in the data. From the Excel output

 

 

the regression model that minimizes MSE for this time series is:

 

Value = 2306.6667 – 642.2917 Qtr1 – 1465.417 Qtr2 + 349.7917 Qtr3 + 23.125t

 

1. Based on the model in part (c), the quarterly forecasts for next year are as follows:

 

Quarter 1 forecast = 2306.6667 – 642.2917(1) – 1465.417(0) + 349.7917(0) + 23.125(13) = 1965.00

 

Quarter 2 forecast = 2306.6667 – 642.2917(0) – 1465.417(1) + 349.7917(0) + 23.125(14) = 1165.00

 

Quarter 3 forecast = 2306.6667 – 642.2917(0) – 1465.417(0) + 349.7917(1) + 23.125(15) = 2011.3333

 

Quarter 4 forecast = 2306.6667 – 642.2917(0) – 1465.417(0) + 349.7917(0) + 23.125(16) = 2676.6667

 

1. For the model from part (b) that only includes seasonal effects:

 

Year	Quarter	yt	Forecast	Forecast Error	Squared Forecast Error
1	1	1690	1780.0000	-90.0000	8,100.0000
1	2	940	980.0000	-40.0000	1,600.0000
1	3	2625	2818.3333	-193.3333	37,377.7778
1	4	2500	2491.6667	8.3333	69.4444
2	1	1800	1780.0000	20.0000	400.0000
2	2	900	980.0000	-80.0000	6,400.0000
2	3	2900	2818.3333	81.6667	6,669.4444
2	4	2360	2491.6667	-131.6667	17,336.1111
3	1	1850	1780.0000	70.0000	4,900.0000
3	2	1100	980.0000	120.0000	14,400.0000
3	3	2930	2818.3333	111.6667	12,469.4444
3	4	2615	2491.6667	123.3333	15,211.1111
				Total	124,933.3333

 

MSE = 124,933.3333 / 12 = 10,411.1111.

 

For the model from part (d) that includes both trend and seasonal effects:

 

Year	Quarter	t	yt	Forecast	Forecast Error	Squared Forecast Error
1	1	1	1690	1687.5000	2.5000	6.2500
1	2	2	940	887.5000	52.5000	2,756.2500
1	3	3	2625	2725.8333	-100.8333	10,167.3611
1	4	4	2500	2399.1667	100.8333	10,167.3611
2	1	5	1800	1780.0000	20.0000	400.0000
2	2	6	900	980.0000	-80.0000	6,400.0000
2	3	7	2900	2818.3333	81.6667	6,669.4444
2	4	8	2360	2491.6667	-131.6667	17,336.1111
3	1	9	1850	1872.5000	-22.5000	506.2500
3	2	10	1100	1072.5000	27.5000	756.2500
3	3	11	2930	2910.8333	19.1667	367.3611
3	4	12	2615	2584.1667	30.8333	950.6944
					Total	56,483.3333

 

MSE = 56,483.3333 / 12 = 4,706.9444.

 

The mean squared error for the model from part (d) that includes both trend and seasonal effects is much smaller than the mean squared error for the model from part (b) that includes only seasonal effects. This supports our preliminary conclusions reached in review of the time series plot constructed in part (a) – these data show a linear trend with seasonality.

 

25. a.

 

There appears to be a seasonal pattern in the data and perhaps a slight upward linear trend.

 

1. After putting the data into the following format:

 

			Hourly Dummy Variables
Date	Hour	yt	1	2	3	4	5	6	7	8	9	10	11
July 11	6:00 a.m. – 7:00 a.m.	25	1	0	0	0	0	0	0	0	0	0	0
July 11	7:00 a.m. – 8:00 a.m.	28	0	1	0	0	0	0	0	0	0	0	0
July 11	8:00 a.m. – 9:00 a.m.	35	0	0	1	0	0	0	0	0	0	0	0
July 11	9:00 a.m. – 10:00 a.m.	50	0	0	0	1	0	0	0	0	0	0	0
July 11	10:00 a.m. – 11:00 a.m.	60	0	0	0	0	1	0	0	0	0	0	0
July 11	11:00 a.m. – 12:00 p.m.	60	0	0	0	0	0	1	0	0	0	0	0
July 11	12:00 p.m. – 1:00 p.m.	40	0	0	0	0	0	0	1	0	0	0	0
July 11	1:00 p.m. – 2:00 p.m.	35	0	0	0	0	0	0	0	1	0	0	0
July 11	2:00 p.m. – 3:00 p.m.	30	0	0	0	0	0	0	0	0	1	0	0
July 11	3:00 p.m. – 4:00 p.m.	25	0	0	0	0	0	0	0	0	0	1	0
July 11	4:00 p.m. – 5:00 p.m.	25	0	0	0	0	0	0	0	0	0	0	1
July 11	5:00 p.m. – 6:00 p.m.	20	0	0	0	0	0	0	0	0	0	0	0
July 12	6:00 a.m. – 7:00 a.m.	28	1	0	0	0	0	0	0	0	0	0	0
July 12	7:00 a.m. – 8:00 a.m.	30	0	1	0	0	0	0	0	0	0	0	0
July 12	8:00 a.m. – 9:00 a.m.	35	0	0	1	0	0	0	0	0	0	0	0
July 12	9:00 a.m. – 10:00 a.m.	48	0	0	0	1	0	0	0	0	0	0	0
July 12	10:00 a.m. – 11:00 a.m.	60	0	0	0	0	1	0	0	0	0	0	0
July 12	11:00 a.m. – 12:00 p.m.	65	0	0	0	0	0	1	0	0	0	0	0
July 12	12:00 p.m. – 1:00 p.m.	50	0	0	0	0	0	0	1	0	0	0	0
July 12	1:00 p.m. – 2:00 p.m.	40	0	0	0	0	0	0	0	1	0	0	0
July 12	2:00 p.m. – 3:00 p.m.	35	0	0	0	0	0	0	0	0	1	0	0
July 12	3:00 p.m. – 4:00 p.m.	25	0	0	0	0	0	0	0	0	0	1	0
July 12	4:00 p.m. – 5:00 p.m.	20	0	0	0	0	0	0	0	0	0	0	1
July 12	5:00 p.m. – 6:00 p.m.	20	0	0	0	0	0	0	0	0	0	0	0
July 13	6:00 a.m. – 7:00 a.m.	35	1	0	0	0	0	0	0	0	0	0	0
July 13	7:00 a.m. – 8:00 a.m.	42	0	1	0	0	0	0	0	0	0	0	0
July 13	8:00 a.m. – 9:00 a.m.	45	0	0	1	0	0	0	0	0	0	0	0
July 13	9:00 a.m. – 10:00 a.m.	70	0	0	0	1	0	0	0	0	0	0	0
July 13	10:00 a.m. – 11:00 a.m.	72	0	0	0	0	1	0	0	0	0	0	0
July 13	11:00 a.m. – 12:00 p.m.	75	0	0	0	0	0	1	0	0	0	0	0
July 13	12:00 p.m. – 1:00 p.m.	60	0	0	0	0	0	0	1	0	0	0	0
July 13	1:00 p.m. – 2:00 p.m.	45	0	0	0	0	0	0	0	1	0	0	0
July 13	2:00 p.m. – 3:00 p.m.	40	0	0	0	0	0	0	0	0	1	0	0
July 13	3:00 p.m. – 4:00 p.m.	25	0	0	0	0	0	0	0	0	0	1	0
July 13	4:00 p.m. – 5:00 p.m.	25	0	0	0	0	0	0	0	0	0	0	1
July 13	5:00 p.m. – 6:00 p.m.	25	0	0	0	0	0	0	0	0	0	0	0

 

We can use the Excel Regression tool to find the regression model that accounts for the seasonal effects in the data. From the Excel output

 

 

The regression model that minimizes MSE for this time series is:

 

Value = 21.6667 + 7.6667HOUR1 + 11.6667HOUR2 + 16.6667HOUR3 + 34.3333HOUR4 + 42.3333HOUR5 + 45HOUR6 + 28.3333HOUR7 + 18.3333HOUR8 + 13.3333HOUR9 + 3.3333HOUR10 +  1.6667HOUR11

 

1. Using the model estimated in part (b), the hourly forecasts for July 18 (t = 37 through t = 48) are as follows:

 

6:00 a.m. – 7:00 a.m. forecast = 21.6667 + 7.6667= 29.3333

 

7:00 a.m. – 8:00 a.m. forecast = 21.6667 + 11.6667 = 33.3333

 

8:00 a.m. – 9:00 a.m. forecast = 21.6667 + 16.6667 = 38.3333

 

9:00 a.m. – 10:00 a.m. forecast = 21.6667 + 34.3333 = 56

 

10:00 a.m. – 11:00 a.m. forecast = 21.6667 + 42.3333 = 64

 

11:00 a.m. – noon forecast = 21.6667 + 45 = 66.6667

 

noon – 1:00 p.m. forecast = 21.6667 + 28.3333 = 50

 

1:00 p.m. – 2:00 p.m. forecast = 21.6667 + 18.3333 = 40

 

2:00 p.m. – 3:00 p.m. forecast = 21.6667 + 13.3333 = 35

 

3:00 p.m. – 4:00 p.m. forecast = 21.6667 + 3.3333 = 25

 

4:00 p.m. – 5:00 p.m. forecast = 21.6667 + 1.6667 = 23.3333

 

5:00 p.m. – 6:00 p.m. forecast = 21.6667 = 21.6667

 

1. After putting the data into the following format:

 

			Hourly Dummy Variables
Date	Hour	yt	1	2	3	4	5	6	7	8	9	10	11	t
July 11	6:00 a.m. – 7:00 a.m.	25	1	0	0	0	0	0	0	0	0	0	0	1
July 11	7:00 a.m. – 8:00 a.m.	28	0	1	0	0	0	0	0	0	0	0	0	2
July 11	8:00 a.m. – 9:00 a.m.	35	0	0	1	0	0	0	0	0	0	0	0	3
July 11	9:00 a.m. – 10:00 a.m.	50	0	0	0	1	0	0	0	0	0	0	0	4
July 11	10:00 a.m. – 11:00 a.m.	60	0	0	0	0	1	0	0	0	0	0	0	5
July 11	11:00 a.m. – 12:00 p.m.	60	0	0	0	0	0	1	0	0	0	0	0	6
July 11	12:00 p.m. – 1:00 p.m.	40	0	0	0	0	0	0	1	0	0	0	0	7
July 11	1:00 p.m. – 2:00 p.m.	35	0	0	0	0	0	0	0	1	0	0	0	8
July 11	2:00 p.m. – 3:00 p.m.	30	0	0	0	0	0	0	0	0	1	0	0	9
July 11	3:00 p.m. – 4:00 p.m.	25	0	0	0	0	0	0	0	0	0	1	0	10
July 11	4:00 p.m. – 5:00 p.m.	25	0	0	0	0	0	0	0	0	0	0	1	11
July 11	5:00 p.m. – 6:00 p.m.	20	0	0	0	0	0	0	0	0	0	0	0	12
July 12	6:00 a.m. – 7:00 a.m.	28	1	0	0	0	0	0	0	0	0	0	0	13
July 12	7:00 a.m. – 8:00 a.m.	30	0	1	0	0	0	0	0	0	0	0	0	14
July 12	8:00 a.m. – 9:00 a.m.	35	0	0	1	0	0	0	0	0	0	0	0	15
July 12	9:00 a.m. – 10:00 a.m.	48	0	0	0	1	0	0	0	0	0	0	0	16
July 12	10:00 a.m. – 11:00 a.m.	60	0	0	0	0	1	0	0	0	0	0	0	17
July 12	11:00 a.m. – 12:00 p.m.	65	0	0	0	0	0	1	0	0	0	0	0	18
July 12	12:00 p.m. – 1:00 p.m.	50	0	0	0	0	0	0	1	0	0	0	0	19
July 12	1:00 p.m. – 2:00 p.m.	40	0	0	0	0	0	0	0	1	0	0	0	20
July 12	2:00 p.m. – 3:00 p.m.	35	0	0	0	0	0	0	0	0	1	0	0	21
July 12	3:00 p.m. – 4:00 p.m.	25	0	0	0	0	0	0	0	0	0	1	0	22
July 12	4:00 p.m. – 5:00 p.m.	20	0	0	0	0	0	0	0	0	0	0	1	23
July 12	5:00 p.m. – 6:00 p.m.	20	0	0	0	0	0	0	0	0	0	0	0	24
July 13	6:00 a.m. – 7:00 a.m.	35	1	0	0	0	0	0	0	0	0	0	0	25
July 13	7:00 a.m. – 8:00 a.m.	42	0	1	0	0	0	0	0	0	0	0	0	26
July 13	8:00 a.m. – 9:00 a.m.	45	0	0	1	0	0	0	0	0	0	0	0	27
July 13	9:00 a.m. – 10:00 a.m.	70	0	0	0	1	0	0	0	0	0	0	0	28
July 13	10:00 a.m. – 11:00 a.m.	72	0	0	0	0	1	0	0	0	0	0	0	29
July 13	11:00 a.m. – 12:00 p.m.	75	0	0	0	0	0	1	0	0	0	0	0	30
July 13	12:00 p.m. – 1:00 p.m.	60	0	0	0	0	0	0	1	0	0	0	0	31
July 13	1:00 p.m. – 2:00 p.m.	45	0	0	0	0	0	0	0	1	0	0	0	32
July 13	2:00 p.m. – 3:00 p.m.	40	0	0	0	0	0	0	0	0	1	0	0	33
July 13	3:00 p.m. – 4:00 p.m.	25	0	0	0	0	0	0	0	0	0	1	0	34
July 13	4:00 p.m. – 5:00 p.m.	25	0	0	0	0	0	0	0	0	0	0	1	35
July 13	5:00 p.m. – 6:00 p.m.	25	0	0	0	0	0	0	0	0	0	0	0	36

 

We can use the Excel Regression tool to find the regression model that accounts for the trend and seasonal effects in the data. From the Excel output

 

 

 

The regression model that minimizes MSE for this time series is:

 

 

Value = 11.1667 + 12.4792HOUR1 + 16.0417HOUR2 + 20.6042HOUR3 + 37.8333HOUR4 + 45.3958HOUR5 + 47.625HOUR6 + 30.5208HOUR7 + 20.0833HOUR8 + 14.6458HOUR9 + 4.2083HOUR10 +  2.1042HOUR11 +  0.4375t

 

1. Using the model estimated in part (d), the hourly forecasts for July 18 (t = 37 through t = 48) are as follows:

 

6:00 a.m. – 7:00 a.m. forecast = 11.1667 + 12.4792 + 0.4375(37) = 39.8333

 

7:00 a.m. – 8:00 a.m. forecast = 11.1667 + 16.0417 + 0.4375(38) = 43.8333

 

8:00 a.m. – 9:00 a.m. forecast = 11.1667 + 20.6042 + 0.4375(39) = 48.8333

 

9:00 a.m. – 10:00 a.m. forecast = 11.1667 + 37.8333 + 0.4375(40) = 66.5

 

10:00 a.m. – 11:00 a.m. forecast = 11.1667 + 45.3958 + 0.4375(41) = 74.5

 

11:00 a.m. – noon forecast = 11.1667 + 47.625 + 0.4375(42) = 77.1667

 

noon – 1:00 p.m. forecast = 11.1667 + 30.5208 + 0.4375(43) = 60.5

 

1:00 p.m. – 2:00 p.m. forecast = 11.1667 + 20.0833 + 0.4375(44) = 50.5

 

2:00 p.m. – 3:00 p.m. forecast = 11.1667 + 14.6458 + 0.4375(45) = 45.5

 

3:00 p.m. – 4:00 p.m. forecast = 11.1667 + 4.2083 + 0.4375(46) = 35.5

 

4:00 p.m. – 5:00 p.m. forecast = 11.1667 + 2.1042 + 0.4375(47) = 33.8333

 

5:00 p.m. – 6:00 p.m. forecast = 11.1667 + 0.4375(48) = 32.1667

 

1. For the model from part (b) that only includes seasonal effects:

 

Date	Hour	yt	Forecast	Forecast Error	Squared Forecast Error
July 11	6:00 a.m. – 7:00 a.m.	25	29.3333	-4.3333	18.7778
July 11	7:00 a.m. – 8:00 a.m.	28	33.3333	-5.3333	28.4444
July 11	8:00 a.m. – 9:00 a.m.	35	38.3333	-3.3333	11.1111
July 11	9:00 a.m. – 10:00 a.m.	50	56	-6.0000	36.0000
July 11	10:00 a.m. – 11:00 a.m.	60	64	-4.0000	16.0000
July 11	11:00 a.m. – 12:00 p.m.	60	66.6667	-6.6667	44.4444
July 11	12:00 p.m. – 1:00 p.m.	40	50	-10.0000	100.0000
July 11	1:00 p.m. – 2:00 p.m.	35	40	-5.0000	25.0000
July 11	2:00 p.m. – 3:00 p.m.	30	35	-5.0000	25.0000
July 11	3:00 p.m. – 4:00 p.m.	25	25	0.0000	0.0000
July 11	4:00 p.m. – 5:00 p.m.	25	23.3333	1.6667	2.7778
July 11	5:00 p.m. – 6:00 p.m.	20	21.6667	-1.6667	2.7778
July 12	6:00 a.m. – 7:00 a.m.	28	29.3333	-1.3333	1.7778
July 12	7:00 a.m. – 8:00 a.m.	30	33.3333	-3.3333	11.1111
July 12	8:00 a.m. – 9:00 a.m.	35	38.3333	-3.3333	11.1111
July 12	9:00 a.m. – 10:00 a.m.	48	56	-8.0000	64.0000
July 12	10:00 a.m. – 11:00 a.m.	60	64	-4.0000	16.0000
July 12	11:00 a.m. – 12:00 p.m.	65	66.6667	-1.6667	2.7778
July 12	12:00 p.m. – 1:00 p.m.	50	50	0.0000	0.0000
July 12	1:00 p.m. – 2:00 p.m.	40	40	0.0000	0.0000
July 12	2:00 p.m. – 3:00 p.m.	35	35	0.0000	0.0000
July 12	3:00 p.m. – 4:00 p.m.	25	25	0.0000	0.0000
July 12	4:00 p.m. – 5:00 p.m.	20	23.3333	-3.3333	11.1111
July 12	5:00 p.m. – 6:00 p.m.	20	21.6667	-1.6667	2.7778
July 13	6:00 a.m. – 7:00 a.m.	35	29.3333	5.6667	32.1111
July 13	7:00 a.m. – 8:00 a.m.	42	33.3333	8.6667	75.1111
July 13	8:00 a.m. – 9:00 a.m.	45	38.3333	6.6667	44.4444
July 13	9:00 a.m. – 10:00 a.m.	70	56	14.0000	196.0000
July 13	10:00 a.m. – 11:00 a.m.	72	64	8.0000	64.0000
July 13	11:00 a.m. – 12:00 p.m.	75	66.6667	8.3333	69.4444
July 13	12:00 p.m. – 1:00 p.m.	60	50	10.0000	100.0000
July 13	1:00 p.m. – 2:00 p.m.	45	40	5.0000	25.0000
July 13	2:00 p.m. – 3:00 p.m.	40	35	5.0000	25.0000
July 13	3:00 p.m. – 4:00 p.m.	25	25	0.0000	0.0000
July 13	4:00 p.m. – 5:00 p.m.	25	23.3333	1.6667	2.7778
July 13	5:00 p.m. – 6:00 p.m.	25	21.6667	3.3333	11.1111
				Total	1076

 

MSE = 1076 / 36 = 29.8889.

 

For the model from part (d) that includes both trend and seasonal effects:

 

Date	Hour	t	yt	Forecast		Forecast Error		Squared Forecast Error
July 11	6:00 a.m. – 7:00 a.m.	1	25	24.0833	0.9167		0.8403
July 11	7:00 a.m. – 8:00 a.m.	2	28	28.0833	-0.0833		0.0069
July 11	8:00 a.m. – 9:00 a.m.	3	35	33.0833	1.9167		3.6736
July 11	9:00 a.m. – 10:00 a.m.	4	50	50.7500	-0.7500		0.5625
July 11	10:00 a.m. – 11:00 a.m.	5	60	58.7500	1.2500		1.5625
July 11	11:00 a.m. – 12:00 p.m.	6	60	61.4167	-1.4167		2.0069
July 11	12:00 p.m. – 1:00 p.m.	7	40	44.7500	-4.7500		22.5625
July 11	1:00 p.m. – 2:00 p.m.	8	35	34.7500	0.2500		0.0625
July 11	2:00 p.m. – 3:00 p.m.	9	30	29.7500	0.2500		0.0625
July 11	3:00 p.m. – 4:00 p.m.	10	25	19.7500	5.2500		27.5625
July 11	4:00 p.m. – 5:00 p.m.	11	25	18.0833	6.9167		47.8403
July 11	5:00 p.m. – 6:00 p.m.	12	20	16.4167	3.5833		12.8403
July 12	6:00 a.m. – 7:00 a.m.	13	28	29.3333	-1.3333		1.7778
July 12	7:00 a.m. – 8:00 a.m.	14	30	33.3333	-3.3333		11.1111
July 12	8:00 a.m. – 9:00 a.m.	15	35	38.3333	-3.3333		11.1111
July 12	9:00 a.m. – 10:00 a.m.	16	48	56.0000	-8.0000		64.0000
July 12	10:00 a.m. – 11:00 a.m.	17	60	64.0000	-4.0000		16.0000
July 12	11:00 a.m. – 12:00 p.m.	18	65	66.6667	-1.6667		2.7778
July 12	12:00 p.m. – 1:00 p.m.	19	50	50.0000	0.0000		0.0000
July 12	1:00 p.m. – 2:00 p.m.	20	40	40.0000	0.0000		0.0000
July 12	2:00 p.m. – 3:00 p.m.	21	35	35.0000	0.0000		0.0000
July 12	3:00 p.m. – 4:00 p.m.	22	25	25.0000	0.0000		0.0000
July 12	4:00 p.m. – 5:00 p.m.	23	20	23.3333	-3.3333		11.1111
July 12	5:00 p.m. – 6:00 p.m.	24	20	21.6667	-1.6667		2.7778
July 13	6:00 a.m. – 7:00 a.m.	25	35	34.5833	0.4167		0.1736
July 13	7:00 a.m. – 8:00 a.m.	26	42	38.5833	3.4167		11.6736
July 13	8:00 a.m. – 9:00 a.m.	27	45	43.5833	1.4167		2.0069
July 13	9:00 a.m. – 10:00 a.m.	28	70	61.2500	8.7500		76.5625
July 13	10:00 a.m. – 11:00 a.m.	29	72	69.2500	2.7500		7.5625
July 13	11:00 a.m. – 12:00 p.m.	30	75	71.9167	3.0833		9.5069
July 13	12:00 p.m. – 1:00 p.m.	31	60	55.2500	4.7500		22.5625
July 13	1:00 p.m. – 2:00 p.m.	32	45	45.2500	-0.2500		0.0625
July 13	2:00 p.m. – 3:00 p.m.	33	40	40.2500	-0.2500		0.0625
July 13	3:00 p.m. – 4:00 p.m.	34	25	30.2500	-5.2500		27.5625
July 13	4:00 p.m. – 5:00 p.m.	35	25	28.5833	-3.5833		12.8403
July 13	5:00 p.m. – 6:00 p.m.	36	25	26.9167	-1.9167		3.6736
						Total		414.5000

 

MSE = 414.5000 / 36 = 11.5139.

 

The mean squared error for the model from part (d) that includes both trend and seasonal effects is somewhat smaller than the mean squared error for the model from part (b) that includes only seasonal effects. This supports our preliminary conclusions reached in review of the time series plot constructed in part (a) – these data show a linear trend with seasonality.

 

26. a.

 

 

The time series plot shows both a linear trend and seasonal effects.

 

 

1. After putting the data into the following format:

 

		Quarterly Dummy Variables
Year	Quarter	Qtr1	Qtr 2	Qtr3	yt
1	1	1	0	0	20
1	2	0	1	0	100
1	3	0	0	1	175
1	4	0	0	0	13
2	1	1	0	0	37
2	2	0	1	0	136
2	3	0	0	1	245
2	4	0	0	0	26
3	1	1	0	0	75
3	2	0	1	0	155
3	3	0	0	1	326
3	4	0	0	0	48
4	1	1	0	0	92
4	2	0	1	0	202
4	3	0	0	1	384
4	4	0	0	0	82
5	1	1	0	0	176
5	2	0	1	0	282
5	3	0	0	1	445
5	4	0	0	0	181

 

 

We can use the Excel Regression tool to find the regression model that accounts for the seasonal effects in the data. From the Excel output

 

 

The regression model that minimizes MSE for this time series is:

 

Revenue = 70.0 + 10.0 Qtr1 + 105 Qtr2 + 245 Qtr3

 

1. Based on the model in part (b), the quarterly forecasts for next year are as follows:

 

Quarter 1 forecast = 70.0 + 10.0(1) + 105(0) + 245(0) =  80

 

Quarter 2 forecast = 70.0 + 10.0(0) + 105(1) + 245(0) = 175

 

Quarter 3 forecast = 70.0 + 10.0(0) + 105(0) + 245(1) = 315

 

Quarter 4 forecast = 70.0 + 10.0(0) + 105(0) + 245(0) =   70

 

1. After putting the data into the following format:

 

		Quarterly Dummy Variables
Year	Quarter	Qtr1	Qtr2	Qtr3	t	yt
1	1	1	0	0	1	20
1	2	0	1	0	2	100
1	3	0	0	1	3	175
1	4	0	0	0	4	13
2	1	1	0	0	5	37
2	2	0	1	0	6	136
2	3	0	0	1	7	245
2	4	0	0	0	8	26
3	1	1	0	0	9	75
3	2	0	1	0	10	155
3	3	0	0	1	11	326
3	4	0	0	0	12	48
4	1	1	0	0	13	92
4	2	0	1	0	14	202
4	3	0	0	1	15	384
4	4	0	0	0	16	82
5	1	1	0	0	17	176
5	2	0	1	0	18	282
5	3	0	0	1	19	445
5	4	0	0	0	20	181

 

we can use the Excel Regression tool to find the regression model that accounts for the seasonal effects in the data. From the Excel output

 

 

the regression model that minimizes MSE for this time series is:

 

Revenue = – 70.1 + 45.025 Qtr1 + 128.35 Qtr2 + 256.675 Qtr3 + 11.675 t

 

1. Based on the model in part (b), the quarterly forecasts for next year are as follows:

 

Quarter 1 forecast = -70.1 + 45.025(1) + 128.35(0) + 256.675(0) + 11.675(21) = 220.1

 

Quarter 2 forecast = -70.1 + 45.025(0) + 128.35(1) + 256.675(0) + 11.675(22) = 315.1

 

Quarter 3 forecast = -70.1 + 45.025(0) + 128.35(0) + 256.675(1) + 11.675(23) = 455.1

 

Quarter 4 forecast = -70.1 + 45.025(0) + 128.35(0) + 256.675(0) + 11.675(24) = 210.1

 

1. For the model from part (b) that only includes seasonal effects:

 

Year	Quarter	yt	Forecast		Forecast Error	Squared Forecast Error
1	1	20	80	-60		3,600
1	2	100	175	-75		5,625
1	3	175	315	-140		19,600
1	4	13	70	-57		3,249
2	1	37	80	-43		1,849
2	2	136	175	-39		1,521
2	3	245	315	-70		4,900
2	4	26	70	-44		1,936
3	1	75	80	-5		25
3	2	155	175	-20		400
3	3	326	315	11		121
3	4	48	70	-22		484
		92	80	12		144
		202	175	27		729
		384	315	69		4,761
		82	70	12		144
		176	80	96		9216
		282	175	107		11,449
		445	315	130		16,900
		181	70	111		12,321
					Total	98,974

 

MSE = 98,974 / 20 = 4,948.7.

 

For the model from part (d) that includes both trend and seasonal effects:

 

Year	Quarter	t		yt		Forecast		Forecast Error		Squared Forecast Error
1	1	1	20		-13.40			33.40			1115.56
1	2	2	100		81.60			18.40			338.56
1	3	3	175		221.60			-46.60			2171.56
1	4	4	13		-23.40			36.40			1324.96
2	1	5	37		33.30			3.70			13.69
2	2	6	136		128.30			7.70			59.29
2	3	7	245		268.30			-23.30			542.89
2	4	8	26		23.30			2.70			7.29
3	1	9	75		80.00			-5.00			25.00
3	2	10	155		175.00			-20.00			400.00
3	3	11	326		315.00			11.00			121.00
3	4	12	48		70.00			-22.00			484.00
		13	92		126.70			-34.70			1204.09
		14	202		221.70			-19.70			388.09
		15	384		361.70			22.30			497.29
		16	82		116.70			-34.70			1204.09
		17	176		173.40			2.60			6.76
		18	282		268.40			13.60			184.96
		19	445		408.40			36.60			1339.56
		20	181		163.40			17.60			309.76
							Total		11,738.40

 

MSE = 11738.4 / 20 = 586.92.

 

The mean squared error for the model from part (d) that includes both trend and seasonal effects is much smaller than the mean squared error for the model from part (b) that includes only seasonal effects. This supports our preliminary conclusions reached in review of the time series plot constructed in part (a) – these data show a linear trend with seasonality.

 

27. a.

 

A plot of these data over time reveals little or no trend and a possible seasonal effect over the nine daily hours of operation.

 

The scatter chart of sales and temperature shows a modest positive relationship between outside temperature and hourly sales.

 

1. We can use the Excel Regression tool to find the regression model that accounts for the causal relationship between outside temperature and hourly sales in the data. From the Excel output

 

 

the regression model that minimizes MSE for this time series is:

 

Sales = 22.2641 + 0.3977 Temperature

 

Given these results, we estimate that hourly sales increase by approximately $0.40 for each one degree increase in outside temperature.

 

Based on this model, estimated hourly sales for today from 2:00 p.m. to 3:00 p.m. if the temperature at 2:00 p.m. is 93⁰ is:

 

Sales = 22.2641 + 0.3977 (93) = 59.2465 or $59.25.

 

1. After putting the data into the following format:

 

			Hourly Dummy Variables
Day	Hour	Temperature	Hour1	Hour2	Hour3	Hour4	Hour5	Hour6	Hour7	Hour8	Sales
Monday	1:00 P.M. to 2:00 P.M.	82	1	0	0	0	0	0	0	0	55.49
Monday	2:00 P.M. to 3:00 P.M.	83	0	1	0	0	0	0	0	0	61.89
Monday	3:00 P.M. to 4:00 P.M.	87	0	0	1	0	0	0	0	0	44.79
Monday	4:00 P.M. to 5:00 P.M.	93	0	0	0	1	0	0	0	0	68.62
Monday	5:00 P.M. to 6:00 P.M.	95	0	0	0	0	1	0	0	0	58.82
Monday	6:00 P.M. to 7:00 P.M.	96	0	0	0	0	0	1	0	0	51.85
Monday	7:00 P.M. to 8:00 P.M.	93	0	0	0	0	0	0	1	0	66.20
Monday	8:00 P.M. to 9:00 P.M.	89	0	0	0	0	0	0	0	1	52.89
Monday	9:00 P.M. to 10:00 P.M.	86	0	0	0	0	0	0	0	0	61.95
Tuesday	1:00 P.M. to 2:00 P.M.	86	1	0	0	0	0	0	0	0	59.55
Tuesday	2:00 P.M. to 3:00 P.M.	90	0	1	0	0	0	0	0	0	47.07
Tuesday	3:00 P.M. to 4:00 P.M.	92	0	0	1	0	0	0	0	0	53.29
Tuesday	4:00 P.M. to 5:00 P.M.	96	0	0	0	1	0	0	0	0	47.42
Tuesday	5:00 P.M. to 6:00 P.M.	99	0	0	0	0	1	0	0	0	60.52
Tuesday	6:00 P.M. to 7:00 P.M.	100	0	0	0	0	0	1	0	0	71.98
Tuesday	7:00 P.M. to 8:00 P.M.	97	0	0	0	0	0	0	1	0	55.71
Tuesday	8:00 P.M. to 9:00 P.M.	94	0	0	0	0	0	0	0	1	64.95
Tuesday	9:00 P.M. to 10:00 P.M.	93	0	0	0	0	0	0	0	0	60.12
Wednesday	1:00 P.M. to 2:00 P.M.	90	1	0	0	0	0	0	0	0	48.72
Wednesday	2:00 P.M. to 3:00 P.M.	94	0	1	0	0	0	0	0	0	66.41
Wednesday	3:00 P.M. to 4:00 P.M.	96	0	0	1	0	0	0	0	0	65.27
Wednesday	4:00 P.M. to 5:00 P.M.	98	0	0	0	1	0	0	0	0	54.76
Wednesday	5:00 P.M. to 6:00 P.M.	100	0	0	0	0	1	0	0	0	48.08
Wednesday	6:00 P.M. to 7:00 P.M.	103	0	0	0	0	0	1	0	0	53.59
Wednesday	7:00 P.M. to 8:00 P.M.	101	0	0	0	0	0	0	1	0	62.99
Wednesday	8:00 P.M. to 9:00 P.M.	98	0	0	0	0	0	0	0	1	66.35
Wednesday	9:00 P.M. to 10:00 P.M.	95	0	0	0	0	0	0	0	0	67.92
Thursday	1:00 P.M. to 2:00 P.M.	88	1	0	0	0	0	0	0	0	47.85
Thursday	2:00 P.M. to 3:00 P.M.	90	0	1	0	0	0	0	0	0	56.62
Thursday	3:00 P.M. to 4:00 P.M.	92	0	0	1	0	0	0	0	0	46.05
Thursday	4:00 P.M. to 5:00 P.M.	95	0	0	0	1	0	0	0	0	56.72
Thursday	5:00 P.M. to 6:00 P.M.	99	0	0	0	0	1	0	0	0	69.94
Thursday	6:00 P.M. to 7:00 P.M.	99	0	0	0	0	0	1	0	0	65.72
Thursday	7:00 P.M. to 8:00 P.M.	97	0	0	0	0	0	0	1	0	51.01
Thursday	8:00 P.M. to 9:00 P.M.	94	0	0	0	0	0	0	0	1	73.51
Thursday	9:00 P.M. to 10:00 P.M.	92	0	0	0	0	0	0	0	0	65.57
Friday	1:00 P.M. to 2:00 P.M.	90	1	0	0	0	0	0	0	0	63.26
Friday	2:00 P.M. to 3:00 P.M.	93	0	1	0	0	0	0	0	0	44.09
Friday	3:00 P.M. to 4:00 P.M.	96	0	0	1	0	0	0	0	0	65.69
Friday	4:00 P.M. to 5:00 P.M.	99	0	0	0	1	0	0	0	0	48.62
Friday	5:00 P.M. to 6:00 P.M.	103	0	0	0	0	1	0	0	0	68.16
Friday	6:00 P.M. to 7:00 P.M.	105	0	0	0	0	0	1	0	0	67.79
Friday	7:00 P.M. to 8:00 P.M.	104	0	0	0	0	0	0	1	0	62.79
Friday	8:00 P.M. to 9:00 P.M.	101	0	0	0	0	0	0	0	1	70.16
Friday	9:00 P.M. to 10:00 P.M.	99	0	0	0	0	0	0	0	0	68.05
Saturday	1:00 P.M. to 2:00 P.M.	88	1	0	0	0	0	0	0	0	60.43
Saturday	2:00 P.M. to 3:00 P.M.	90	0	1	0	0	0	0	0	0	63.78
Saturday	3:00 P.M. to 4:00 P.M.	92	0	0	1	0	0	0	0	0	67.8
Saturday	4:00 P.M. to 5:00 P.M.	95	0	0	0	1	0	0	0	0	69.53
Saturday	5:00 P.M. to 6:00 P.M.	97	0	0	0	0	1	0	0	0	61.8
Saturday	6:00 P.M. to 7:00 P.M.	99	0	0	0	0	0	1	0	0	57.92
Saturday	7:00 P.M. to 8:00 P.M.	98	0	0	0	0	0	0	1	0	53.97
Saturday	8:00 P.M. to 9:00 P.M.	95	0	0	0	0	0	0	0	1	71.55
Saturday	9:00 P.M. to 10:00 P.M.	92	0	0	0	0	0	0	0	0	57.93

 

We can use the Excel Regression tool to find the regression model that accounts for the seasonal effects and the causal relationship between outside temperature and hourly sales in the data. From the Excel output

 

 

the regression model that minimizes MSE for this time series is:

 

Sales = 21.8336 + 0.4498 Temperature – 5.2328 Hour1 – 5.6722 Hour2 – 6.2917 Hour3 – 7.4027 Hour4 – 5.0688 Hour5 – 5.4885 Hour6 – 7.2856 Hour7 + 1.9288 Hour8

 

Given these results, we estimate that hourly sales increase by approximately $0.45 for each one degree increase in outside temperature and that peak sales occur during Hour8 and Hour9 (8:00 p.m. to 9:00 p.m. and 9:00 p.m. to 10:00 p.m.). Recall that Hour9 is the baseline case here and all other dummy variables (except for Hour8) have negative coefficients.

 

Based on this model, estimated hourly sales for today from 2:00 p.m. to 3:00 p.m. (note that this is Hour 2) if the temperature at 2:00 p.m. is 93⁰ is:

 

Sales = 21.8336 + 0.4498 (93) – 5.6722 (1) = 57.9927 or $57.99.

 

3207. For the model from part (b) that only includes outside temperature as a causal variable, MSE = 3207.2217 / 54 = 59.3930.

 

For the model from part (c) that only includes both the seasonal effects (hour of operation) and outside temperature as a causal variable, MSE = 2705.9709 / 54 = 50.1106.

 

The mean squared error for the model from part (c) that includes both the seasonal effects (hour of operation)and outside temperature as a causal variable is only marginally smaller than the mean squared error for the model from part (b) that includes only outside temperature as a causal variable. This is likely because daily hour of operation and outside temperature are highly correlated; over the dates on which the data have been collected, outside temperature consistently rises throughout the day until 6:00 p.m. or 7:00 p.m., after which time the outside temperature consistently falls. Thus, outside temperature and hour of operation have similar relationships with hourly sales. When two models are approximately equal in effectiveness, we use the simpler model, which in this case is the model from part (b) that includes only the causal variable outside temperature.

 

28. a.

 

A plot of these data over time reveals little or no trend and a possible seasonal effect for daily gallon sales.

 

The scatter chart of the price charged by Donna for a gallon of regular grade gasoline and daily gallon sales at Donna’s station provides little evidence of a relationship.

 

The scatter plot the price charged by Donna’s competitor for a gallon of regular grade gasoline and daily gallon sales at Donna’s station provides little evidence of a relationship.

 

1. We can use the Excel Regression tool to find the regression model that accounts for the causal relationships between the daily gallon sales of regular grade gasoline by Donna’s station and the prices that she and her competitor charge for a gallon of regular grade gasoline. From the Excel output

 

 

the regression model that minimizes MSE for this time series is:

 

Sales = 1136.5838 – 817.2317 (Donna’s Price) + 768.1723 (Competitor’s Price)

 

Given these results, we estimate that Donna’s daily sales of regular grade gasoline decrease by approximately 817 gallons when Donna increases her price for a gallon of regular grade gasoline by $1.00 (that is, we estimate that Donna’s daily sales of regular grade gasoline decrease by approximately 8.17 gallons when Donna increases her price for a gallon of regular grade gasoline by one cent). We also estimate that Donna’s daily sales of regular grade gasoline increase by approximately 768 gallons when Donna’s competitor increases its price for a gallon of regular grade gasoline by $1.00 (that is, we estimate that Donna’s daily sales of regular grade gasoline increase by approximately 7.68 gallons when Donna’s competitor increases its price for a gallon of regular grade gasoline by one cent).

 

Based on this model, estimated sales for a day on which Donna is charging $3.50 for a gallon for regular grade gasoline and her competitor is charging $3.45 for a gallon of regular grade gasoline are

 

Sales = 1136.5838 – 817.2317 (3.50) + 768.1723 (3.45) = 926.4673 gallons.

 

1. After putting the data into the following format:

 

				Dummy Variables
Week	Day	Donna’s Price	Competitor’s Price	Mon	Tues	Wed	Thurs	Fri	Sat	t	Sales
1	Monday	3.48	3.48	1	0	0	0	0	0	1	994.33
1	Tuesday	3.52	3.53	0	1	0	0	0	0	2	917.53
1	Wednesday	3.48	3.46	0	0	1	0	0	0	3	920.26
1	Thursday	3.49	3.47	0	0	0	1	0	0	4	940.49
1	Friday	3.37	3.37	0	0	0	0	1	0	5	1026.05
1	Saturday	3.48	3.45	0	0	0	0	0	1	6	982.85
1	Sunday	3.40	3.38	0	0	0	0	0	0	7	868.82
2	Monday	3.59	3.56	1	0	0	0	0	0	8	1001.85
2	Tuesday	3.71	3.71	0	1	0	0	0	0	9	969.33
2	Wednesday	3.53	3.50	0	0	1	0	0	0	10	907.81
2	Thursday	3.41	3.45	0	0	0	1	0	0	11	965.42
2	Friday	3.58	3.61	0	0	0	0	1	0	12	988.87
2	Saturday	3.36	3.37	0	0	0	0	0	1	13	1092.33
2	Sunday	3.38	3.33	0	0	0	0	0	0	14	844.32
3	Monday	3.49	3.50	1	0	0	0	0	0	15	983.25
3	Tuesday	3.59	3.62	0	1	0	0	0	0	16	978.40
3	Wednesday	3.66	3.67	0	0	1	0	0	0	17	905.00
3	Thursday	3.58	3.58	0	0	0	1	0	0	18	926.72
3	Friday	3.29	3.27	0	0	0	0	1	0	19	990.25
3	Saturday	3.38	3.38	0	0	0	0	0	1	20	954.78
3	Sunday	3.46	3.47	0	0	0	0	0	0	21	905.12
4	Monday	3.40	3.40	1	0	0	0	0	0	22	1032.73
4	Tuesday	3.49	3.46	0	1	0	0	0	0	23	937.92
4	Wednesday	3.54	3.54	0	0	1	0	0	0	24	954.31
4	Thursday	3.61	3.59	0	0	0	1	0	0	25	967.57
4	Friday	3.69	3.71	0	0	0	0	1	0	26	987.20
4	Saturday	3.37	3.36	0	0	0	0	0	1	27	976.94
4	Sunday	3.38	3.33	0	0	0	0	0	0	28	824.32
5	Monday	3.59	3.62	1	0	0	0	0	0	29	985.53
5	Tuesday	3.50	3.45	0	1	0	0	0	0	30	1004.14
5	Wednesday	3.49	3.49	0	0	1	0	0	0	31	939.62
5	Thursday	3.46	3.44	0	0	0	1	0	0	32	952.71
5	Friday	3.67	3.69	0	0	0	0	1	0	33	1006.75
5	Saturday	3.34	3.35	0	0	0	0	0	1	34	967.02
5	Sunday	3.59	3.53	0	0	0	0	0	0	35	817.03
6	Monday	3.42	3.41	1	0	0	0	0	0	36	986.83
6	Tuesday	3.63	3.63	0	1	0	0	0	0	37	970.02
6	Wednesday	3.55	3.55	0	0	1	0	0	0	38	945.04
6	Thursday	3.60	3.60	0	0	0	1	0	0	39	934.36
6	Friday	3.63	3.64	0	0	0	0	1	0	40	998.38
6	Saturday	3.61	3.62	0	0	0	0	0	1	41	1031.23
6	Sunday	3.68	3.70	0	0	0	0	0	0	42	867.18
7	Monday	3.49	3.51	1	0	0	0	0	0	43	994.00
7	Tuesday	3.47	3.45	0	1	0	0	0	0	44	1005.76
7	Wednesday	3.55	3.54	0	0	1	0	0	0	45	959.69
7	Thursday	3.51	3.51	0	0	0	1	0	0	46	950.12
7	Friday	3.38	3.36	0	0	0	0	1	0	47	1063.42
7	Saturday	3.50	3.52	0	0	0	0	0	1	48	1003.03
7	Sunday	3.51	3.55	0	0	0	0	0	0	49	861.59
8	Monday	3.45	3.42	1	0	0	0	0	0	50	1004.04
8	Tuesday	3.51	3.51	0	1	0	0	0	0	51	953.20
8	Wednesday	3.66	3.66	0	0	1	0	0	0	52	996.66
8	Thursday	3.36	3.34	0	0	0	1	0	0	53	938.04
8	Friday	3.39	3.41	0	0	0	0	1	0	54	1072.94
8	Saturday	3.59	3.59	0	0	0	0	0	1	55	1042.82
8	Sunday	3.27	3.28	0	0	0	0	0	0	56	885.81

 

we can use the Excel Regression tool to find the regression model that accounts for the trend and seasonal effects in the data. From the Excel output

 

 

the regression model that minimizes MSE for this time series is:

 

Sales = 841.6613 + 141.9016938 Mon + 110.56045 Tues + 84.0126 Wed + 89.3325 Thurs + 158.5785 Fri + 147.6607 Sat + 0.5591 t

 

Given these results, we estimate that Donna’s daily sales of regular grade gasoline are increasing by approximately 0.56 gallons per day and her station’s peak sales days are Friday, Saturday, and Monday.

 

Based on this model, estimated sales for Tuesday of the first week after Donna collected her data (note that this is the second day of week nine, so t = 58) is:

 

Sales = 841.6613 + 110.56045 + 0.5591 (58) = 984.6485 gallons.

 

1. We can use the Excel Regression tool to find the regression model that accounts for the trend and seasonal effects as well as the causal relationships between the daily gallon sales of regular grade gasoline by Donna’s station and the prices that she and her competitor charge for a gallon of regular grade gasoline. From the Excel output

 

 

the regression model that minimizes MSE for this time series is:

 

Sales = 991.5095 – 237.3570 Donna’s Price + 194.9033 Competitor’s Price + 140.8731 Mon + 113.4747 Tues + 86.9137 Wed + 89.6734 Thurs + 156.3951 Fri + 144.7502 Sat + 0.5408 t

 

Given these results, we estimate that Donna’s daily sales of regular grade gasoline decrease by approximately 237 gallons when Donna increases her price for a gallon of regular grade gasoline by $1.00 (i.e., we estimate that Donna’s daily sales of regular grade gasoline decrease by approximately 2.37 gallons when Donna increases her price for a gallon of regular grade gasoline by one cent). We also estimate that Donna’s daily sales of regular grade gasoline increase by approximately 195 gallons when Donna’s competitor increases its price for a gallon of regular grade gasoline by $1.00 (that is, we estimate that Donna’s daily sales of regular grade gasoline increase by approximately 1.95 gallons when Donna’s competitor increases its price for a gallon of regular grade gasoline by one cent). We also estimate that Donna’s daily sales of regular grade gasoline are increasing by approximately 0.54 gallons per day and her station’s peak sales days are Friday, Saturday, and Monday.

 

Based on this model, estimated sales for Tuesday of the first week after Donna collected her data (note that this is the second day of week nine, so t – 58) day if Donna is charging $3.50 for a gallon for regular grade gasoline and her competitor is charging $3.45 for a gallon of regular grade gasoline is:

 

Sales = 991.5095 – 237.3570 (3.50) + 194.9033 (3.45) + 113.4747 + 0.5408 (58) = 978.0190 gallons.

 

169771. For the model from part (b) that only includes the causal variables price Donna charges for a gallon of regular grade gasoline and the price Donna’s competitor charges for a gallon of regular grade gasoline, MSE = 169771.374 / 56 = 3031.6317.

 

For the model from part (c) that only includes the trend and seasonal effects (day of operation), MSE = 41144.7411 / 56 = 734.7275.

 

For the model from part (d) that includes the trend, seasonal effects (day of operation), and the price of a gallon Donna charges for a gallon of regular grade gasoline and the price Donna’s competitor charges for a gallon of regular grade gasoline as causals variables, MSE = 39698.82917 / 56 = 708.9077.

 

The mean squared errors for the models from part (c) and part (d) are much smaller than the mean squared error for the model from part (b). Furthermore, the mean squared error for the models from parts (c) and (d) are essentially equal and the model in part (c) is simpler than the model in part (d). When two models are approximately equal in effectiveness, we select and use the simpler model, which in this case is the model from part (c) that includes only the causal relationship between outside temperature and hourly sales.