5.04 Time series data in tables

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

Question 2