Improving the quality of financial forecasting

The deal is set up and you’re in the middle of it all. You have the board’s approval. The bank documentation is ready to be signed.  Everyone has been burning the midnight oil to make this project happen and it’s ready to go.

But you're a finance director with a few tiny doubts. It’s not you who pulled the forecasts together. On impulse you crack the numbers open. You see the figures and, after probing, you realise you’re not completely sure of the detail of how all the numbers moving in front of you have been arrived at.

Here’s a list of seven things to look out for when opening up a set of financial forecasts.

1 Unpacking the trends that are applied

It’s relatively easy and common to extrapolate a set of forecasts from prior recent trends like revenue growth or margin improvement. An alternative approach would see the forecast broken into more layers that link directly to concrete drivers such as volumes due in from specific contracts, or sales from customers (who past experience tells us regularly re-purchase). That way the forecasts become much more credible than the alternative of “last year plus a bit”. 

If you can see forecasts that are ‘broad brush’, you might want to ask for them to be unpacked in more detail, and linked to their drivers, in the interests of making sure that the new forecast is more likely to be deliverable.

2 The ability to conduct stress testing

One of the purposes of forecasts is exploring the ‘what if’ possibilities. The forecast should have some obvious input areas where you can change key assumptions and look at the impact.

If you find yourself in the situation where you can’t yet, for example, use the forecast to gain assurance that a small slip in revenues won’t result in cashflow pressure, then that’s one obvious area for further improvement.

You should be able to change the things that matter, confident that knock-on impacts will be felt in the balance sheet (working capital, tax liabilities, cash and debt) and cashflow.

3 Exploring the relationship between growth, and the investment required to support it

Purchasing an asset that’s going to support business growth is going to cost cash. Imagine a business telling us it can increase sales without anticipating spending on new assets that might be required to support that growth. Most of us would be curious as to how it could be supported.

There’s the risk of a possible disconnect with strategy when asset spending has been trimmed down in the forecasts. The forecast should allow you to, for example, change sales and asset spending (at the same time), and it should give someone reviewing the forecasts confidence around the balance between those two things.

4 Incorporating working capital movements

A common forecasting methodology could see someone looking at the historic average for something like debtor days (our clients pay in 45 days) and applying that forward. The problem is real life doesn’t always play out like the forecast says it could. Perhaps one of your largest customers withdraws. Alternatively, maybe an attractive large new customer comes on board, but is a slower payer than average.

As well as taking the trouble to include modelling around working capital, you want to be able to use it to explore issues such as seasonality, past working capital patterns, and what happens if your working capital is ‘shocked’.

5 Short Excel formulas that enhance ‘checkability’

Long complex formulas that are hard to unpack will always be a risk area in forecast models. But extra lines cost us very little in Excel.  

If you see long formulas we’d recommend you challenge the model creator to use short ones, taking one small logical step at a time, breaking it into more lines. This way formulas become as simple, rather than as complex, as possible – making the model easier to trace and check.

6 Direct Excel ‘flow’

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're easily identified and changed.

As soon as you start detecting poor ‘flow’ (this links to that, which doubles back on itself, and becomes an output at the top of the model, which becomes the input for another calculation in the model) there’s a risk that the model becomes harder for someone else to understand.

You want to see a forecast model that travels from assumptions to calculations to outputs as directly as possible.

7 Evidence of collaboration

People with deep experience of wiring up Excel cells know that, while working quickly, they can inadvertently manufacture a spreadsheet artefact or error. That’s why you want to make sure that the work you see in front of you has been the output of some reasonably-intense collaboration, starting with agreeing the design, progressing through putting main model components in place; “is this usual? Is it novel? How much thought and review does the approach to these particular calculation blocks need?”, and on to detailed inspection of the wiring. The last thing you want is your critical forecast to be the output of one solo modeller repeatedly burning that midnight oil.

To help gain assurance about the integrity of your forecasts, you want to make sure the modelling equivalent of all the roles on a regular ‘real life’ build are played: the building standards inspector, architect, project manager, foreman, or master builder - when it’s bricklayers who are spending the most time on site.

Building confidence in your forecasts

Yes gaining assurance around the production process for your forecasts can feel like a good-sized challenge. The great news is that the checklist above represents common forecasting sense, and the individual steps aren't particularly difficult to implement, as long as you apply enough thought throughout the process. 

For more insight and guidance, contact our team.