Spreadsheets are without doubt the ‘killer’ application that turned the PC into an indispensable business tool. Before then, computing was the preserve of geeks and specialists who spoke in a language few accountants could understand as they served expensive, inaccessible machines locked away in their own air-conditioned environment.

Spreadsheets, for the first time, gave the business professional a way of performing complex calculations that reflected the ledger paper and columnar forms they had used to report and plan.  Without the need to ‘program’, accountants were in charge of their own analytic destiny – and they soon took advantage of the new powerful tool that spoke ‘their language’.

In a bid to gain market share, rival spreadsheet vendors dazzled users with an array of functions to model the organisation and perform feats that soon outreached the capabilities of their mainframe counterparts.

Today, the spreadsheet is the most widely used business tool.  With little training, FP&A staff can create sophisticated financial models, create complex databases and perform a range of analyses from advanced graphics to ‘slice & dice’ abilities found in most specialised analytic applications.

So what’s wrong with that?

The main issue is that while spreadsheets provide individual end-users with fantastic ‘easy to use’ capabilities, as the basis of a corporate wide analytic solution they are an absolute nightmare.  That’s because most of the capabilities required in an analytic solution are missing.  For example:

Multi-user database / Security:  Spreadsheets don’t have the security or multi-user capabilities of a mainstream database.  To restrict what users can see and what they can do at any point in time, data needs to be duplicated, often ending in hundreds of separate files.  Managing this array of individual data sets is extremely time consuming and no one can be sure that each person is using the right version - an essential feature for a corporate-wide system.

Model builder: When building a financial model, the creator decides what the rows/columns represent.  As spreadsheets only have 3 ‘dimensions’ (row, column, sheet), each has to represent multiple business dimensions e.g. columns may hold months and versions, rows hold accounts and products, with sheets holding departments.  This makes the design more complex and not easy to change.

Business rules:  Because data is stored according to its cell/row/sheet location, business rules need to use this notation.  This means there is no in-built integrity check and that every rule needs to be decoded as to what kind of data is held by a particular cell.  Similarly, if data is to be totalled or aggregated across multiple business dimensions (e.g. summing months to years and departments into the total company), then more files will be needed – and you can’t just ‘copy’ rules or they will give the wrong result.

For this reason, it is estimated that most spreadsheets contain multiple undetected errors, all of which lead to integrity issues with any results produced.

Data / User control:  When using spreadsheets to collect budgets and forecasts, there is a need for workflow capabilities that control when people can enter data, and at what time this access is to be locked.  For example, users can enter data up until the end of the month after which, submissions are no longer allowed unless approved by a senior manager.  These types of capability do not exist in a spreadsheet. Even if some attempt is made to do this via different versions of the spreadsheet, you still can’t be sure that the right version will be used.

There are many other limitations, but the above are enough to show that spreadsheets are not suitable as the basis of an analytic application.  Although it is worth pointing out that many specialised analytic applications do provide direct spreadsheet access to the data in a secure way.  i.e. users can access their portion of data as determined by an administrator, so they can use the spreadsheets graphic and formatting capabilities to produce their own reports. However, the version of data and its security are firmly controlled centrally. 

Now that is a good use of a spreadsheet which takes advantage of their strengths but without the limitations.

The article was originally published in Unit 4 prevero Blog