If it is difficult or laborious for people to extract and present information regularly, many fail to do it…. ‘not worth the effort!’ Nowadays we have the software to enable us to set up an automatic real time extraction quite easily using spreadsheets and databases or proprietory business systems.

In an earlier article on ‘Key Performance Indicators…Questions and Answers’ there was a list of common problems experienced with KPIs.

This article covers common information collection and presentation issues such as those below:-

  1. IT is an issue. They think the software for producing KPIs will cost too much to set up and will be an upheaval.
  2. OR …There is still a paperwork culture which will make KPIs a very laborious process.
  3. OR… Primary recording is an issue. They can’t get the base data to input because they cannot persuade people to record accurately or at all.
  4. OR… KPIs are produced, but standards for the production of KPIs are set too low, with KPIs coming out at monthly intervals using too much resource to produce them and not leading to actions.

Set standards for operation of the KPIs high.

Many of those who fail to do KPIs or struggle with implementation actually founder on the practicalities of the collection of information rather than disagreement with the philosophy. The way some people do their analysis, on an “as and when” or “ah hoc”, “special investigation” basis, it does not surprise me that they find it painful and disruptive.

You cannot really expect people to be totally positive about getting out KPIs when the majority of their involvement is spent in pulling together, consolidating and tabulating the information instead of looking at it with their people to get ideas and inspiration to improve processes. It’s as if they have been given an extra chore and in many cases they don’t see the extra effort as being worthwhile. For that reason we need to set standards high. Build the system so that once the base information is input on a day by day basis, the KPI spreadsheets and charts are produced automatically, in whatever detail is required without any consolidation and analysis work. Also they should be real-time as much as possible and at worst up to the previous day.

It is almost certain that a lot of the information required will already be in the IT system somewhere anyway. Usually 90% is. There may be need some extra Primary Recording necessary to capture the last 10%. Then it is a matter of building a store for the relevant dta and a means of automatic display

Make sure you have the right KPIs before setting up the collection and presentation process

People, in even the ordinary average business, do want to do a good day’s work and they want to avoid delays, rework, poor quality outputs, lost customers, hassle and stress. They much prefer a day’s work to go smoothly and to achieve what they set out to do.

Therefore, for each process within the business, you need to make sure that the information being collected tells you whether today has been a good day or not and why. In establishing what KPIs to collect and present, start with a clean sheet of paper, literally and define the information you must have. This information will be on inputs, outputs, errors and waste and it will be root cause information. Then find out whether that information is in the network or not. That will tell you how much new recording has to be done, if any. It is all too easy to bypass this ‘clean sheet of paper’ stage and lose objectivity, going instead for information which is easy to get. If you can draw up the ideal KPI spreadsheet, you have a good chance of getting the information. If you don’t you have no chance.

Microsoft Excel, Access and other systems in use in the Small to Medium sized business.

In the typical medium sector business you will have an accounting sytem like Sage, perhaps an MRP system, perhaps a Contact Management System (CRM) like Act or Goldmine and a number of independent Excel workbooks. Many businesses will have built an Access based system with numerous tables in a database. All of these applications can export into Excel or Access. This means that you can design a data capture sheet or sheets in Excel to take the data the way it comes out of the other systems. Then you can set Excel up to re-present the data the way you want to see it presented in a series of spreadsheet formats. Charting is easy and flexible and you then have yourself a process which can be driven by a few minutes downloading every day.

There will almost certainly be some new/upgraded primary recording needed to drive an upgraded management control system. This is because operating information need to actually run the business (as opposed to monitoring information to manage it with) may record quality and quantity of output but by its very nature does not seek to reflect on patterns of behaviour and root causes of errors and waste. The classic situation is that time-sheeting or machine shift reports are not broken down to show downtime by cause.

If you have to institute such recording, you have to minimize the difficulty and make it as easy as possible:-

  • Try to minimize handwriting, which then has to be keyed into Excel (e.g.) daily because there is no PC near the physical process
  • If some has to be keyed in by operators try to pre-write basic information into the spreadsheet (such as date, shift, machine) to save them the trouble (using a spreadsheet). Often the production that happens in a day, week or month has been planned, so recording of what actually happened is a matter of confirming or amending some minor detail of a pre-printed plan


An example of the kind of transformation which can be achieved in two or three months is an organisation which needed KPIs implementing to monitor and improve most aspects of the business.

They were managing with a monthly Profit and Loss and Balance Sheet and a couple of high level figures such as sales value of product per employee hour per week. This figure was fluctuating up and down as much as plus and minus 40% from the average, and it was difficult to track why. Also the MD was aware that performance could be improved everywhere even thought the business was very profitable. These are the headlines of the information they now have and how it was put together.

Sales leads or enquiries and potential customer details were previously held by several different sales people in hard copy in their desks. A simple enquiry log database with about 40 fields was created, at first in Excel. This showed source and progress of all enquiries and performance of potential customers. It feeds (from a download which takes two minutes a day) a collection sheet to show enquiries by source and by market sector plus conversion rates to orders and lead time/gestation times. It also allows much easier communication, and all this with NO extra recording, beyond that which used to be recorded manually.

The existing MRP system has always provided invoicing information to Sage in line by line, product by product detail. By exporting that weekly into a new Excel collection sheet we can see sales for any period by product group and by market sector, and can drill down to see how that is made up each period.

Each contract or product (part number) was costed manually by sales and technical people, but was not held on the system. By standardising the costing process and costing on excel we have gross margin and value added information for every line on every invoice and can show gross margin per unit or per cent by product, product group, customer and market sector easily and automatically.

Finally with new time recording we can see the downtime by cause, the speeds of operation of the main machinery versus standard The material usage losses with reason, again automatically displayed comes from the MRP system which was already operating. There are other enhancements but those are the main ones. This represents a dozen or so days of programming in Excel. All of this means we can get a daily P&L with estimated overheads and can drill down to the lowest level of detail without any effort.