Question

4. Consider the following time series:

Quarter	Year 1	Year 2	Year 3
1	80	74	65
2	69	61	51
3	48	50	43
4	68	71	82

a. Construct a time-series plot. What type of pattern exists in the data? Is there an indication of a seasonal pattern? (10 points)

b. Use 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 else; Qtr2 = 1 if quarter 2, 0 else; Qtr3 = 1 if quarter 3, 0 else. (20 points)

c. Compute the quarterly forecasts for next year. (10 points)

Answer 1

a. Time series plot.

Put the data in excel as shown below and create a new column called Year Quarter.

Highlight the data and go to insert and insert a line chart as shown
The need graph will be generated.

Yes, the pattern indicates a seasonal pattern.

b. Regression.

Step 1 : Put the data in excel as shown.
We need to create dummy variable for quarters.
create a variable q1 and put it as 1 if quarter is 1 or else 0
Similarly we do it for Q2 and Q3

Step 2 : go to DATA -> data analysis -> regression

Step 3 : Input the values as shown.

Step 4 : The output will be generated as follows.

The values highlighted are in yellow are the cofficient of the variables.

Hence the regression equation is

y = 73.67 - 0.67 *Q1 - 13.33 *Q2 -26.67 *Q3

c. Forcast for next year

For Q1, put Q1 = 1 and others 0
y = 73.67 -0.67   Q1 -13.33 Q2 -26.67 Q3
y = 73.67 -0.67*(1) -13.33 *(0) -26.67 *(0)=73

For Q2, put Q2 = 1 and others 0

y = 73.67 -0.67   Q1 -13.33 Q2 -26.67 Q3
y = 73.67 -0.67*(0) -13.33 *(1) -26.67 *(0)=60.34

For Q3, put Q3 = 1 and others 0
y = 73.67 -0.67   Q1 -13.33 Q2 -26.67 Q3
y = 73.67 -0.67*(0) -13.33 *(0) -26.67 *(1)=47

For Q4, put all as 0
y = 73.67 -0.67   Q1 -13.33 Q2 -26.67 Q3
y = 73.67 -0.67*(0) -13.33 *(0) -26.67 *(0) = 73.67