topic badge

5.03 Seasonal adjustments and deseasonalising data

Lesson

Seasonal indices

The seasonal index (also called seasonal effect or seasonal component) is a measure of how a particular season compares on average to the mean of the cycle. The graph below shows raw seasonal data as well as the data smoothed with a moving average. From the green line we can see that December is always a peak season above the smoothed data line and March is always a low season below the smoothed data line. The seasonal index is a number that can be given as a percentage or as a decimal. The seasonal index for December in this case is $106.56%$106.56% which means figures for December are $1.0656$1.0656 times higher than the average (or $6.56%$6.56% above the cycle mean).

We use seasonal indices for two purposes.

  • They can be used to smooth data by a process called deseasonalising.
  • They can be used to help with predicting future scores with time series data. Once an initial predicted value from a smoothed line is calculated, the seasonal index is used to correct that value up or down depending on which season we are predicting for.

 

Calculating the seasonal index using the average percentage method

  1. Calculate the mean for each cycle. 
  2. Express each piece of raw data as a proportion of the relative cycle mean. Written as a decimal or percentage.
  3. Calculate the average proportion for each season. This is the seasonal index for each season.

 

 

Exploration

Step 1: Calculating the mean for each cycle

We'll use the data from the graph above to illustrate each of the steps involved. There are $4$4 time periods in each year. From the graph and the table below we can determine that there are $4$4 seasons in a cycle.

First we need to calculate the mean for the 2012, 2013 and 2014 cycles.

The first and last are already done for us, we need to calculate the mean for 2013 cycle.

$\text{2013 mean}=\frac{427+463+484+494}{4}=467$2013 mean=427+463+484+4944=467

 

Step 2: Expressing the raw data as a proportion of the cycle mean

To calculate $X$X, $Y$Y and $Z$Z in the table, we need to calculate each of their corresponding raw data values as a proportion of the mean for the cycle (or year) that they belong to.

$X=\frac{480}{490.75}=0.9781$X=480490.75=0.9781 We could also write $97.81%$97.81%. This is less than $100%$100% and indicates that this raw data value is $2.19%$2.19% below the cycle mean.

$Y=\frac{427}{467}=0.9143$Y=427467=0.9143 We could also write $91.43%$91.43%. This is less than $100%$100% and indicates that this raw data value is $8.57%$8.57% below the cycle mean.

$Z=\frac{499}{462.25}=1.0795$Z=499462.25=1.0795 We could also write $107.95%$107.95%. This is more than $100%$100% and indicates that this raw data value is $7.95%$7.95% above the cycle mean.

Step 3: Calculating the average proportion for each season

We will now calculate the average proportion for each season our data. This is known as the seasonal index or seasonal component.

The seasonal component for June is $98.97%$98.97% and for December is $106.56%$106.56%.

To determine the seasonal index for March we calculate the average seasonal change for each March quarter. The seasonal index for March is then:

$\frac{0.9598+0.9143+0.9194}{3}=0.9312=93.12%$0.9598+0.9143+0.91943=0.9312=93.12%

We can similarly calculate the seasonal index for September, which is:

$\frac{1.0025+1.0364+1.0016}{3}=1.0135=101.35%$1.0025+1.0364+1.00163=1.0135=101.35%

Summary of seasonal indices

Season Seasonal Index Meaning
March $93.12%$93.12% This season is on average $6.88%$6.88% below the year mean.
June $98.97%$98.97% This season is on average $1.03%$1.03% below the year mean.
September $101.35%$101.35% This season is on average $1.35%$1.35% above the year mean.
December $106.56%$106.56% This season is on average $6.56%$6.56% above the year mean.

 

Note that the average for the four seasons will be $100%$100% or the sum of the four seasons will be $400$400. This is because seasonal indices are calculated as a proportion of the average, so the average of these proportions will always return the true average, the mean, which is represented as $100%$100%. When working with decimal values for seasonal indices, we have the same relationship, except instead of $100%$100% we convert to the decimal $1.00$1.00.

This also means that if we know all the seasonal indices of a season except for one, we can calculate the missing score using an average of $100%$100%.

 

Practice questions

Question 1

The local police station records the number of speeding fines issued each quarter.

The table alongside has the data for each quarter from 2016 to 2018.

Time Period

Data

Percentage of yearly mean

March 2016 $105$105 $106.06%$106.06%
June 2016 $91$91 $x$x
September 2016 $101$101 $102.02%$102.02%
December 2016 $99$99 $100%$100%
March 2017 $101$101 $y$y
June 2017 $83$83 $89.01%$89.01%
September 2017 $96$96 $102.95%$102.95%
December 2017 $93$93 $99.73%$99.73%
March 2018 $99$99 $108.2%$108.2%
June 2018 $82$82 $89.62%$89.62%
September 2018 $94$94 $102.73%$102.73%
December 2018 $91$91 $z$z
  1. For 2016, 2017 and 2018, calculate the mean number of speeding tickets issued in each time period.

    Give your answers to two decimal places.

    Year 2016 2017 2018
    Mean $\editable{}$ $\editable{}$ $\editable{}$
  2. Use your answers from part (a) to calculate the value of $x$x.

    Give your answer to two decimal places.

  3. Use your answers from part (a) to calculate the value of $y$y.

    Give your answer to two decimal places.

  4. Use your answers from part (a) to calculate the value of $z$z.

    Give your answer to two decimal places.

Question 2

A local cafe stays open for $5$5 days a week in the city, and records how many cappuccinos they sell on Friday, Saturday and Sunday for four weeks.

The table alongside has the data for the four weeks.

Week

Day

Data

Percentage of weekly mean

Week $1$1 Friday $25$25 $107.14%$107.14%
Saturday $24$24 $x$x
Sunday $21$21 $90%$90%
Week $2$2 Friday $12$12 $37.89%$37.89%
Saturday $36$36 $y$y
Sunday $47$47 $148.42%$148.42%
Week $3$3 Friday $16$16 $58.54%$58.54%
Saturday $12$12 $43.9%$43.9%
Sunday $54$54 $197.56%$197.56%
Week $4$4 Friday $12$12 $39.56%$39.56%
Saturday $23$23 $75.82%$75.82%
Sunday $56$56 $z$z
  1. Calculate the mean number of cappuccinos sold during each week.

    Give your answers to two decimal places.

    Week Week $1$1

    Week $2$2

    Week $3$3

    Week $4$4
    Mean $\editable{}$ $\editable{}$ $\editable{}$ $\editable{}$

     

  2. Use your answers from part (a) to calculate the value of $x$x.

    Give your answer to two decimal places.

  3. Use your answers from part (a) to calculate the value of $y$y.

    Give your answer to two decimal places.

  4. Use your answers from part (a) to calculate the value of $z$z.

    Give your answer to two decimal places.

  5. Calculate the seasonal index for each day.

    Give your answers to four decimal places.

    Day Friday Saturday Sunday
    Seasonal index $\editable{}$ $\editable{}$ $\editable{}$

     

  6. The seasonal index for Friday tells us that coffee sales on Friday tend to be

    Around $39%$39% greater than the average sales over the three days.

    A

    Around $61%$61% less than the average sales over the three days.

    B

    Around $39%$39% less than the average sales over the three days.

     

    C

    Around $61%$61% greater than the average sales over the three days.

    D

 

How we use the seasonal index

We use the seasonal index when predicting from time series data. The data is first smoothed either using by a moving average or by deseasonalising (see below). We then calculate a predicted value using the equation of the least-squares regression line from the smoothed data. We then use the seasonal index to adjust the predicted value so that it takes the particular season into consideration. In the above example, a predicted value for December will be adjusted to be higher whereas a predicted value for March will be adjusted lower.

 

Deseasonalising the raw data

Deseasonalising data is also called making seasonal adjustments. The seasonal indices are used to smooth or deseasonalise our data in a similar way that a moving average is used. Both methods smooth the data as shown in the graphs below.

Note that the $7$7 point moving average line is smoother than the deseasonalised data line, but both methods are used in the real world to assist with predicting from time series data.

Deseasonalised data formula

$\text{Deseasonalised data}=\frac{\text{Raw value}}{\text{Seasonal index}}$Deseasonalised data=Raw valueSeasonal index

Note that the seasonal index should be a decimal when using this formula.

 

Exploration

Returning to the exploration above, this means all March raw data should be divided by $0.9312$0.9312. March is the lowest season so dividing by a number less than one will increase the scores.

All June raw data should be divided by $0.9897$0.9897. June is also a below average cycle season so again this will increase the scores.

All September raw data should be divided by $1.0135$1.0135. September is above the cycle average season so dividing by a number greater than $1$1 will lower the scores.

All December raw data should be divided by $1.0656$1.0656. December is the highest season so again this will lower the scores.

So dividing each raw score by the seasonal index, we get the following deseasonalised data.

$X=\frac{492}{1.0135}=485.4465$X=4921.0135=485.4465

$Y=\frac{463}{0.9897}=467.8185$Y=4630.9897=467.8185

$Z=\frac{425}{0.9312}=456.4003$Z=4250.9312=456.4003

 

Practice questions

Question 3

The table shows the number of new gaming apps released each quarter, from the beginning of 2016 through to the end of 2018.

Time Period

Data

Proportion of yearly mean

March 2016 $43$43 $0.77$0.77
June 2016 $45$45 $0.81$0.81
September 2016 $54$54 $0.97$0.97
December 2016 $81$81 $1.45$1.45
March 2017 $51$51 $0.82$0.82
June 2017 $50$50 $0.8$0.8
September 2017 $60$60 $0.96$0.96
December 2017 $89$89 $1.42$1.42
March 2018 $57$57 $0.81$0.81
June 2018 $52$52 $0.74$0.74
September 2018 $69$69 $0.98$0.98
December 2018 $103$103 $1.47$1.47
  1. Calculate the seasonal component for the first quarter, correct to two decimal places.

  2. Deseasonalise the data for March 2018. Give your answer to two decimal places.

  3. Calculate the seasonal component for the third quarter. Give your answer to two decimal places.

  4. Calculate the seasonal component for the fourth quarter. Give your answer to two decimal places.

  5. Seasonally adjust the data for December 2017. Give your answer to two decimal places.

Question 4

Every four months Neil records the growth of his bean plant (starting with a new plant every year).

The data provided is from the beginning of 2016 to the end of 2019.

Time Period

Growth (in cm)

Proportion of yearly mean

April 2016 $95.6$95.6 $0.99$0.99
August 2016 $106.7$106.7 $a$a
December 2016 $87.8$87.8 $0.91$0.91
April 2017 $c$c $0.99$0.99
August 2017 $101.2$101.2 $1.1$1.1
December 2017 $84.1$84.1 $0.91$0.91
April 2018 $86.3$86.3 $1.01$1.01
August 2018 $93.6$93.6 $1.09$1.09
December 2018 $77.3$77.3 $0.9$0.9
April 2019 $76.1$76.1 $0.99$0.99
August 2019 $83.4$83.4 $b$b
December 2019 $71.8$71.8 $0.93$0.93
  1. Calculate the value of $a$a in the table. Give your answer to two decimal places.

  2. Calculate the value of $b$b in the table. Give your answer to two decimal places.

  3. If the mean for 2017 is $92.2$92.2, calculate the value of $c$c.

    Write each line of working as an equation, and give your answer to two decimal places.

  4. Calculate the seasonal component for April. Give your answer to two decimal places.

  5. Seasonally adjust the data for April 2019. Give your answer to two decimal places.

 

Using a spreadsheet application to calculate seasonal indices and deseasonalise data

A spreadsheet is a powerful tool for dealing with numbers and formulae. Although many calculators have spreadsheet applications, the screen is very small so it is more practical to use an application on your computer.

 

Worked example

The Beach Cafe records the following customer numbers quarterly. The owners wish to gain a clear picture of seasonal variations over time. Use a spreadsheet application to calculate the seasonal index for each month and hence deseasonalise the data. Display the original data and the deseasonalised data on the same graph.

  Jan Apr July Oct
2016 1670 1218 815 1384
2017 1865 1327 903 1449
2018 2325 1731 1112 1835
2019 2520 1890 1232 2016

 

Step 1: Set up your table structure in your spreadsheet application

Note that it is a good idea to write the data vertically in order from 2016 Jan to 2019 October. It also helps to give each time period a number as the numbers will be used for predictions later. In order to graph the data with time numbers on the horizontal axis we need to enter the time numbers as text.

Step 2: Create the formulae

Below is a screen shot of the finished spreadsheet. Do NOT type these numbers in. We will use formulae to calculate them.

Here are the formula used

Note:

  • Using horizontal borders to separate the $4$4 cycle groups makes the spreadsheet clearer to read.
  • We know that the $4$4 values for 2016 have the same cycle mean as do the values for the other years so therefore merging the cells for cycle mean for each year makes sense and allows you to copy the formula for 2016 data down the column.
  • In this example the formula in cell E3 is =AVERAGE(D3 : D6) has been used. We could also use the formula $=\frac{(D3+D4+D5+D6)}{4}$=(D3+D4+D5+D6)4
  • In column F we are calculating the percentage of cycle mean for each data point. The four 2016 numbers all need to be divided by cell C3. If we use an absolute reference ($C$3) in the formula in cell F3 then we will be able to copy the formula down to cell F6. The $ signs ensure the C3 is 'locked' in place.
  • The four seasonal indices have been calculated in a separate table to the right. It's a good idea to check that the average of these four numbers is $100%$100% (or the total is $400$400). That way you know that your formulae are working correctly.

Step 4: Create the graph

Select the cells you wish to display on the graph. In this case, it is cells C3 to D18 and also F3 to F18.

Select Insert ►Chart from the menu and choose the 2D line with markers type.

The finished chart should look something like the one below. We can see the original data has been smoothed by being deseasonalised. Note that the title can be changed by double clicking in the title box and typing an appropriate name.

 

Practice question

Question 5

A sports store records the sales of its hockey sticks every 4 months. The finance department create a spreadsheet to record the data and analyse the seasonality of the figures.

  1. Which of the following formulae could be entered into cell $M7$M7 to calculate the cycle mean for 2017?

    =AVERAGE(L7:L9)

    A

    =AVG(L7:L9)

    B

    =($L$6+$L$7+$L$8)/3

    C

    (L7+L8+L9)/3

    D

    =($L$7+$L$8+$L$9)/3

    E

    =3/(L7+L8+L9)

    F
  2. Which of the following formulae could be entered into cell $N13$N13 to calculate the Percentage of Cycle mean for January 2019?

    100*(L13/M13)

    A

    =L13/M13*100

    B

    =L13/$M$13*100

    C
  3. The following formula is entered into cell $K20$K20 to calculate the seasonal index for May

    $=(O5+O8+O11+O14)/4$=(O5+O8+O11+O14)/4 . Something is wrong with the formula. Write the correct formula.

     

    $\editable{}$

  4. The following formula is entered into cell $O6$O6 to deseasonalise the data for September
    2016: $=L6/K20*100$=L6/K20*100. Something is wrong with the formula. Write the correct formula.

    $\editable{}$

  5. One method to check that the calculation of the seasonal indices is correct is to make sure the mean is equal to $100%$100%. What formula could be entered into cell $N18$N18 to check this?

    =AVG(J20:L20)

    A

    =AVERAGE(J20:L20)

    B

    (J20+K20+L20)/3

    C

    =(J20+K20+L20)/3

    D
  6. Another method to check that the calculation of the seasonal indices is correct is to check that the sum of the indices is $300$300. What formula could be entered into cell $N18$N18 to check this?

    $\editable{}$

Outcomes

3.2.2.2

calculate seasonal indices by using the average percentage method

3.2.2.3

deseasonalise a time series by using a seasonal index, including the use of spreadsheets to implement this process

What is Mathspace

About Mathspace