The Power of Trailing Twelve Month Graphs

Dave Abraham, writes ....

If you don't already use Trailing 12 Month (TTM) graphs, you should really consider using them.

Trailing 12 month (TTM) graphs give you trends by showing month by month fluctuations, but largely eliminate the impact of seasonal trends so that you can see trends. They also give you numbers as an annual amount, which makes them more comparable to annual accounts, so part way through a year you can see how you are doing compared to your full last financial year. And from this, when you get to month 11 of your financial year, your TTM graph will show you what the underlying metric (e.g. revenue, profit, or any other metric) if "your final month is similar to the final month last year". And therefore towards the end of the year, you can actually start to forecast your full year metric with quite good accuracy.

As just mentioned, TTM graphs are great because they can be applied to almost any metric - sales, revenue, profit, availability, number of support cases, number of customers, and many others.

So let's look at how you may graph revenues. You have probably used a bar chart or line chart to graph data, such as sales per month for the last 12 month. This can allow you to see trends that are hard to see just by looking at a spreadsheet of numbers. For example:

For most people, this is much easier to read than reading a list of 12 numbers.

But, as I'm sure you can see, it could lead to several interpretations. 2 possible examples being:

1. One interpretation of the above graph would be that revenues were consistent in Jan - April, before tailing off a bit, which we may be able to explain because one of our sales people handed his notice in, took his eye of the ball, and then left. And we didn't get a replacement in place until July, she took some time to find her feet and August was a poor month, but then she started really delivering and proving she was much better than the last guy anyway, and we grew really well through the autumn, leading to November, which was an awesome month. It's now January the next year, and we have no idea what happened in December, because our star sales person is still doing the same things, but sales just stalled. But it's probably just a blip because of Christmas, and we shouild be on track for a record year next year as long as she keeps doing the same things.
2. Of course, it doesn't take a rocket scientist to suggest an alternative explanation - that the product we sell is seasonal, with demand being poor in August probably due to holidays, and whatever product it is that we sell, has it's peak demand in November (maybe fireworks, maybe a product sold to retailers for Christmas), and that whilst the above staff changes have happened, they didn't have the impact on sales as suggested above.

So has the new person had a positive impact? Is the November peak higher or lower than last year? So to get this insight, you may have then extended the graph to 2 years:

Now what is the trend? Are sales up or down?

You can see the dips in August each year, and peaks in November, so it does indeed look seasonal (which shows the value of this type of graph). And by comparing the individual bars for each month of each year, for example most months in Jan - Jul look higher in the second year than the prior year, and most months in the autumn look higher, so you may be able to infer that "probably" there is a trend going on of higher revenues in the second year than the first year.

But if we view the trailing 12 months graph of the same data, the trend is instantly clear:

Now it is instantly clear that there is actually a very smooth steady underlying growth trend from January to October. What's more though, we can also see that the trailing 12 months in January was £2.2m and that the trailing 12 months sales to December was £2.4m. So not only have we seen the underlying trend, but also can see what the annual sales of the business have been, so we don't have to wait for a full year analysis, or add up the 12 individual numbers - we can get it instantly, visually, ongoing through the year.

Where the TTM graph is really helpful, is that even though it is looking at 12 months worth of data historically, you can see immediately if a problem happens, or if you start accellerating. For example in the example, it is actually true that every month is in fact 10% higher than the same month the previous year, except in November and December, where they are at the same level as last year. Which might mean that actually sales carried on just fine when our previous sales person left, and the new person picked up and carried on the trend. But once she got to 3 or 4 months into the job, when her impact should really be taking effect, and at our peak selling time, in fact we didn't sell any more than the same months the previous year.

If in example above, the new sales person had actually carried on the 10% growth compared to previous years up until October, but then in November and December had sold 20% more than the same months last year, this would show up as an acceleration:

So Trailing 12 Months graphs give you the benefit of seeing trends year to year, ironing out seasonal variations, and giving you numbers in a 12 month form that may help you align KPIs with annual targets - whilst also enhancing the ability to see changes in the last few week's sales instantly. They are not the only graph you need - as seen you still need a regular graph to see the seasonal trends if there are some, but they can be incredibly powerful. I encourage you to start using them!

How to create a Trailing 12 Months graph
The reason why TTM graphs are not used as often as they could be, is because they take a bit of effort to create, because you need to use a spreadsheet to compile the trailing 12 months numbers before you graph them. So some systems don't make it easy to create them in their reporting functions. To create them in a spreadsheet, you will need 24 months worth of data. Create a spreadsheet with 3 columns - Month, the actual KPI data for that month, and the trailing 12 months. Populate the first first 2 columns with actual data. And then put a formula in the 3rd column, starting on the 12th row, which is simply the sum of that month's figure, and the preceding 11 months. E.g. Cell C13 (trailing 12 months for December) would be the formula "=SUM(B2:B13) ":

Then create a graph from with the X axis being cells A13 to A25, and the Y axis being cells C13 to C25.

Of course an easy way is to use a reporting tool that has trailing 12 months graphs as a core piece of functionality, such as IS Online's Visual Customer Landscape Dashboard.

Other blog articles:

• Categorising Customers
• The Power of Focus
• Trailing 12 month graphs