Question

Consider the following time series:

Quarter	Year 1	Year 2	Year 3
1	74	71	65
2	44	36	46
3	61	63	56
4	78	81	72

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.
	Year Quarter Ft 4 1 4 2 4 3 4 4

Answer 1

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

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

Year	Quarter	Qtr1	Qtr2	Qtr3	Ft
4	1	1	0	0	70
	2	0	1	0	42
	3	0	0	1	60
	4	0	0	0	77