Spreadsheets — Demons to Some, Angels to Others

Shôn Ellerton, November 1, 2016

Let’s bring up the subject of the most abused and addictive type of software known to the business world. I think you may probably have guessed what it is and you would be correct if you were thinking on the lines of electronic spreadsheets. For most of us, this would most certainly be Microsoft Excel; however, I have an aversion to using vendor-specific names (this also applying to Hoovers, Xeroxes and Googling!) so, in the interest of being fair and unbiased, I will refer to electronic spreadsheets as simply, spreadsheets.

During my entire working lifetime, I have been confused, dazzled, mesmerised, dumbfounded, appalled, even assaulted by spreadsheets in all shapes and forms. I have been witness to many computers frying their innards out trying to calculate Excel files with dozens of tabs each containing a veritable concoction of VBA macros, charts, linked tables to databases, and formulae which only make sense to its proud creator. I am sure that there must be someone out there with a geekish mind who has already worked out the excess energy used in kWh by our hundreds of millions of computers opening up and calculating spreadsheets on a daily basis. So why has this come to my attention? Perhaps, partly because I am guilty of the same crime to some extent. I have, at some stage or another in my working life, created some incredibly complex spreadsheets based on underlying VBA code which, if I were to look at it now, would leave me feeling dazed and confused. This would leave only the most experienced and expert of spreadsheet programmers to be able to dissect and reverse-engineer its function and that does not include the underlying business processes underneath it all.

However, there is one practice that I have never used on a spreadsheet (apart from purely personal uses) and that is as a repository to store company data shared by more than, say 5–10 work members. Now, if you, like myself, have worked for a number of fairly large respectable businesses, I have a very high-level of confidence that you will have seen, firsthand, the incredible practice of shunting around high volumes of commercially-sensitive data sourced in spreadsheets. Now when I mean sourced, that is exactly what I am implying meaning that the data comes from nowhere else but from that spreadsheet. Ah! That’s not so simple either! Which copy of the spreadsheet are we talking about? Perhaps it’s Dave’s spreadsheet in Queensland, after all, the file is prefixed with v3 so it’s obviously more recent than mine which says version 2.5. But what about Andrea’s copy which has the today’s date and no version number? She couldn’t spell either as it says 5G Trail Scenarios instead of 5G Trial Scenarios. Ok, enough said.

Let me give you a story of one of my favourite episodes whilst working for one of UKs big four mobile phone operators during the 2000s. Now, for those of you that have not worked in the mobile phone and data provider industries, you would be grossly incorrect in assuming such an industry would lead the way to shy away from the practice of storing business-critical and financial data in spreadsheets. In fact, I was asked by the Financial team to look at a little problem with maintaining and reconciling rates accounts for the thousands of mobile phone towers littered across the UK. A seemingly simple task it would appear; perhaps, an error in the business-logic of the underlying GUI (graphical user interface) or a database stored procedure, maybe an orphaned record in a table in the underlying database created by a relationship that was never implemented, maybe just processes and procedures within the finance team that have not kept up with a recent change? …none of the above! It would appear that maintaining, paying and reconciling payments for council rates for every single tower was administered by one very kindly, grandmotherly figure (who has now happily retired) who resided on the lowest floor of the building amidst a stack of council bills, final notices and bailiff letters which reached such a height that it obscured her from her nearest neighbour sitting opposite. For those of us that have watched Monty Python’s The Meaning of Life, there was a little mini-film at the beginning about the fictitious Crimson Permanent Assurance Company sailing the high seas of chartered accountancy. In the film, an old Victorian (not Australian Victorian!) building complete with aged accountants slaving over reams and reams of paperwork floats insignificantly through avenues of shiny glassy skyscrapers of which, inside, there are beady-eyed executives poised to raid and take over the building. Satire of a hostile acquisition at its best! Well, this was it!

Before our grandmotherly figure even opened her mouth to explain the problem, I knew what the problem was, but didn’t quite know the extent of it. Our grandmotherly figure was armed with no less than 300 separate spreadsheets (many of vastly different formats), each one covering a UK principal authority. I do tell a fib inasmuch she had some respite from time-to-time from a part-time junior assistant. The system which she was using was very simple: the first bills and notices at the bottom of the pile pretty much, well, remained at the bottom of the pile resulting in the arrival of more ‘interesting’ notices and letters of which one, in particular, caught my eye. I picked it up and on the note was handwritten a brief inventory of items including: ‘a 50in plasma TV’, ’10 Compaq workstations with Aeroflex executive chairs’,’4 magenta easy chairs and 1 magenta soft couch’. I’m sure, by now, you would know which mobile operator I have been referring to if you followed the clues but it only registered in my head that this note was taken down by a bailiff astutely examining his surroundings in the main foyer whilst waiting to be seen! You can well imagine that it was not an insignificant amount of money that was spent on summons, payment defaults and late payment penalties. I addressed the problem, formulated and implemented a solution in a matter of a few weeks. This was a relatively simple exercise (for me at least) of migrating the data to one location in the form of a daily backed-up database (in this case, Microsoft’s SQL Server) and using Microsoft Access as a lightweight desktop application (containing no user or company data). For those of you who are converse with business intelligence systems, the solution was extremely simple but highly effective. The key was using a true database (rather than a file-based database or file which could be copied) which was secure and acting as a single source of truth. Properly formatted Excel spreadsheets could be generated from the Access client but it no way were they ever again used to store source data. Amazingly, this system (which was part of a suite of systems I dubiously named Icarus as I suspected it would eventually fall at some point), survived for several years after I departed.

The above story is just one of many that I have encountered and one of many that a simple cost-effective method was used to stymie the progress of what I call, spreadsheet entropy, in which, in loose jargon, the more crap you put into the spreadsheet, the more chaotic and overgrown it becomes (like my neighbour’s garden). Ultimately, it could cost your business a very tidy sum of money.

So to close off this topic for the time being, I will say that the electronic spreadsheet is an invaluable tool for doing what it is originally supposed to be doing. Being a spreadsheet. To quote from Clive Barker’s Hellraiser, ‘Demons to some, Angels to others’.