Spreadsheets form a huge part of our work-life, acting as to-do lists, lightweight databases or complex engineering tools. Since the 1960’s, spreadsheets have been a cornerstone of productivity in many industries. Considering current initiatives such as digitalisation within the Oil & Energy industry to deliver added value solutions, spreadsheet tools should be recycled into modern, browser-based web applications.
Why Are Spreadsheets So Popular?
The widespread use of spreadsheets across the Oil & Energy industry is down to the fact that our computers were/are pre-loaded with Excel. The compelling power of spreadsheets is the ability to create calculations based on input cells to get instant results without any high tech skills or training needed.
Furthermore, the time to learn a new programming language to develop a true application is much longer and unattractive compared to starting up a spreadsheet. Creating a program in a spreadsheet form is considered easier and less hassle and so has been the most popular choice amongst teams.
What’s The Problem with Spreadsheets Today?
Extensive research by the European Spreadsheet Risks Interest Group has shown that the main issues associated with spreadsheets are:
a) Human Error — To err is human, hence the majority (>90%) of spreadsheets contain errors. Because spreadsheets are rarely tested [Panko, 2006] [Pryor, 2004] these errors remain. Recent research has shown that about 50% of spreadsheet models used operationally in large businesses have material defects [Powell, Baker, Lawson, 2007] [Croll, 2008]. Approximately 50% of executives recently surveyed had encountered spreadsheet related problems up to and including staff dismissal [Caulkins, Morrison & Weideman, 2007].
b) Fraud — Because of the ease with which program code and data is mixed, spreadsheets are the perfect environment for perpetrating fraud [Mittermeir, Clermont, Hodnigg, 2005]. The $600m fraud perpetrated by John Rusnak at AIB/Allfirst was spreadsheet related [Butler, 2002]. Other spreadsheet related frauds have occurred and have been notified to the regulator, but have not been reported.
c) Overconfidence — Because spreadsheet users do not go looking for errors, they don’t find any or many. Spreadsheet users are therefore overconfident in their use of spreadsheets [Panko, 2003].
d) Interpretation — Translation of a business problem into the spreadsheet domain can “…lead to a position where decision makers may act in the belief that decisions can be made with confidence on the output from the spreadsheet despite evidence to the contrary” [Banks & Monday, 2002].
e) Archiving — “The case of failed Jamaican commercial banks demonstrates how poor archiving can lead to weaknesses in spreadsheet control that contribute to operational risk” [Lemieux, 2005].
Further Challenges with Spreadsheets
Based on my personal experience, technical and non-technical challenges with spreadsheets include:
f) Collaboration — Utilising two or more engineers to work on a spreadsheet tool is quite a non-collaborative, tricky process. Updates and bug fixes post-deployment cannot be “pushed” to spreadsheets within the organisation.
Working from a single spreadsheet is very limiting in terms of collaboration as only one user can work within the tool at a given time. Although, Google Sheets solves that issue in today’s world.
g) Data Accessibility — Across an organisation, valuable data is often hidden within spreadsheets which are poorly named and organised. This makes the process of extracting value from business data extremely challenging, even impossible in many cases. Although recording/importing/exporting data with spreadsheets can be convenient and simple to do, often there is no supporting documentation to explain the “story” behind the data. This leaves other users frustrated and confused.
Unfortunately, today, 80% of engineers’ time is spent on extracting, transforming and loading data. Only the remaining 20% is focused on creating value from that data.
h) User Interface (UI) and User Experience (UX) — Spreadsheets don’t deliver a good UX due to the fact that we don’t consider good UI design when developing spreadsheet tools. Have you ever opened a colleague’s spreadsheet for the first time and understood what its purpose is/was? Have you ever created a spreadsheet tool and then opened it in two months time?
In the first scenario, you might be totally confused and wonder where to start. What if that colleague has left the company before you got a chance to ask some questions about it? In the second scenario, it would probably take an afternoon for you to remember what you did and how you did it. How does this happen? Approximately, we spend:
- 80% of our the time working on the tools core functionality
- 10% of our time fixing bugs and enhancing formulae/ VBA code (macros)
- 10% of our time testing the tool
No time has been allocated to adding instructions, comments, user tips/help, or making the tool look attractive and compelling to use. Compare this with a web-based application you use today. Quite a contrast indeed.
i) Control — When a spreadsheet tool is released into the “wild”, users may stick with the version they receive to use for their particular task. If there is a new update or overhaul of the tool, it can be difficult to “supercede” the obsolete version and enforce the use of the latest and greatest tool. This can mean that errors can still go undetected which will have consequences on the quality of the results/output.
j) Duplication — In large organisations with several regional offices, tools with a specific functionality are most likely duplicated in Excel or Mathcad form. This can be due to the preference of the originator, lack of communication between teams and other interoffice factors. This is not a lean process and leads to unnecessary waste.
To remedy the aforementioned issues, web apps delivered in a browser are the ideal vehicle to deliver software tools. Recycling Excel/Mathcad tools can repurpose business tools that can add value to teams. This is possible by utilising the vast amount of open source libraries that developers can utilise.
One of the compelling reasons to use browser-based applications is the power of their User Interface (UI) and associated User Experience (UX). Advances in web technologies (coding languages, frameworks and developer tools) means new powers are available to the developer to implement into their applications.
Benefits of Browser-based Applications
Browser-based applications are centrally hosted which means that multiple users can access the same application window in real-time. As the application is hosted on a centralised server, several benefits are available to empower the user/users:
- Instantly available — no download or installation times
- Updates are deployed over the air without action from the user
- Secure and controlled access — real-time monitoring and multi-factor authentication (MFA) etc.
- Rich, modern UI’s offer a unique UX that can automatically eliminate user errors, offer modern data visualisation functionalities etc.
- Data from company databases can be “mined”
Web applications coupled with intelligent databases can be a sensible strategy to tipping the scales towards 20–80 in terms of extracting, transforming and loading data.
It should be noted that there are two types of a web application. Above, I’ve discussed browser-based web applications. The second type that exists is a native web application which sits on the home screen of a mobile device. Both application types are similar but:
a) A native app sits locally on the device
b) Browser-based apps reside in a remote server i.e., not on the device.
In a business environment, browser-based apps and native apps work quite well on business approved devices. Mobile Device Management (MDM) software could be installed on the mobile device to remotely access and control the device to manage/delete/monitor company data safely.
Yet, company applications (native type) are not widely used on personal devices due to data and privacy concerns as all data resides on the user’s device.
Spreadsheets are very familiar tools amongst teams with lots of time and efforts dedicated to developing those tools. They offer a great deal of functionality and relatively little programming experience is required to develop a tool.
The objective of this article is not to denounce spreadsheets altogether. In many cases, it makes sense to work with data in a spreadsheet. Yet, we need to harness the benefits of business data, artificial intelligence and real-time collaboration — this makes web applications the ideal tool of choice for tomorrow.
Developing a web application is not a simple task compared to developing the same application in spreadsheet form. Nevertheless, web applications make a compelling business case given the value and benefits that can be obtained.