Question

7.

1. Construct a time series plot. Comment on what type of pattern exists in the data.
2. Use a regression model with dummy variables as follow to explain sales: Qtr1 = 1 if quarter 1, 0 otherwise; Qtr2 = 1 if quarter 2, 0 otherwise; Qtr3 = 1 if quarter 3, 0 otherwise. Write out the model that you’ve estimated in equation form using the values of your estimates. Explain, in a sentence or two, what this model tells us.
3. Use a new, simple linear regression model with “t” as the only predictor where t = 1 for the 1^st quarter of year 1, t = 2 for the 2^nd quarter of year 1,…, t = 12 for the 4^th quarter of year 3. Write out the model that you’ve estimated in equation form using your values of the estimates. Explain, in a sentence or two, what this model tells us.
4. Use a new regression model with BOTH the dummy variable approach you created in part “b” and the time predictor, “t” that you created in part “c.” Write out the model that you’ve estimated in equation form. Explain, in a sentence or two, what this model tells us.
5. Compute (or take from Excel) the mean squared error (MSE) from each of your models from “b”, “c”, and “d.” (Hint: MSE is the same as MS Residual on the Excel output). Comment, in a sentence or two, on which of the models appears the best and why. Does this match up with the adjusted R^2 values of the models?
6. Using the model that you identified as the “best” in “e”, compute the quarterly forecasts for the year AFTER next (skip year 4, and tell me the forecasts for Q1, Q2, Q3, and Q4 of year 5). If you were going to tell the textbook store manager your forecasts, would you tell him/her exactly these numbers? Might there be a reason to explain your numbers slightly? What would you tell the manager?

Year	Quarter	Sales
1	1	2690
1	2	1940
1	3	3625
1	4	3500
2	1	1800
2	2	900
2	3	2900
2	4	2360
3	1	1550
3	2	800
3	3	2630
3	4	2315

Answer 1

ANSWER A:

The time series plot for the given data looks as shown below:

The drop in sales in periods 2,6,10 (Quarter 2) and the surge in sales in periods 3,7,11 (Quarter 3) indicates the presence of Seasonality.

Also, there appears to be linear downward trend in overall sales (as shown by the dotted line) and as the seasonal patterns are not appearing to be a function of the downward trend, the seasonality can be considered additive

So, the series is a Negative linear series with Additive seasonality

ANSWER B:

The dummy variables & the model based on defining the predictor variables as required (dummy variables for quarters) is shown below:

The equation of the model is Sales = 2725 + (-712*Qtr1) + (-1512*Qtr2) + (327*Qtr3)

The negative co-efficients of Qtr 1 & Qtr 2 's variables suggest that they contribute in bringing down the yearly sales and the positive co-efficient of Qtr 3 's variable suggests that this quarter contributes positively to yearly sales. p-value indicates only Qtr 2 variable is significant at 95% level. R² of 0.67 suggests the Qtr variables explain 67% of variability in the Sales. While it captures the seasonal trends, it doesn't account for the yearly downward trend as we don't model a variable to capture that.

ANSWER C:

The table with variable 't' based on given definition and the model predicting sales based on 't' is presented below:

The equation of the model is Sales = 2791 + (-83*t)

This model doesn't fit the Sales well as it is a straight line equation that doesn't capture the seasonal portion of the Sales data. It only accounts for the negative linear trend seen in Sales. The R² value of the model being as low as 0.11 also vindicates the earlier statement.

ANSWER D:

Combining the Qtr dummy variables and the 't' variable of the models above, the table of data and the model predictions are shown below:

The equation of the model is Sales = 3840 + (-1130*Qtr1) + (-1790*Qtr2) + (187*Qtr3) + (-139*t)

This model fits the data the best as it has variables that explain both the quarterly seasonality and the yearly trend seen in the Sales data. The high value of R² (0.91) also suggests that.

ANSWER E:

Mean squared error (MS Residual) of models 1,2,3 (from b, c, d sections) are 368325, 795659 and 65736 respectively. The lower the error the better the model is, and is in alignment with the Adj R² values (model with lower error has higher Adj R² )

A plot comparing the fits of models 1,2,3 is shown below:

ANSWER F:

Based on the best model's equation (model 3), the prediction for next year and year after next are shown below (extended yellow line):

This clearly follows the trend of 2 years and expects sales to drop at same rate (which explains the fall below 0 in t=18). The model fitted existing data better but using 12 quarter's data to forecast 8 more quarters is not recommendable as the projections may be too focused and may not be realistic.

Best approach would be to use this to project sales for next 2 quarters, and after those, use the actual data to forecast for 2 more quarters and so on.