Question

Perform an analysis of the data below.

1. A time-series plot. Comment on the underlying pattern in the time series.
2. Using the dummy variable approach, build the forecasting model. Show your final time series table and regression equation.
3. Using your model, forecast sales for January through December of the fourth year.
4. Assume that January sales for the fourth year turn out to be $295,000. What was your forecast error? What can you do to resolve the owner's uncertainty in the forecasting procedure?

Time Period	Sales ($1000s)
Jan Year1	242
Feb Year1	235
March Year1	232
April Year1	178
May Year1	184
June Year1	140
July Year1	145
Aug Year1	152
Sept Year1	110
Oct Year1	130
Nov Year1	152
Dec Year1	206
Jan Year2	263
Feb Year2	238
March Year2	247
April Year2	193
May Year2	193
June Year2	149
July Year2	157
Aug Year2	161
Sept Year2	122
Oct Year2	130
Nov Year2	167
Dec Year2	230
Jan Year3	282
Feb Year3	255
March Year3	265
April Year3	205
May Year3	210
June Year3	160
July Year3	166
Aug Year3	174
Sept Year3	126
Oct Year3	148
Nov Year3	173
Dec Year3	235

Answer 1

Step 1: Create dummy variables for all the months as follows. I have shown few rows and columns. For example, copy the formula incolumn D into all the columns between D_1 to D_12. Also these formulae should be copied for all the rows of 3 years.

Step 2: Run the regression. I have included all the 12 dummy variables and excluded the intercept. One may include 11 dummy variables along with the intercept. The regression results are as below:

Step 3: Extend the dummy variables for 4th year using the same formula used for dummy variable creation as below.

Step 4: Calculate forecast values for these 12 months of 4th year using the regression coefficients. Note that I have populated the values of the coefficients in row 1. Then using these coefficient values and the values of the dummy variables, the forecast series is calculated. The error series is then calculated as the difference between the actual and the preducted values. Finally, the % error is calculated as the percent deviation of the prediction from the actual. The first table below shows the calculated values, whereas the next table presents the formula view of the calculations.

Step 5: The forecast error for Jan (4th year) will also be calculated as 295 - 262.333 = 32.667, where 295 thousand is the actual value, 262.333 is the forecast value. Then the % error = 32.667/295 = 11.1%. That is the error is 11.1% of the actual.

-- Owner's uncertainty can be resolved by capturing information about the owners and use it in the above model as explanatory variables. This will help to resolve such uncertainties.