FP&A

Moving Annual Average – a powerful data transformation

Learn how to use a process to calculating an MAT

We can’t use an MAT for a stock type item (like Accounts Receivable or Headcount) or a statistic (such as gross margin % or the £/$ Exchange Rate) because totalling these items overtime periods doesn’t make any sense.

In a previous post, we looked at the Moving Annual Total (MAT) as a simple, powerful way of eliminating seasonality within a 12 month period to enable the underlying trend of a data series to be discovered.

The MAT works fine for items that flow, like Sales, Profit and Shipment Volumes because these can be cumulated over time periods to provide meaningful totals.  But we can’t use an MAT for a stock type item (like Accounts Receivable or Headcount) or a statistic (such as Gross Margin % or the £/$ Exchange Rate) because totalling these items over time periods doesn’t make any sense. The solution is to calculate the Moving Annual Average (MAA).  The simplest way to calculate this is to use a very similar process to calculating an MAT – the end result is then divided by 12 (or however many periods we have within our year) to provide an average monthly value.

In cases of statistics like Gross Margin % (made from a flow item divided by another flow item), we can construct a more accurate weighted average value for each month by calculating the total of the last 12 months for both the numerator and denominator of the ratio and then dividing one by the other.