Life-and-death spreadsheet errors

Life-and-death spreadsheet errors do occur. Two recent spreadsheet errors in the healthcare sector in Scotland and England had impacts beyond mere dollars and cents. Especially in these instances, controls to ensure proper spreadsheet design, use, and verification, are critical.  

In an article entitled “Sick Kids delay due to spreadsheet blunder”, the August 27, 2020 edition of the Scotsman reported that a 2012 “human error in copying” data, delayed the July 2019 opening of a £150M hospital. The recently discovered error caused material miscalculations related to the ventilation system, which in turn led to the system failing the required operational standards. The opening delay meant that prospective patients lost access to possibly lifesaving or at minimum, life-improving services from the hospital.

In an October 5, 2020 article entitled Covid: Test error ‘should never have happened’ – Hancock, the BBC describes a spreadsheet error which caused Public Health England (PHE) to understate the reporting and contact tracing of positive COVID-19 cases by about 16,000 persons. One result of the error is that these 16,000 persons unknowingly continued to expose thousands of others to a life-threatening illness.

As the BBC explains, use of an old Excel file format caused the error. PHE implemented a system in which it imported text files into Excel templates to accumulate test results from commercial COVID-19 testing centres. However, the templates used “XLS”, which is an old Excel file format. (This file format predates Excel 2007). One limitation of the XLS file format is that it can only accommodate about 65,000 rows of data. Once each of the PHE templates hit that maximum row number, it simply omitted the remaining test results.

In the ensuing post-mortem about these errors, one popular view is that multi-billion-dollar behemoths responsible for critical healthcare policy and spending, should not be cobbling together data from spreadsheets. Instead, the thinking goes, such organizations should implement sophisticated data modelling software solutions.

Nonetheless, absent more appropriate data modelling solutions, there are still several internal controls which will reduce the risk of spreadsheet errors. An obvious control is to always use the most current version of software, to benefit from improved functionality, technical support and other features typical of newer versions. Had PHE used XLSX, which is a current Excel file format, it would have avoided the error. XLSX handles 1,048,576 rows compared to the 65,000 limit with XLS.

The PHE error also underscores the value of batch totals as a control procedure. PHE might have uncovered the error sooner if it had simply checked that the total of all the templates equalled the sum of all the results in the text file. In other words, if it imported 10 text files with a total of 10 test results each, then it should have ensured that it had 100 test results in the final templates.

Excel’s Formulas Tab includes Formulae Auditing functionalities which can detect errors. Quite often, even a simple visual review of cell contents (CTRL + ~) for inconsistencies or errors, will reveal mistakes.

A critical control is to design spreadsheets to reduce the risk of errors, from the outset. For instance, if several calculations will use the same value, do not retype the value in each of the calculations. Instead, enter this value only once, and refer all calculations which require that value to that single cell reference. This reduces the risk of errors from typos. It also makes it easier to modify the spreadsheet and perform “What-If Analysis”.

Or, as the Institute of Chartered Accountants in England and Wales (ICAEW) explains in Twenty principles for good spreadsheet practice, under Designing and building your spreadsheet, “Separate and clearly identify inputs, workings and outputs”. This and other practices will help to eliminate timewasting and costly errors.

Meeting your duty of care: Design spreadsheets to reduce the risk of errors from the outset. Implement error-checking and other control procedures in the design, use and verification of spreadsheets.

Log in to Finance and Accounting PolicyPro, and review SPP FN 7.06 – Spreadsheets, for procedures which will reduce the risk of spreadsheet errors.

Policies and procedures are essential, but the work required to create and maintain them can seem daunting. Finance and Accounting PolicyPro, Not-for-Profit PolicyPro, and Information Technology PolicyPro, co-marketed by First Reference and Chartered Professional Accountants Canada (CPA Canada) contain sample policies, procedures, checklists and other tools, plus authoritative commentary to save you time and effort in establishing and updating your internal controls and policies. Not a subscriber? Request free 30–day trials of Finance and Accounting PolicyPro here, Not-for-Profit PolicyPro here, and Information Technology PolicyPro here.

• About
• Latest Posts

Apolone Gentles, JD, CPA,CGA, FCCA, Bsc (Hons)

Apolone Gentles is a CPA, CGA and Ontario lawyer and editor with over 20 years of business experience. Apolone is leveraging 20 years of business and accounting experience to build a commercial litigation practice with an emphasis on construction law. She has held senior leadership roles in non-profit organizations, leading finance, human resources, information technology and facilities teams. She has also held senior roles in audit and assurance services at a “Big Four” audit firm. Apolone has also lectured in Auditing, Economics and Business at post-secondary schools.

Latest posts by Apolone Gentles, JD, CPA,CGA, FCCA, Bsc (Hons) (see all)

• October 19, 2021: Changes for Ontario entities - October 6, 2021
• Cybersecure … like a bank - September 8, 2021
• Cybersecurity and insurance - August 4, 2021