In: Statistics and Probability
Consider the following time series:
Quarter | Year 1 | Year 2 | Year 3 |
1 | 69 | 66 | 60 |
2 | 45 | 37 | 47 |
3 | 55 | 57 | 50 |
4 | 83 | 86 | 77 |
(a) | Choose a time series plot. | |||||||||||||||
|
||||||||||||||||
- Select your answer -Graph (i)Graph (ii)Graph (iii)Graph (iv)Item 1 | ||||||||||||||||
What type of pattern exists in the data? Is there an indication of a seasonal pattern? | ||||||||||||||||
- Select your answer -Positive trend pattern, no seasonalityHorizontal pattern, no seasonalityNegative trend pattern, no seasonalityPositive trend pattern, with seasonalityHorizontal pattern, with seasonalityItem 2 | ||||||||||||||||
(b) | Use a multiple linear 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. For subtractive or negative numbers use a minus sign even if there is a + sign before the blank. (Example: -300) | |||||||||||||||
ŷ = + Qtr1 + Qtr2 + Qtr3 | ||||||||||||||||
(c) | Compute the quarterly forecasts for next year. | |||||||||||||||
|
a)
with seasonality Horizontal pattern
b)
year | Qurter | Y | Q1 | Q2 | Q3 |
1 | 1 | 69 | 1 | 0 | 0 |
1 | 2 | 45 | 0 | 1 | 0 |
1 | 3 | 55 | 0 | 0 | 1 |
1 | 4 | 83 | 0 | 0 | 0 |
2 | 1 | 66 | 1 | 0 | 0 |
2 | 2 | 37 | 0 | 1 | 0 |
2 | 3 | 57 | 0 | 0 | 1 |
2 | 4 | 86 | 0 | 0 | 0 |
3 | 1 | 60 | 1 | 0 | 0 |
3 | 2 | 47 | 0 | 1 | 0 |
3 | 3 | 50 | 0 | 0 | 1 |
3 | 4 | 77 | 0 | 0 | 0 |
Excel > Data > Data Analysis > Regression
SUMMARY OUTPUT | ||||||||
Regression Statistics | ||||||||
Multiple R | 0.968245837 | |||||||
R Square | 0.9375 | |||||||
Adjusted R Square | 0.9140625 | |||||||
Standard Error | 4.55521679 | |||||||
Observations | 12 | |||||||
ANOVA | ||||||||
df | SS | MS | F | Significance F | ||||
Regression | 3 | 2490 | 830 | 40 | 3.65996E-05 | |||
Residual | 8 | 166 | 20.75 | |||||
Total | 11 | 2656 | ||||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
Intercept | 82 | 2.62995564 | 31.17923313 | 1.21753E-09 | 75.93531142 | 88.06468858 | 75.93531142 | 88.06468858 |
Q1 | -17 | 3.719318934 | -4.570729292 | 0.001824043 | -25.57676484 | -8.423235158 | -25.57676484 | -8.423235158 |
Q2 | -39 | 3.719318934 | -10.48579073 | 5.95026E-06 | -47.57676484 | -30.42323516 | -47.57676484 | -30.42323516 |
Q3 | -28 | 3.719318934 | -7.528260011 | 6.74509E-05 | -36.57676484 | -19.42323516 | -36.57676484 | -19.42323516 |
Y = 82-17*Q1-39*Q2-28*Q3
c)
Year | Quarter | Ft = 82-17*Q1-39*Q2-28*Q3 | Q1 | Q2 | Q3 |
4 | 1 | 65 | 1 | 0 | 0 |
4 | 2 | 43 | 0 | 1 | 0 |
4 | 3 | 54 | 0 | 0 | 1 |
4 | 4 | 82 | 0 | 0 | 0 |