topic badge

5.04 Time series data in tables

Lesson

Finding missing values in time series data tables

It is often convenient to present time series data in tabular form. We have two different ways to smooth data to remove the peaks and troughs and see the underlying trend. We can use a moving average or we can deseasonalise the data using the seasonal indices. Examiners often test understanding of the two processes by giving data in tabular form and asking students to calculate the missing values. The three worked examples below are typical examination style questions.

 

Worked examples

Example 1

Complete the table of seasonal indices below.

Quarter Jan Apr July Oct
Seasonal Index $92.34%$92.34% $124.12%$124.12% $\editable{}$ $98.45%$98.45%

Think: The mean of the seasonal indices is $100%$100% (or $1$1 if they are given as a decimal rather than a percentage). Let the index for July be $x$x and create an equation using the mean, which equals the sum of the scores divided by the number of scores.

Do:

$\frac{92.34%+124.12%+x+98.45%}{4}$92.34%+124.12%+x+98.45%4 $=$= $100%$100%

Write out the equation for the average of the $4$4 indices.

$92.34%+124.12%+x+98.45%$92.34%+124.12%+x+98.45% $=$= $400%$400%

Multiply both sides by $4$4.

$314.91%+x$314.91%+x $=$= $400%$400%

Simplify the left-hand side.

$x$x $=$= $85.09%$85.09%

Solve for $x$x.

 

Therefore the missing value is $85.09%$85.09%.

Reflect: For seasonal indices written as percentages, the sum of seasonal indices will always be $k\times100%$k×100%, where $k$k is the number of seasons. For seasonal indices written as decimals, the sum of seasonal indices will be $k$k. So, we could start at the second line in the calculation above.

 

Example 2

The table below shows some time series data where t represents time.

$t$t $1$1 $2$2 $3$3 $4$4 $5$5 $6$6 $7$7 $8$8 $9$9
$x$x $120$120 $160$160 $180$180 $250$250 $210$210 $\editable{}$ $163$163 $134$134 $142$142

(a) Calculate the $3$3 point moving average at $t=4$t=4.

Think: For an odd moving average, we need the given number of points ($3$3) with $t=4$t=4 being the middle point.

Do: 

3MA $=$= $\frac{180+250+210}{3}$180+250+2103
  $=$= $213.33$213.33 (to 2 decimal places)

 

(b) The $4$4 point centred moving average at $t=4$t=4 is $203.125$203.125. Determine the missing data when $t=6$t=6.

Think: For a $4$4 point moving average we need $5$5 points with $t=3,4,5$t=3,4,5 being the $3$3 middle points and using half of $t=2$t=2 and half of $t=6$t=6 to make the $4$4th data point. Create an equation and solve. 

Do: 

$\frac{0.5\times160+180+250+210+0.5x}{4}$0.5×160+180+250+210+0.5x4 $=$= $203.125$203.125

Write out the equation for the $4$4 point moving average.

$0.5\times160+180+250+210+0.5x$0.5×160+180+250+210+0.5x $=$= $812.5$812.5

Multiply both sides by $4$4.

$720+0.5x$720+0.5x $=$= $812.5$812.5

Simplify the left-hand side.

$0.5x$0.5x $=$= $92.5$92.5

Rearrange to solve for $x$x.

$x$x $=$= $185$185

 

 

Example 3

A company specialising in repairing clothes dryers records its number of call-outs quarterly and results are recorded in the table below. Determine the values of $a$a, $b$b, $c$c and $d$d in the table.

Year Data number ($n$n) Quarter Call-outs Quarterly mean Percentage of quarterly mean Deseasonalised data ($D$D)
2017 $1$1 Jan $d$d

 

 

$491.25$491.25
 

$25.45%$25.45% $427$427
  $2$2 Apr $654$654 $133.13%$133.13% $499$499
  $3$3 Jul $841$841 $171.20%$171.20% $c$c
  $4$4 Oct $345$345 $70.23%$70.23% $477$477
2018 $5$5 Jan $159$159

 

 

$a$a
 

$30.80%$30.80% $543$543
  $6$6 Apr $672$672 $130.17%$130.17% $513$513
  $7$7 Jul $867$867 $167.94%$167.94% $518$518
  $8$8 Oct $367$367 $71.09%$71.09% $507$507
2019 $9$9 Jan $176$176

 

 

$556$556
 

$b$b $601$601
  $10$10 Apr $723$723 $130.04%$130.04% $551$551
  $11$11 Jul $904$904 $162.59%$162.59% $541$541
  $12$12 Oct $421$421 $75.72%$75.72% $582$582

Note: The cycle mean here is the average number of call-outs per quarter for that year. Hence, this column may sometimes be labelled "Quarterly mean", "Yearly mean" or simply the "Cycle mean".

(a) Calculate $a$a

Think: The cycle mean is the mean of the $4$4 seasons in the cycle. Sum the data points and divide by $4$4.

Do:

$a$a $=$= $\frac{159+672+867+367}{4}$159+672+867+3674
  $=$= $516.25$516.25

(b) Calculate $b$b

Think: The percentage of quarterly mean is the raw data score divided by quarterly mean multiplied by $100%$100% (to show as a percentage).

Do:

$b$b $=$= $\frac{176}{556}\times100%$176556×100%
  $=$= $31.65%$31.65% (to 2 decimal places)


(c) Calculate $c$c

Think: Deseasonalised data is the raw data score divided by the seasonal index (with the index written as a decimal). This is a July value so we need to calculate the seasonal index for July which is the mean of the three July percentage of quarterly mean values.

Do: The seasonal index for July is:

Index $=$= $\frac{171.20%+167.94%+162.59%}{3}$171.20%+167.94%+162.59%3
  $=$= $167.24%$167.24% (to 2 decimal places)
  $=$= $1.6724$1.6724 (as a decimal)

Therefore,

$c$c $=$= $\frac{\text{raw data}}{\text{seasonal index}}$raw dataseasonal index
  $=$= $\frac{841}{1.6724}$8411.6724
  $=$= $503$503 (rounded to nearest whole number of call-outs)

Note: The raw data is given as whole numbers so the deseasonalised data should match and also be rounded to the nearest whole number.

(d) Calculate $d$d

Think: We have three options here. We can use the quarterly mean, or the percentage quarterly mean or the deseasonalised figure. All three calculations are shown below.

Do:

Method 1

Using quarterly mean

Method 2

Using percentage quarterly mean

Method 3

Using deseasonalised figure

$\frac{d+654+841+345}{4}=491.25$d+654+841+3454=491.25

$\frac{d}{491.25}\times100%=25.45%$d491.25×100%=25.45%

Seasonal index for Jan is:

$(25.45%+30.80%+31.65%)\div3=29.30%$(25.45%+30.80%+31.65%)÷​3=29.30%
 

$d+1840=491.25\times4$d+1840=491.25×4

$\frac{d}{491.25}=0.2545$d491.25=0.2545

$D=\frac{\text{raw score}}{\text{seasonal index}}$D=raw scoreseasonal index

$d+1840=1965$d+1840=1965

$d=0.2545\times491.25$d=0.2545×491.25

$427=\frac{d}{0.293}$427=d0.293

$d=125$d=125

$d=125$d=125 (to nearest whole number)

$d=427\times0.293$d=427×0.293

   

$d=125$d=125 (to nearest whole number)

The answer using any of the $3$3 methods is $d=125$d=125.

Reflect: The longest method is method $3$3 where we use the deseasonalised figure (raw data divided by seasonal index for January) as we need to find the seasonal index for January first.

 

Practice question

question 1

Data on the number of cartons of chocolate milk sold at the school canteen is collect every day over a $6$6 week time period. The seasonal indices for each day are calculated in order to deseasonalise the data.

  1. Find the missing value in the table of seasonal indices below.

    Monday Tuesday Wednesday Thursday Friday
    $102.21%$102.21% $97.54%$97.54% $114.65%$114.65% $88.98%$88.98% $\editable{}$$%$%
  2. Which day is the most popular day for buying chocolate milk?

    Monday

    A

    Tuesday

    B

    Wednesday

    C

    Thursday

    D

    Friday

    E

    Monday

    A

    Tuesday

    B

    Wednesday

    C

    Thursday

    D

    Friday

    E
  3. Will deseasonalised data for Wednesday be higher or lower than the original raw data for Wednesday?

    Higher

    A

    Lower

    B

    Higher

    A

    Lower

    B
  4. The number of chocolate milk cartons sold on Thursday of week $2$2 was $57$57. What is the deseasonalised score for Thursday of week $2$2? Round your answer to the nearest whole number.

Question 2

The quarterly power bills of a suburban household are recorded in the table below.

Year Month Time ($t$t) Bill ($\$$$) Quarterly mean ($\$$$) Percentage of Quarterly mean Deseasonalised data ($\$$$)
2016

Jan

$1$1 $456.24$456.24 $348.37$348.37 $130.97%$130.97% $341.37$341.37

Apr

$2$2 $A$A $95.40%$95.40% $347.23$347.23
Jul $3$3 $300.43$300.43 $86.24%$86.24% $354.12$354.12
Oct $4$4 $304.45$304.45 $87.39%$87.39% $354.85$354.85
2017 Jan $5$5 $477.05$477.05 $B$B $132.85%$132.85% $356.94$356.94
Apr $6$6 $343.77$343.77 $95.73%$95.73% $359.16$359.16
Jul $7$7 $305.98$305.98 $85.21%$85.21% $360.66$360.66
Oct $8$8 $309.54$309.54 $86.20%$86.20% $360.78$360.78
2018 Jan $9$9 $494.22$494.22 $367.47$367.47 $134.49%$134.49% $369.79$369.79
Apr $10$10 $352.56$352.56 $C$C $368.34$368.34
Jul $11$11 $310.65$310.65 $84.54%$84.54% $366.17$366.17
Oct $12$12 $312.43$312.43 $85.02%$85.02% $364.15$364.15
2019 Jan $13$13 $510.45$510.45 $374.56$374.56 $136.28%$136.28% $381.94$381.94
Apr $14$14 $358.76$358.76 $95.78%$95.78% $D$D
Jul $15$15 $312.25$312.25 $83.37%$83.37% $368.05$368.05
Oct $16$16 $316.76$316.76 $84.57%$84.57% $369.20$369.20

 

Seasonal indices
Jan Apr Jul Oct
$E$E $95.716%$95.716% $84.839%$84.839% $85.797%$85.797%
  1. Calculate the value of $A$A in dollars.

     
  2. Calculate the value of $B$B in dollars.

    Round your answer to the nearest cent.

  3. Calculate the value of $C$C as a percentage.

    Round your answer to two decimal places.

  4. Calculate the value of $D$D in dollars.

    Round your answer to the nearest cent.

  5. Calculate the value of $E$E as a percentage.

  6. The deseasonalised data can be used to identify the underlying trend. The trend is:

    Increasing

    A

    Decreasing

    B

    Remaining stable

    C

    Increasing

    A

    Decreasing

    B

    Remaining stable

    C

Outcomes

ACMGM089

smooth time series data by using a simple moving average, including the use of spreadsheets to implement this process

ACMGM090

calculate seasonal indices by using the average percentage method

ACMGM091

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

What is Mathspace

About Mathspace