The financial fails and business risks of spreadsheets

It’s now 35 years since Microsoft first launched its Excel spreadsheet software. It dates back to a time of Live Aid concerts and Mikhail Gorbachev as the leader of the Soviet Union.

Despite its age, Microsoft Excel (and subsequent spreadsheet products) has remained a part of the finance function for many businesses and…that’s a problem.

Why are spreadsheets a problem?

It’s an issue because spreadsheets were not designed to handle the volume and complexity of data that’s generated in today’s digitally connected world.

An example of the risks posed was an incident that occurred early this month and involved the UK's health authority, Public Health England (PHE). In involved the tracking of COVID-19 data.

Public Health England: What went wrong?

PHE operates as part of the UK’s National Health Service. To help tackle the coronavirus pandemic, the health authority was tasked with collecting and collating the thousands of COVID-19 test results coming from public and private labs across England.

The data was supplied in the form of CSV files, which PHE then imported into Microsoft Excel. The use of an outdated XLS format to carry out the transfers meant that the information imported was limited to 65,000 rows of data.

With imports often exceeding this limit, it resulted in 15,841 positive COVID-19 test results being lost to the system. Simply put, the error could have been avoided if spreadsheets had been replaced by a more up-to-date format.

Since then, the incident has prompted a review of ‘legacy’ systems within the NHS and highlighted the inherent dangers of an outdated service and reliance on manual processes.

History of financial spreadsheet fails

The problems caused by the manual handling of Excel spreadsheets are nothing new. Why? Because humans error.

This makes for an unfortunate history of errors and mix-ups that have caused some eye-watering financial mistakes:

1995 Fidelity Magellan Fund ($2.6 billion)

The omission of a single minus sign when entering financial data into a spreadsheet caused a $2.6 billion overstatement for this business. Fidelity Magellan’s president explained to shareholders: “the error occurred when the accountant omitted the minus sign on a net capital loss of $1.3 billion and incorrectly treated it as a net capital gain”.

2003 Fannie Mae ($1.1 billion)

The US mortgage provider, Fannie Mae, had to correct its 2003 financial results after finding a $1.1 billion spreadsheet error. A review of the incident concluded it was due to, “honest mistakes in a spreadsheet used in the implementation of a new accounting standard”.

2004 University of Toledo ($2.4 million)

A typo when entering a spreadsheet formula resulted in the University of Toledo overestimating its budget by $2.4 million. An investigation found that “an increase mistakenly was shown in a spreadsheet formula that led officials to overestimate enrolment and therefore revenue.”

2012 London Olympics (£200 thousand)

For the 2020 Olympics in London, a staff member accidentally keyed ‘20,000’ instead of ‘10,000’ into a spreadsheet. This resulted in the oversell of 3,000 tickets for a synchronised swimming event. It resulted in organisers having to contact the angry ticket holders to offer refunds or alternate events.

How many businesses still use manual spreadsheets?

While more finance teams are moving to automated and cloud-based alternatives, the Excel spreadsheet remains embedded within most businesses. Research by the Institute of Management Accountants (IMA) in 2018 found that 69% of US companies with annual revenue below $25 million use manual spreadsheets.

A similar level was found in a UK study from the same year. The FSN survey revealed that 71% of businesses were reliant on manual management of spreadsheets. The research also found more than half of respondents complaining about the time spent managing info by hand.

What are the business problems caused by spreadsheets?

The issues with Excel aren’t anything to do with the software itself - it’s the way that it’s used.

Microsoft Excel was created in 1987 as a way for small businesses and personal users to manually collect and collate information. It was designed to handle small pockets of data in a world where the Internet was still a little known academic research project.

An Excel spreadsheet keeps data locally, on a PC or laptop, with no integrated ways to easily share that information across a business. If you want to share something, you have to create a copy of your Excel document.

That wasn’t so much of a problem for small companies in the pre-Internet era. In the intervening years, however, this has become a major management weakness that creates risks for businesses and institutions throughout the globe.

Here’s why:

Lack of connectivity

Whenever a copy of a spreadsheet is made, it creates a financial management risk. With multiple duplicates existing across a company, it’s a constant struggle to identify the most accurate and up-to-date dataset. Whenever updates are made, it creates a flood of legacy docs that remain lurking in the system.

Research by the Delft University of Technology into the management of a Dutch bank found more than 2.5 million copies of spreadsheets in existence, this is in a company of 1,500 employees. It creates the kind of chaotic and fragmented data environment where mistakes are unavoidable.

Manual process

Spreadsheets are designed for data to be manually inputted into each cell. It’s a process that takes up so much of a finance team’s time and resources and makes human errors inevitable. As shown in the examples above, even the most trivial of data entry mistakes can have severe financial implications.

Growing pains

Lack of connectivity and manual processing combine to make Excel spreadsheets an increasing danger as a business grows. A spreadsheet system that may cope with a small number of employees and simple tasks will struggle to scale as a workforce grows and the finance function becomes increasingly complex.

‘Sticker plaster’ solutions

As a company grows, a finance manager often looks to adapt Excel spreadsheets to handle any increased complexity. This can be done using external templates or internal development of custom formulae. While these can work, they are also fraught with risks.

It creates ‘sticker plaster’ solutions that fail to address the underlying problems which are caused by outdated software. Speaking about the Public Health England incident, Professor Jon Crowcroft from the University of Cambridge stated:

Excel was always meant for people mucking around with a bunch of data for their small company to see what it looked like. And then when you need to do something more serious, you build something bespoke that works - there are dozens of other things you could do.

What are the alternatives to spreadsheets?

A whole generation of software and services has emerged to remove any reliance on Excel spreadsheets. These have the advantage of being designed to harness all of the benefits provided by cloud-based data management and process automation.

Instead of information being locked inside each locally stored document, the data is managed online via a cloud service. This means that the same spreadsheet can be accessed, shared and updated by any number of finance professionals from wherever they have an online connection.

The benefits of this have been particularly evident during the COVID-19 crisis as businesses have to switch to remote working operations. With a cloud-based approach, this transition can be easily handled.

Examples of modern cloud-based spreadsheets include Google Sheets and Apple Numbers. Microsoft has also created a digitally connected version of Excel as a part of their Office 365 suite.

But the real benefits come from using tools in which data directly integrates with. An example of this is the way that cloud-based services have transformed the way finance processes such as employee expenses accounts payable can be managed.

How does Integrated Data Management work?

Cloud expense management software integrates the collection and storage of expense data into the system. Whenever an employee makes a claim, all of the information is automatically stored and made instantly accessible to the relevant account handlers.

It removes the need to manually key data into Excel spreadsheets. It also brings easy access to company-wide data without the need to collate information from multiple documents. This in itself is a game-changer for financial reporting and analysis of company spend.

Connectivity to other accounting systems is built into the system using Application Programming Interfaces (APIs). These ensure that data can flow seamlessly between different systems and software without any need for spreadsheets.

Connectivity and automation deliver a fully scalable system that can handle anything from a small start-up to all the challenges of a large global corporation. By removing the manual processes, it significantly reduces the risks posed by human errors.

Tools that offer this kind of integrated approach are now available for every aspect of financial management, from processing invoices to tracking stock.

Smarter ways to manage finance spreadsheets

While there will always be a role for manual spreadsheets, as a primary tool for managing business finances, it’s no longer up to the job. The world it was designed to serve has changed and no amount of tinkering can cover its inherent shortfalls.

An effective tech stack eliminates the need for Excel spreadsheets, creating a data environment in which software, services and processes are able to communicate freely, creating stronger, more agile and future proof ways of working.