How to Use the Moving Average Forecast in Excel
This post explores how to set up a moving average forecast in Excel.
To calculate a moving average, you take the mean of several values and then track how that average changes over time.
It’s a technique that has a wide range of useful applications, and Excel is the ideal platform to do it with.
4 steps to creating a moving average forecast in Excel
Creating a moving average forecast in Excel is simple.
All you need to do is gather your data together and apply the =AVERAGE formula.
Let’s go through a simple example that will give you a great tool and help you learn how to present your data!
Then you can choose a chart type to display your moving average for easy visualisation. After that, you’re ready to examine what the data’s telling you.
Let’s go through it step by step.
Step 1: Gather and organise data for forecasting
First, you’ll need to collect the data you want to use for your moving average and set it up in Excel so that it’s easy to work with.
a) Organise your data with dates in one column and values in another
Put the columns side by side, like this:
b) Open a new spreadsheet in Excel and paste your data into it
This step isn’t essential, but it can be helpful to extract just the data you want to work with and paste it into a new spreadsheet so you can see what you’re doing.
On top of that, it’s easy to mess up an existing spreadsheet accidentally if you start moving things around to make space for something else.
Using a fresh spreadsheet also means you’ll have plenty of room available when you get to the visualisation stage.
Step 2: Use the =AVERAGE formula to get the moving average
Next, let’s get into the specifics of how to apply the moving average formula.
a) Choose a moving average period based on your data. For example, use a 3-month moving average for monthly sales data over a year
The reason we do it this way is that three months is a good base time period for our purposes. What we’re trying to do here is smooth out the spikiness of the sales data to get at the underlying trend.
If we only look at monthly averages, the overall trend will be obscured by monthly peaks and troughs. Whereas if we choose, say, a six-month moving average, we might not generate enough data points to gain any meaningful insights.
b) Create a new column next to your data and label it “Moving Average”
Like this:
c) Use =AVERAGE(B2:B4) to calculate the average of the first three data points in the first cell under “Moving Average”
When you do this, Excel automatically calculates the arithmetic mean of the three numbers in cells B2, B3, and B4:
d) Drag the formula down to calculate the moving average for subsequent periods.
With cell C2 selected, hover over the bottom right-hand corner of it.
When your pointer symbol turns into a small black cross, click and hold, then drag it down the page.
With a bit of Excel magic, it automatically calculates every three-month average for your 2022 sales data.
One word of warning: remember not to go too far! The reason we’ve stopped at row 11 is that that’s the final value in the series.
It gives you the three-month average for October to December.
If you drag all the way down to the bottom, you’ll get extra, meaningless averages calculated using zero values from cells B14 and B15, which isn’t what we want.
If you think you might trip up with this, you can start off by putting the January to March average in cell C4 instead of C2 (so aligning it with March rather than January).
Then you can drag all the way to the December line without a problem.
(Don’t forget to check out our Excel tips page for more handy hints.)
Step 3: Choose a chart type to visualise the forecast data
They say a picture is worth a thousand words, and it’s certainly a good idea when you’re analysing data trends.
So the next stage is to create a chart to help you see what’s going on with the numbers.
a) Select data with original values and moving average column
You want to make sure you select everything, including the headers:
b) Go to the “Insert” tab and choose a chart type like a line chart
Line charts are good for displaying moving averages because they, well, move. A pie chart wouldn’t be a great choice here.
c) Add labels and titles to your chart to make it more straightforward
Because you selected the headers along with the data, they appear on the chart automatically. You just need to add a title by clicking at the top where it says “Chart Title” and making it your own.
d) Add a trendline to your chart by clicking anywhere, and selecting Trendline > Moving Average
To find the Trendline option, click once on the chart and then once on the big cross that appears top right:
This makes a list appear, and all you have to do is tick the “Trendline” box (and state which series you want it calculated for) to add one to your chart:
Step 4: Analyse and interpret the results of the forecast
Now we can see the trendline, it gives us a fair idea of how sales are developing. You’ll notice a gentle upward slope, which is good news.
To forecast future sales, we can take the trendline and extrapolate it into 2023 and beyond.
Of course, it’s important to check other aspects of sales patterns too. For example, it’s noticeable that sales experienced quite a bump in December.
That may not be a huge surprise given it’s the festive season, but as a data point, it is an outlier.
That means it might be worth calculating a moving average without the December figure as well, to make sure it’s not skewing the overall trendline.
Common uses of moving average forecasting
Moving average forecasting offers a structured and analytical approach to predicting future trends based on historical data.
For that reason, it crops up in a wide range of fields, from accounting to project management. Some of the specific benefits include:
Helps in creating production planning strategies
The power of the moving average forecasting technique to smooth out fluctuations in sales data can help companies plan their production strategy for the coming year.
They will be able to align their production capacity with the forecast to optimise the process.
This means avoiding the potential problems of overproduction (wasting resources on producing goods that won’t sell) and underproduction (missing out on sales).
Streamlines inventory management processes
Another plus is that this technique makes it more straightforward to predict periods of stronger demand, which is ideal for efficient inventory management.
Let’s say a fashion retailer wants to identify seasonal fluctuations in demand.
As the summer season approaches, identifying exactly when to start stocking summer dresses can make a huge difference to the bottom line.
Jump too soon or too late, and the retailer could miss out on sales. Moving average forecasting helps managers judge it just right.
Predicts demands for better resource distribution
This can be useful in all kinds of situations.
A hotel might use moving average forecasting based on data from past occupancy rates to predict how many staff it will need for each season, for example.
Or a freelancer might use it in conjunction with the data importing and graphing features of self assessment tax software to estimate their income patterns through the year so they can make sure to put enough money aside to meet their obligations.
Aids in strategic investment decisions
It can sometimes be a challenge for organisations to decide which business tools are worth spending money on, and which aren’t entirely necessary.
A startup that’s scaling up may consider switching to payroll automation software to make sure it remains compliant as the number of employees it takes on rapidly increases.
The senior team might use moving average forecasting to predict when their staff numbers will hit a particular level where use of such software becomes unambiguously cost-effective.
That way, they can prepare to make the switch at the right time.
Reinforces thorough risk assessments
Financial institutions such as banks or insurance companies incorporate moving average forecasting when calculating risk.
If, say, analysts at the bank suspect a recession is in the offing, they may refer to loan delinquency data from past recessions and apply this kind of forecasting technique to inform policy decisions.
Final thoughts
As you can see, moving average forecasting is both a simple and powerful technique.
And luckily, getting started with it using Excel is pretty straightforward.
You’ll probably find all sorts of useful applications for it in your own work.
Feel free to experiment. One of the great things about Excel is its flexibility, so you’ll discover plenty of ways to customise your forecasts to suit your needs.
