Nineteen free moving average forecast templates for Microsoft Excel. A moving average is a smoothing tool — it strips the noise out of a bumpy series so you can see the underlying direction. That makes it genuinely useful, and it also means there are situations where it will quietly mislead you.
- Templates19 sheets
- FormatMicrosoft Excel
- Opens inExcel, Google Sheets, LibreOffice
- CostFree, no signup
What a moving average actually does
You take the average of the last few periods, and you recalculate it each time a new period arrives — the window “moves” forward, dropping the oldest value and picking up the newest.
Say monthly sales ran 100, 120, 95, 130, 110. A three-month moving average gives you:
- After month three: (100 + 120 + 95) ÷ 3 = 105
- After month four: (120 + 95 + 130) ÷ 3 = 115
The raw figures jump around by thirty per cent month to month. The smoothed line moves from 105 to 115. That is the whole idea: individual months are noisy, and the average of several is a steadier signal of where things are actually heading.
Used as a forecast, the method says next period will be roughly the recent average. Which is reasonable for a stable business, and the source of its main weakness — see below.
The templates
Nineteen sheets with the calculation set up, covering three-month, six-month and annual windows, most with a chart area so you can see the smoothed line against the raw data.
Choosing how many periods to average
This is the only real decision you make, and it is a trade-off with no universally correct answer.
| Window | Behaviour |
|---|---|
| Short — 3 periods | Responsive. Picks up genuine changes quickly, but also reacts to one-off blips as though they were trends. |
| Medium — 6 periods | The usual compromise for monthly business data, and a sensible default if you are unsure. |
| Long — 12 periods | Very smooth, and slow. A twelve-month average also cancels out annual seasonality entirely, which is useful when you want the underlying trend and unhelpful when the seasonality is the thing you care about. |
A practical approach: run two windows side by side on the same chart, a short and a long one. Where the short line pulls away from the long one, something real may be changing.
Simple, weighted, or exponential?
- Simple. Every period in the window counts equally. Easiest to explain and to defend, which matters if other people rely on the number.
- Weighted. Recent periods count for more, by weights you choose. Useful where the latest months genuinely are more informative.
- Exponential. Weights decline smoothly the further back you go, so nothing is ever fully discarded. More responsive than a simple average of the same length, and the usual choice in more formal forecasting.
For most small-business use, a simple moving average is fine. The gain from the more sophisticated versions is usually smaller than the gain from having clean data in the first place.
Where it will mislead you
Worth knowing before you plan around the output.
- It always lags. An average of past periods trails behind reality by roughly half the window. At a turning point — the month growth stops, or a decline begins — the smoothed line is the last thing to notice.
- It has no trend component. A simple moving average forecast says “next month looks like the recent average”. In a steadily growing business that will systematically under-forecast; in a declining one it will over-forecast. The error is consistent, not random, which makes it easy to miss.
- It cannot handle seasonality on its own. If December is always your biggest month, a moving average will under-forecast it every single year. Deal with seasonality separately — use the moving average to establish the underlying trend, then apply your seasonal pattern on top.
- It assumes the future resembles the past. A new competitor, a price change, a lost major client or a product launch all break that assumption, and no amount of smoothing will tell you so.
Pair it with a method that works forward from activity rather than backward from history — our sales forecast templates cover pipeline and bottom-up approaches.
Three ways to do it in Excel
-
The formula
With your figures in column B starting at row 2, put =AVERAGE(B2:B4) in C4 for a three-period average, then drag it down. Excel shifts the range automatically, so each row averages its own preceding three periods. Change the range to B2:B7 for six periods.
-
The Analysis ToolPak
Go to Data, Data Analysis, Moving Average. If Data Analysis is not on the Data tab, enable it under File, Options, Add-ins. It outputs the whole smoothed series and can draw the chart for you.
-
A chart trendline
Quickest for a visual. Chart your raw data, right-click the series, choose Add Trendline, then Moving Average and set the period. It does not give you numbers to use, but it shows the smoothing immediately.
-
Then check it against what happened
Keep a column of actuals beside your forecasts. Over six months the lag and any systematic bias become visible, and you can adjust the window or change method accordingly.
General information, not financial advice. A moving average describes what has happened; it does not predict what will. If a forecast is driving borrowing, stock purchases or hiring, have an accountant or finance adviser review the assumptions behind it.
Common questions
How many periods should I average?
Three for responsiveness, six as a general default for monthly data, twelve to remove seasonality and see the underlying trend. Running a short and a long window together tells you more than either alone.
How much history do I need?
At least two or three times your window, and ideally two full years if seasonality matters. With only a few months of data, a moving average has almost nothing to smooth.
Can I use it for stock or inventory planning?
It is commonly used for exactly that, with the same caveat about lag — it will be slow to respond when demand shifts, so hold safety stock accordingly.
What is the difference between this and a trendline?
A moving average follows the data, smoothing as it goes. A linear trendline fits one straight line to the whole series. The moving average adapts to changes; the trendline assumes a constant rate.
Do I need Microsoft Excel?
No. Google Sheets and LibreOffice Calc both support the AVERAGE formula and moving average trendlines. The Analysis ToolPak is Excel-only.
Can I use these commercially?
Yes, including with clients. You may not resell or redistribute the template files themselves. See our Terms of Use.
