Moving Averages are used to see a wider picture of how the numbers have been changing over periods. See here.
The image above shows the sales made over the past 12 months.
Create a simple line chart out of it to see the trend of sales over the last year.
Seems like a series of mountains 😉
The graph of sales shows steep rises and falls in sales throughout the year. It’s hard to study the trend of sales over the year, let alone forecast it for future years.
Let’s make the moving average of these sales to analyze the trend of sales better. The moving average for every two months’ sales.
There are three ways how you can apply the moving average method to forecast numbers.
1. Manually using the AVERAGE function
We are making a two-months moving average so the first average would be calculated at the end of month 2.
1. So, activate a cell in a new column parallel to February (2nd month of our data):
2. Write the AVERAGE function as below:
= AVERAGE (B2:B3)
3. Excel calculates the average for the first months.
4. Drag and Drop this formula to the whole list.
What has Excel done? As the cell references were not absolute but only relative, the cell references change for each next average.
And so, we get the moving average for the next two months (like February and March) in the example above.
5. Turn both these series (sales and moving average of sales) into a line graph to study the trend.
6. Select the whole data set.
7. Go to Insert tab > Charts > 2D Line Chart Icon.
8. Choose any 2D line chart, as desired.
Excel plots both series on the line chart (each with a different color).
Note how the blue line has sharp highs and lows. Whereas the orange line is smoothed out. This makes it easier to study the trends over a given period.
Based on this orange trend line, you can study the historical trend of sales over the year. Assuming sales would seek the same trend in the future, you can forecast future sales.
2. Using Data Analysis
Excel offers an in-built tool to calculate moving averages in Excel.
1. Go to Data Tab > Analysis > Data Analysis.