Blogging all things data

The Achilles Heel of Finance: The Hazards of Using Financial Planning Spreadsheets

The Achilles Heel of Finance: The Hazards of Using Financial Planning Spreadsheets

A comprehensive study commissioned by F1F9, revealed that the humble Excel spreadsheet could be considered nothing less than the very lifeblood of international business itself. According to the research, 72% of medium-to-large businesses use basic financial planning spreadsheets as a multi-purpose enterprise finance solution. They are employed for of budgeting and/or forecasting, underpinning the decision making which drives global business. 

At the same time, there is a sense of this spreadsheet dependency being unacknowledged or taken for granted, leading to losses in time and money when this infrastructure fails. The larger and more complex the business, the greater chance there is for spreadsheet breakdown to cause problems at the strategic level. Around 33% of large businesses reported that spreadsheet issues are directly responsible for poor decision making. This post will cover the damage that the reliance on spreadsheets is causing and the reasons why they fall under the category of shadow IT.

financial planning spreadsheet in excel open on a laptop

The Problem with Spreadsheets

These findings simply quantify an idea that has been circulating for some time, that excessive spreadshees may be detrimental to an organisation’s health. A Forbes articles even makes the claim that “Excel might be the most dangerous software on the planet” based on the vital role it plays in fundamental economic projections. The most spectacular example given is the case of JP Morgan, which suffered a $2 billion dollar trading loss. The decisions made which led to the loss were based on financial modelling made entirely on crude, manual, copy and pasted spreadsheets which contained formula errors.

Grenville Croll, a spreadsheet researcher, claims that spreadsheets are “integral to the function and operation of the global financial system” while simultaneously “posing significant risks to organisations”. In fact, he  directly traces the complete collapse of the Jamaican banking system in the 1990’s to systematic spreadsheet failure. Some of the contributing issues that he highlights include: lack of naming or storage convention, no document retention policy and lack of any business context for individual documents. All of these issues would appear eerily familiar to anyone who has worked in a spreadsheet heavy environment.

Croll’s research doesn’t simply look at the big picture effects of spreadsheets. In a different study, he focused on the creation of a financial projection within a small Leisure Services Organisation. This projection was created in a single spreadsheet which was presented by the finance team to management. Even though the spreadsheet contained serious errors which mistakenly predicted a period of financial difficulty for the company, Croll highlights something that happened among the leadership team which he calls “reification”.

financial modelling graph trending upwards

Reification of Financial Spreadsheets 

What this term means is that the spreadsheet containing the deeply flawed financial modelling, in the minds of those who were using it, was imbued with high levels of “believability, correctness, appropriateness, concreteness, integrity, tangibility, objectivity and authority.” According to Croll, none of those were justified in any way and would have led the company into a period of turmoil had a secondary review not been performed.

The study report concludes with the amazing claim that a spreadsheet-based model that has been “reified” in this way and has been accepted by an organisation, will be up to six times more time consuming to replace than it took to implement it in the first place. The implications are clear, financial forecasts deal with the very real material future of a company and should be taken very seriously. Part of this outlook should involve a healthy level of scepticism towards basic spreadsheets containing convoluted projections being used as a complete enterprise finance solution.

Shadow IT and its Hazards

These cases cover “official” financial projection spreadsheets, ones that are part of the sanctioned company infrastructure. On top of this, compounding the problem, is the realm of “Shadow IT”, which is a term covering all the ad-hoc, improvised, stopgap solutions that exists below the radar of IT departments. Some estimates place Shadow IT proliferation in large companies at up to 50% of all IT resources

Because of this shadow status, these systems frequently fail to meet existing company standards, specifications and security requirements, also falling outside conventional review processes. These unofficial tools are the ones which are most likely to fail, most likely to be compromised and least likely to have these failures reported and acted upon.

The Rise of the Shadow Spreadsheet

Because of how easy they are to pick up and use as well as their overall flexibility, financial planning spreadsheets are a prime candidate for Shadow IT status. Shadow IT emerges when existing tools aren’t meeting the requirements of the workers. When this occurs, the temptation is high to quickly throw together a quick spreadsheet in order to solve an immediate issue rather than going through the organisation’s proper IT processes. One description of the problem describes Shadow spreadsheets as a “hidden productivity killer” and also a phenomenon which distorts the facts about real allocation of IT resources. IT expenses appear to be lower, when in reality the costs to the organisation are higher because processes are being performed poorly with spreadsheets.

As already mentioned, spreadsheets seem to generate an aura of overconfidence. The trust placed in improvised solutions which have not undergone a proper review is high, given that error rates for spreadsheets, per document, have been reported to be as high as 88%

man upset over bad shadow it financial modelling spreadsheet

The Case of Financial Modelling

Financial modelling is susceptible to being performed within the Shadow IT sphere and, in particular, within spreadsheets. The reason for this is that budgeting, forecasting and modelling, by their nature require flexibility in being able to extract, manipulate and visualise assorted data in a variety of ways. This is often at odds with existing enterprise finance solutions and rigidly established IT data infrastructure, which may constrain data with access controls, strict schemas, calculation and read/write bottlenecks. There may also be a restricted ability to utilise real-time financial reporting, which is increasingly becoming important for budget forecasters.

An IBM Financial Performance Management white paper states in no uncertain terms that “spreadsheets have no place in forecasting, budgeting and many other core financial routines”. The paper emphasises that robustness is the most important element of a forecasting and modelling tool. Spreadsheets can only achieve a bare mimicry of robustness, at the expense of being opaque, overcomplicated and functioning through chains of dependency. The paper also emphasises the need for system documentation, quality assurance and version control in order to have a dependable financial modelling tool.

What is the Cure for a Sales Forecast Spreadsheet?

Finance chiefs from numerous large companies like Adobe are making a determined effort to move their teams and processes away from spreadsheets and towards more robust and dependable systems. This is likely to become more pronounced across more industries over time. The question is, what exactly are these systems and what is the best way of transferring complicated financial processes to them?

Kepion is one of the more popular solutions in this space. It is a cloud-based modelling platform which offers a unified, scalable and secure financial planning and reporting tool. Directly integrating with a general ledger and delivering real-time data, it provides customisable visualisation options powered by high-performance cloud analytic and processing services.

However, migrating from your existing collection of systems and tools to Kepion may not always be a straightforward path. Depending on specific organisational needs, stakeholders and requirements, the journey out of shadow may take a number of different forms. Organisations are increasingly turning to the services of third party data analytics agencies in order to guide them through the challenging process of consolidating all of their financial data and moving it to next-generation enterprise financial solutions.

To get some perspective on how this might look for your finance department, tune in to BizData’s free webinar on Accelerating Analytics for Enterprise Finance Data.