What’s Wrong with Spreadsheets?
August 1, 2016
I love spreadsheets. Spreadsheets are wonderful things, I use them all the time, and so do our clients. But it can be difficult to know when to stop using them.
When the subject arises with our clients, the issues are the same for both large and small organisations.
Consider a boutique Fund Manager using an in-house developed complex spreadsheet for managing portfolio, orders and risk. This might make perfect sense when starting up – low cost of technology, developed by an investment manager who knows exactly what they need and how to maintain the spreadsheet.
As well the business grows, and the spreadsheet is adapted and evolves with it – the same as any other software. But there are some problems specific to spreadsheets.
It’s more than just data input Errors
This is certainly a problem, as data validation is limited, and there is always the risk that a formula gets accidentally overwritten. That said, it’s also possible to make input errors using technologies other than spreadsheets, so it’s not actually the main problem that distinguishes spreadsheets.
Security, in more than one sense
- It’s just a file – the spreadsheet file can be copied and taken
- User access can be restricted, but it’s difficult to create an audit trail of who has used the spreadsheet
Multi-user
- Of course, anyone can use it, but there is limited ability to control who can use which functions via roles and user ID’s
- Maintaining consistent data is very difficult. Other than protection from writing over the whole file, there is no ability control over when different users edit specific parts of the spreadsheet. One client told me that the way they handle this is to shout across the room to let other know that a spreadsheet is about to be edited!
Database – history and audit trails
While you can of course store a large amount of data in a spreadsheet, the ability to update that data in an automated and consistent way is limited. New rows can be added, but only in a very simplistic way, and relationships with other data are difficult to maintain (see below). This makes historical reporting very difficult to do.
Keeping an audit trail of when and how data was changed is not something that can be incorporated into a spreadsheet. This obviously an extremely important issue for risk management and auditing.
General Robustness
One of the great benefits of spreadsheets is that data and formulae are placed in the same place ie cells of the sheet. It’s so easy to just start typing data in and manipulate it with formulae right next to the data. For more complex logic, you can write code too.
The problem arises when data gets moved around or added in ways that the writer hasn’t foreseen. Sometimes errors indicate the problem, but it’s easy to point to the wrong cell without knowing, or even to completely overwrite data!
If you are struggling to maintain your spreadsheets, The Bridge can advise on how to restructure or even replace them with a more robust technology.
If you’d like to discuss your software development options with The Bridge at no cost or obligation, feel free to call Lawrence on 02 9993 3300 or email lawrence@thebridgedigital.com.au