Up to 90% of spreadsheets are wrong…
by Iain Webster

…so are you sure that yours are right? Corporate Finance Partner Iain Webster and Corporate Finance Manager James Beswick offer some timely tips on making your spreadsheets as accurate as possible.

In today’s business world enormous numbers of financial models are built using spreadsheet programs. These range from single-page, single-purpose spreadsheets to multi-page, fully interactive models which replicate whole business entities. Some will be used to derive figures for budgets, others for project financing or management buy-outs, and some will be developed for use as databases to give continuous output. Most will be used by senior management to make business decisions, including some critical ones, such as deciding whether to develop new products, capital expenditure decisions and even bidding decisions in the case of tender situations.

Models can therefore range from simple formulae to complex computer programs that may take days to run. But no matter what the level of complexity the fact is that business-critical decisions are often made on the basis of the output.

Recently we built just such a model for the tender bid for the Northern Isles Ferry Service on behalf of our client, Caledonian MacBrayne Limited. This lifeline ferry service runs daily passenger and vehicle vessels between the mainland and the Orkney and Shetland Isles.

The purpose of the model was to determine the level of financial aid required from the Scottish Executive to support the uninterrupted running of the lifeline services, and it projected the entire business over a six-year period and extended to over 60 worksheets and more than 120 pages of output. The 12 megabyte model had tens of thousands of calculations in it, which had to be very carefully checked before the bid was submitted – in the knowledge that the output would be subject to rigorous independent review.

It was a delight to all concerned in the bidding team when the tender was won, and it was a relief for Johnston Carmichael when the model stood up to review with no errors.

Business leaders very rarely question whether the models that they use are ‘error-free’ and, just as important, that they are flexible and robust enough to model changing circumstances relating to their business. In the absence of manifest error they simply assume that the models are correct.

However, independent research shows that around 90% of spreadsheets contain significant errors. This is a staggering percentage, and it means that there is a 90% chance that some of your key decisions are made based on erroneous data. With that incidence of error, your business might actually be safer not using models at all. However, that is an unrealistic approach, and to help you we can offer a few pointers which may reduce the probability that your models are in this worrying 90%.

Keep it simple: make sure the purpose of the model is understood and always kept in mind, and don’t let it expand like ‘Topsy’ beyond what it was intended to achieve.

Always remember that it is very dangerous to take an ‘old’ model and try to update it when the original model may have been designed by someone else, or for another purpose.

Always keep the following precepts in mind:

  1. Use a formal structure, such as keeping your inputs, calculations and outputs on separate worksheets.
  2. Do not mix formulae with hard coded ‘data’ information which might have to be changed. Keep
    data completely separate.
  3. Build in cross-checks and balances and ‘error’ messages for key outputs.

Always get someone independent of the person building the model to ‘sense-check’ the outputs. This can be someone within your organisation or, better still, your financial adviser.

For complex models there simply is no substitute for engaging a specialist financial modeller whose day-today job is model building and review. The approach taken by specialists is very methodical and detailed, with the ultimate aim being to add value and comfort to the client ’s decision-making process.

Typical scenarios where complex financial models are required include:

  • Business planning (start-up’s or expansion)
  • Management Buy Outs (MBOs)
  • Mergers and Acquisitions
  • Project Finance bids
  • Forecasting, budgeting and management reporting

The author : Iain Webster

Close window