If you work in the maritime sector and are still reliant on spreadsheets, what are the pros and cons when they are used to manage data critical to the success of your business? What are the alternatives, and how valuable are they?
According to Satya Nadella, the Microsoft CEO, Excel is used by an estimated 800 million people worldwide. Launched in 1985 and replacing Lotus 1-2-3 as the industry standard from around 1993, it’s no wonder it’s so widely used with Microsoft Office being the default software used by businesses all over the world. Not only that, when you want to export data from an app, it’s likely you will be offered the options of either Excel or pdf.
From talking to literally thousands of people working in the maritime sector we estimate that around 60% still rely on spreadsheets when managing their businesses. We come across some of the biggest names where Excel still plays a critical part in what they do. Being such big names, and being so successful, what can be wrong with that? Why change something that’s not broken? Surely, well over half a billion people can’t be wrong?
No doubt about it, Excel is indeed an incredibly valuable, highly flexible tool. Most users know the basics and there is minimal time wasted waiting for support; every organisation seems to have an Excel master, who can answer most of the more difficult questions; a quick search online will often reveal video instructions for the more complicated queries; and it’s great for the basic analysis of data.
That sounds like a pretty convincing argument to stick with spreadsheets, particularly if change makes you feel uncomfortable.
However, even if you are currently happy with what you’ve got, there is something you ought to be aware of, if not already: Excel is not necessarily the most efficient way to manage workflows and using it, for some businesses at least, does have some major shortcomings and significant risks. It’s also possible some of those big names could be even more successful, but most definitely they could better protect their business.
Regardless, whatever the final decision, at the very least, being aware of both the risks in using spreadsheets and the potential upside from using alternative solutions is a valuable thing to know – hence the reason for this article. For some it will serve to draw attention, to keep everyone up to speed, for others, maybe they will discover a more productive, less risky way to handle their data and manage their business.
If the decision is to take a closer look at how to reduce one’s reliance on spreadsheets, speaking to a sector specific software provider would be highly recommended. CompassAir, for example, is very widely used in the maritime sector by both ship owners and managers as well as SnP and chartering brokers. Our clients have found that the combination of specialist maritime knowledge, together with improvements in data entry, validation and search, deliver significant added value to their businesses. And if a user still wants to use spreadsheets to perform analyses, additional processing or to produce reports, then exporting to Excel is still available.
However, first things first…
What is the difference between Excel and a Database?
Probably, when we think of Excel we think of columns and rows of data, tables. Moving on to data tables and databases it’s easy to get confused, to misunderstand the differences between spreadsheets and databases.
That confusion is not unreasonable because a database is actually very similar to a spreadsheet – it has data stored in tables (in Excel you think of worksheets), with records (rows) and fields (columns).
There are however major differences. To start with, Excel has around a million rows, whereas databases are almost unlimited in comparison. At its simplest, Excel is essentially an electronic version of a sheet of accounting paper. The ability to create tables containing data in a spreadsheet leads some to believe wrongly that spreadsheets are interchangeable with databases. The similarities are easy to see – both can contain large amounts of data; both can perform calculations; both can analyse data; and both nowadays frequently have multiple users.
However, in a spreadsheet each cell can contain different types of values, from names through to formulae, through to numbers, each being formattable. Databases only contain raw data, no fonts or colours, with each field containing only a pre-defined type of data. Trying to insert a name in a number field within a database will flag up as an error. In a spreadsheet there will be no error message. In a spreadsheet, cells can also contain formulae – meaning that cells can contain calculations. A database only contains data and it is on that that calculations and operations are based. Data records and calculations are therefore impossible to be mistaken.
So what are the risks or shortcomings of spreadsheets?
Part of the problem is that Excel is a “jack of all trades, master of none”. Reports can be written, Gantt charts produced, dashboards created and presentations prepared even though other solutions, just as easily available even in the same suite such as Word, Project and PowerPoint, would be more appropriate. This familiarity and ease of use is maybe what increases the risk we rely on them too much, and don’t challenge them enough when it comes to holding critical data, analysing that data and then making projections about the future.
Bearing in mind that spreadsheets often drive presentations to board members and investors, any weaknesses they have will result in those same parties either having a misleading or incomplete view of their business. In a 2019 survey by FSN, based on responses from 532 senior finance professionals around the globe, across 23 different industries, the survey found that the current state of finance systems in use by many organizations are unable to support the dynamic environment in which businesses operate. The report concluded that finance systems are failing CFOs in their role as strategist and business partner and are leaving organizations vulnerable by failing to enable businesses to respond quickly to market changes. FSN found that 43% of respondents did not know how many spreadsheets were being used in their organisations, too much time was spent checking numbers after changes were made, in 57% of cases only one person could work on a report at a time, and 97% lost sleep over missing deadlines, making mistakes and the lack of adequate controls.
Scalability and Insufficient Memory
As mentioned above, each spreadsheet has over a million rows and around 16,000 columns. Enough for anyone’s needs you might think. Imagine however the capacity required to collect AIS data on the global fleet. Over half a million vessels use AIS to transmit their location, with co-ordinates being recorded every 3 minutes of every day. If you have ever tried to enter just a single number “1” in each of the over 17 billion cells in a spreadsheet, you will probably have seen the message “There isn’t enough memory to complete this action”. Excel has to load data into memory before it can be processed, so scalability is limited. Even with not so large spreadsheets, lags can be experienced when performing the most basic calculations. Separating data into different sheets and files makes working with the data more difficult, it makes organising them a challenge and increases the risk of inconsistencies.
It is much easier to search for information using a database, data can be organised and sorted based on different queries and, unlike Excel, code is not needed to do it.
This has not been such a significant challenge in the maritime sector, however if your business is accumulating large amounts of data then with Excel’s limitations in the number of records available combined with performance issues, it’s probably time to look at databases. With a database, data is much more streamlined, using less memory. Instead of multiple spreadsheets, data can be kept in one place, allowing a much better and more complete insight into the information held.
Data Errors and Consistency
One of the advantages of Excel is the ease with which data can be input. Unfortunately that also means errors can be made as there are limited controls in place to check the quality of that data. Oracle, who of course have a vested interest in promoting databases, claims that one in five large businesses have suffered financial losses as a result of spreadsheet errors. In a small business that can sometimes result in its demise. Spreadsheets are created by people and people make mistakes. In 2016 Ventana Research published the results of its survey in which more than 35% of the participants said that data errors are common in the most important spreadsheet they use in their job, 26% that formulae errors were common, with 19% aware of formatting errors.
Cells in Excel can accept any type of data. For example, a spreadsheet will allow you to enter a name in a cell which is meant to contain a number. Excel has limited controls to validate input data and those safeguards that do exist can easily be overridden. Poor data integrity is the result of missing data management tools that ensure accuracy and consistency. If an organisation suspects that it has inaccurate data then now is the time to look for alternatives.
Data integrity is a major advantage when working with databases. A change can be made that is then visible to all users. Data duplication is eliminated. When using a spreadsheet, making a change in one cell does not necessarily update that information being held in another cell, even more unlikely will be that it updates another worksheet. Using a relational database this can be avoided – a user accesses a data table and makes the change only once, thereby saving time and eliminating inconsistencies.
Time Consuming to Create and Maintain
Spreadsheets take time to construct and maintain – copying and pasting data from one sheet to another, checking formulae, linking sheets, it all eats up time. In addition, how a spreadsheet or a series of linked spreadsheets and worksheets is constructed will often depend on the expertise and past experience of the individual involved. It seems reasonable to say that most users will be self taught, which means there will exist a multitude of different ways to design a spreadsheet that needs to achieve even a relatively simple objective. Not only is time spent constructing the spreadsheet, but when used by a colleague there will also be a learning curve to understand how it works. Productivity can increase by making use of carefully chosen alternatives.
An example of how time consuming spreadsheets can be is when they are used for financial analysis. Often spreadsheets used in a finance department will cover monthly or annual periods. When putting together an analysis or presentation that is in respect of, say, a period of three years, it will take time to produce a consolidation, especially when taking precautions to either eliminate or at least ensure the risk of errors is minimised.
Lack of an Audit Trail
Passwords in Excel do prevent unauthorised access, but they do not prevent anyone in possession of the password from compromising data either deliberately or through human error. Data can be changed, duplicated and manipulated when there is minimal supervision. Anyone who has worked as an auditor will probably have at least one story about a fraud being committed that took advantage of this weakness. Even where changes are made with the best of intentions, they can still result in issues with data integrity and accountability. Whilst passwords do provide some protection, aside from knowing who is in possession of that password, a manager or auditor cannot track who has seen or accessed data, nor tell whether or not a password has been shared.
An inability to track who changed what and when, as well as spreadsheets being prone to manual input errors, broken links and incorrect formulae, makes it difficult for an organisation to be confident the records it keeps are completely free from errors. This is aggravated by the fact that Excel lacks version control: without copies of files being stored manually in folders, it is impossible to “roll back” corrupted files to an earlier version. Once again, this relies on manual intervention and is therefore prone to errors.
Connected Data and Duplication
A single spreadsheet can link to multiple workbooks, which in turn can link to other workbooks. Data can therefore be duplicated and, combined with the ability to copy and paste data so easily, data might not always be consistent. With multiple linked spreadsheets and workbooks it can be very difficult to understand relationships and hence data flow, especially if they have been constructed by a colleague. One database means one store of information, without duplication, contained within a stable structure that everyone understands.
Security is not a spreadsheet’s strong point. Whilst passwords and Windows based permissions are available, they are sometimes compromised with different operating systems, and tools are readily available online that can easily crack passwords.
Spreadsheets tend to be created by each person in an organisation. Because most individuals are well versed in the basics of Excel, and given it takes very little time to create a spreadsheet, to define the cells and to start entering data, an organisation will have not only what can easily amount to thousands of different files, but also there will often be multiple versions of the same file. In other words, there can be simply too many workbooks to be confident an even minimal level of security exists.
Excel is ideal where workflows are straightforward. However, when various types of related information are involved, entering, analysing and then displaying becomes that much more difficult.
The fact that spreadsheets do not handle relational data means that productivity will suffer. As an example, one spreadsheet may contain data on a vessel’s fuel consumption, a second spreadsheet about its location. There will not necessarily be a connection between the two and therefore producing a report covering both variables will be time consuming . However, this would not be the case if only one database were used, easy access then being available to all the information held on a particular vessel.
We have already referred to the time needed to understand someone else’s spreadsheet. Even when a spreadsheet is understood by all who use it within a department, multiple users accessing a single spreadsheet can be painful.
Spreadsheets were not designed to accommodate multiple users and simultaneous use, when it does occur, can corrupt files and cause performance issues. With several users, there is a danger that multiple versions of the same spreadsheet are updated and stored separately, resulting in uncertainty over which is the latest version. The most obvious challenges come from data being duplicated, files being overwritten, and delays whilst waiting for a colleague to finish what they are doing. With inadequate version control, data integrity is easily compromised.
When access to a file in a shared location is not available, it means spreadsheets need to be transferred or emailed between users. Not only is this inefficient, it also leads to multiple versions that soon become impossible to track. Several copies of a spreadsheet can co-exist, again with confusion over which is the latest.
With a database, multiple people can access and update at the same time, leading to increased efficiency and a reduction in the risk of error when entering and updating data.
Some Horror Stories
The purpose of this article is not to persuade you to move away from spreadsheets – as we said at the start, Excel is indeed an incredibly valuable, highly flexible tool. Instead it is to raise the question: are spreadsheets the best way to manage your business, both in terms of productivity and, importantly, data integrity?
Knowing an organisation has available accurate data on which it can make decisions is critical to its success. Unfortunately the use of spreadsheets has been at the centre of some of the most notorious blunders in recent years, the size and reputation of an organisation not serving to reduce the risk, instead it seems merely to amplify its impact.
The British Government has a particularly bad reputation when it comes to spreadsheet mistakes. Its most recent being related to the £22 billion (spent, a further £15 billion budgeted) system for Covid-19 track and trace, according to Boris Johnson another “world beater” from the newly independent UK. It may be surprising for many to find out that Excel was at the heart of this system. However, it gets worse as apparently the Government was relying on such an old version that only 65,536 rows of data were available, instead of the now over a million. Unfortunately, in all too recent darker times, that was approximately the number of daily infections. It appears that lab test results were being entered in Excel templates that were no longer updated once the row limit was hit. Hence significant positive cases of coronavirus were missed in daily reporting and, because it was used for track and trace, an unknown number of people were exposed to the virus unnecessarily.
Back in 2012 the award of the West Coast Rail Franchise turned into a shambles after the UK Department of Transport found flawed assumptions in the spreadsheet-based financial model it was using. The DoT had to retract the £9 billion ten year contract, starting the process afresh, and the whole thing was said to have cost the British taxpayer up to £300 million.
The British public were to suffer even more, this time with a decade of public austerity conceived in 2010. The British Government was this time basing its strategy on the conclusions of a research paper “Growth in Time of Debt”, by Harvard Professors Carmen Reinhardt and Kenneth Rogoff. At the time it was highly regarded and concluded that when a country’s debt hit 90% of its GDP its economy would start to shrink. Unfortunately it turned out some rows of data were omitted from the calculation and as a result the analysis was flawed. Instead of contracting by 0.1% at 90%, it could be expected to grow by 2.2%. The consequences were cuts to welfare and public services, with the British Medical Journal attributing over 120,000 deaths to this policy.
The second decade of the 21st century was not a good one for the UK in many ways. At the 2012 London Olympics 20,000 tickets were sold for synchronised swimming. Unfortunately in a spreadsheet being used a “2” had been entered in place of a “1”. Much to the embarrassment of the organisers, twice the number of available seats were therefore sold, the result of a simple typo.
Of course, it’s not just the British Government that makes these mistakes. In 2003 the US Federal National Mortgage Association (Fannie Mae) had to make a $1.4 billion correction to shareholder equity, the Senior VP describing the error as an “honest” mistake made in a spreadsheet used when implementing a new accounting standard.
Even larger was Fidelity Investment’s 1994 mistake, where an employee forgot to use a minus sign on the fund’s net capital loss. Because it was then seen as a gain, dividend estimates were out by $2.6 billion, having promised a $4.32 dividend this subsequently had to be cancelled.
Enron collapsed in 2001 for a number of reasons, not helped by thousands of inaccurate spreadsheets not detected by its auditors, Arthur Andersen, who shortly after also sank. In the proceedings that followed it was stated that around 24% of the formulae used by Enron in its spreadsheets were incorrect, maybe some were intentional, others not.
Another US example concerned JP Morgan Chase in 2012. An employee copied and pasted data and formulae from one spreadsheet to another without reviewing for errors or testing calculations. The result was a loss of over $6 billion. It concerned the “London Whale”, trader Bruno Iksil, whose estimated trading loss turned out to be three times higher than the expected $2 billion, highlighting the shortcomings in the firm’s risk management systems and internal controls.
Back to the UK, in 2008 Barclays sent a spreadsheet to Cleary Gottleib, its law firm, in relation to purchasing certain assets from the remnants of Lehman Brothers. The spreadsheet included a list of contracts to be included in the purchase agreement. It had around 1,000 rows with more than 24,000 cells that first needed to be reformatted and then converted to a pdf. The associate working on the Excel document failed to notice there were hidden rows, marked to indicate that 179 contracts should not be included in the deal. The notes stating these contracts were not wanted were not transferred to the pdf, whereas the hidden rows were.
With data integrity and the reputation of a business being so closely entwined, clearly the greatest care needs to be taken when choosing the tools used to record, store and work with what is the lifeblood of a organisation.
Bear in mind that, even if it appears to be working just fine for you right now, Excel is not necessarily the most efficient way to manage workflows and when using it, it does have some major shortcomings accompanied with significant risks. It is therefore undoubtedly in the best interests of any organisation to review its use of spreadsheets and take a closer look at the alternatives. Even if the final decision is to stick with what you have, at least everyone will have a better understanding of what is at stake.
As mentioned previously, if the decision is made to reduce one’s reliance on spreadsheets then an organisation would be well advised to look first at sector specific software. CompassAir, for example, has been in use in the maritime sector for a number of years and a program of continuous development is fundamental to its popularity. Not only do we endeavour to incorporate new ways of working and technology, AI being a good example, but also we work closely with our clients to ensure their suggestions for improvements are included in software updates wherever possible.
As such, CompassAir has become the preferred solution for many maritime professionals, with its fully integrated database that stores both messages and shipping information. Being web based and featuring a “best-in-class” mobile app have helped to bring about an unprecedented increase in its use over the last twelve months. Feel free to contact us to find out more about how we’ve helped other maritime businesses and how we can add significant value to your organisation.
A few words about CompassAir
Creating solutions for the global maritime sector, CompassAir develops state of the art messaging and business application software designed to maximise ROI. Our software is used across the sector, including by Sale and Purchase brokers (S&P/SnP), Chartering brokers, Owners, Managers and Operators.
Through its shipping and shipbroking clients, ranging from recognised World leaders through to the smallest, most dynamic independent companies, CompassAir has a significant presence in the major maritime centres throughout Europe, the US and Asia.
Our flagship solution is designed to simplify collaboration for teams within and across continents, allowing access to group mailboxes at astounding speed using tools that remove the stress from handling thousands of emails a day. It can be cloud based or on premise. To find out more contact firstname.lastname@example.org. If you are new to shipping, or just want to find out more about this exciting and challenging sector, the CompassAirShipping Guide might prove to be an interesting read.
Contact us for more information or a short demonstration on how CompassAir can benefit your business, and find out how we can help your teams improve collaboration and increase productivity.