on target

« KPIs for IT: a book... | Main | Measuring Online... »

Tuesday Nov 03, 2009

Automated Measures Definition – The spreadsheet

In a previous post I considered how business measure should be defined. That post contain a list of information that shall result from definition process. Here is a spreadsheet to automate collection of those measures.

Once again, I am not suggesting any process or technique to define measures. That's a task for qualified consulting professionals: they have great expertise and different approaches, but that's absolutely not my business!

On the other side, as an implementor of BI solution and manager of my organization, I am absolutely sure that you need those information to implement effective measurement. How you get them is another matter.

I received several appreciations for the post and I understood that I could do something more for consultants involved in defining business measures. Something that could simplify their jobs and also help us (implementors) on a later phase. Something simple, fast and free.

After hunting some budged (hard this year!) I started a small project called KpiStudio with the the following goals:

allow rapid and incremental definition of measures – no more cut & paste,
cross reference them,
validate their consistency,
generate formatted documentation (PDF, HTML),
produce artifacts to speed up implementation phase.

On a first glance I considered an interactive application, but one of our developers gave me a great hint: “Look at what happened to visual tools, nobody uses then any more, everybody is going agile: tools that work from pure text written with your preferred text editor”.

“It is absolutely true” I replied “but consultants are not like you. They are not handy with text editors!”. “In fact” he told me “we shall build an agile solution based on spreadsheets that business consultant uses exactly like we (developers) use text editors”.

And that was exactly the approach I chose. KpiStudio reads input from usual spreadsheets (almost zero learning curve). You can easily integrate with other tools like version control, and you will always be able to restore and transform provided information.

In this first post I shortly describe the spreadsheet, and some simple conventions to fill it.

In next post (expected next week) I will explain how to submit ti to KpiStudio to get all goodies listed above, and obviously provide a link to KpiStudio application (to be deployed in about a week and will be freely available to everybody through the net).

You can find spreadsheet template by clicking here (in Excel/97 format to be portable across different platforms. Should you find any difficulty let me know):

You will notice that headers and first column are frozen to keep context while scrolling. Each header has a note to describe expected content. Just move cursor on red corner to display it:

Columns basically reflect my previous post with two differences:

Measure units has been split to separate column (to shorten measure name).
Interrelationship with other measures has disappeared.

Second point is very important: where can you tell that measure Y and Z are important when analyzing measure Z? And that measure P and Q can compromise S (antagonistic)? And than M and N are used to compute O?

The response is simple, you use special notation in other columns (interpretation, computation) and KpiStudio does the trick to hyper-links and cross references them.

Defining measures inter-relationships

You shall only follow a simple convention: enclose referenced measures in square braces (not the measure itself – column 1!). To define, for example, how profit margin is computed write:

([sales]–[cost of products sold]) /[sales]

Simple and readable, isn't it? And agile too!

The same applies to interpretation and any other field:

… you shall also consider[pages per visit]and[visit duration]to determine if …

Upper and lower cases make no difference thus [pages per visit] and [Pages Per Visit] are the same.

And now an usual variation. Here is how to define sales growth by comparing sales, year to date to sales year to date one year ago:

[Sales: year to date] / [Sales:year to date last year] – 1

You are referring to the same measure (sales) in two different contexts. You simply append context after a colon inside square braces. Use colloquial language to express context, it is up to your IT staff to map it later to multidimensional expression.

Identifying pending issues

During any project, it is common to “park” issues and open points for later resolution. KpiStudio can cross-reference such issues too, simply conclude them with a double question mark, for example:

[sum of mail response time] / [inquiries]how can we get mail response time??

That's all for today post. Feel free to download and use the spreadsheet “as is” or freely use KpiStudio that has now been released.

Are you a management consultant?

Would like to try or coach this technique with your clients, do you need any additional or technical detail? Please let me know! Component Bases Solutions has great interest in partnership with business consultants. We can help you automate your proposed solutions in a very short time. We can also help to increase your visibility through links from many management tools we make freely available on the Web. Please contact for more detail.

Did you like this post?

Please, spend a few seconds and click on button below to socially bookmark it or tweet it. It will help to promote this blog and improve its content, Thank you!