David Lyford-Smith is an expert at solving spreadsheet mysteries. Once, in a previous job, he was sent a payroll form to look over for a new starter. It had the number 40,335 in a random box, and payroll wasnt clear why it was there. So they assumed it was a joining bonus for the employee and drew up a draft pay slip with a £40,335 bonus, he says. But, when it comes to spreadsheets, assumptions can be costly.
Lyford-Smith isnt just a spreadsheet enthusiast. Hes the technical manager for the Institute of Chartered Accountants in England and Wales (ICAEW), running its Excel community group and as such has always been suspicious of numbers in that range. Thats how Excel stores dates, as serial numbers, he says. He was right: that wasnt a generous signing bonus, but the new hires starting date.
Lyford-Smith is part of a community of accountants, auditors and Excel power users who have joined forces in a quiet battle against illogical formulas, copy-and-paste errors, and structural chaos that cause data carnage.
Last week, the government stumbled into its own spreadsheet nightmare when it admitted contact-tracing efforts were stymied by a simple data processing mistake. Theyre not the first to fall victim to the curse of Excel and they wont be the last either. Last year, Canadian marijuana grower Canopy Growth had to correct its quarterly earnings after incorrectly posting a £40 million loss the real figure was £88m, miscalculated by a formula error. The companys stock fell two per cent. Boeing leaked employees personal data in a hidden spreadsheet column. An investment bank analysis of Teslas purchase of Solar City undervalued the company by $400m after double counting its debt in a spreadsheet. These may be egregious errors, but they are hardly uncommon.
Read next
Research suggests more than 90 per cent of spreadsheets have errors, and half of spreadsheet models used in large businesses have material defects. Given some 750 million people use Excel globally, there are plenty of errors needing attention. One prominent researcher calls spreadsheets the dark matter of corporate IT. And thats why people like Lyford-Smith have become defenders of the spreadsheet, mitigating the risks by fixing everyone elses mistakes.
Theyre an organised bunch, which is perhaps no surprise for spreadsheet specialists. The European Spreadsheet Risks Interest Group (EUSpRig) runs an annual conference to gather research (cancelled this year in favour of a webinar series), and collates best practice, training materials, and horror stories on its website and theres also a Yahoo Groups mailing list, where members offer tips and tricks, share links to resources and pick apart press coverage of the contact tracing debacle. In short, they cant figure out what the real problem was because the reporting is so disjointed and unclear. One popular share was a YouTube clip of a satirical Spreadsheet News Network from Matt Parker of Stand-up Maths as one member posted, it made my day.
Despite such extensive resources and the regular missteps that make headlines, this activity mostly goes without notice. Many companies disregard their warnings, because they dont think itll happen to them, says Patrick OBeirne, chairman of EUSpRig. Its the same as cybersecurity. Nobody expects to be hit by a phishing attack.
The spreadsheet defenders who guard against corporate chaos come from myriad backgrounds, but many get their start in accountancy and auditing professions that make heavy use of Excel. Such work is particularly common in two settings: in-house at financial and insurance giants, who have regulatory requirements around risk management, as well as consultancies and accounting firms.
Lyford-Smith started his accountancy career as an auditor, picking up spreadsheet skills along the way and training colleagues who needed help. And there was very clearly a need for extra attention, he says. Lyford-Smith now works for ICAEW, in his last role held the job title senior Excel practitioner for accounting firm BDO, where his email handle was just excel@.
Read next
Thats a similar path followed by other spreadsheet defenders: they started in programming or auditing, got good at Excel by teaching themselves, and then started to see the problems the rest of us miss. Hiran De Silva was a consultant to big firms, working six-figure contracts to build systems with spreadsheets. Dean Buckner once worked for the Financial Services Authority. Adrian Miric a chartered accountant working for South Africas AuditExcel, where he designs and audits spreadsheets and offers training, cut his teeth at KPMG. After a career in infrastructure and financial modelling, Kenny Whitelaw-Jones founded consultancy Gridlines.
Their clients are big banks, regulators and multinational companies, who cant find it pleasant to have someone swoop in to pick apart their formulas. But Lyford-Smith says throughout his career companies generally took criticisms well, and were happy to have the help. Almost everybody who uses Excel is self-taught, he says. I think theyre aware theres usually a better way of doing it. Outside of the accounting world, he believes spreadsheets arent appreciated as much as they should be. They arent seen as serious or proper, he says.
Of course, the spreadsheet checking done by these auditors is in high demand from those who understand what can go wrong. People will pay us because theyre like, Holy crap, were going to build this million dollar thing, we better make sure the numbers are right, says Whitelaw-Jones.
Thats especially true when they spot potentially expensive errors before they become costly. Buckner audited a spreadsheet built by a large bank to manage complex derivatives solutions, spotting a flaw in how the revenue models calculated interest. Had this carried on, it could have cost them hundreds of millions, Buckner says. They were gobsmacked that someone could walk in, look at the code and see that mistake.
The problem is that executives see a false dichotomy when a flawed or potentially risky spreadsheet is spotted. They tend to believe they can either continue using the spreadsheet as is or upgrade to a formal, bespoke software solution, which will be expensive. But theres another way: mitigating the risks of the spreadsheet. They should be thinking about how well they can control that, and what controls we should be implementing, he says.
Read next
The process of examining each spreadsheet is unique. There are software and tools to look for inconsistent formulas or problems with the structure, but a human touch is still required, says Simon Thorne, a lecturer in computing at Cardiff Metropolitan University and a EUSpRig member, because logical problems cant be picked up by such tools.
Part of the challenge of this work is that spreadsheet defenders must not only be Excel experts but know the industry that theyre working in. Beyond programming errors and typos, there are also logical faults perhaps the cost of a product is counted instead of its revenue, says Thorne. The logic is flawed in some way, and they [errors] are hard to spot because you have to be a domain expert to understand that its the wrong choice in a scenario.
To audit a complex spreadsheet, Miric uses software to go line-by-line to spot errors, as well as ones that could crop up from continued use. One basic test is to change the inputs and see if the outputs react as expected, perhaps putting in extremely high figures or random letters. In short, that means this work comes down to spending entire days reading spreadsheets. I would try and look at it and see if I could understand how it works without the person telling me, says Lyford-Smith. Usually, the answer would be no.
Thats a common theme for this work: even the people who make a spreadsheet cant always explain whats happening in it. Buckner begins by asking what data is being used a disturbing number of clients cant answer that one, he says and looks to understand where its being pulled in from. Once he lists out the data sources going in, and then seeks to understand how the data outputs will be used. In one instance, a team didnt realise their spreadsheet was used as a data source for another team further down the chain in the company, and changed a value in it. That changed the other teams output figures. The potential impact could have been in the hundreds of millions of dollars, he says.
Thats especially true for anyone on a budget and if our own government contractors are turning to spreadsheets as a cheaper option, know that countries with less money are leaning on them just as much. Miric notes that bespoke software simply isnt an option for some of his African clients. As a result spreadsheets almost become the default especially within governments, he says.
Read next
In the end, the problem isnt spreadsheets, but people. Lyford-Smith says horror stories such as the contact-tracing chaos tend to spark a backlash against Excel. But Excel is universally available and very accessible, he says. People are going to keep using it and usually the problems arent systems problems, theyre management or risk problems. Until we get better at Excel, well need people to protect the world from its own dependence on poorly designed spreadsheets.
More great stories from WIRED
The best Prime Day 2020 deals hand-picked by the WIRED team
Pee fanatics want to feed the world with your liquid gold
YouTubers are using AI to bring the past to life. Historians arent happy
Listen to The WIRED Podcast, the week in science, technology and culture, delivered every Friday
Follow WIRED on Twitter, Instagram, Facebook and LinkedIn
Get WIRED Daily, your no-nonsense briefing on all the biggest stories in technology, business and science. In your inbox every weekday at 12pm UK time.
by entering your email address, you agree to our privacy policy
Thank You. You have successfully subscribed to our newsletter. You will hear from us shortly.
Sorry, you have entered an invalid email. Please refresh and try again.
Home>>Sports>>Spreadsheets run the world. When they break, governments and companies turn to an elite group of experts to save the day
Sports