Transforming data

,

Analysis of Management Information frequently includes transforming discrete period by period data from the raw numbers, in order to find insight.  The most common of these “transformations” include:

  1. Year to date (for weekly, monthly or quarterly data)
  2. Growth on last year
  3. Moving annual average
  4. Moving annual total (sometimes also called Trailing 12 Months or Last 12 Months)

These calculations can be created within data warehouses, data marts and spreadsheets, but there are some potential pitfalls to be aware of. Let’s look at the potential issues with creating year to date data.

Calculating the year to date value of a flow item like sales is very simple.  It is simply the sum of each value of a given time interval from the start of the financial year to a given point in time (usually the current period that is being reported).

However, the year to date value of a stock item such as accounts receivable or headcount cannot be calculated in this way, because the values of these kinds of item relate to a single point in time.  In this case, we need to interpret year to date as either meaning the average value from the beginning of the financial year to a given point in time, or the value at that given point in time.  The particular interpretation we apply will depend on the context of our analysis.

And how do we calculate the year to date value of a statistic?  That depends on how the statistic is calculated.  If it is a ratio of items we already collect, and the ratio is made up of a flow item divided by a flow item (e.g. Gross Profit Margin %, which is Gross Profit over Sales Revenue), we can set up a calculation to work out year to date Gross Profit divided by year to date Sales Revenue.  If the ratio is a flow item divided by a stock item (e.g. Return on Capital Employed), we need to average the denominator over the whole time period we are looking at.  We also need to annualise the numerator of the ratio correctly in order to create a number that is comparable to the annual value, because this is how such ratios are conventionally displayed.  And if the statistic is an exchange rate or something similar, we have to interpret year to date as being the average value from the start of the financial year to the given point in time, or the closing value at that point in time.

It is essential that the designer of an MI dashboard takes account of these distinctions to avoid the danger of displaying meaningless or misleading data values.

Leave a Reply