FP&A Technologies: (ii) The Shortcomings of Spreadsheets

Excel is the application that turned personal computers into valuable business tools. Spreadsheets are an irreplaceable tool in nearly every finance department, but as a means to support corporate planning they soon become an unmanageable nightmare. That failure to be fit for purpose is nearly always due to limitations caused by their fundamental design, which can be summarised as follows:

Single User:

Only one person can update the contents of a spreadsheet file at a time. When used as an enterprise planning application, where actions are to be planned and consolidated from across the organisation, this presents a major problem. To get around this limitation, the different parts of a plan are typically split into multiple files so that users are provided with no more than their portion. Yet even within small organisations the number of spreadsheets can increase to hundreds of files.

This proliferation of files causes its own maintenance and control issues. For example, when someone is given a spreadsheet to complete a budget or forecast, how do you know that the version they send to you is the latest one, and has the same contents as the one they are viewing? You don’t, as it’s not possible to control when they are no longer allowed to change values entered, and what version they send to you.

Similarly, in order to consolidate the answers, it requires a sheet that ‘links’ to all the other sheets to get the ‘latest’ data. However if that data is not actually the latest data, the integrity of the consolidated plan is always in question. And if a new sheet is issued with new rules or accounts, what happens if they don’t use that version? For these reasons version control becomes a nightmare.

Lack of Workflow Capabilities:

Most planning applications require a distinct set of operations to be carried out in a set order. For example, there is no point planning support activities until after the sales forecast has been entered and approved. Similarly, when a forecast has been generated, it shouldn’t be changed until the next round of planning. In the same way, data on current actual spend should be loaded before departments are asked to review and forecast expenditure in future periods.

The order in which things take place needs to be carefully controlled and orchestrated so that everyone knows what they need to do and when. Those overseeing the process need to know what the status is and where there might be ‘bottlenecks’ that are holding up others in the chain. None of these capabilities exist within a spreadsheet-based system.

Cell Meaning:

All data held in a spreadsheet is typically referenced by an intersection of row, column and sheet. A particular cell reference, ‘C23’, has no particular meaning and only by applying rules or macros does the content of any cell take on its meaning. Excel has the capability to define range names, but as this facility involves a high degree of maintenance and can’t be used to track how Excel calculates a particular value, it is rarely used.

Cell references are fine when the system is dealing with a relatively simple analysis, such as displaying the profit and loss (P&L) for a single company for one year. However, when the data has to deal with multiple companies, with multiple versions of the data (actual, budget, forecast) over multiple years, with a mixture of balance sheet, P&L and statistical accounts, then controlling the meaning of a particular cell and the way it should be treated within a calculation becomes increasingly difficult.

For example, creating a variance or adding up accounts over time requires knowledge about the account type in order to create the correct formula. Balance sheet accounts can’t be accumulated over time; creating a budget/actual variance with P&L accounts isn’t a simple subtraction, as the user needs to know whether the account is debit or credit. Copying formula between types will give the wrong answer, so it’s not even safe to ‘drag’ formulae between rows and columns.

In addition inserting a new row/column to cope with a new service/product line creates a real danger that the rule logic will be compromised. With luck you might get a ‘#VALUE’ error message to advise there is an issue. If not, the error will go undetected until a crucial decision is taken and the error becomes apparent.

Limited Business View:

Spreadsheets only hold one view of the data, unless that data is duplicated via cell links. This view is fixed by determining what the rows and columns represent. For example, columns may be set up as time, with accounts displayed as rows, and the different sheets representing departments. Of course, it is possible to mix dimensions such as displaying actual and budget values within a particular time period as columns.

The way the spreadsheet is laid out gives the user one view of the business. However, what if he or she requests a different view from the way the data was collected? The budget/cash forecast will typically be entered with the columns representing each period next year. However, reporting actual results will want to pick up just one of those budget periods (the current period) and then compare it with actual and forecast results. Obviously things are never simple, as the forecast month that is picked up will change each month and so any cell references to the original budget will have to change.

What if the user wished to analyse spend by market sector or by product? To do this requires a different view of the data, where rows and columns represent different items, but involves either duplicating content or creating a large number of error-prone cell links to switch the data around.

Finally, as no organisation is static, this network of interconnected sheets then has to be updated for new departments/products, or changes to the business structure, without impacting integrity.

As mentioned earlier, these limitations are caused by the fundamental architecture of a spreadsheet and are the direct cause of a number of major issues when used for enterprise planning and reporting – issues that will lead to wrong results, many of which will go undetected.

21 views

Related reading