The Calculation Of The Seasonal Variation
Seasonal variations are upwards or downwards swings in the values of data around the trend line, repeated on a regular basis. To calculate the seasonal variation we need to find the difference between the observed data values and the trend values.
Return to your spreadsheet of TASK 2 and take the following steps:
Create a column headed 'Time series - Trend',
(Ts - T)
in the table below.
Then for each quarter with a Trend figure, subtract the trend from the original sales.
This column now contains the individual quarterly deviations from the Trend.
You will notice that there are some differences between deviations in the same quarter from year to year e.g. Quarter 4's deviation in 2003 was -3.4 but in 2004 it was -4. (Negative deviations indicate that sales were below Trend in that quarter).
This is because there are other factors at work in addition to seasonal ones and whilst we cannot isolate these influences we can reduce any extreme results by finding the average deviation from the trend for each of the four quarters.
Average seasonal variations
To calculate the average seasonal variations we need to create another table with headings as shown below.
Transfer the quarterly deviations to the relevant cell in the above table e.g. the first deviation of 9 was for quarter 3 of 2003, etc.
Total each quarter's deviations and divide by the number of observations to give the average deviation from trend for each quarter.
Note that the figures up to the total row have been rounded to 1 d.p. which is as accurate as the seasonal calculations can be. The 2 d.p. employed in the averages is a provisional "protection" of the first decimal place.
Add up the averages across the quarters and this should be equal to 0.
If it is not, (as in this case, when the sum of averages was 0.23) then use Step 5.
If the total does not equal 0 then calculate the error which must be shared out amongst the quarters.
Divide the error by 4. In this case
0.23/4 = 0.06 (to 2 d.p.)
Adjust the averages by subtracting this figure from each quarterly figure to make the total across equal to 0. Had the "error total" been negative, we would have to add the relevant adjustments to each quarter.
These adjusted averages then become the seasonal variations (in this case, quarterly variations).