Top tips for financial modelling
Financial models are useful business tools. In our view, they are the only reliable way to quantify a business plan. The model may be of a specific business case, perhaps to evaluate a proposed investment, or of the whole business. Financial models come in all shapes and sizes, including short-term cash forecasts, annual budgets and 5-year business plans.
However, financial models have come in for some bad press, particularly as the essential underpinning of complex financial products. And some financial models quickly fall out of use through being unrealistic, overly complex or too cumbersome to use.
But these negatives shouldn’t put you off financial models – they are very effective in helping you understand your business better, and allowing you to make more informed decisions about strategy, plans and investments.
Here are our ten top tips for business owners in how to build and implement a financial model:
1. Don’t be afraid to take an unconventional view
A financial model doesn’t need to follow your current chart of accounts, although it is advisable to reconcile the model back to your accounts. Businesses often find themselves constrained by current accounting practices, and a financial model is a low-risk way to start realigning the accounts – if you are considering doing something different, your financial model should reflect this.
2. Keep it simple
Models are simplifications of reality. To work efficiently and well, they need to encompass all the relevant factors as straightforwardly as possible. Over-elaboration leads to complexity, opacity and mistakes. Unless you specifically need to evaluate a longer period, limit your model to a maximum of five years.
3. Integrated and commercially-based
Build the model up from commercial activities, so unit sales are based on resources, turnover is based on sales units x price, cost of sales is based on sales units x costs, gross profit is turnover less cost of sales, and so on. Commercial performance should be translated in the profit and loss statement, which is reflected in balance sheet movements which in turn give you cash flows. The four elements: commercials; P&L; balance sheet; and cash flow, should be fully integrated.
4. Model each category separately
Keeping each product category, line of business or territory separate allows you to evaluate differing performance (and in the case of overseas territories, allows you to model exchange rates effectively). Your model should have a single layout that applies to each category, each having its own commercials, P&L, balance sheet and cash flow. These can then be consolidated into a set of whole company views. If you have a number of (relatively) immaterial products or activities, it’s probably best to lump these into ‘Other’ – but be prepared to break out any that are found to be material.
5. Separate logic from numerical assumptions
Hold your numerical assumptions together on one worksheet and label them clearly: these can be single point assumptions (e.g. a discount rate) or variable assumptions, by time (e.g. annual pay increases), product category (e.g. cost inflation) or territory (e.g. distribution costs). Separating the logic from (numerical) assumptions means that assumptions can be changed easily if required – this also makes sensitivity analysis a lot easier.
6. Provide summary output and use charts
The summary output should be boiled down to a few top-level lines shown on an annual basis. It’s easy to drill down into the detail if you need it, but it can be difficult to spot the key messages if too much output is presented. Use charts to illustrate and complement numerical analysis – a dashboard-style usually works well.
7. Ensure easy updating
You should update your model regularly with actual performance, so consider ease of updating at the design stage (even in a one-off business case, as you’ll want to review what the actual results were). If the financial model is to have any longevity, you should also permit new products (etc.) to be added easily.
8. Reconcile the starting position
It is essential that you can reconcile the model’s starting position to your accounts, personnel records and any other key inputs, and that the initial balance sheet balances. Whenever you update the model with actual data, you will need to check it still reconciles.
9. Include sensitivity analysis
Every financial model should include some form of sensitivity analysis: what happens if sales are X% down on forecast or payroll costs up by Y%? You may need to provide for scenario analysis, looking at a number of scenarios, each of which has an internally consistent set of assumptions. You may even want to use the Monte Carlo method to generate a large number of outputs with key variables randomised, and then review the range of outputs to get a sense of the most likely outcome.
10. Testing, more testing, documentation and sign-off
Test the model thoroughly before use, and have someone other than the modeller carry out the tests. Initial testing should include calibration of the model to ensure it reflects what is currently happening in the business. Stress testing should be employed to check whether the model breaks within its normal operating range and what happens when you push assumptions outside a ‘reasonable’ range. Sensitivity analysis tests allow you to check input-output relationships and be confident that key assumptions and variables behave as expected. Once you are happy the model performs well in testing, it is useful to allocate a business owner for each of the main assumptions, and reflect to them in the model’s documentation. Finally it is essential to gain business sign-off for the model ideally from the board or, in an owner-managed business, from the business owner.
Financial modelling is an essential tool for all but the smallest of business and it is vital that business owners and managers understand how to deploy a financial model and how to get the best from it. We hope these tips are a useful start.