01
Aug
Spreadsheets are great. Aren’t they?
in General
Comments
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
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.
Complex Workflows
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.
Collaboration
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.
