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.
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{}$$%$% Which day is the most popular day for buying chocolate milk?
Monday
ATuesday
BWednesday
CThursday
DFriday
EWill deseasonalised data for Wednesday be higher or lower than the original raw data for Wednesday?
Higher
ALower
BThe 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% | $84.797%$84.797% |
Calculate the value of $A$A in dollars.
Calculate the value of $B$B in dollars.
Round your answer to the nearest cent.
Calculate the value of $C$C as a percentage.
Round your answer to two decimal places.
Calculate the value of $D$D in dollars.
Round your answer to the nearest cent.
Calculate the value of $E$E as a percentage.
The deseasonalised data can be used to identify the underlying trend. The trend is:
Increasing
ADecreasing
BRemaining stable
C