Question

The quarterly sales (number of books sold) over the past 3 years are as shown below.

Year	Quarter	Period	Sales
1	1	1	1700
1	2	2	1000
1	3	3	2700
1	4	4	2500
2	1	5	1800
2	2	6	900
2	3	7	2900
2	4	8	2400
3	1	9	1800
3	2	10	1100
3	3	11	2900
3	4	12	2600

1. Construct a time series plot for this data.

Paste the time series plot

2. What type of pattern exists in the plot?

3. You will use a regression model with dummy variables to develop an equation to account for seasonal effects in the data (here, assume there is no trend)

Set up the dummy variables in excel, run the regression model with dummy variables

Paste output of regression model with dummy variables here

4. Write the equation of the regression with dummy variables using the regression coefficients in your regression output.

5. Based on your model, what is the forecast for quarter1, quarter2, quarter3, quarter4 for year 4? (hint: use the regression equation for your calculations)

6. Since the data has a seasonality with a linear trend, construct another regression model with dummy variables that also takes into consideration the linear trend. That means, include the periods as independent variables when running your regression model. Dummy variables will also be independent variable while sales will be the dependent variable.

Insert the regression output for the model here

7. Using the coefficients or parameters generated in your regression output, construct a regression equation with dummy variables that takes into consideration the linear trend.

8. Based on the seasonal effects in the data and linear trend, computer the quarterly forecast for the 4^th year. That is, calculate sales for quarter1, quarter2, quarter3, quarter4 for year 4.

Answer 1

a)

Time series plot

The time series plot is obtained in excel by using the columns period and sales. The screenshot is shown below,

b)

There is a seasonal pattern of sales with an upward trend.

c)

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,

d)

The regression equation is.

e)

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

Year	Quarter	Qtr1	Qtr2	Qtr3	Forecast
4	1	1	0	0	1766.667
	2	0	1	0	1000
	3	0	0	1	2833.333
	4	0	0	0	2500

f)

Now adding a trend variable Period, 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 Period' column then OK. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

g)

h)

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

Year	Quarter	Period, t	Qtr1	Qtr2	Qtr3	Forecast
4	1	13	1	0	0	1891.667
	2	14	0	1	0	1125
	3	15	0	0	1	2958.333
	4	16	0	0	0	2625