Financial modelling means creating a smaller, simpler version of your balance sheet that can be manipulated to forecast the outcome of various scenarios. Rob Bayliss explains what level of detail you need to include in your financial model to keep it accurate, but useful.
Let’s stop and think for a second about what that word ‘model’ means and where it comes from, aside from how it gets used in the world of Excel financial modelling. I have in mind an Airfix plastic airplane (that never quite got finished) or the ‘space marines’ my children build and play with.
There are many dictionary definitions of ‘model’. I found these ones most relevant:
- "A copy of something, usually smaller than the original object"
- "A simple description of a system, used for explaining how something works or calculating what might happen".
Models are not supposed to be exact, life-sized copies of the real thing. They’re smaller versions that you can hover over, peer at from the top or the side or hold up above your head and spin around. They’re there to help you see the whole picture and, hopefully, you’ll be able to use financial modelling to imagine what an improved real-life version could look like. Where we see a model as a calculation tool, it can focus on the main features of the business.
The wrong way to do financial modelling
Pausing for a moment to try and answer the “what is a model?” question has some value. It tells us that the wrong way to progress with financial modelling might be to start with a print-out that has all the line items in our chart of accounts down the side and weeks across the top, and then to try to replicate that. Each of those line items needs an input.
What you end up creating is probably going to be unusable because it becomes hard to see what’s affecting what and it’s hard to get yourself in the position where you feel like you’re sitting on top of the whole beast. If you don’t start thinking really hard early on, you're not doing financial modelling, you’re just going to end up with a vast grid of data.
Grouping and granules – down and across the page
Imagine you have 25 administration overhead cost line categories you show in your management accounts. Only some of those items would normally move significantly from where they’re trending now. Only some of those line items could be big.
If you were pushed, for your model, you could probably force yourself to group the 25 into between five and ten, with the biggest ones and the ones you’re most concerned about right at the top of the list. At the bottom, you could add a ‘catch-all’ category for the small items that are unlikely to move much. Every good model has a cost line called ‘other’.
You could do the same kind of thing with your customers or product lines; grouping your list of hundreds of customers (or many thousands of products) into ten categories focusing, again, on the ones that are largest and/ or most likely to change. Perhaps a proverbial 80:20 rule could be applied.
By grouping together large populations of business detail, the model is going to become more useful and help you answer the questions that are most important to you, letting you see the wood for the trees. The model that has been clever enough to use some grouping is going to become a better tool for coming up with an answer to the “what would happen if costs increased in this area?” question or the “what would happen if we were able to implement a price rise with this group of customers?” question.
One of the knacks in financial modelling sees you make some early and good judgements about the best level of detail to model in – what level of granularity do you include and what are your ‘granules’? Every granule is a row of inputs with a matching repetition of each row of calculations, over and over, down the page. Too few and your model is over-simple; too many and we 're back to that vast grid of data.
This also applies across the page. What granularity of timeline do you need? Years or quarters or months or weeks? It’s probably not worth doing days, unless you run a bank or a power station, or you want a full-time job maintaining your vast spreadsheet.
Accounting helps financial modelling, but it’s not everything
Basic accounting knowledge seems pretty essential for financial modelling. For example, if we are modelling fixed assets, it helps to know that they go up by capital expenditure and down by depreciation and disposals. It also helps to remember we want to model key features, not super-accurate details.
The model needs to be sensible and realistic enough to be recognisable. If forecast Balance Sheets ignore key lines that appear in your audited Balance Sheet for last year, then they’re probably misleading, and the model may not be so much use. But the model doesn’t have to show every line if they are immaterial or uninteresting.
Accounting has a long list of rules, often very precise, they have to be applied correctly for accounting that records the past performance of a business. But financial modelling means looking forward, not backward, and isn’t actually subject to formal rules or any guaranteed scrutiny, so we have more leeway in the approaches we apply.
It helps to know some accounting to help inform financial modelling, but it’s not the aim of the model to replicate every accounting rule. For example, deferred tax is often a big number in your accounts, but modelling it into the future could be super-complex, and for most businesses won’t affect any decision-making. That particular complexity will just get in the way of having a focused, useful model.
Accounting rules and recognition typically focus on profit and asset valuation. Excel models typically focus more on cash flows. Early judgement is required as to the level of accounting detail you use. Your model needs to be a simplified picture, after all.
Planning with the goal in mind
When we build a model with a business, much of our time goes into discussing the above and agreeing the right levels of detail and granularity. Data often abounds. We bring focus and a charming ruthlessness in keeping the model focused on the task in hand and we can’t understate the importance of planning before launching into the hands-on wiring.
For help with financial modelling, contact Rob Bayliss.