Blogging all things data

What are the Dangers of Financial Forecasting in Excel Spreadsheets?

What are the Dangers of Financial Forecasting in Excel Spreadsheets?

If your business relies on Excel spreadsheets for financial planning and forecasting, you're not alone. A comprehensive study has revealed that the humble Excel spreadsheet could be considered nothing less than the very lifeblood of international business itself. According to the research, for 72% of medium-to-large businesses, financial planning using numerous Excel spreadsheets acts as a multi-purpose enterprise finance solution. However, there is a strong case to be made that this solution is very fallible and potentially dangerous.

In many cases, this use of financial forecasting spreadsheets falls under the realm of Shadow IT, which refers to all the improvised and highly fallible solutions that emerge as ad-hoc solutions within a business. There are more reliable and secure solutions available, however, they have to be implemented hand-in-hand with broader cultural and organisational change.


Contents:

  1. The Problem with Financial Forecasting in Excel
  2. Reification of Financial Forecasting Spreadsheets
  3. Shadow IT and its Hazards
  4. The Case of Financial Modelling
  5. What is the Cure for a Sales Forecasting Spreadsheet?

financial forecasting in excel being done on a laptop sitting on a table

The Problem with Financial Forecasting in Excel

There is a sense of 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 is a quantification of an idea that has been circulating for some time, that excessive spreadsheets 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 a number which would be familiar to anyone who has ever done financial forecasting in Excel:

  • lack of naming or storage convention
  • no document retention policy
  • lack of any business context for individual documents

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 Forecasting Spreadsheets 

What this term means is that the spreadsheet containing the deeply flawed financial modelling and forecasting, 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 forecasting in excel 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 modelling 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 financial planning using Excel spreadsheets which contain convoluted projections and are 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 Forecasting 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. Financial planning using Excel 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 Worksheet?

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, watch BizData’s free webinar on Accelerating Analytics for Enterprise Finance Data.