Moving Averages

August 4, 2020

In this article we’re going to talk about moving averages; How they work and when to use them.

In the moving averages method, we’re going to look at a set number of periods and take the average. The number of periods can vary, it could be 3 periods, 6 periods, or even 12 periods, whatever you decide.

As I did when explaining the Naive Forecasting approach, I've created a document with 3.5 years of sales periods. I have created a column labeled % Difference which represents the percentage of difference between the forecast and the actual sales. The formula is, =IF(Forecast=0,0,(Actual Sales/Forecast)-1), and can be seen in the image below:

In this example, we’re going to be looking at the previous 3 periods. This means that the first period we are going to be able to forecast using a moving averages method is going to be the fourth method.

To find the average of 3 periods, we are simply going to put the formula, =AVERAGE(Period 1:Period 3), into the forecasting column:

We are immediately able to see 24.35% populate in the % Difference column. Because the number is positive, this means that the actual sales were 24.35% higher than the forecast. This could present some problems when planning inventory, but let's look at the rest of the periods and see how it compares to the naive approach when we apply this method to the whole 3.5 years worth of periods:

Right away we can see that there are some extremely percentage differences. The reason why this forecast looks so terrible, is because we're really using one seasonal trend to determine a large number of periods,

Savvy demand planners that use moving averages for supply and operations planning will often use a weighted average for specific periods. Using a weighted average is taking each of the periods within our three period spread and using a percentage of that periods average. For example, we may take 15% of period 1, 20% of period 2, and then 35% of period 3 to reach a more accurate forecast.

While moving averages can be an effective method of forecasting in some instances, you can get better accuracy by combining forecasting methods. Avercast takes each of the forecasting methods and uses their strengths while discarding their weakness, sign up for a free trial today!

How this applies to inventory planning?

When considering the role of a demand planner, the moving averages model is ideal for demand planning long in advance. Due its ability to analyze sales trends over time, the moving averages method works well if you have a product either consistently increasing in sales, or consistently decreasing in sales over a substantial number of sales periods. Consistency is key. As a demand planner or supply planner remember that, because this model supports consistency, it will not be useful in identifying trends or seasonal factors in your forecast. That is why I recommend combining this method and the trend projections method in order to account for spikes and avoid stock-outs or overstocks.