Question

Consider the following quarterly time series.

Quarter	Year 1	Year 2	Year 3
1	923	1,112	1,243
2	1,056	1,156	1,301
3	1,124	1,124	1,254
4	992	1,078	1,198

a. Construct a time series plot. What type of pattern exists in the data?

b. 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.

c. Compute the quarterly forecasts for next year based on the model developed in part (b).

(that is for all the four quarters next year)

Answer 1

a.

Time series plot

The time series plot is obtained in excel by first arranging the data values in a single column. The screenshot is shown below,

Select all the data values > INSERT > Recommended Charts > X Y (Scatter) > Scatter with Smooth Lines and Markers > OK. The screenshot of the chart is shown below,

From the plot, we can observe that there is a seasonal pattern with an upward trend in the data values.

b)

Let the dummy variables Qtr1, Qtr2 and Qtr3 and are defined as

The regression analysis is done in excel by following steps

Step 1: Write the data values in excel. The screenshot is shown below,

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

Step 3: Select Input Y Range: 'Y' column, Input X Range: 'Qtr1, Qtr2 and Qtr3' column then OK. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

The regression equation is.

c)

Year	Quarter	Qtr1	Qtr2	Qtr3	Forecast
4	1	1	0	0	1092.667
	2	0	1	0	1171
	3	0	0	1	1167.333
	4	0	0	0	1089.333