UK COVID-19 Test Error: Another Reminder of Spreadsheet Risk
When the news broke recently of a spreadsheet error that caused the temporary loss of thousands of COVID-19 test results in England, I could feel the sympathy of many in business who are trying to avoid this exact situation.
However, the good news is that there are tools available which can help reduce the chance of something like this happening to you—especially if you are working under pressure and tight deadlines.
Public Health England issue: What happened?
On Sunday, 4 October, Public Health England (PHE) announced that almost 16,000 coronavirus cases went unreported in England because of an issue that occurred during the ‘data load process’ that transfers COVID-19 positive lab results into reporting dashboards.
This database is used by the National Health Service (NHS) Test and Trace team to identify individuals for contact tracing. The issue was identified on Friday, 2 October, and it caused the temporary loss of data that was entered between 25 September and 2 October.
According to reporting done by the BBC, this ‘technical issue’ was caused by the way PHE collated the COVID-19 testing data. PHE had set up an automatic process to pull data from companies paid to analyse test results to discover who has the virus into traditional spreadsheets that would then be uploaded into the reporting dashboards.
However, the problem is that PHE chose to use an old spreadsheet format, called XLS, which could only handle about 65,000 rows of data. Essentially, the spreadsheet ran out of space, and it wasn’t caught by anyone until it was too late.
Temporarily losing this data may have caused delays in tracking the contacts of people who tested positive—a concerning result during a critical time when the UK is seeing an acceleration in the outbreak.
Reducing the chance of error
It’s understandable that this has been an incredibly trying time for everyone at PHE and the NHS, including the analysts working on this together with the private contractor. They are working under intense pressure during an already stressful time.
Even though lives are not literally on the line for all of us in accounting and finance, we can still relate to this situation. Lots of scrutiny, tight deadlines and constantly under the magnifying glass. And, of course, spreadsheet errors are nothing new to us, either. From a cut-and-paste error that costs millions of dollars, to manually entering wrong numbers in cells—the list of desktop spreadsheet errors that have caused both massive financial and reputational damage is longer than you would expect!
But, the right tools can help reduce the chance of error. Here are a few suggestions for what you should be looking for in a tool to replace your traditional spreadsheets:
Fit for purpose
Traditional desktop applications are popular because they are accessible, quick to pick up and they can easily be used in situations they were not designed for—but that means they may not be the robust solution you need for complex situations. Plus, desktop applications don't have the controls to help teams validate and detect inevitable problems early on.
Using a collaborative cloud technology—especially one that is purpose-built for accounting and finance teams—not only helps you reduce risk, but you can also save time and cut costs.
Connect your data
One of the big sources of stress for accounting and finance teams is ensuring their numbers are consistent and up to date throughout all their spreadsheets and reports. With the structured and unstructured use of spreadsheets, it is difficult to guarantee the accuracy of data residing in these applications.
Worrying about the accuracy of their data has surely caused many sleepless nights for CFOs, but this also leads to long hours for teams who need to manually make updates, and then check and recheck documents to ensure the data is consistent and accurate.
Connected solutions eliminate these issues because they allow you to link data throughout all of your documents. Whenever a number changes at the source, it updates automatically everywhere else. And, you always know exactly where, when and what source it came from originally. This is a huge advantage for teams working to tight deadlines because it provides the confidence that your numbers are consistent and accurate throughout your work.
Remove manual processes
Eliminating manual processes goes a long way to reducing risk. When you are sourcing data from multiple disparate locations then manually copying and pasting this data from your spreadsheet into a document hundreds or thousands of times, that means you have hundreds or thousands of opportunities for error—which can be incredibly costly and damaging.
You can reduce the chance of error by connecting your spreadsheets and documents directly to your source data. By connecting data, you remove the need to repeatedly convert formats and, as a result, mitigate all the risks associated with this process. Plus, you can automate data refreshes, so all your numbers are always up to date. This will not only help you reduce risk, but it also creates time for you to conduct value-add tasks or other efficiency projects.
Where to start?
The reality is that this is just the latest high-profile spreadsheet error. It certainly won't be the last. This, however, should serve as a reminder of the tools available that can make sure this doesn't happen to you.
Understandably, your organisation may not have the budget or time in reporting cycles to immediately address a situation like this. However, you can still start small with a few critical functions that can help mitigate short-term risk and set your team on the path to long-term transformation.
Which spreadsheet errors are keeping you up at night? Get in touch with us, and we can talk through the challenges you are facing right now.
About the Author
Andie’s main area of interest is the role of technology and data in the future of corporate reporting. She is an experienced data and semantic modeller and has been working with XBRL® for over 15 years. Andie is co-chair of the Entity-Specific Disclosure Task Force, a member of a number of other XBRL International task forces and a member of the FASB Dimension Modelling Working Group. Prior to her role with Workiva, Andie spent five years at the IASB working on the IFRS Taxonomy and technology in corporate reporting. Andie has a degree in biological sciences from St Catherine’s College, Oxford.