Spreadsheets in the time of COVID
Updated: Dec 15, 2020
By Guy Barnes.
Spreadsheet risk has once again hit the news following the recent NHS COVID case reporting error, and yet through the headlines, comments, articles and tweets there seems to be little in the way of consensus on how to tackle this problem.
We believe it is sometimes too easy to blame an application (Excel) for high profile mistakes, when ultimately it is a failure of process and control, but how do you make sure mistakes like this don’t happen in your organisation?
Excel can create problems because it is used by amateurs and professionals alike and the quality of the solution is usually undifferentiated between the two. When these tactical, and potentially amateur, solutions pass into widespread usage (and become the norm over many years), the inherent risk becomes impossible to manage, and the chance of mistakes increases dramatically.
Whilst the leaps in alternative technology to support analytics, reporting and business processes are clear for all to see, the fact remains that Excel is still the go-to business tool for the majority of companies, and large parts of even the biggest financial institutions would be helpless without it.
There is often nothing wrong per se with using spreadsheets as a tactical solution, so long as the risks are known, and appropriate control and validation checks are put in place.
But those risks can be big. Reports and numbers produced by spreadsheets have little provenance or auditability and the calculations are uncontrolled and untested. Often updated at any time by anyone, and whether these changes are on deliberate, accidental or malicious is impossible to prove. Manual processes put in place to try to overcome these problems create inefficiency and the repeated work leads to high operating costs.
The reality we have seen is that many insurance, finance and trading companies can have literally millions of spreadsheets and identifying which of these are business critical is often an insurmountable problem.
So if you have decided that your company has to finally address the problem, and make a meaningful change to the way spreadsheet risk is managed, here are our recommendations:
1. You will need to invest in technology, but there is no silver bullet
Spreadsheets are the most flexible business tool out there, and the most widely understood. Don’t expect a single technology to fix all your problems (see: Are you missing out on a ‘Low-Code’ revolution). Inevitably, you will need a mix of solutions: migrating processes to core systems (e.g. CRM or accounting systems), reporting and data tools (Tableau, PowerBI, etc), implementing best-of-breed web-based applications (time tracking, expenses) and continuing to use spreadsheets where it makes sense to do so.
2. Change the culture: a project will not fix the problem
Given the number of spreadsheets generally in use within an organisation, a project team alone is unlikely to make a meaningful improvement. As these spreadsheets are generated by the business, it needs to be the business that owns the challenge of making a change. We find an approach that works well is to establish a project team to set a pattern of work, then use early successes to build knowledge and enthusiasm to adopt the same approach more widely.
3. Invest in training
When was the last time you heard of someone going on an Excel training course? Probably not that often, and yet some recent analysis completed by a banking client of ours indicated that around 30% of the productive time of ALL resources was spent in Excel. It's one of those things that many people are expected to be good at in a job, but very rarely have structured or regular training in. Investing in people so they can build "good" spreadsheets is an often overlooked, and undervalued, approach because it is not as 'sexy' as going out and buying the latest software. A good spreadsheet, for example, can be easily understood by a new user, can validate and test input and output data and will perform well as it scales. Don’t want to invest in boring Excel training? Then consider alternatives that will add value such as training in operational process improvement or lean methods.
4. Learn from software development practices: Design, Develop, Test, Deploy
Spreadsheets often grow organically, starting as a quick solution to a new problem, then expanding and evolving over time, sometimes many years. Little serious planning goes into the layout, functionality, testability and robustness, especially when considered in a multi-user environment, but why not? Have you ever considered that some practices from agile software development could be adopted by business users using spreadsheets? How about proper review cycles, getting people to review each other’s work before spreadsheets can be used for important processes? Or even pair programming to help foster more creative and robust solutions whilst sharing knowledge and reducing key man risk?
5. Improve the worst areas first, apply risk-based triage
Here's the bad news, if your company essentially runs on spreadsheets: it may take years to sort out, so it is imperative that you focus on the worst bits first. Try to undertake an audit of all processes that are done in spreadsheets, and find out which are the ones that support the highest profile processes (reputational risk), which ones take the longest to run and manage (operational effort) and which ones are the most complex, and likely to go wrong (operational risk and potentially key man risk). If that sound like a lot of work, this is exactly what Schematiq Discovery can help you do automatically.
At Schematiq we have used our experience and technology to help companies large and small manage spreadsheet risk. Uniquely, we help throughout the improvement cycle, with cutting edge discovery and diagnostics software and a platform that helps not only to automate but also simplify and scale troublesome spreadsheet processes. If you would like to find out more, schedule a demo or even just get some advice on the best approach to quantifying and managing spreadsheet risk, please do get in touch.