Question

Consider the following time series data.

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

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

   
   

   The time series plot indicates a linear trend and a seasonal pattern

2. Show the four-quarter and centered moving average values for this time series.

    

3. Compute seasonal indexes and adjusted seasonal indexes for the four quarters.

Answer 1

The given data is as follows:

To plot the graph in excel for the same, follow the steps explained below:
1. Type the data with the right column headings and row headings.

2. Go to the "Insert" option in the toolbar, select the "Line" graph option and select the type of line chart you want to plot.

3. Right-click on the chart that appears on the sheet and click on the "Select Data" option.

4. Click on the "Chart Data Range" box of the dialog box that appears.

5. Select the data required and you will observe some lines appearing in the graph. Click OK to confirm the same.

The chart plotted for the above data is as below:

Now we need to calculate the seasonal index.

The seasonal index is calculated as follows:
1. Calculate the mean (sum up all the values for that particular month and divide by the number of years)  of the data for each month.

2. Now using the average values obtained from the data, calculate the grand mean of these monthly means.

3. To find the seasonal index for each of the month divides the individual mean of that particular month by the grand mean.

The calculations of the step by step procedure explained above is as follows:

Now to calculate the forecast for required months, we need to deseasonalize the demand by dividing the seasonal index of that particular quarter with the demand in that particular quarter of any year. Note that for all the years any particular quarter has the same seasonal index. The values for the same is as shown below:

After deseasonalizing the demand we need to find the trendline equation. And this is done as follows:

Starting from the first year number the periods as 1,2,3,4,5.... so on and so forth. This is our independent variable (X) and the deseasonalized demand is the dependent variable (Y)

Solving the equations below the value of a and b required for the trend line is as calculated. The equations are:

∑Y = n*a + b*∑X
∑XY = a*∑X + b*∑X^2

Trend line or regression line = Yc = a*x + b
where, Yc = Forecasted value

The other required values i.e ∑Y, ∑X, ∑(X*Y), *∑X, ∑(X*X) are calculated and as are as shown below:

The value of a and b after solving the above-mentioned equations and trendline equation is as follows:

a = 0.392

b = 2.617

The trendline equations is as follows:

y = 0.392 * x + 2.617

Now for the fourth year forecast, we need to substitute the various values of x starting from 13, 14, 15, 16 and find the corresponding demand. This is done because the periods for the fourth year will continue as previously. Once the forecasted demand using the trend line equation is found, we need to multiply the corresponding month's forecasted value with the corresponding seasonal index. The calculations of the same are as follows: