Buggy spreadsheets: Russian roulette for the corporation

The chasm between common sense and computer programming


How many scenarios can you imagine where a momentary loss of concentration could cost over $1bn? Perhaps a nuclear power station meltdown...or if a currency trader hit a few wrong keys? Well, another possibility is a simple spreadsheet error.

In October 2003, soon after announcing third quarter earnings, Fannie Mae had to restate its unrealised gains, increasing them by $1.2bn. This highly unwelcome outcome was said to stem from "honest mistakes made in a spreadsheet used in the implementation of a new accounting standard".

The really, really bad news is that millions of similar errors are almost certainly being made every year, many of them in business-critical financial spreadsheets. Although they are the quintessential end-user tool, spreadsheets of any complexity are just as hard to write and maintain as any other kind of software - if they are to yield consistently accurate results, anyway.

At Cutter Consortium's inaugural European Summit in March, I took part in a spirited panel discussion about SOA and related matters. At one point the redoubtable Oliver Sims suggested that Excel was the world's most widely-used software development tool - a statement that struck me as probably correct. But if so, how grave are the implications? After all, the overwhelming majority of spreadsheet users are not professional programmers, and it is doubtful whether most of them have even been formally trained to write spreadsheets.

If only organisations realised the harm they are laying themselves open to, they might be inclined to seek advice from a suitably qualified expert. Such as Dr Louise Pryor, an independent software risk consultant who specialises in complex financial models and spreadsheets.

As a Fellow of the Institute of Actuaries, with a PhD in Computer Science and extensive hands-on experience in commercial software development, Pryor is ideally equipped to explain how spreadsheets should be designed, written, and tested. Her website provides detailed and convincing evidence of prevailing spreadsheet error rates, some hair-raising war stories, good advice, and loads of other useful information.

Other valuable resources include the spreadsheet research (SSR) site maintained by Professor Ray Panko of the University of Hawaii, and the European Spreadsheet Risks Interest Group (EuSpRIG). Several books have been written on the subject; for instance Patrick O'Beirne's Spreadsheet Check and Control.

Panko has collected the best available evidence for spreadsheet error rates, based on field audits by organisations such as Coopers and Lybrand, KPMG, and HMCE (the UK's Customs and Excise department). Of 54 spreadsheets audited between 1997 and 2000, no fewer than 49 were found to contain significant errors - a defect rate of 91 per cent. In a more recent exercise, every single one of 30 project financing spreadsheets scrutinised had at least one mistake.

This is not to suggest that spreadsheets are uniquely bug-ridden; there is convincing evidence that virtually all non-trivial software contains defects. According to Panko's Human Error website, "spreadsheet error rates look like error rates in normal program development and in code inspection". He estimates typical human error rates at about 0.5 per cent for simple mechanical tasks like typing, and five per cent for more complex logical activities such as programming.


Other stories you might like

  • Prisons transcribe private phone calls with inmates using speech-to-text AI

    Plus: A drug designed by machine learning algorithms to treat liver disease reaches human clinical trials and more

    In brief Prisons around the US are installing AI speech-to-text models to automatically transcribe conversations with inmates during their phone calls.

    A series of contracts and emails from eight different states revealed how Verus, an AI application developed by LEO Technologies and based on a speech-to-text system offered by Amazon, was used to eavesdrop on prisoners’ phone calls.

    In a sales pitch, LEO’s CEO James Sexton told officials working for a jail in Cook County, Illinois, that one of its customers in Calhoun County, Alabama, uses the software to protect prisons from getting sued, according to an investigation by the Thomson Reuters Foundation.

    Continue reading
  • Battlefield 2042: Please don't be the death knell of the franchise, please don't be the death knell of the franchise

    Another terrible launch, but DICE is already working on improvements

    The RPG Greetings, traveller, and welcome back to The Register Plays Games, our monthly gaming column. Since the last edition on New World, we hit level cap and the "endgame". Around this time, item duping exploits became rife and every attempt Amazon Games made to fix it just broke something else. The post-level 60 "watermark" system for gear drops is also infuriating and tedious, but not something we were able to address in the column. So bear these things in mind if you were ever tempted. On that note, it's time to look at another newly released shit show – Battlefield 2042.

    I wanted to love Battlefield 2042, I really did. After the bum note of the first-person shooter (FPS) franchise's return to Second World War theatres with Battlefield V (2018), I stupidly assumed the next entry from EA-owned Swedish developer DICE would be a return to form. I was wrong.

    The multiplayer military FPS market is dominated by two forces: Activision's Call of Duty (COD) series and EA's Battlefield. Fans of each franchise are loyal to the point of zealotry with little crossover between player bases. Here's where I stand: COD jumped the shark with Modern Warfare 2 in 2009. It's flip-flopped from WW2 to present-day combat and back again, tried sci-fi, and even the Battle Royale trend with the free-to-play Call of Duty: Warzone (2020), which has been thoroughly ruined by hackers and developer inaction.

    Continue reading
  • American diplomats' iPhones reportedly compromised by NSO Group intrusion software

    Reuters claims nine State Department employees outside the US had their devices hacked

    The Apple iPhones of at least nine US State Department officials were compromised by an unidentified entity using NSO Group's Pegasus spyware, according to a report published Friday by Reuters.

    NSO Group in an email to The Register said it has blocked an unnamed customers' access to its system upon receiving an inquiry about the incident but has yet to confirm whether its software was involved.

    "Once the inquiry was received, and before any investigation under our compliance policy, we have decided to immediately terminate relevant customers’ access to the system, due to the severity of the allegations," an NSO spokesperson told The Register in an email. "To this point, we haven’t received any information nor the phone numbers, nor any indication that NSO’s tools were used in this case."

    Continue reading

Biting the hand that feeds IT © 1998–2021