How to calculate a rolling 12-month average
If your company's sales suddenly double one month, you might want to pause to consider the overall direction you're heading before heading out to celebrate your short-term success. A 12-month rolling average, also known as a "moving average," provides you with that long-term perspective.
As new months roll in, this indicator updates. Each shifting 12-month period generates a new average. Though this calculation requires holding on to more data, when forecasting sales or budget expenses, this kind of tracking makes the difference between assumptions and analysis.
Write the month and year down the left side of a sheet of notebook paper starting with a date at least 25 months in the past.
Write a starting figure you want to include in your calculation to the right of your first date. For example, fabricate sales for the hypothetical XYZ Corporation by writing down £65 for the first month.
Add £65 to the preceding amount for month two and continue until you reach £845 at month 13.
Continue entering sales, but this time subtract £65 from each preceding month so that by month 24, you're back to the original amount of £65. Stop there: this will give you a plausible business cycle that rises and falls.
Add all sales for the first 12 months and divide the total by 12: (1m+2m+3m+4m+5m+6m+7m+8m+9m+10m+11m+12m) / 12 = £422.
Place the result in a third column next to the £780 for month 12.
Repeat this formula but shift the period by one month to the next set of 12 months. In other words, replace the first and last months of the set with the next ensuing months: (2m+3m+4m+5m+6m+7m+8m+9m+10m+11m+12m+13m) / 12 = £487.
Place the result just below the £422 you just entered.
Continue plugging in your 12-month rolling average calculations until your result again equals £422, matching the first rolling average you calculated.
Enter a £130 sales figure for month 26. This will represent two things: sales for the most recent month and a doubling of the prior month's sales figures.
Calculate the rolling 12-month average for the most recent month and compare it to that of the prior month's rolling average. You'll find that the change, rather than pointing to an uptrend, reveals a decreasing sales indicator of -13 per cent. So in the short term, business for XYZ Corp. appears to be booming. But considering the long-term downward trend of a 12-month rolling average, the company might be wise to consider a change in sales strategy.
- "Budgeting Basics and Beyond, Third Edition"; Jay K. Shim and Joel G. Siegel; 2009
- "Financial Planning using Excel, Second Edition"; Sue Nugus; 2005
- Once you get the hang of it, you might want to use a computer spreadsheet program to speed up the calculation and plot the 12-month rolling average on a graph. This can provide a better picture as trends take shape.