Tangled logic, unintelligible formulas and stories of growth without capital investment – Rob Bayliss and Mark Robson warn of what to be wary about in financial forecasts
This article was first published in the November 2018 Africa edition of Accounting and Business magazine.
We’ve all been there. It’s the night before signing the big deal. The board is happy, the bank is happy, and even the lawyers are happy. You’re a finance director on top of the world; you and your team have burned the midnight oil and everything seems to be in great shape.
Or is it? You decide to look again at those forecasts. You didn’t put them together and don’t know all the details. You open the model and start to dig around and you realise that everything might not be in such great shape after all. Everything could be fine but when should you start to feel frightened?
Here are some things to watch out for in financial forecasts.
Trends that carry on forever
If you are reviewing someone else’s model, you can assume that the model’s growth rates, profit margins, working capital ratios and asset spending have been played with until the spreadsheet nicely churns out cash and repays debt. But is it realistic to conclude that these trends will continue forever?
No ability to stress-test
The model might ‘work’ – cashflows are fine, debt is paid down, key ratios are within their limits. But a valuable model provides insight into what happens if things shift: how far would the key inputs have to move before the picture changed? If your financial model doesn’t have easy-to-find boxes to flex key assumptions, then you aren’t getting much to show for the blood and sweat that went into putting it together.
The devil is in the detail
Granularity is important: break your forecasts down. For instance, how much of your revenue is contracted in? Unless the reader can see the layers, they are unlikely to believe the total.
Imagine a hardware chain that told you it could double sales without having to spend anything on its stores, or a distribution centre that kept adding volumes without adding physical space: growth costs money. Capital is needed to keep the business growing. Constraints on people, trucks or shops will mean you need more of them and need to spend. Too often, forecasts miss this consistency of story. The model should include a few key ratios about asset intensity or the utilisation of people or machinery.
Forecasts should read from top to bottom and left to right like a book, starting with assumptions clearly labelled and collected up in one place, where they can be easily identified and changed. You and your readers need to be able to follow the logic and if that logic flows in many tangles in many directions, who will have any faith that it works?
Long Excel formulas
We’ve all seen Excel files with formulas that stretch across three lines and are unintelligible to anyone, including the person who proudly created it. Building a forecast isn’t the time to show off how good your Excel skills are. Challenge yourself, or your team, to make your formulas as simple, rather than as complex, as possible. Take one small logic step at a time. Excel lines are cheap and you should usually use more of them.
Signs of an untidy mind
The list of things to be wary of could carry on for some time but here are a few more danger signs:
- Random colours and formatting throughout the spreadsheet. They may be meaningful to you but will anyone else understand the significance? Use too many colours and they lose their power.
- Formulas you can’t fill left to right. The spreadsheet that forecasts five sets of 12 months, with an annual subtotal slotted between, is another warning sign. Each new year leads to a new formula, and each new formula leads to a greater risk of an error somewhere.
- Circular references. Excel can get so hung up on these that it stops calculating any file that you have open. Experienced modellers know how to manage circular logic without building circular references into formulas, so a circular reference rings instant alarm bells.
- Poor model notes or labelling. There is so much that needs to be explained in a model. Key calculation steps floating around with no narrative is a bad sign.
- Macros. Although these can be clever, they can also hide important things in ways impenetrable to the average reader, turning the model into a real ‘black box’. There probably isn’t much that your model needs them for. We’d expect an experienced modeller to think carefully before relying on them.
It’s all eminently fixable
None of the issues outlined above guarantees that there will be a modelling or forecasting error, but experienced modellers have been around the block enough times to know that if we see a model that has more than a few of these, we’re bound to encounter deeper problems, both with the numbers and the processes they should support. The issues above are our modelling equivalent of a canary in a coal mine.
Rob Bayliss and Mark Robson are part of a team at Grant Thornton UK that pulls together beautiful forecasts for clients who have important decisions to make
"If your financial model doesn’t flex key assumptions, you aren’t getting much to show for the blood and sweat that went into putting it together"