Topics
5. Time Series Analysis
topic badge
AustraliaAU
Year 12

5.04 Time series data in tables

Lesson
Worksheet
Practice
Lesson

Time series data in 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.

Examples

Example 1

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

\text{Month}\text{Time }\left(t\right)\text{Bill } \left(\$\right)\text{Quarterly} \\ \text{mean}\left(\$\right)\text{Percentage} \\ \text{of quarterly} \\ \text{mean}\text{Deseasonalised} \\ \text{data}\left(\$\right)
2016\text{Jan}1456.24348.37 130.97\%341.37
\text{Apr}2A 95.40\%347.23
\text{Jul}3300.43 86.24\%354.12
\text{Oct}4304.45 87.39\%354.85
2017 \text{Jan}5477.05B 132.85\%356.94
\text{Apr}6343.77 95.73\%359.16
\text{Jul}7305.98 85.21\%360.66
\text{Oct}8309.54 86.20\%360.78
2018 \text{Jan}9494.22367.47 134.49\%369.79
\text{Apr}10352.56 C368.34
\text{Jul}11310.6584.54\%366.17
\text{Oct}12312.43 85.02\%364.15
2019\text{Jan}13510.45374.56 136.28\%381.94
\text{Apr}14358.76 95.78\%D
\text{Jul}15312.25 83.37\%368.05
\text{Oct}16316.76 84.57\%369.20

Seasonal indices are displayed in the table below:

JanAprJulOct
Seasonal indexE95.716\%84.839\%85.797\%
a

Calculate the value of A in dollars.

Worked Solution
Create a strategy

Use the quarterly mean to write an equation involving A.

Apply the idea

The quarterly mean in 2016, 348.37, is equal to the average of the bills for 2016. So we can use this to write an equation involving A.

\displaystyle \dfrac{456.24+A+300.43+304.45}{4}\displaystyle =\displaystyle 348.37Equate the quarterly mean to the mean of the bills
\displaystyle \dfrac{1061.12 + A}{4}\displaystyle =\displaystyle 348.37Simplify the numerator
\displaystyle 1061.12+A\displaystyle =\displaystyle 1393.48Multiply both sides by 4
\displaystyle A\displaystyle =\displaystyle \$332.36Subtract 1061.12 from both sides
b

Calculate the value of B in dollars.

Worked Solution
Create a strategy

Find the quarterly mean for 2017.

Apply the idea

B is equal to the average bill payment for 2017.

\displaystyle B\displaystyle =\displaystyle \dfrac{477.05+343.77+305.98+309.54}{4}Find the mean of the bills
\displaystyle =\displaystyle \$359.09Evaluate
c

Calculate the value of C as a percentage. Round your answer to two decimal places.

Worked Solution
Create a strategy

Divide the bill for that time period by the quarterly mean and multiply by 100\%.

Apply the idea

C is for April 2018, which has a bill of \$352.56 and a quarterly mean of \$367.47.

\displaystyle C\displaystyle =\displaystyle \dfrac{352.56}{367.47}\times 100\%Divide the bill by the quarterly mean and multiply by 100\%
\displaystyle =\displaystyle 95.94\%Evaluate
d

Calculate the value of D in dollars.

Worked Solution
Create a strategy

Use formula: \text{Deseasonalised data} = \dfrac{\text{raw data}}{\text{seasonal index}}.

Apply the idea

D is the deseasonalised data for April 2019, which has a bill of \$358.76 and a seasonal index of 95.716\%=0.957\,16.

\displaystyle D\displaystyle =\displaystyle \dfrac{358.76}{0.95716}Substitute the values
\displaystyle =\displaystyle \$374.82Evaluate
e

Calculate the value of E as a percentage.

Worked Solution
Create a strategy

Create an equation by equating the sum of the seasonal indices to the number of seasons per cycle and solve for E.

Apply the idea

There are 4 seasons per cycle. This is 400\% as a percentage.

\displaystyle E+95.716+84.839+84.797\displaystyle =\displaystyle 400Equate the sum and the number of seasons
\displaystyle E+265.352\displaystyle =\displaystyle 400Simplify the addition
\displaystyle E\displaystyle =\displaystyle 400-265.352Subtract 265.352 from both sides
\displaystyle E\displaystyle =\displaystyle 134.648\%Evaluate
f

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

A
Increasing
B
Decreasing
C
Remaining stable
Worked Solution
Create a strategy

Consider how the deaseasonalised data in the table is changing over time.

Apply the idea

From 2016 to 2019, the deseasonalised data seems to be increasing.

The correct option is A: Increasing.

Example 2

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

a

Find the missing value in the table of seasonal indices below:

MondayTuesdayWednesdayThursdayFriday
Seasonal index102.21\%97.54\%114.65\%88.98\%
Worked Solution
Create a strategy

Add the seasonal indices from Monday to Thursday and subtract the sum from 500\%.

Apply the idea

The seasonal indices should add up to 500\% since there are 5 of them. So we can subtract the given indices from 500\% to find the missing one.

\displaystyle \text{Sum}\displaystyle =\displaystyle 102.21+97.54+114.65+88.98Add the given indices
\displaystyle =\displaystyle 403.38Evaluate
\displaystyle \text{Friday index}\displaystyle =\displaystyle 500-403.38Subtract the sum from 500
\displaystyle =\displaystyle 96.62Evaluate

The seasonal index for Friday is 96.62\%.

b

Which day is the most popular day for buying chocolate milk?

Worked Solution
Create a strategy

Choose the day that has the highest seasonal index.

Apply the idea

Wednesday has the highest seasonal index, so it is the most popular day for buying chocolate milk.

c

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

Worked Solution
Create a strategy

Consider what will happen when we divide by a percentage over 100\%.

Apply the idea

To deseasonalise data, we divide by the seasonal indices. Since the seasonal index for Wednesday is 114.65\% \gt 100\%, dividing by this will make the result lower.

So the deseasonalised data for Wednesday will be lower than the original raw data.

d

The number of chocolate milk cartons sold on Thursday of Week 2 was 57. Calculate the deseasonalised score for Thursday of Week 2, rounding your answer to the nearest whole number.

Worked Solution
Create a strategy

Use the formula: \text{Deseasonalised data}=\dfrac{\text{Raw Value}}{\text{Seasonal index}}.

Apply the idea

We need to divide the raw data of 57 by the seasonal index for Thursday which is 88.98\%=0.8898.

\displaystyle \text{Deseasonalised score}\displaystyle =\displaystyle \dfrac{57}{0.8898}Divide the raw data by the seasonal index
\displaystyle \approx\displaystyle 64Evaluate and round
Idea summary

We have two different ways to smooth data: we can use a moving average or we can deseasonalise the data using the seasonal indices.

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