Last 12 Months Analysis Made Easy With Cloud BI

  • Matthew Scullion
  • March 6, 2013

last-12-months-analysis-made-easy-with-cloud-biLast Twelve Months (LTM) or Trailing Twelve Months (TTM) is a measurement used to show your company’s health, growth and performance using the 12-month period prior to the reporting date and it’s now easy with Cloud BI.

It’s useful, as unlike period based measures (such as prior quarter or prior year), it is not skewed by seasonal trends that may affect the reporting figure on the day you report.  Daily reports, or monthly, quarterly and half-yearly figures, may only take into account, for instance, the last 3, 6 or 9 months of trading.  LTM however can give a truer reflection of the company’s performance.

For the above reasons, LTM is an important measure in company valuation, which in turn makes it important in, for instance, private equity  or venture capital owned businesses.  It’s also a useful measure for any analyst, executive or business owner who wants a meaningful reflection of his company’s health at a given point in time.

Calculating an LTM number for the current reporting period over one or two top-line measures (such as revenue or profit) is quite simple with Cloud BI.  Take the year to day (YTD) figure and add it to the prior year (PY) figure, then subtract off the “stub” of the prior year that equals the period elapsed so far in the current year.  As long as you know what the figure was on the same date in the prior year (which, if you are using a Cloud BI tool like Matillion BI, you could quickly establish with a date filter), you’re good to go.

If you need lots of LTM figures though, the work involved can be laborious.

For instance, if you wished to plot the LTM revenue for your company on a line graph (to show growth), with months as the data points and going back over the prior 36 months, you have 36 individual LTM figures to calculate.  If you then wanted to see on your chart a line for each of your operating companies, product groups, geographies or customer segments, it becomes even more involved.  Change to weekly intervals, or want to drill-down, and either you’ll need a copy of MS Excel and a clear week, or a better way. That better way cloud lie with Cloud BI.

The problem is complicated when drilling down.  Generating the top line LTM figures for your business will be comparatively easy as you’ll have consolidated accounts to work from.  But if you want to drill-down, for instance into operating company, product group or geography, you may need to be analysing at invoice level detail which would need to be converted to the appropriate exchange rates, categorised correctly, and then be presented in LTM.

Using the analysis functionality with Cloud BI, you can calculate and plot Last Twelve Months (LTM) sales analysis figures automatically.  This can be across as many data points  as you need and can be broken down or drilled-down into by the appropriate dimensions e.g. product group, customer sector, operating company etc.), right down (if required) to invoice level.

How to build a sales analysis LTM graph for gross margin, broken down by customer sector within Cloud BI tool Matillion.

We start off by creating a new report in Matillion BI (assuming we don’t have a suitable one already).

Last 12 Months Analysis in Cloud Business Intelligence - Create a Report

On the ‘Data Chooser’, we navigate to ‘Cubes’ and select an appropriate cube.  Here we’ll choose a cube called ‘Sales Invoice Rolling LTM’ which is available in Matillion BI if you have the Sales Analysis module.

Last 12 Months Analysis in Cloud Business Intelligence - Data Chooser

*Note: cubes you need are defined as part of your Matillion BI implementation, and new ones can be added or existing ones tweaked at any time as part of your subscription.

We will build a sales analysis showing LTM sales revenue on a monthly basis.  To do this we first drag-and-drop on the measure, ‘Net Value Rolling LTM’.

Last 12 Months Analysis in Cloud Business Intelligence - Add LTM Measure

Then we add ‘Month’ to the rows.

Last 12 Months Analysis in Cloud Business Intelligence - Add Month

If we now drag-and drop ‘Customer Sector’ to the columns, we get monthly LTM figures by customer sector.

Last 12 Months Analysis in Cloud Business Intelligence - By Customer Sector

Visualising LTM Data

This data can also be visualised graphically, using the Data Visualisation features in Matillion BI.

Here we are looking at the same data, broken down this time by ‘Product Group’ and for the last 24 months.  We’ve also added filters (right of screen-shot) to allow us to isolate the figures for particular customer sectors, as well as change the date range.

Last 12 Months Analysis in Cloud Business Intelligence - Data Visualisation

Using LTM measures, Cloud BI can allow you to analyse the performance and growth of your business as a whole – or of particular product lines, markets, territories or divisions – quickly, easily and powerfully.

Do you use LTM measures to track your business?  Could Cloud BI help speed this process up? find out more by registering for our free webinar below.