From independent accountants to the biggest financial companies in the world, spreadsheet blunders are all too common, and remind us why it's so important to triple-check our work.
In fact, EuSpRIG (the European Spreadsheet Risks Interest Group) has dozens of records of such incidents – and each one teaches us a different lesson.
Here, we reveal the most common spreadsheet blunders and how to avoid them.
Typos
The easiest spreadsheet error to make is also one of the most common – and the potential consequences can be sweat-inducing. A recent survey by Bloomberg BNA, a legal, tax, regulatory and business information provider, found that human error is the leading cause of accounting mistakes. Specifically, they found that 27.5% of professionals reported that incorrect data had been manually entered into their systems at work.
Small keystroke errors can have big ramifications, too. At the London 2012 Olympics, for example, four synchronised swimming events were oversold by 10,000 tickets after a staff member accidentally logged ‘20,000’ available tickets instead of ‘10,000’ into a spreadsheet cell. Easily done, but, unfortunately, not so easily solved.
Copy/paste errors
Copy and paste errors can lead to similar issues as typos, but they can also create a whole new kind of problem, as AstraZeneca found out not long ago.
The pharmaceutical giant was required to reissue financial forecasts released in December 2011 after it emerged that the originals, which were sent out to sector analysts, contained confidential information about the business. As a result, the company’s shares fell by 0.4%.
AstraZeneca said the error occurred when “confidential company information was inadvertently embedded in a spreadsheet template” during a “routine consensus collection process". This mistake reminds us all of the importance of proofing documents before sharing them.
Formula fails
Sometimes it’s the maths that matters. Back in 2013, JP Morgan’s Chief Investment Officer lost billions of dollars due to errors in their 2012 value at risk (VaR) model – a spreadsheet that quantified the risks attached to their investments.
JP Morgan's Task Force Report explained that a formula designed to factor in changes to hazard rates over time had a critical flaw: after subtracting the old rate from the new rate, the spreadsheet then divided by their sum rather than their average, which “had the effect of muting volatility by a factor of two and of lowering the VaR.”
The report specifically highlights that the mathematician and model developer behind the spreadsheet was under-supported and cut corners due to time pressure – which clearly illustrates the benefits of not rushing a delicate task.
Missing data
Accidental deletion is one of the biggest fears of any spreadsheet user – particularly if you don't catch it with enough time to 'undo'. A simple slip and, within a nanosecond, a formula can fall into error mode, causing hours of head-scratching and delay.
Of course, sometimes the missing data isn’t so easily explained, as Clallam County, USA, found out when accusations of theft and money laundering emerged after more than $600,000 was found missing from the accounts – a discrepancy masked by transactions included in hidden rows. This case demonstrates the value of effective checking and oversight.
Dead links
Still over in the US, the Knox County Trustee’s Office found that simply failing to organise your spreadsheets can cause problems too, when, back in 2011, auditors KPMG pinpointed a $6m “accounting error”.
The problem, it seemed, was that a single account had been incorrectly linked in the spreadsheet, causing the accounts to show cash in hand of $122.7m instead of the actual figure of $128.9m. The error was first blamed on a “software issue” before human error was discovered.
The lesson? Check, double-check and triple-check link formulas to make sure they're pulling data correctly.
Formatting errors
Even the most careful organisations in the world aren’t immune to spreadsheet errors. UK security service MI5 inadvertently collected subscriber data from 134 telephone numbers that weren’t related to its investigations, after incorrect spreadsheet formatting led to requests for phone numbers ending in “000” instead of the three numbers they really needed.
The data was subsequently destroyed and, reassuringly, they now check numbers manually before making requests. We are all vulnerable when it comes to protecting data from human error, however it is also important that private data remains, well, private.
How can I increase my level of protection?
Keeping data private has always been a huge priority for accountants, but a recent UK Supreme Court ruling has made it more important than ever.
In a landmark case against Google, it was decided that compensation must be paid out in the event of a security breach, even if the breach hasn't resulted in any financial loss for the client.
The 2015 Google Inc. v Vidal-Hall court case ruling has nothing to do with accounting, but it does have massive knock-on effects for any business that deals with sensitive data. The case was based on Google working around the privacy settings of Safari (the Apple iPhone browser) to share the claimant's search data with advertisers. No-one was claiming for financial loss, but rather for distress and anxiety.
The court ruled in the claimant's favour, ordering Google to pay compensation. This ruling both clarified and broadened the Data Protection Act, as it was previously thought that companies would only need to pay compensation if the data breach resulted in a financial loss.
How will it affect accountants?
John Warchus (of law firm Moore Blatch LLP) believes that this ruling could result in more litigation towards accountants. He warns that "accountants should urgently review their data protection procedures and strengthen where necessary as more compensation claims are likely and the amount of damages awarded is also likely to increase.”
Security breaches can happen in any industry, but in a field, such as accounting – where professionals routinely handle highly sensitive, personal information – the risks, and potential consequences, are particularly serious.
Having robust, up-to-date firewalls and anti-virus software installed on computers is essential. And, although it might be slightly onerous to continually install anti-virus software updates, it's important to keep up with these to stay protected against evolving threats.
Another smart measure is to keep on top of deleting and destroying old client files, purging folders once fortnightly or monthly to minimise build-up. The Data Protection Act states that sensitive data shouldn't be kept for longer than necessary, and it's a rule that could help to minimise the impact of a breach.
The Association of Chartered Certified Accountants also emphasises that, even if accountants are using cloud platforms to store personal data, they can't transfer full responsibility to a third-party provider in the event of a breach. That's why it's crucial for accountants to go through a thorough due diligence process before selecting a provider, paying special attention to encryption security and the location of data centre servers.
An up-front investment of time (and even paying slightly more for a more secure provider) could prove more cost-effective in the long run.
These data nightmares could happen to absolutely anyone, which is why it's important to have rigorous checking procedures, follow best practice advice from experts like PwC, and ensure you're protected for any eventuality.
Ever made a serious mistake on a spreadsheet? Let us know in the comments below.
