Mathematics

 

Time Series and Moving Average(MA)

In this tutorial, you will learn the following:

  • What time-series is all about.
  • The significance of time-series in data analysis
  • Moving Average
  • The various intervals of moving average - 2-point moving average, 3-point moving average etc.
  • How to calculate moving average interactively
  • Drawing a trend line based on moving averages

Time Series

If a variable changes with time, the data produces a time series on X-Y grid, with the variable along the y axis and the time along the x axis respectively.

E.g.

E.g.

The following graph shows two sets of time series, representing the sale of ice cream in a week by two small grocery shops owned by Mahesh Patel in Britain, hundreds of miles apart in two different regions.

Since the two cities are in Scotland and England, the daily temperature can vary significantly, even in summer. Based on the data we see, we can draw the following conclusions - a few among many:

Similarly, we can draw a few casual conclusions from time-series plots - simply by observation. If you need a more scientific analysis, however, we need make a statistical approach - both for a reliable analysis and predictions.

Moving Average - MA

The moving average, also known as the rolling average or running average, provides us with a value to make more meaningful predictions from a time-series; it will be clearer to you before the end of this tutorial how moving average can smooth out a data cure, while giving a more accurate picture for forecasts/predictions. It takes into account a few successive data values and find an average, and then extends it to cover the whole time-series. It is done as follows:

You may have noticed that in order to find 3-point moving average, we select the first 3 data, then leaving the first one out, the next three and so on.

E.g.
1st 3-point moving average = (56 + 58 + 60)/3 = £ 58.00
2nd 3-point moving average = (58 + 60 + 59)/3 = £ 59.00
3rd 3-point moving average = (60 + 59 + 62 )/3 = £ 60.33

The 4-point moving average was calculated as follows:

E.g.
1st 4-point moving average = (56 + 58 + 60 + 59)/4 = £ 58.25
2nd 4-point moving average = (58 + 60 + 59 + 62)/4 = £ 59.75

3-Point Moving Average - interactive practice

Here is an opportunity for you to practise the moving average interactively. Just click the button to generate a new set of data and find out how 3-point moving averages are generated. It's the sale of DIY toolkits in a local DIY store for 6 consecutive weeks.

Please note how the moving average is plotted against the middle class of the three classes in question. You may have noticed that the curve of moving averages is much smoother than the original curve of data.

Finding Moving Average with Microsoft Excel

You can find the moving averages of a large dataset with Microsoft Excel. Excel gives you moving averages in a separate column and a graph - if selected as an option - showing both actual data and predicted values, the moving average data points.

In order to use Excel for moving averages, you need to make sure Data Analysis is part of Data tab. If not, please follow the steps below to get it:

  1. Click the File tab, then Options and finally click the Add-Ins category.
  2. In the Manage box, select Excel Add-ins and then click Go, next to it.
  3. You will now see the Add-Ins box. Check the Analysis ToolPak check box, and then click OK.
  4. Now, click the Data tab on the default Excel screen and you will see Data Analysis at the top right-hand end of the screen.

Once you get the Data Analysis on Excel, follow the steps below to find moving averages of a given dataset. In this tutorial, I focus on the simple dataset shown below.

E.g.

The enrolments of students on term-basis for two years, for a guitar club in a college are given below.

Moving Average with Microsoft Excel

 

  1. Click Data tab, and then Data Analysis.
  2. Choose Moving Average.
  3. Choose Input Range - B2 to B7, in the above case.
  4. Choose Interval - 3 for 3-point moving average.
  5. Choose Output Range - a cell such as D3, in the above case.
  6. Choose Chart Output option - click the check box.
  7. Click OK.

You will get the moving averages along with a graph. The graphs shows the actual data and a forecast based on moving averages.

You must have noticed:

Now that you have read this tutorial, you will find the following tutorials very helpful too:

 

 

Recommended Reading


 

Maths is challenging; so is finding the right book. K A Stroud, in this book, cleverly managed to make all the major topics crystal clear with plenty of examples; popularity of the book speak for itself - 7th edition in print.

Recommended - GCSE & iGCSE


 

This is the best book available for the new GCSE(9-1) specification and iGCSE: there are plenty of worked examples; a really good collection of problems for practising; every single topic is adequately covered; the topics are organized in a logical order.

Recommended for A Level


 

This is the best book that can be recommended for the new A Level - Edexcel board: it covers every single topic in detail;lots of worked examples; ample problems for practising; beautifully and clearly presented.