Then someone does an analysis by country and discovers that there is £10m for “Ireland” but another £5m for “Eire”. It turns out the validation has been changed, and new customers are now Eire, old customers are still Ireland.
Or perhaps the field isn’t validated, and there’s another £2m for variations like “Ierland”. In addition there’s a whole host of records where the country field is blank, of which £1m relates to Ireland. So actually it’s £18m in total, nearly double the first figure.
They say “Garbage in, Garbage Out”. If data will be relied upon for decisions, it must be sufficiently accurate. If data is pulled into a data warehouse or spreadsheet, or just sitting in the master system, then if only 5 countries are involved, it’s worth checking. An exception report that lists anything other than those 5 values can be run off the system, or as part of the data load process, or both. Incorrect data can then be repaired.
Simple variations such as Northern_Ireland and Northern-Ireland may not be apparent to the naked eye, but will be handled completely differently by a computer system.
As an analyst, if you are asked a question like this, shouldn’t you do a complete analysis to check? Or as the FD or manager asking the question, should you be asking for the answer to be accompanied by a complete analysis, so you can be confident nothing major has been overlooked?
Key Performance Indicators
Similar issues arise with Key Performance Indicators. Each KPI may have been set up as a formula in a spreadsheet or BI system, which in some way is dependent on the accuracy of the underlying transactions.
There is also an additional issue. With some of the popular databases, it is possible to “nest” or “layer” selections, so that you can select from a subset of a subset of a subset of the total database.
I reviewed the monthly KPIs from one system that was using layered queries. It was effectively impossible to understand whether each KPI figure was based on a complete set of relevant data, and therefore what each KPI actually meant. The figures weren’t being tracked in any form of trend, other than by comparison to previous month, so it was difficult to judge if each month’s figures were realistic. The monthly explanations of movements were clearly nothing but guesswork.
It’s therefore worth ensuring that the calculation of KPIs are clearly defined in the system. Where they represent an exception, or other subset of data, then it’s worth periodically checking the nature of the remaining data so no relevant data is accidentally excluded.
Commercial Advantage
Monitoring the analysis of data can also produce a direct cash advantage.
One system I managed was for the collection of overdue debts, including where a direct debit had been unpaid. Overdue transactions were pulled into this separate system, which was used to drive telephone calls and other communication with debtors. During the handover I asked about how direct debits were handled, and was left unconvinced by the answers.
The system ignored any records that stated that they were collected by direct debit – a simple Y in one of the fields. Within a few days of a direct debit being unpaid, this Y would be removed so the debt could be collected. So I ran a simple exception report of Y records that were more than 5 days overdue. 80,000 euros of debt was identified, where the removal process hadn’t been done properly at various times. This money could now be chased. The exception report was thereafter run daily, so action could be taken promptly.
Exception reports in other places tightened up the process, and made sure other transactions were identified for collection, and didn’t fall through the “gap”.
Next Steps
So there a variety of simple techniques that can be used to improve the accuracy of management reporting, and produce direct cash advantages.
Much you can do yourself. But if you’d like a review of any of your systems, then do give me a call.
No comments:
Post a Comment