One fact staring us in the face is that AI will automate many tasks currently performed by the finance team, and at the same time, render many aspects of their business redundant or impact them by new players.

Finance teams and their organisation need to adapt, NOW. How do you do this? By replacing all spreadsheets over 100 rows with more robust solutions.

I wrote about this in the article ‘Beware of the spreadsheet’ published in CPA’s ACCOUNTING AND BUSINESS.

In the article I wrote:

The use of large spreadsheets for reporting, forecasting and planning should be abandoned because of their susceptibility to error.

Spreadsheets have no place in forecasting, budgeting and reporting routines. A spreadsheet is a great tool for creating static graphs for a report or designing and testing a reporting template; what it is not, and should never have been, is a building block for your organisation’s reporting, forecasting or planning systems.

Rule of 100

If you can build a forecasting, reporting or planning model in a spreadsheet and keep it under 100 rows, you can do so without much risk. But pass this threshold and you expose yourself, your finance team and the organisation.

Forecasting requires a robust tool, not a spreadsheet built by an innovative accountant that no one can now understand. I always ask in my workshops: ‘Who has a massive spreadsheet written by someone else and prays before using it?’ You can see the pain in the instant response.

Most people know that whoever built the spreadsheet was not trained in operational systems design. The workbook will be a collage of evolving logic that only the originator has a chance of understanding.

Often, the main hurdle is the finance team’s reluctance to divorce themselves from spreadsheets. It has been a long and comfortable marriage, albeit one that has limited the finance team’s performance.

Error-prone

Senior managers are often blissfully unaware of the risks they take every time they rely on information from large spreadsheets. They generate a high level of errors, the most common of which are:

• Broken links or formulae. An individual might add or eliminate a row or column so that, when a group of spreadsheets is rolled up, the master spreadsheet takes the wrong number from the one modified incorrectly.
• Consolidation errors. Often, a spreadsheet will lock up or show a screen full of ‘REF’ errors because it was not designed to handle a roll-up of dozens of worksheets.
• Data entry errors. Entering the wrong number can happen in any process, but spreadsheet-based systems often require information to be rekeyed, which can produce data inconsistencies. A spreadsheet might use an out-of-date look-up table, or an entry might have been inadvertently or mistakenly overwritten.
• Incorrect formulae. A subtotal might omit one or more rows or columns. An individual might overwrite a formula, believing their own to be more accurate, or use an outdated spreadsheet. Allocation models might not allocate 100% of the costs, or use inconsistent allocation methods.
• Poor version control. The use of outdated spreadsheets is common.
• Lack of corporate office control. 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’.

In the article I proposed the next steps.

• Log all spreadsheets used by the finance team that have more than 100 rows.
• Identify tools that work well with your general ledger.
• Make sure all members of finance understand the weaknesses of spreadsheets – read this article for a full list.

My Lean 21st Century Finance Team Implementation Guides will prepare you and your team for the future.