The economic and social risks of spreadsheet errors
Whenever executives think of business risks, they usually consider well-known factors like competitors, compliance, and cybercrime, at the moment also COVID-19. However, there is a less obvious, yet potent risk at their fingertips – their trusted spreadsheet programme, which in most cases is Microsoft Excel. It is not just the quirks of the software itself, but rather the way business people use it that leads to trouble.
When Microsoft released the first version of Excel for the Macintosh in 1985 and two years later for Windows, nobody could have guessed how ubiquitous the use of this spreadsheet software would become. Already by the early 1990s, Excel had gained a dominating market position against its toughest competitor at the time, Lotus 1-2-3. It did not take long until the calculations and formulas in cells, rows, and columns led several users astray, causing a never-ending series of spreadsheet errors with sometimes spectacularly disastrous consequences.
History of errors
IT professor Raymond R. Panko from the University of Hawai’i has investigated spreadsheet errors for the last three decades and has come to devastating conclusions: almost 90 percent of all spreadsheets have errors. And even the most carefully edited spreadsheets have errors in one percent of all formula cells. This means that in larger spreadsheets with thousands of formulas there are dozens of errors.
While this in itself may not yet sound shocking, the implications of spreadsheet errors definitely are scary. Nearly one out of five large companies has suffered financial losses due to spreadsheet errors. Typically these errors are caused by a combination of human mistakes and the complexity of large spreadsheets, which provide plenty of opportunity to go wrong. Most of these mistakes do not have an economic impact, but some do. And sometimes, the damage is huge, as the following examples show.
US photographic product company Eastman Kodak had to restate financial results for two quarters by combined 15 million dollars because of an erroneous spreadsheet. It had miscalculated the severance and pension-related termination benefits accrued by one employee.
JPMorgan Chase, the largest bank in the United States, made a wrong credit portfolio assessment based on several faulty equations in a spreadsheet, which cost them approximately 6.5 billion dollars in losses and fines. US mortgage-loan company Fannie Mae had to restate its 2003 third-quarter financials due to a 1.1 billion dollar spreadsheet error, which was due to the flawed implementation of a new accounting standard.
Lost COVID-19 test results in England
While the economic damage by erroneous spreadsheets of businesses large and small is already huge, there is also a high social price to be paid for spreadsheet errors in the public sector. One of the most recent cases involved the loss of COVID-19 test results in England. As BBC News reported in early October 2020, the stunning number of 16,000 coronavirus cases went unreported in England, due to a flawed Excel template by Public Health England (PHE), an executive agency of the UK Department of Health and Social Care. The problem was caused by the way PHE assembled logs produced by commercial firms paid to analyse swab tests of the public, to discover who has the virus.
The firms recorded their results correctly in CSV files, text-based lists that can be processed by Excel. PHE had set up an automatic process to pull these data together into Excel templates for upload to a central system. The problem was that the developers at PHE had chosen an old file format for the templates, the XLS format, instead of the current XLSX format. As a consequence, each XLS-based template could handle only about 65,000 rows of data instead of the one million-plus rows that the XLSX format is capable of. And as each test result created several rows of data, this meant that each template was limited to about 1,400 cases. When that total was reached, further cases were cut off.
How to contain spreadsheet errors
Efforts to understand and fight the problem of spreadsheet errors go back more than two decades. Already in 1999, a group of British spreadsheet researchers from the University of Greenwich, the University of Wales Institute Cardiff and Her Majesty’s Customs and Excise joined forces to create the European Spreadsheet Risks Interest Group (EuSpRIG), which is dedicated to the art of spreadsheet risk management. EuSpRIG claims to be the largest source of information on implementable methods for processes and methods to inventory, test, fix, document, backup, archive, compare and control the legions of spreadsheets that support critical corporate infrastructures. EuSpRIG runs an annual conference which provides a forum for researchers, practitioners, trainers, vendors, consultants, regulators and auditors to discuss the latest developments in spreadsheet risk management.
Despite the efforts of EuSpRIG and others to reduce the occurrence and damage of spreadsheet errors, the problem seems to persist. In some cases, like the handling of COVID-19 test results in England, the solution may not be to improve the spreadsheet or to better educate users in the proper use of Excel. Instead, it might be better in such cases to rather get rid of spreadsheets altogether and handle large amounts of data in databases that ensure the consistency and integrity of the processed data.
© AdobeStock