Microsoft Excel was one of the ground-breaking inventions of data science, and is still used today by over 60% of businesses. Despite its easy to use features, large storage, and useful functions for organizing and analyzing data, it still has one big flaw: It relies on manual entries, which creates the potential for human errors.
For these reasons, reducing manual entries has been on the forefront of financial teams’ minds. In addition to reducing potentially catastrophic errors, Excel consolidation and forecasting platforms can save a tremendous amount of time and leave professionals with more resources to analyze, and less time spent doing copy-paste.
Although around 90% of Excel sheets contain errors, they are usually very minor mistakes or spotted before the data is used or published. However, there have been numerous mistakes that have been news worthy due to their huge repercussions.
Big or small, comical or scary, private organizations or government agencies, Excel errors can produce all sorts of negative results.
Here are 5 of the biggest mistakes that have caused a considerable amount of monetary damage or embarrassment to organizations:
1) Biggest Loss Overall
The 2012 London Whale debacle at JP Morgan made headlines around the world at the time of occurrence. Trader Bruno Iksil received the nickname the London Whale for accumulating a huge amount of outsized Credit Default Swaps (CDS) in the market as part of the bank’s hedging strategy. After hedge fund insiders started noticing that the CDS market was being affected by his aggressive trading tendencies, chaos ensued and resulted in immediate losses of $2 billion for the firm.
Only a few months later, the loss was reported to be above $6 billion(!) Some experts put it even higher, after taking into account all of the chain reactions. After complex investigations involving over 1000 people, it was found that the value at risk model (VaR) which was used to determine JP Morgan’s hedging strategies relied on manually copying and pasting data across spreadsheets. The formulas were not adjusted accordingly, as the data incorrectly divided rates by their sum, rather than their average.
While the investigation uncovered an additional host of internal, leadership, and risk value problems within the organization, a mistake as trivial as manual Excel inputs contributed greatly to over $6 billion in losses. If caught at the right time, the London Whale debacle may have been far less damaging.
2) Disaster Avoided
The 2012 London Olympics Excel mishap is now a comical error, but had the potential for a much worse outcome. Tickets were hard to come by for the biggest sporting event in the world, so many were surprised when the 2nd round of ticket sales for the synchronized swimming competition had thousands of seats available.
After realizing something was off, it was revealed that an employee for the ticketing company made the simple mistake of typing “2” instead of “1”, resulting in 20,000 seats going on sale instead of 10,000. Luckily, the mistake was caught early on and those who bought the non-existent seats (the metaverse wasn’t created yet) were able to exchange them for other events in the Olympics.
Some customers came away satisfied with the outcome, as they were able to upgrade their tickets to higher profile competitions, which they wouldn’t have been able to attend otherwise. In the end, the only harm that was done was having to admit an embarrassing mistake, but imagine the chaos if it wasn’t caught on time and 10,000 unhappy fans were left at the entrance to the event with invalid, very expensive tickets.
3) One Small Dash can be Worth Billions
Although Fidelity’s Excel mistake didn’t reach the record $6 billion error of JP Morgan, one small character can make a huge difference, especially when that character is a minus sign. In 1994, Fidelity Investment announced that they would make a $4.32/ share distribution on their Magellan fund by the end of the year.
The good return was in fact too good to be true and Fidelity had to retract the dividend distribution after realizing that one small mistake made their estimate extremely inaccurate. While transferring records from one spreadsheet to another, an accountant neglected to put a minus sign on a net loss of $1.3 billion, making it a net gain.
This mistake created a double error, as not only was the loss not reported, but it was input as a gain. This small Excel error made the report be off by a grand total of $2.6 billion.
4) Biggest Mistake not in Dollar Value
Excel errors don’t only involve incorrect dollar amounts, and some can have other negative consequences. The most recent mistake involved Public Health England (PHE) and occurred while reporting Covid-19 cases in October of 2020. They set up an automatic process to pull Covid-19 test results together into Excel templates before uploading them into a central database. However, without thinking ahead, they chose XLS, an old and outdated file format which can only hold about 65,000 rows of data, a far cry from Excel’s 1 million row capacity.
Since each entry held quite a few rows of data, each PHE file could only contain around 1,400 cases, meaning that many were simply not saved in the system due to running out of space. Although the agency caught the mistake within a few days, the huge gap in missing data delayed the contact tracing, and proper measures were not put in place for those whose results came back positive. Had they used XLSX (20 years newer than XLS), then data entries would have had around 16 times more space. That being said, Excel’s limit of 1 million rows would eventually run out as well when using huge quantities of data. Big organizations such as a country of 67 million’s health system need to upgrade the way they use the software.
5) U.S. Government’s Biggest Excel Mishap
The British government is not the only one that makes costly mistakes, as the U.S. had one that incorrectly reported over $1.1 billion. Fannie Mae, the popular name for the U.S. Federal National Mortgage Association, released their 3rd quarter earnings in October of 2003. Not long afterwards, they were forced to correct some big mistakes from the financial statements and attributed it to a spreadsheet error.
The statement that was released after the fiasco talked about how they needed to implement a new accounting standard in a short period of time: ”To implement this standard, Fannie Mae utilized information from its internal, automated systems in conjunction with spreadsheets that made additional calculations necessary under the new rule.” One of those spreadsheets contained a major error. The mistake caused its mortgage portfolio to grow by $1.7 billion, its total asset by $1.04 billion, and its unrealized gains by $1.3 billion.
The full report was never released on what exactly happened, but whatever the “small” spreadsheet mistake was, it managed to cause a lot of damage. After announcing and fixing the error, Fannie Mae’s stock fell, and the company came under much scrutiny with calls for more auditing, specifically because it is a government sponsored enterprise.
Conclusion
There are many ways to prevent manual Excel errors. To begin with, double and triple checking the work is a great way to prevent harmful errors. This should not only be done by the person who created the spreadsheet, as checking your own work will not always catch the mistakes. Having additional employees on the team go over the data is crucial, and in some cases bringing in an outside auditor is worth the time and money. Another way to highlight mistakes is by identifying outliers in the data. Lastly, when there is a deadline, rushed work is more prone to error. It is better to take the extra time to ensure that it is done right, rather than rushing through the processing of the data and allowing room for potential errors.
While all of these tips are useful, and if implemented correctly would probably have saved all of the mess-ups above from occurring, there are other ways to take steps to ensure that manual entries won’t create problems. There are many companies that have tried to “recreate” Excel by making a spreadsheet platform similar to it, but with less manual entries and more automated data and functions. However, businesses simply don’t want to leave Excel. It’s a user-friendly platform, they have been using it for years, and it’s simply too hard to train an entire team how to use a new spreadsheet system.
FP&A software solutions that work with Excel instead of replacing it have been the best answer for cutting down on manual entries that can lead to mistakes. Since the familiarity of Excel is still there, no mistakes can occur on the basis of learning new functions and keys. In addition, by consolidating and automating data collections, anything from finance and operations to budget and forecasting becomes an easier task and leaves more time to analyze. On the way, the system will help prevent your organization making it into the Wall of Shame for Excel errors.
