9 reasons why you should migrate from forecasting spreadsheets

By David Parmenter

Forecasting requires a good robust tool not a spreadsheet, built by some innovative accountant, which now no one can understand. Often the main hurdle is the finance team’s reluctance to divorce itself from Excel. It has been a long and comfortable marriage albeit one that has limited the finance team’s performance.

Why Excel is inappropriate

Spreadsheets have no place in forecasting, budgeting and many other core financial routines. Spreadsheets were not designed for many of the tasks they are currently used to accomplish. In fact, I often remark in jest at workshops that many people, if they worked at NASA, would try to use Microsoft Excel for the US space program, and many would believe that it would be appropriate to do so. A spreadsheet is a great tool for creating static graphs for a report or designing and testing a reporting template. It is not and never should have been a building block for your company’s planning systems. The high level of errors in spreadsheets is the main reason why. A major accounting firm pointed out that there is a 90 percent chance of a logic error for every 150 rows in an Excel workbook.[i]

A decade ago, the electronic spreadsheet was still state-of-the-art for the budgeting process and the only practical option for most midsize companies. However, what might have started as a simple budget model often grew into a spreadsheet that soon got out of control. Moreover, considering the time and effort required to turn that mass of spreadsheets into a coherent budget, they should not have been considered “inexpensive.” With the introduction of dedicated planning tool software for all sizes of organizations, spreadsheets are not the optimal approach any longer.

Rule of 100

I believe you can build a forecasting model in a spreadsheet application and can keep it within 100 rows without much risk. Pass this threshold and you expose yourself, your finance team and the organization. Forecasting requires a robust tool, not a spreadsheet that was built by an innovative accountant and that, now, no one can understand. I always ask in workshops, “Who has a massive spreadsheet written by someone else that you have to pray before you use it?” You can see the pain in the instant response. Most people know that the person who built the spreadsheet certainly was not trained in operational systems design. The workbook will be a collage of evolving logic that only the originator has a chance to understand. Often, the main hurdle is the finance team’s reluctance to divorce itself from the spreadsheet program.

Career limiting

Acquiring a planning tool is the major step forward, and one that needs to be pursued, not only for your organization’s future, but also for the future careers of the finance team. Soon, a career prerequisite is likely to be planning tool experience, and, conversely, being a spreadsheet guru is likely to be career limiting. To those readers who believe a spreadsheet is still appropriate, I say to them, why not build your general ledger in a spreadsheet program and while you are at it, all your operations systems? Try explaining to the CEO that only one person knows how these systems work and he or she left four years ago. You might as well clear your desk now.

Common problems with spreadsheets

Senior management is often blissfully unaware of the risks they take every time they rely on information from large spreadsheets.

Some of the common problems with spreadsheets are:

1. Broken links or formulas	An individual might add or eliminate a row or column so that, when a group of spreadsheets is rolled up, the master spreadsheet is taking the wrong number from the one that was modified.
2. Consolidation errors	Often, a spreadsheet will lock up or show a screen full of “REF”, “REF” “REF” errors, because it was not designed to be a tool for handling a rollup of dozens of different worksheets.
3. Input of the wrong numbers	Entering the wrong number can happen in any process, but spreadsheet-based systems often require rekeying of information, which can produce data inconsistencies. A spreadsheet might use a look-up table that is out of date, or an entry might have been inadvertently or mistakenly overwritten.
4. Incorrect formulas	A subtotal might omit one or more rows, columns or both. An individual might overwrite a formula because they believe theirs is more accurate. Or someone might use an outdated spreadsheet. Or allocation models might not allocate 100 percent of the costs. Allocation methods might be inconsistent.
5. No proper version control	Using an outdated version of a spreadsheet is very common.
6. Lack of robustness	Confidence in the number a spreadsheet forecast churns out is not assured. Many times, you cannot check all the formulas because they can be found in any cell of the spreadsheet.
7. Inability to accommodate changes to assumptions quickly	What would you do if your CEO asked, “If we stopped production of computer printers, what would be the financial impact? I need the answer at the close of play today.” Your spreadsheets are not able to provide that quick answer.
8. Design is by accounting staff who are not programmers	Most accounting staff have not been trained in system documentation and quality assurance, which you expect from a designer of a core company system.
9. Multiple versions of the truth	Many people in a business can use spreadsheets to create their own forecasts at a ridiculous level of detail. This can lead, as a friend once said to me, “To the march of a million spreadsheets.”

[i] Rickard Warnelid, “Reducing the Risk in Excel Risk Modelling,” CompAct, January 2011

For more details access my implementation guide that currently is on sale:

These 9 reasons why you should migrate from forecasting spreadsheets are discussed in great length in my implementation guide How to Implement Quarterly Rolling Forecasting and Quarterly Rolling Planning – and get it right first time // implementation guide (Whitepaper + e-templates)

For more details access my implementation guide that currently is on sale:

These 9 reasons why you should migrate from forecasting spreadsheets are discussed in great length in my implementation guide How to Implement Quarterly Rolling Forecasting and Quarterly Rolling Planning – and get it right first time // implementation guide(Whitepaper + e-templates)

You can have a look inside the implementation guide

These 9 reasons why you should migrate from forecasting spreadsheets are discussed in great length in my implementation guide How to Implement Quarterly Rolling Forecasting and Quarterly Rolling Planning – and get it right first time // implementation guide (Whitepaper + e-templates)