Spreadsheets – Dr. Jekyll & Mr. Hyde

Jekyll & Hyde

Over the years I have come across companies who end up with a huge reliance on spreadsheets to manage critical functions of their business. Unfortunately spreadsheets were never intended as a replacement for properly designed database systems yet it doesn’t stop people trying. It is common to see spreadsheets being used in the budgeting & forecasting process as well as time recording, invoicing, resource & capacity management and so on.

It is understandable that this happens, it is very easy to start a new spreadsheet and in no time at all someone has what looks like a good system. It is a quick fix but people love it because it solves an immediate need. Very quickly a department or team come to rely on the spreadsheet and overtime more people add new functionality to it because it is so easy to do.

Then it starts to get ugly. Within no time at all there are multiple “versions” of the spreadsheet but no control, no security, no support, no documentation and no change control. Worse still there are often individual spreadsheets per week, per client, per team and suddenly all the information the business needs is spun across hundreds of spreadsheets. It only when someone starts to look for a report that trends the information over time or filter it for a particular project that people realise that they just can’t do it. 

Inevitably the whole “system” has grown into a monster, too large for anyone to understand it or maintain it. Comprehensive reporting (or Business Intelligence), if even possible, is very difficult to do due to the multiple versions, different structures and inconsistencies between spreadsheets.

Another major issue with using spreadsheets this way is the significant reliability problem. Research studies (Stephen G. Powell, Kenneth R. Baker, Barry Lawson (2007-12-01)) estimate that 94% of spreadsheets deployed in the field contain errors. I have seen spreadsheet “systems” that were used to determine weekly payroll commissions and bonuses which were far from accurate. Sometimes an employees bonus had another nice unexpected bonus on top of it. But it was impossible to diagnose the system issue when it involves hundreds of spreadsheets all with their own formulae and equations. Worse still, the employee who was the spreadsheet wizard and had originally built all the spreadsheets had since left the company.

It is important that we understand when to use spreadsheets but more importantly when to stop using them before you hit the problems outlined above. Spreadsheets are great for end-user data analysis and to an extent for prototyping new systems. But just keep an eye out for the tell tale signs that you are beginning to see the start of the spreadsheet monster. It is possible to replace spreadsheets with a proper database system and it is something I would strongly recommend.