Having spikes in your data can sometimes make it difficult to carry out predictive analysis. These spikes can be due to seasonal fluctuations in sales, for example more sales of soft drinks and ice cream in the summer, or indeed sales of shoe ice grips in the winter. Extreme examples I know, but volume fluctuations can be extreme.

So, as I said, this can make analysis tricky sometimes, looking for averages over a full year to set monthly targets is not always the most reliable method or most reflectve of the real life situation, so I prefer to use a moving average over three months. It’s really simple to do in Excel using the AVERAGE function.

As you can see below I have my first months of data already entered in cells B4 and B6.

In cell C6 enter the formula =AVERAGE(B4:B6), Excel will display an error as you are ignoring similar data in the Sales column, we know that, but thanks anyways for the warning Excel!.

Just hit Ignore Error in this case.

All you then need to do is double click the fill handle and your formula down the rest of your data set.

Let’s turn up the volume a little on this and look at how the moving average looks on a graph plotted against the actual sales volumes.

As you can see I have plotted actual versus the 3 month moving average which smoothes out these spikes in volumes. I find that setting targets with this smoother average line is easier than the spiked data volumes.

Why not give this a go and feel free to leave a comment on how you found it?.

How do you set your targets if you have huge fluctuations in your volumes over a period of time?