Let’s discover more about the 10 Rules of Financial Modelling. This post looks at why having clarity to your design is vital in any good financial model. It’s essential to lay out inputs and calculations separately in addition to laying out the results separately.
Good financial models depend on providing results that the user can feel confident enough to make informed decisions.
The importance of structure
It’s a widely accepted approach to ensure that any financial model should be easy to follow. For example, a clear layout, making it easy to track through the audit trail, is best practice.
Any user should be able to quickly locate information and formulas with the trail between inputs and outputs obvious. The sensitivity analysis should also be easy to identify and follow.
The Operis approach to ‘structure’
Here at Operis, we recognise the importance of structure, and we lay out our financial models with this in mind. Assumption, calculations and reports are all given the same approach and configuration to ensure clarity and comprehension.
Where do we start with the structure of a financial model?
Perhaps surprisingly, we start at the end. We consider what the output – the deliverable – will be. Which reports are required for the user to gain the most benefit and secure the outcome they want? Another critical objective is to ensure that the financial statements are transparent and integrated if possible.
We look at cash flow, profit loss and balance sheet. There may be a ratio sheet, dashboard or an executive summary that puts everything in perspective to go with these. The structure of the entire model is critical to ensuring that the right deliverables are produced and that they’re easy to both digest and apply in decision making.
What’s next?
After considering the reporting element, then it’s time to inspect the formula results. The working sheet is the powerhouse and the engine of the entire model, with the inputs being the fuel. Depending on what you have to achieve, these inputs may be on a single or multiple input sheets.
What does ‘good’ look like?
Operis’s typical approach starts with a single working sheet, which would contain all the calculations.
The argument for this approach is that it’s easier for the developer of the financial model to get familiar with the written calculations as they will need to write the ‘code’. If it’s all on one page, it’s easy to refer to, digest, and genuinely understand.
Financial modelling isn’t just a list of calculations. It’s a set of integrated calculations that feedback and forth into cash flows and earnings and so on. So the developer has a good sense of how everything fits together. It can be tricky conceptually to follow via an audit trail if the precedents are spread across several sheets.
The workings sheet approach means that the inputs and outputs essentially belong to the users, and they should have little reason to do anything with the underlying workings.
For sensitivity analysis, in which the inputs are changed, the user would go to the outputs to observe the effects of those changes. They don’t necessarily need to look at your working sheet at all. Knowing that this is the best approach, you can confidently set it all up in a way that allows them to run the sensitivities. They can then be fully confident that the trail between the inputs and the output is valid and appropriately audited.
So that’s the structure we would recommend. It’s open to interpretation. Organisations that use many data feeds may have commercial data such as the Reuters system or file links from internal databases. This being the case, we recommend to build a separate input sheet as those will be classed as inputs, but to have them then linking to individual sheets.
In effect, each external source has a single sheet to receive them and populate the model as required. It’s difficult if file link type formulas are mixed with data dumps in existing formats. The source information should be managed in a consolidated manner before feeding through to the outputs.
From that, you can see that the output sheets are the deliverable. That’s what you’re being paid to do. The output sheets in themselves are not particularly complex at all. They simply list the results from the workings.
Clear structure = “Good Model”
A “good model” is usually identified very early on by its transparent structure. There are other issues attached to that in terms of consistency and simplicity, and so forth. But the first thing you’d observe when you open a spreadsheet for the first time would be that idea of structures are clearly labelled with tabs that would indicate the sort of things you can expect to see. You’d find that the process you would follow to work with that model is apparent, whether you’re the developer or indeed the user.
One last but important point
It’s essential to remember that you start with the outputs, whether you’re a developer or a user. That’s your common ground. That’s the stuff you’re usually more familiar with, whether they are financial statements or ratio sheets or result sheets or executive summaries. That’s your starting point to then working back into the more complex parts of the model, as you might find on the working sheet.
In summary, a good structure is critical to the success of your financial model. It needs planning that considers the deliverables and what will give you the best results.
