Meet the Excel warriors saving the world from spreadsheet disaster
Spreadsheets run the world. When they break, governments and companies turn to an elite group of experts to save the day
By Nicole Kobie
Tuesday 13 October 2020
Getty Images / WIRED
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 wasn’t 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 isn’t just a spreadsheet enthusiast. He’s 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. “That’s how Excel stores dates, as serial numbers,” he says. He was right: that wasn’t a generous signing bonus, but the new hire’s 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. They’re not the first to fall victim to the curse of Excel – and they won’t 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 company’s stock fell two per cent. Boeing leaked employees’ personal data in a hidden spreadsheet column. An investment bank analysis of Tesla’s 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.
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 that’s why people like Lyford-Smith have become defenders of the spreadsheet, mitigating the risks by fixing everyone else’s mistakes.
They’re 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 there’s 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 can’t 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 don’t think it’ll happen to them,” says Patrick O’Beirne, chairman of EUSpRig. “It’s 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@.
That’s 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 Africa’s 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 can’t 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 they’re aware there’s usually a better way of doing it.” Outside of the accounting world, he believes spreadsheets aren’t appreciated as much as they should be. “They aren’t 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 they’re like, ‘Holy crap, we’re going to build this million dollar thing, we better make sure the numbers are right,’” says Whitelaw-Jones.
That’s 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 there’s 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.
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 can’t 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 they’re 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 it’s 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.”
That’s a common theme for this work: even the people who make a spreadsheet can’t always explain what’s happening in it. Buckner begins by asking what data is being used — a disturbing number of clients can’t answer that one, he says — and looks to understand where it’s 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 didn’t 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 team’s output figures. The potential impact could have been in the hundreds of millions of dollars, he says.
That’s 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 isn’t an option for some of his African clients. “As a result spreadsheets almost become the default – especially within governments,” he says.
In the end, the problem isn’t 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 aren’t systems problems, they’re management or risk problems.” Until we get better at Excel, we’ll need people to protect the world from its own dependence on poorly designed spreadsheets.
Spreadsheets run the world. When they break, governments and companies turn to an elite group of experts to save the day
By Nicole Kobie
Tuesday 13 October 2020
Getty Images / WIRED
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 wasn’t 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 isn’t just a spreadsheet enthusiast. He’s 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. “That’s how Excel stores dates, as serial numbers,” he says. He was right: that wasn’t a generous signing bonus, but the new hire’s 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. They’re not the first to fall victim to the curse of Excel – and they won’t 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 company’s stock fell two per cent. Boeing leaked employees’ personal data in a hidden spreadsheet column. An investment bank analysis of Tesla’s 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.
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 that’s why people like Lyford-Smith have become defenders of the spreadsheet, mitigating the risks by fixing everyone else’s mistakes.
They’re 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 there’s 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 can’t 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 don’t think it’ll happen to them,” says Patrick O’Beirne, chairman of EUSpRig. “It’s 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@.
That’s 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 Africa’s 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 can’t 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 they’re aware there’s usually a better way of doing it.” Outside of the accounting world, he believes spreadsheets aren’t appreciated as much as they should be. “They aren’t 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 they’re like, ‘Holy crap, we’re going to build this million dollar thing, we better make sure the numbers are right,’” says Whitelaw-Jones.
That’s 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 there’s 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.
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 can’t 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 they’re 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 it’s 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.”
That’s a common theme for this work: even the people who make a spreadsheet can’t always explain what’s happening in it. Buckner begins by asking what data is being used — a disturbing number of clients can’t answer that one, he says — and looks to understand where it’s 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 didn’t 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 team’s output figures. The potential impact could have been in the hundreds of millions of dollars, he says.
That’s 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 isn’t an option for some of his African clients. “As a result spreadsheets almost become the default – especially within governments,” he says.
In the end, the problem isn’t 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 aren’t systems problems, they’re management or risk problems.” Until we get better at Excel, we’ll need people to protect the world from its own dependence on poorly designed spreadsheets.
