With the new year ahead many marketers and analysts will be tasked with creating a marketing plan for the year. Imagine you are one of those and are asked to present the plan next week. Sooner or later during the preparation you will have to address one major pain point usually every analyst or data driven marketer has to go through: How to realistically forecast sales, revenue, conversions or something similar for the next year?
Obviously during the presentation you would have to defend those numbers and as such you have to have a robust model for predicting them. On the other hand it can’t be too complex as you have to present already in a couple of days.
So what if you had a simple technique which allows you to accurately predict metrics, while it can be implemented very easily.
That’s what this tutorial is about. It teaches you the moving average forecasting method for forecasting future sales, revenue, etc. in Google Sheets. If you are reading this last minute and you need forecasts right away you can just plug-in your numbers in below template, but I highly recommend working through the guide to understand everything.
First of all, for those of you, who only need a sales template for predicting revenue or other metrics, please find it above. Make a copy, open the sheet Data Input and copy your monthly revenue or conversion numbers from the last two years into cells D3:D26 and E3:E26 respectively.
You’ll find the output, i.e. the actual projections in the sheet Forecast.
You can simply change the column headers (D2 + E2), if you want to name your forecasts differently.
However in order to understand the techniques behind it and to potentially tweak and change the forecast I highly recommend working along the following guide. I’ll show how the predictions were modeled, including the ratio to moving average forecasting method.
The Ratio to Moving Average Forecasting Method
In order to work along please also access above Google Sheets Workbook. In addition to the Data Input and Forecast sheets you’ll find two additional Worksheets – the first one containing the example data, which you can use to work along and the second one the solution to the example data. Even though conversions as well as revenue is forecasted in the template we will only work on revenue prediction in the following. However the forecasting technique used is the same for both.
The example data has already been cleaned and prepared, so you can start right away.
The ratio to moving average forecasting method uses trend and seasonal indices to accurately forecast future sales, revenue, conversions or whatever other time series you decide you want to forecast. It is an very easy-to-use four step method. We will use it in our example to forecast sales revenue. As such we’ll have the following four steps:
- Estimate the deseasonalized level of sales during each month (using centered moving averages).
- Define a trend line to the the deseasonalized estimates.
- Determine the seasonal index for each month and estimate the future sales by extrapolating the trend line.
- Predict future sales by adding seasonality to the trend line estimate.
Calculating Moving Averages and Centered Moving Averages
First you’ll have to create a full year moving average for each month by averaging the current month, the six prior months and the next five months respectively. By creating a full year average seasonality will be eliminated. To do so copy the following formula in cell F8 and drag it down to cell F20: =AVERAGE(D2:D13).
This means for example that the moving average for month no 13 (January-18) is $18.7k. The moving average for month no 14 averages months no 7 to 18. Adding these up (7+8+9+10… +18) and averaging those month numbers will give you 12.5. As such the moving average for month no 13 is centered at month no 12.5. Similarly the moving average for month no 14 is centered at month no 13.5. Averaging those two moving averages will give you a centered moving average that estimates the actual centered moving average at the end of month no 13. As such to estimate the sales revenue during each month (de-seaonalized), copy the formula =AVERAGE(F8:F9)down from cell G8.
Defining the Trend Line to Centered Moving Averages
We’ll now use the centered moving averages to define a trend line that can be used to estimate future sales revenue. We’ll need to find an intercept and slope to do this
Luckily there are two functions, which will do exactly this for us. In cell L3 put =SLOPE(G8:G20,A8:A20to find the slope of the trendline and in cell L4 write =INTERCEPT(G8:G20,A8:A20)to find the intercept of the trendline.
Now copy the formula =A26*L$3+L$4 from G26 to G37. This will give you the estimated revenue (without seasonality) for the future months.
Calculating the Seasonal Indexes
Start by calculating for each past month Sales / Centered Moving Average. So simply put =D8/G8 into H8 and copy it down to H20.
E.g. for July you’ll get 0.9 (2017) and 1.02 (2018) respectively. This means in July 2017 sales have been at 90% of an average month and in July 2018 at 102% of an average month. Averaging those two numbers will give you the seasonal index for July, which is 96%. So July usually generates 96% of the sales a average months would generate. In order to calculate the seasonal index estimates for all month we can work with the AVERAGEIF formula. The AVERAGEIF formula will only average values, which fulfill certain criteria.
Put the numbers 1 to 12 in the cells K7 to K18 respectively. This numbers represent the individual months. The formula =AVERAGEIF(B:B,K7,H:H)in L7 will average all revenue numbers for January. Copy that formula down to L18 to do the same for the remaining months.
We have to ensure that the seasonal indices average exactly to 1 to normalize them. This is actually quite easy. Put =L7/AVERAGE(L$7:L$17) into M7 and copy it down to M18.
Forecasting future months
In order to forecast the revenue for future months you have to multiply the trend line estimate for each month’s revenue with the the appropriate seasonal index. Copy the formula =VLOOKUP(B26,K$6:M$18,3)*G26 from I26 to I37 to predict revenue for the next 12 months.
That’s it you are done!
The model is adjustable for more recent trends as well, if you believe the recent trend of the series has changed significantly. As such you don’t have to take all Centered Moving Average for calculating the slope but could take only more recent months (e.g. the last half year in our case) in order to calculate the slope with months closer to the current date.
As usual the disclaimer that this is model won’t predict the future to 100% as it is based on historical data.The model will obviously be more accurate the more past data you have and furthermore the less volatile your time series development is.
Nevertheless above is an very easy to follow accurate method for forecasting sales and other metrics.