Tuesday 8 May 2012

If you don't know where you are going, then you'll probably get there

It is one of the main set of KPIs that a lot of our customers look for in any BI implementation.  They want to see performance versus the same period last year.  Or perhaps the year to date.  Or maybe a moving annual total.

Mr. and Mrs. Doe leave their house to go for a drive to somewhere.  After about an hour, Mr. Doe asks Mrs. Doe how far they have come since they left home.  She confirms that they have successfully traveled approximately 100km from their point of origin.  Satisfied, Mr. Doe continues on.

After about another hour, he asks again how far they have gone.  Again, Mrs. Doe confirms that they have traveled approximately 100km from the last checkpoint.  They continue on the way.  Each time checking that they have traveled the appropriate distance since the last checkpoint.  After 4 hours, Mr. Doe is surprised to find that they have arrived back in their home town, but on the wrong side of the tracks, and their gas tank is running low!

Look at the image above.  It is a KPI showing that our YTD is well down on the previous year.  There must be wailing and gnashing of teeth at the next board meeting.  However, let us consider what this is measuring.

Unless you have an extremely stable business, your sales in any one period will actually be influenced by multiple different factors - many of which you have had no control over.  It is, effectively, a random number.  If I was in the Energy business in the UK, this chart might reflect my business - but is that because of the weather?  January 2011 was far milder than January 2010 so my sales will have been down.  In 2010, they would have been up and everyone would have been smiling - but it was because of the weather, not anything that I had control over.  If I was a retailer, Easter 2010 was in early April so I might have had a seasonal spike in sales in late March.  Easter 2011 was late in April so the spike might not have kicked into the QTD figures.  Hence sad faces on the shop floor.

What if the figures are up on last year?  Does that mean that I need to give everyone a extra holiday to celebrate?  Not necessarily.  Again, lots of different things might be affecting the numbers.  Perhaps you launched a new product.  Maybe you hired a whole load of sales people in a new territory and the stock is flying off the shelves.  Your business this year will be so different from your business last year that comparing the two is comparing apples and oranges.

There is a better way.

Mr. and Mrs. Roe leave their house in Ellsworth, WI, planning to take a drive to Lake Wisconsin - about 4 hours away.  Before they leave, they plan their journey on the map and mark out whey they should be at each hour.  After the first hour of the journey, Mr. Roe asks Mrs. Roe how far they are from their first marker.  She tells him that they are a little short of it.  Mr. Roe is a careful driver and has been driving a bit under the speed limit so he gives it a little more gas.

After about another hour, he checks in again about how for they are from the next target.  This time they are a little ahead.  He knows that he could take his foot off the gas a little but decides that he wouldn't mind getting there a little earlier.  He lets Mrs. Roe know this and she readjusts the markers.  They ended up reaching their destination ahead of time and had a great time at the lake.

A business should not be relying on random events to compare how they are doing.  If I am a retailer, I will know when the major holidays are going to occur.  I will look at my last year figures, apply some thought as to where I can see growth, apply some mathematics, and come up with a sales forecast for the year - a forecast that should reflect the strategic direction of the company.  If things change (like a really good summer!), I can change the forecast to reflect things.  If I am in the energy business, I will be constantly looking at long range weather forecasts and modifying the sales forecast.

So, unless your year-on-year business is extremely stable, comparing one set of effectively random numbers against another set isn't really going to tell you much about your business.  Comparing them against a well thought out and planned set of numbers is going to tell you exactly where you are going and then the KPI is going to let you know if you need to intervene and change things - exactly what a KPI should do.

If you don't know where you are going, then you'll probably get there.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner


  1. what do you use to create the targets ? While qlikview has linear equations - multiple linear does not seem possible. be great for sales forecasting to pass in week numbers, season, even weather conditions and use qlikview to create predicted values. you can create trend lines but you can use these to predict to the future.

    do you usually create the targets off system and pass to qlikview to compare against actuals

  2. Lots of tools out there to do budgeting with. If you are looking to integrate into QlikView, you could look at MaxiPlan (http://www.maxiplan.com/).


Note: only a member of this blog may post a comment.