How to Build the Perfect Scorecard

Last Updated: Friday, October 19, 2012 by Johnathan Briggs

Scorecards are a very popular and powerful way to visualise the numerical values of your metrics. The term "scorecard" has been a little hijacked by the " Balanced Scorecard" approach of analysing your business; however a scorecard only needs to contain data that is useful to you in your circumstances. Scorecards are particularly useful when used on an overview KPI dashboard because they allow a lot of information to be placed into a relatively small space. In this post I'm going to look at how to make, what I believe is, a perfect scorecard to fit onto your dashboards.

The perfect scorecard must start with a good and well defined metric or key performance indicator - so make sure you've read our guide on things to consider before you build a KPI dashboard. There are very few key performance indicators that do not have a target or budget figure. When building any form of visual scorecard a target/budget figure gives you a baseline from which to measure your actual values. It therefore makes sense that ideally your KPI has a respective target associated with it.

If you are building a 'balanced scorecard' then you should consider the target to be more like the ideal maximum performance of that particular KPI, in other words, 100% is perfect performance of our metric.

Here is what I consider to be the perfect scorecard. This is actually a scorecard that Target Dashboard creates for you automatically in just seconds but if you're not using Target Dashboard I'm going to explain how this is made.

A scorecard is generally an overview so the key ingredients for the perfect scorecard are:

  • a clear title explaining the metric

· current actual values, of the last completed period

· previous actual values, from the period before the current one

  • a colour-coded change indicator
  • a colour-coded on target indicator
  • some form of trend indicator or spark line

Given the fact that we would wish to make a scorecard as compact as possible it therefore makes sense not to have very long titles for our metrics, but at the same time we need to clearly communicate the area of the business that we are focusing on. A good practice is to group together scorecards into logical areas of the business thus you can normally use shorter titles and still clearly communicate to the reader.

The most obvious value that you would plot on any scorecard is the current actual value but even this requires clear definition. For example you may collect data on a daily basis, for example sales value, but you must decide what frequency of time you wish your scorecard to present. Typically this timeframe will be one of these:

  • days
  • weeks
  • months
  • quarters
  • years

If you collect your data on a monthly basis and you wish your scorecard to also present monthly then this is very simple and straightforward. However if you collect your data daily and wish to present your scorecard monthly then you will be required to do some data manipulation to group together your metrics by month.

Having grouped together your data by the appropriate time frequency you can then present the actual value within your scorecard. In my opinion it is a good idea to also present the previous value alongside the current value to provide a greater level of context. This makes it easier to look at the actual value and make decisions.

So for example if we are currently presenting the month of October then alongside we should also present the values for the month of September.

Any visual indicator must first consider its reader. A scorecard is often used to give an overview of the performance of a particular metric or area of the business. It is not designed to provide detailed information. If the scorecard were to create 'questions' in the reader then it is likely that the reader would then delve deeper into the metric to find out more. So it is good practice when building a financial dashboard, to provide a scorecard as an overview but also some detailed charts on the same metric so that the user can investigate in more detail should they so wish.

Numerical data can be particularly difficult to look at and our goal when building any visual indicator is to make something that shouts out from the screen or page when a problem exists or a key opportunity is available. You should always consider the readability of the numbers you present so if you are presenting numbers with multiple thousands then hide the decimal places and also try to use number formatting as this makes it easier on the eye and quicker for people to read. Consider these three numbers:

  • 54765.23
  • 54765
  • 54,765

Most people will find it much easier to read the third option thus we make our indicator clearer using this number formatting style.

Calculating a percentage change is a very clear way of indicating the performance of a particular metric. You must not forget that some metrics are desirable when increasing yet others are desirable when decreasing. For example, 'sales' is desirable when it is increasing but 'costs' are desirable when decreasing. So if you plan to use conditional formatting to colour the background of your scorecard then you must take into account the desired direction of travel.

Calculating percentage change can be a little tricky, but here's the formula

%change= (CurrentValue-PreviousValue) / PreviousValue x100

In Target Dashboard our scorecard automatically colours the background to one of 13 different shades of red or green depending on how extreme the percentage change is. If you are building a scorecard in Microsoft Excel this is quite difficult to do so calling a simple red or green might be easier.

The "On Target" percentage is very similar to the percentage change and should be colour-coded to match the approach of the percentage change. If you are using a balanced scorecard then it is likely that you're on target percentage may be called your "score" and would be almost always be less than 100%, where 100% is perfect performance. However for most organisations they would likely set a metric with an associated target that could be met or exceeded. In which case it's not unusual to present an on target value in your scorecard that is greater than 100% because you have exceeded your target.

In the example scorecard above (from Target Dashboard), we have also added year-to-date figures into the scorecard. This is a nice-to-have feature that gives you an idea of how things are progressing for the year but is not suitable for every metric.

A hugely important part of any scorecard is a trend indicator. The trend indicator takes the actual or previous values and displays them in the context of a wider time period. There are several ways to indicate trend. A spark line is one such way and makes the trend very clear. Another approach on which I am particularly keen is to use coloured bars. If you show the actual values but then determine if target was met you can then colour-code the bars over the twelve previous months. This provides a significant amount of useful communication from such a small space. The downside of using bars instead of a spark line is that a bar chart is slightly more difficult to see the overall trend in especially if the values are very similar.

When building any scorecard you must always remember that its purpose is to communicate quickly and clearly, so be sure to keep in mind this guide to dashboard charts and graphs design. Using well formatted numbers and coloured conditional formatting can make problems jump out of the screen and make decision-making quicker. Scorecards are just one style of visual indicator to put onto your dashboard, and you must always remember that is likely that a scorecard is to be used just as an 'overview' and more detailed visual indicators should be elsewhere in your KPI dashboards.