5.02 Moving averages

When we say we want to smooth the data, we mean that we remove the peaks and troughs so that we are able to see the underlying trend of the data more clearly.

One method we can use to smooth the data is to calculate the moving averages of the response variable (y) and then plot this against time (t). A linear regression model can then be applied to the soothed set of data and used to analyse underlying trends.

As the name suggests, moving averages are averages of rolling sets of data. The moving average method calculates the average of a set number of points around the given data point and then the moving average is plotted against time. This lowers the peaks and raises the troughs, hence smoothing the original data.

It is important that we select the correct type of moving average based on the number of seasons in the time series cycle. If it is a 5 season cycle we use a 5 point moving average, if there are 3 seasons in a cycle we use a 3 point moving average. These are both examples of odd moving averages because we have an odd number of seasons in the cycle and to calculate the average we divide by an odd number.

If the correct moving average is calculated and graphed we will see a continually increasing, decreasing or stable underlying trend. If the incorrect moving average is calculated we will still have some peaks and troughs in the data.

Examples

Example 1

Consider the Time Series graph drawn below, along with two sets of moving averages.

a

Which moving average is most appropriate for this data?

A

5 point moving average

B

7 point moving average

Worked Solution

Create a strategy

Count how many points there are in a row before the graph repeats itself.

Apply the idea

1

2

3

4

5

6

7

8

\text{Time period}

8

10

12

14

16

\text{Data}

There are two large peaks at time periods 1 and 8, so we can count the number of points from peak to peak as shown in the graph.

We count 7 points, so we need a 7 point moving average. The correct answer is option B.

Watch question walkthrough

b

Which two is the reason why is 7 point moving average is the most appropriate?

A

It removes the most seasonality.

B

It best smooths the data.

C

It has the same number of points as the original graph.

Worked Solution

Create a strategy

The moving average should always match the number of seasons per cycle in the original data and smooth the data.

Apply the idea

There are more points than 7 in the original graph, so option C is incorrect.

The correct answers are options A and B.

Watch question walkthrough

Odd moving averages are used for cycles with an odd number of data points. They are named 3MA (3 point moving average), 5MA (5 point moving average), 7MA (7 point moving average), etc.

If we are calculating a 3MA we find the average of response variable for three data points (For example: t=1,\,t=2,\, and t=3) and the result will be plotted against the middle point (t=2). It's not possible to find a 3MA for t=1 as we don't have a data point below t=1.

If we are calculating a 5MA we find the average of the response variable for five data points (For example: t=1,\,t=2,\,t=3,\,t=4 and t=5) and the result will be plotted against the middle point (t=3). It's not possible to find a 5MA for t=1 or t=2 as we don't have enough points below them.

Examples

Even moving averages are used for cycles with an number of data points. We will divide by an even number when calculating the average.

If we calculate an even moving average in the same way we calculated an odd moving average above, we run into a problem. Consider the table below:

We solve the problem using a centred moving average. To calculate the 4 point centred moving average at t=3 we use the first 5 scores. We add half of the first (t=1) value and half of the fifth (t=5) value with the middle three values and divide by four.

Even centred moving averages are named 4CMA (4 point centred moving average), 6CMA (6 point centred moving average), 8CMA etc. In order to calculate the average for a particular time period (n) we must use (n+1) data points.

If we have five data points a,\,b,\,c,\,d and e: \text{4CMA}=\dfrac{0.5a+b+c+d+0.5e}{4}. By taking a half of the first and the last data point, this counts as only one data point and effectively we have only used 4.

If we have five data points a,\,b,\,c,\,d,\,e,\,f and g: \text{6CMA}=\dfrac{0.5a+b+c+d+e+f+0.5g}{6}.

Examples

Spreadsheets are a powerful tool for calculating moving averages and graphing both the original and smoothed data sets.

Follow the instructions to calculate a 3MA for the data below using a spreadsheet and then use your spreadsheet to graph both the original data and the smoothed data.

Note: The instructions shown use a particular spreadsheet software, the notation and behaviour of your spreadsheet may differ slightly from what is presented if you are using a different program. If in doubt, ask your teacher.

Step 1: Use the first row to enter titles for the columns - Time for column A, Cost for column B and 3MA for column C. Then enter the time and cost data in their appropriate columns.

Step 2: Enter the formula for the moving average into Column C.

• In cell C3 type the formula for the 3 point moving average using the cell reference of the first three cost values as follows: =(B2+B3+B4)/3

• Note that formulas in spreadsheets always start with an equals sign.

  

• Press enter and the value of the first 3 point average, 15, will appear.

Step 3: Fill down the formula the formula from cell C3 to cell C9

• Click on cell C2 to highlight it.

• Click and drag from the bottom right hand corner to extent to highlighted region down the column to cell C9.

• The moving averages will now be displayed.

Step 4: Graph the data

• Highlight the entire table of data.

• Select the Insert toolbar.

• From Charts select a scatter graph with straight line connectors.

• Both the original data and the smoothed data will be graphed on the same axes.

• Adjust format such as labelling.

Examples

Example 4

Justin weighs himself every 4 months and plots his weight on the graph below.

a

Which moving average should Justin use if he wishes to smooth the data in order to make predictions and have the moving average values plotted at each month and not between months?

A

3MA

B

3CMA

C

4MA

D

4CMA

Worked Solution

Create a strategy

Count how many points there are in a row before the graph repeats itself.

Apply the idea

From the peak at Jan '17 to the before the peak at Jan '18 there are 4 data points.

The correct answer is option D: 4CMA.

b

Justin enters the following incorrect formula into cell \text{D3} in his spreadsheet in order to calculate a 4 point centred moving average:

Write the correct formula for \text{D3}.

Worked Solution

Create a strategy

Use formula: \text{4CMA}=\dfrac{0.5a+b+c+d+0.5e}{4}.

Apply the idea

Justin onlu used 4 data values when he should have used 5. He should have also used the value in cell \text{F2}.

The correct formula for \text{D3} is: \text{4CMA}=(\text{B2/2 + C2 + D2 + E2 + F2}/2)/4

c

Calculate the correct value for the 4CMA for cell \text{D3}. Round your answer to one decimal places.

Worked Solution

Create a strategy

Use the formula from part (b).

Apply the idea

\displaystyle \text{4CMA}	\displaystyle =	\displaystyle (\text{B2/2 + C2 + D2 + E2 + F2}/2)/4	Write the formula from part (b)
	\displaystyle =	\displaystyle \dfrac{\dfrac{81}{2}+78+75+74+\dfrac{80}{2}}{4}	Substitute the values in \text{B2},\,\text{C2},\,\text{D2},\,\text{E2},\,\text{F2}
	\displaystyle =	\displaystyle 76.9	Evaluate