topic badge

5.04 Time series data in tables

Worksheet
Tables
1

The projected quarterly sale figures for a manufacturer of solar panels for three years in millions of Australian dollars are shown in the table below:

a

What is the seasonal index for Q1?

b

What is the seasonal index for Q4?

c

What would be the most appropriate moving average to smooth this data?

d

Describe the underlying trend in this data.

Q1Q2Q3Q4
20190.730.851.071.36
20200.680.841.251.28
20210.840.921.411.53
2

Data on dam levels is collected tri-annually in January, May and September over a period of 4 years. The seasonal indices are calculated in order to deseasonalise the data.

a

Find the seasonal index for January.

b

The dam level in September of the first year was 72.3\% full. Deseasonalise this score.

c

The deseasonalised score for May of the second year was 70.1\%. Calculate the actual dam level for May.

Seasonal index
January
May96.23\%
September117.51\%
3

Data on the number of traffic accidents at a set of traffic lights is collected weekly over a period of 6 months. The seasonal indices are calculated in order to deseasonalise the data. Assume there are 4 seasons in a cycle.

a

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

Week 1Week 2Week 3Week 4
Seasonal index1.340.871.05
b

The number of traffic accidents in Week 4 of the first month was 34. Deseasonalise this score, rounding your answer to the nearest whole number.

c

The deseasonalised score for Week 1 of the second month was 32. Calculate the actual number of traffic accidents in this week. Round your answer to the nearest whole number.

4

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\%
b

Which day 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?

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.

5

Data on the number of cloudy days is collected quarterly over a number of years. The seasonal indices are calculated in order to deseasonalise the data.

a

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

SpringSummerAutumnWinter
Seasonal index0.81.31.4
b

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

c

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

d

The number of cloudy days in Winter 2018 was 65. Calculate the deseasonalised score for Winter 2018. Round your answer to the nearest whole number.

6

Data on the number of customers at a local cafe was collected daily over a number of weeks. The seasonal indices are calculated in order to deseasonalise the data.

a

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

MonTuesWedThursFri
Seasonal index67\%78\%82\%139\%
b

Which days of the week have raw scores that are higher than the average in a cycle?

c

Which days of the week have raw scores that are lower than the average in a cycle?

d

If the number of customers for Wednesday of Week 3 was 84, calculate the deseasonalised score. Round your answer to the nearest whole number.

7

Data on the number of people cycling to work is collected monthly over a number of years. The seasonal indices are calculated for each month.

a

Find the missing value in the given table of seasonal indices.

b

Which months have raw scores that are higher than the average in a cycle?

c

Which months have raw scores that are lower than the average in a cycle?

d

The number of people cycling to work in March of the second year is 125. Calculate the deseasonalised score, rounding your answer to the nearest whole number.

e

Which month would have the strongest 'peak' in a time series graph of the data?

Seasonal index
Jan1.1
Feb1.0
Mar0.9
Apr0.8
May0.6
Jun0.8
Jul0.9
Aug
Sep1.1
Oct1.2
Nov1.5
Dec1.2
8

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

t12345678
x25.427.224.125.628.325.1y29.1
a

Calculate the 3 point moving average at t = 3. Round your answer to one decimal place.

b

If the 4 point centred moving average at t = 5 is 26.0, find the value of y.

9

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

t12345678
x15.217.314.612.618.315.8y18.2
a

Calculate the 4 point centred moving average at t = 3.

b

If the 5 point moving average at t = 5 is 25.44, find the value of y.

10

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

t12345678
xy9890851071009186
a

Calculate the 6 point centred moving average at t = 5, correct to one decimal place.

b

If the 3 point moving average at t = 2 is 96.67, find the value of y, correct to the nearest whole number.

11

A bank collects data on the number of customers being served at the branch on a weekly basis and uses a spreadsheet to calculate a number of moving averages:

TimeData3MA4CMA5MA8CMA
1110
2135117.33
3107102.67103.875104.6
46692.67103.125109.4
5105101.67102.125102.4101.38
6134113100.593D
71009898.87599.498.19
86086B102.896.69
99893.3394.2594.895.19
10122104.6792.875C92.19
119490.3391.592.289.31
125580.3387.87592.488
1392A84.37585.886.06
149993.3383.1257782.44
158979.3380.62581.476.44
1650727780
177770.6768.567.2
188565.67
1935
a

Calculate the values of the following to one decimal place:

i
A
ii
B
iii
C
iv
D
b

State the moving average(s) that show a strictly decreasing trend.

c

Explain why 4CMA is the most suitable for this data.

12

A gym owner records attendance numbers in the table below:

WeekAttendance numbersCycle meanPercentage of cycle meanDeseasonalised data
Jan1760 1064.5 71.40\%1093.68
21123 105.50\%1012.73
3A75.56\%1052.22
41560 146.55\%1097.31
Feb 18301172.570.79\%1194.41
21267 108.06\%D
3903 77.01\%1165.83
41690 144.14\%1188.75
Mar 1835 1222 C1201.61
21387 113.50\%1250.80
395478.07\%1231.68
41712 140.10\%1204.23
Apr1880 B 67.45\%1266.37
21520 116.50\%1370.74
31020 78.18\%1316.89
41799 137.88\%1265.42

The seasonal indices are as follows:

Week 1Week 2Week 3Week 4
Seasonal index64.49\%110.89\%E142.168\%

Calculate the values of the following, giving answers to two decimal places if necessary:

a
i

A

ii

B

iii

C

iv

D

b

Calculate the value of E as a percentage, correct to three decimal places.

13

Soup kitchen volunteers cook meals for the homeless on Friday, Saturday and Sunday nights. Data is recorded for four weeks and entered into the table below:

DayMeals cookedWeekly meanPerentage of weekly meanDeseasonalised data
Week 1 \text{Fri}225192.33116.98\%192
\text{Sat}202105.03\%193
\text{Sun}150 C191
Week 2 \text{Fri}243B 116.45\%208
\text{Sat}218104.47\%209
\text{Sun}165 79.07210
Week 3 \text{Fri}251214.67 116.93\%214
\text{Sat}223103.88\%213
\text{Sun}170 79.19\%217
Week 4\text{Fri}272230.67 117.92\%D
\text{Sat}A108.48\%231
\text{Sun}179 77.60\%228

Seasonal indices are as follows:

FriSatSun
Seasonal index1.171E0.785

Calculate the values of the following, giving answers to two decimal places if necessary:

a
i

A

ii

B

iii

C

iv

D

b

Calculate the value of E to three decimal places.

c

Describe the underlying trend identified by the deseasonalised data.

14

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{Yearly} \\ \text{mean}\text{Percentage} \\ \text{of yearly} \\ \text{mean}\text{Deseasonalised} \\ \text{data}
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\%345.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 values of the following to two decimal places:

i

A

ii

B

iii

C

iv

D

b

Calculate the value of E as a percentage, correct to three decimal places.

c

Is the underlying trend identified by the deseasonalised data increasing, decreasing or remaining stable?

15

A cafe owner in a suburban supermarket recorded the number of customers they had for each quarter over four years in the table below:

MonthNo. of CustomersYearly meanPercentage of yearly meanDeseasonalised number
2016\text{Jan}16871293.75130.3961284
\text{Apr}121894.1451256
\text{July}88668.483D
\text{Oct}1384106.9761318
2017 \text{Jan}1789A130.8251361
\text{Apr}132797.0381369
\text{July}90566.1791358
\text{Oct}1449105.9601380
2018 \text{Jan}23251748.5132.9711769
\text{Apr}174599.8001800
\text{July}B63.5971669
\text{Oct}1812103.6321726
2019\text{Jan}25651951131.4711952
\text{Apr}1890C1949
\text{July}133368.3242000
\text{Oct}2016103.3321920

The following table also shows the seasonal index for the quarters:

JanAprJulyOct
Seasonal index131.4296.964E104.975
a
Calculate the value of:
i

A

ii

B

iii

C

iv

D

v

E

b

Using deseasonalised number column, state whether the trend is increasing, decreasing, or remaining stable.

c

The accountant recommends that the cafe owner employ more staff in the January quarter. Do you agree? Explain your answer.

16

A car yard records the sales of a particular brand of car and records the data in the table below:

MonthSalesYearly meanPercentage of yearly meanDeseasonalised data
2016\text{Jan}1030.3332.9726
\text{May}55181.3232
\text{Sept}X85.7129
2017 \text{Jan}14Y38.8936
\text{May}63175.0036
\text{Sept}3186.1135
2018 \text{Jan}1639.3432.9742
\text{May}68Z39
\text{Sept}3893.4443
2019\text{Jan}1945.0042.22P
\text{May}76168.8944
\text{Sept}4088.8945

The following table also shows the seasonal index for the months:

JanMaySept
Seasonal index38.36173.11K
a

Calculate the value of:

i

X

ii

Y

iii

Z

iv

P

v

K

b

Does the value of Z imply that the raw data for May 2018 is above or below the average for that year?

c

Without using your calculator, state the average of the percentage of yearly means for January.

d

The accountant recommends that a good time for staff to take holidays is January. Do you agree? Explain your answer.

17

A university records the number of students cycling to the library each day and records the data in the table below:

DayNumberWeekly meanPercentage of weekly meanDeseasonalised data
Week 1 \text{Mon}66103.5763.72100
\text{Tue}6865.66P
\text{Wed}7168.55100
\text{Thur}6562.76103
\text{Fri}L246.21104
\text{Sat}111107.17107
\text{Sun}8985.93107
Week 2 \text{Mon}68M66.57103
\text{Tue}6765.59100
\text{Wed}7270.49101
\text{Thur}6664.62105
\text{Fri}250244.76102
\text{Sat}107104.76103
\text{Sun}8583.22102
Week 3 \text{Mon}6698.5766.96100
\text{Tue}68N101
\text{Wed}7273.04101
\text{Thur}6262.0998
\text{Fri}244247.5499
\text{Sat}99100.4395
\text{Sun}7980.1495

The following table also shows the seasonal index for the days:

MonTueWedThurFriSatSun
Seasonal index0.660.670.71Q2.461.040.83
a

Calculate the value of:

i

L

ii

M

iii

N

iv

P

v

Q

b

Does the value of the percentage of weekly mean for Wednesday in Week 1 imply that the number of students cycling that day is above or below the average for that week?

c

Without using your calculator, state the average of the percentage of weekly means for Sunday.

d

Using the deseasonalised number column, determine whether the trend decreasing, increasing or remaining stable.

Graphs
18

A pizza delivery company records the number of daily deliveries over a period of 3 weeks and records the data in the table below.

The seasonal indices are as follows:

MonTuesWedThursFriSatSun
79.10\%D58.81\%72.50\%143.52\%157.21\%135.39\%
a

Use the following table to calculate the values of:

i

A

ii

B

iii

C

iv

D

b

Construct a time series graph for the number of orders against t.

c

There appears to be an outlier in the seasonal trend. Which value of t contains the outlier?

d

On what day of the week does the outlier occur?

\text{Day}\text{Time }\left(t\right)\text{No. Orders}\text{Weekly} \\ \text{mean}\text{Percentage} \\ \text{of weekly} \\ \text{mean}\text{Deseasonalised} \\ \text{data}
Week 1 \text{Mon}13569.1 50.62\%44.25
\text{Tues}237 53.51\%69.22
\text{Wed}342 60.74\%71.41
\text{Thur}4A 75.21\%71.72
\text{Fri}5104 150.41\% 72.46
\text{Sat}6114 164.88\%72.51
\text{Sun}7100 144.63\%73.86
Week 2
\text{Mon}84075.6 52.93\%50.57
\text{Tues}942 B78.57
\text{Wed}1047 62.1979.91
\text{Thur}115775.43\%78.62
\text{Fri}12114 150.85\%79.43
\text{Sat}13124 164.08\%78.87
\text{Sun}14105 138.94\% 77.55
Week 3
\text{Mon}1512089.7 133.76\%151.70
\text{Tues}1646 51.27\%86.05
\text{Wed}1748 53.50\%81.61
\text{Thur}186066.88\%82.75
\text{Fri}19116 129.80\% 80.82
\text{Sat}20128 142.68\%81.42
\text{Sun}21110 122.61\%C
19

A shop owner collects data on the number of thefts per quarter and uses a spreadsheet to calculate a number of moving averages:

YearQuarterTimeData3 pt moving avg4 pt moving avg5 pt moving avg6 pt CMA8 pt CMA
1\text{Jan}115
\text{Apr}21819.33
\text{Jul}32521.0019.7519.00
\text{Oct}440A20.2520.0020.17
2\text{Jan}51719.0020.7521.8021.5020.75
\text{Apr}62021.3321.2521.2021.3321.25
\text{Jul}72723.0021.7521.0021.0021.75
\text{Oct}82222.67B22.0022.1722.25
3\text{Jan}91921.0022.7523.8023.5022.688
\text{Apr}102223.3323.2523.2023.2523.188
\text{Jul}112925.0023.62522.8022.9223.688
\text{Oct}122424.3324.12524.00C24.063
4\text{Jan}132023.0024.62525.6025.2524.438
\text{Apr}142525.0024.87524.8024.9224.938
\text{Jul}153026.6725.2524.4024.58
\text{Oct}162525.6725.7525.8026.08
5\text{Jan}172224.6726.62527.80
\text{Apr}182728.00
\text{Jul}19D
a

Calculate the value of:

i

A

ii

B

iii

C

iv

D

b

State the two purposes of a moving average.

c

Which centred moving average clearly shows an increasing trend using the most data points?

d

Give two reasons as to why this centred moving average is the most suitable for this data.

e

Which other centred moving average also shows an increasing trend?

f

Graph the original data and the 4 pt moving average data.

Sign up to access Worksheet
Get full access to our content with a Mathspace account

Outcomes

4.1.3

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

4.1.4

calculate seasonal indices by using the average percentage method

4.1.5

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

What is Mathspace

About Mathspace