Okay, so you've just got your shiny new Excel financial modelling spreadsheet. It looks amazing and you can't wait to get started using it. Then you go to the input sheets and your heart sinks a little. Look at all those input cells. Where do you even begin finding all these forecast assumptions?
From our perspective, the answer to 'Where do the assumptions come from?' is simple – they come from the client. But, as someone who has actually been that client in a former role, I know first-hand that a ready-made set of assumptions is not always just sitting there, waiting to be united with the perfect model.
Here are my top five sources of forecast assumptions to give you some ideas about where to start looking for inputs.
This is usually one of the first places people go. It's easy to find since your business already has it (in theory). It's also irrefutable (in theory). These qualities make it a good starting point.
As a minimum you're probably going to need starting cash balances. You can also use historic trends to work out growth rates, working capital cycles, or costs in relation to revenue, and then use these to drive your forecasts.
But this option has its limitations. Your business may be going through changes. Indeed that's probably why you wanted a model in the first place. As the small print so often reminds us, past performance is no guarantee of future outcomes.
If your business is the kind to have long-term contracts in place then you'll probably want them to feature in your forecast.
It's important to distinguish between what's 'locked in' and what's subject to a renewal or extension agreement. It may be the case that customers normally do agree extensions, but it's probably less certain, and your model should reflect that. In the same way, if you track a pipeline of possible contracts, at various stages of negotiation, this is an obvious source of evidence, but again it's probably less certain, and that should come through in your sensitivity analysis.
Of course, if you only include current contracts and identified pipeline, your forecast will eventually taper off. Assumptions for new customer introductions and 'blue sky' business are still needed to complete the picture.
Furthermore some businesses don’t have nice predictable long-term contracts. If that’s you, don’t worry, there are still other sources you can use.
Naturally, economic and industry reports are a good place to get your macro-economic assumptions from. Using these allows you to benefit from a much wider pool of data and gives your projections credibility. However, it is usually not free and you have to take it on trust. You don't always get to see the workings, which means you can't drill into it when answering questions. If the external data is different next month, you could be left guessing as to why.
Also, what are similar businesses achieving? Perusing competitors' published accounts can be a useful way to validate the numbers.
What if your business is just starting up in a new sector? There's no historic data, no contracts and no established industry reports. When there's no alternative, it’s time to do some maths.
This kind of model is likely to be built around theory and be very specific to the situation. It could mean working through production stages and materials costs, or timetables and productivity. Expect flow charts and expansive gestures. It's also a useful approach when considering constraints, such as capacity, floor space and market size that are important cross-checks in many forecasts.
This sort of approach is often a stimulating intellectual challenge but beware of getting carried away in the detail. Yes, in theory, every phone call you make has an electricity cost but is that really going to affect your strategy? Also remember, theory and reality eventually have to meet and these abstract assumptions are quite likely to need refining at a later stage.
In all seriousness, despite the flippant heading, the person who knows your business best is very likely to be you and your team, not an external data source or a spreadsheet. It's reasonable to use that knowledge and judgment. After all, you'll be using that same judgment to make sure you're comfortable with the resulting forecast.
Where possible, of course, it's a good idea to base your inputs on hard data, rather than subjective guesswork. However, there is almost always something for which the data is not available, non-existent or irrelevant. And even if there is data, sometimes there just isn't time to obtain and properly analyse it. Blindly cramming in data without looking is best avoided.
Understandably, it can feel uncomfortable applying judgment. There's no database or source that you can point to. In short, there's no one else you can blame. But a fear of making an estimate and trusting your instinct could lead to you rely on a data source that you really shouldn't.
Making human-based forecast assumptions doesn't have to rest entirely on one person's shoulders. For example, there might be a commercial director with a view on long-term demand or an HR team best placed to estimate which way market salaries are heading. Getting them 'bought in' to the forecast can really add value, provided time is allowed for that.
It's a rapidly changing world but I believe human brains still have some uses.
There is one additional option that I just want to mention because I see it relatively often, and have mixed feelings about it.
This is where you already have a pretty clear idea what outputs you are looking for and so you find yourself trying out different inputs in an effort to get the right answer – or shortcutting that whole process using Excel’s goal seek functionality. In my view, this is a strong sign that your model has an output that should really be an input. If it's something you already know with confidence, wouldn't it be easier just to type it in and let the model do the work?
In general, goal seeking the inputs just to get an answer that will please everyone, risks undermining the whole point of having a model. And when you are inevitably asked where the forecast assumptions come from, it just doesn't sound good to say, "Well I back-solved it to get the answer I wanted."
There are occasional situations where goal seeking across a big model and a big set of inputs does make sense, however, and in those cases we would look to employing VBA code and macros to do the legwork.
In the end, you have to base your assumptions on sources that you are comfortable with. Your business and financial forecasting will depend on both the quality of the model and the quality of the inputs. All the ingredients have to be there or the recipe won't work.
For more information, contact Phil Gunter-Rees.
