Question

Consider the following time series data.

Quarter	Year 1	Year 2	Year 3
1	2	5	7
2	0	2	6
3	5	8	10
4	5	8	10

(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.
	If required, round your answers to three decimal places. For subtractive or negative numbers use a minus sign even if there is a + sign before the blank. (Example: -300) If the constant is "1" it must be entered in the box. Do not round intermediate calculation.
	ŷ =   +   Qtr1 +   Qtr2 +   Qtr3
(c)	Compute the quarterly forecasts for next year based on the model you developed in part (b).
	If required, round your answers to three decimal places. Do not round intermediate calculation.
	Year Quarter Ft 4 1 4 2 4 3 4 4
(d)	Use a multiple regression model to develop an equation to account for trend and seasonal effects in the data. Use the dummy variables you developed in part (b) to capture seasonal effects 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.
	If required, round your answers to three decimal places. For subtractive or negative numbers use a minus sign even if there is a + sign before the blank. (Example: -300)
	ŷ =   +  Qtr1 +  Qtr2 +  Qtr3 +   t
(e)	Compute the quarterly forecasts for next year based on the model you developed in part (d).
	Do not round your interim computations and round your final answer to three decimal places.
	Year Quarter Period Ft 4 1 13 4 2 14 4 3 15 4 4 16
(f)	Is the model you developed in part (b) or the model you developed in part (d) more effective?
	If required, round your intermediate calculations and final answer to three decimal places.

Answer 1

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)

The seasonal forecast for year 4 is obtained by putting the value of the independent variable in this formula

Year	Quarter	Qtr1	Qtr2	Qtr3	Ft
4	1	1	0	0	4.667
	2	0	1	0	2.667
	3	0	0	1	7.667
	4	0	0	0	7.667

d)

Now adding a trend variable t

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.

Step 3: Select Input Y Range: 'Y' column, Input X Range: 'Qtr1, Qtr2, Qtr3 and t' column then OK.

The result is obtained. The screenshot is shown below,

The regression equation is.

e)

The seasonal forecast for year 4 is obtained by putting the value of the independent variable in this formula

Year	Quarter	Qtr1	Qtr2	Qtr3	t	Ft
4	1	1	0	0	13	9.917
	2	0	1	0	14	7.917
	3	0	0	1	15	12.917
	4	0	0	0	16	12.917

f)

To compare the accuracy of the two models, the Mean Square Error (MSE) is obtained using the following formula,

For seasonal forecast (b)

Y
2	4.667	7.111
0	2.667	7.111
5	7.667	7.111
5	7.667	7.111
5	4.667	0.111
2	2.667	0.444
8	7.667	0.111
8	7.667	0.111
7	4.667	5.444
6	2.667	11.111
10	7.667	5.444
10	7.667	5.444
	Sum	56.667

For seasonal with trend forecast (d)

Y
2	2.042	0.002
0	0.042	0.002
5	5.042	0.002
5	5.042	0.002
5	4.667	0.111
2	2.667	0.444
8	7.667	0.111
8	7.667	0.111
7	7.292	0.085
6	5.292	0.502
10	10.292	0.085
10	10.292	0.085
		1.542

We can see that the MSE for seasonal trend forecast in part (d) is much smaller than the seasonal forecast in part (b). Hence the model developed in part (d) is more accurate.