In a survey taken at the RWM exhibition, Prodware discovered that more than 50% of companies use in excess of 100 spreadsheets on a regular basis to manage their businesses, this is a worrying trend. Microsoft Excel is a powerful tool that can aid personal productivity, but when it becomes an integral part of your business process, or worse still, a transaction processing tool, it has many flaws.
Excel began life over 20 years ago as a humble spreadsheet application, similar to Lotus 123. As a grid of rows and columns it was a useful tool that allowed data manipulation and the application of simple arithmetic operations. Over the years, and through many new releases, Microsoft has created a tool that is now incredibly powerful, and very sophisticated. It can handle huge volumes of data and can access easily external data sources. The presentation layer allows the creation of professional looking reports, and its programing and macro tools create a development environment that has the potential to replace traditional programing languages and tools. But herein lies the problem; its strength is its weakness. It is too commonly used as a quick fix to business issues.
Worryingly, most users have had no or very little formal training in the use of Excel. If you were to survey users of Excel you would find that few know how to correctly reference cells in a formula, or understand the errors that a simple copy & paste can create.
Many surveys have been carried out on the number of spreadsheets containing errors – then tend to range between 80 and 90%. What is really interesting is the impact of those errors. PwC research show that 91% of spreadsheets contained errors with at least a 5% error margin impact. The USA auditor Butler concluded that 86% of spreadsheet errors resulted in the over payment of tax! A financial misstatement by NASA resulted in a difference of $644 Million based on undetected spreadsheet errors. Search the web and you will find many more war stories, but these tend to be from public institutions as the private sector does not generally wash its laundry in public.
The most common errors take the following forms:
- Mistakes in logic
- Use of hard-coded values (e.g. VAT at 17.5%)
- Copy & Paste creating incorrect cell references e.g. use of absolute references
- Bad data input
- Cells omission (from references e.g. SUM() covering the wrong range of cells)
Developing a traditional software solution to a business problem would generally involve a specification which is signed-off from a business and technical perspective. Version control would ensure that changes are made in a controlled manner. Testing would take place to reduce and hopefully eradicate bugs. Formal training would ensure the users are confident and competent in its use. Spreadsheets, however, are more akin to a game of Russian roulette. Organisations must take control of them if they are to reduce the number of errors and the impact they can have.
Data, data everywhere….
Spreadsheets are standalone islands of data that rarely synchronise and are often out of date. When multiple users try to update the same spreadsheet all manner of unforeseen issues can arise.
Many businesses try to use spreadsheets as a substitute for a database – this is a common and potentially costly mistake.
De-risk with business management solutions
Spreadsheet data is often confused with database management; they are similar in many ways and concepts, but they differ in a number of critical areas.
Spreadsheets are especially good at ‘number crunching’ and if programmed well, can give people the exact results they are after. But spreadsheets are designed specifically for that single purpose. If you require any further analysis from this information, more often than not, you need to create a new macro or process to transform the current data.
There is no substitute for a good operational business system running on a modern database engine. Such systems are generally implemented in a controlled and well thought-out manner. Requirements are validated and the solution is tested. The users would be trained to a good level of competency.
Modern business software solutions should support the business process from end to end. Using a central database, data is only entered once, and it is validated. Data is captured once.
Data security ensures changes are tracked, and job authority controls access. This reduces the chance of mistakes and ensures the value of your data is secured. Because the data is in a single central location it can be protected. But its value can be realised through modern, powerful reporting tools.
All the benefits of such a system can far out way the cost; If you are one of those companies that is being throttled by inefficient systems, or you are exposed to critical levels by potential errors in your spreadsheets, then it’s time to think hard about securing your business. It’s all about using the right tool for the job.