Seeing the underlying trend

,

Developing an understanding of performance trends and identifying turning points is fundamental to managing the future.  So what is the best way to spot the underlying trend in a data series that includes actuals, prior year actuals, budgets and forecasts?

The table below contains a data set for the monthly sales revenue of a company with a December year end.  Actual data for the current financial year extends to June 2013, and we have the corresponding data by month for the whole of the previous year.  We also have the phased budget for 2013 and the current management forecast by month from July to December 2013.

Seeing the underlying trend just from the data in the table requires a good deal of mental agility.

Table1

To make life a little easier, we can start by transforming our table into a bar chart of the type that we introduced in this previous post, which helps us to see the shape of the numbers, compare them against those for the prior year and highlight the adverse variance against budget for the current year.

TrendA

 

Whilst this representation of the data is powerful, it doesn’t really help us to answer the question “what is the underlying trend?”

To do so, we can change the data that we present from monthly values to moving annual totals (MATs).  We do this for the actuals by totalling the monthly sales figures over a series of 12 month periods.  As time proceeds and the value of a new month’s sales comes in, this figure is added to the MAT and the previous year’s corresponding month’s figure is removed. So, the MAT at January 2013 is the total of the monthly sales from February 2012 to January 2013. The MAT at February 2013 is the total of the monthly sales from March 2012 to February 2013, and so on.

So we can plot the MAT of actuals as a line:

TrendB

 

This tells us that the underlying trend from January to May showed consistent growth, but the MAT has dropped in June, possibly indicating a turning point.  It is helpful now to superimpose the latest management forecast onto the chart.  So how do we calculate an MAT of this forecast?

The key here is to join the forecast data series back to the actuals and prior year values, since the forecast is expected to be a continuation of those numbers that we already know.  Hence, the MAT for the July forecast is the total of the monthly sales from August 2012 to June 2013, plus the forecast for July 2013.  We can calculate the MAT for the forecast for the remainder of 2013 in a similar way. Superimposing this data series results in this graph.

TrendC

 

This shows that the underlying trend in sales is expected to increase for the remainder of the year, but to fall back in December.

We might now wish to compare the forecast against the original budget for this data series.  So what is the best way of calculating the Budget MAT?

The correct calculation is to use a similar method as we used to calculate the MAT of the forecast, i.e to join the budget numbers for 2013 back to the actuals for 2012.  This is because the budget for 2013 was set to be a continuation of the actuals for 2012, rather than a continuation of the 2012 budget (which was set a year previously and was therefore out of date when the 2013 budget was set).

Overlaying the MAT budget calculated in this way on our chart gives us this result:

TrendD

 

The reduction in the MAT of sales in December 2013 was anticipated in the budget as well as the forecast, as we can also see from the first chart that we drew of the monthly values.

The numbers for the various MAT calculations we have performed are set out in this table for reference:

MAT table

 

An MAT is a very simple and powerful way of eliminating from the data any seasonality that takes place during the year.  This is because each data point consists of a full 12 months of values and as each month moves forward the new data is added to the MAT and the corresponding value 12 months ago is removed.

Leave a Reply