Our team are often asked what ‘good’ looks like when it comes to financial modelling, particularly when the model’s purpose is to support in completing a M&A or funding transaction. While the answer to that is highly subjective and dependent on the scenario, the business, the intended purpose and the intended audience, there are a number of things that can improve the usability, credibility and investability of your financial model.
In this three part series of articles, we provide our thoughts and examples of how to build a ‘good’ financial model. The first looks at the planning, structuring and presentation of the model. The second focusses on the financial model dashboard – where the users of the model will seek the answers to their questions. Finally, the last article looks at some scenario specific examples of things to include in a good financial model.
Please note: In these articles, we are talking specifically about integrated models of all three financial statements (i.e., profit and loss, balance sheet, and cash flow) built in Microsoft Excel, rather than software financial modelling.
First things first
If we first talk about the financial model in its entirety then ‘good’ is, unfortunately, highly subjective. Regarding functionality, however, good can be achieved fairly easily – the modeller simply needs to be able to use excel in its core ways and clearly distinguish between 1) what’s coming into the model, 2) what the model is doing, and 3) what the outputs are.
By separating out each of the elements, each can be looked at and analysed in turn. At a very foundational level, there should be three key tabs (or worksheets) in the model:
- Inputs – where every performance assumption and input measure going into the model is recorded. A few examples include details of employees both current and future, their start date and salaries, or product pricings, input unit costs and the expected growth rates over time. In essence, an input is anything that, if changed, has an impact on the model’s calculations and outputs.
- Calculations – this is the ‘black box’ of the model and where the financial modeller really earns their fee! The calculation tab interprets the inputs and, using appropriate maths and calculations into financial statements, produces the outputs.
- Outputs – this tab is crucial as it communicates what the model is trying to achieve: the story of how the business will grow and how the proposed transaction (if it is an event driven model) fits into it.
The rule of thumb – no formula in the model should be longer than a thumb. Ultimately, it must be readable, easy to understand and clear what it is trying to achieve. If you need a longer formula, then simply break it down into steps in separate rows and then report the final number in the outputs tab.
It is important to note that ‘good’ is not just in what a model looks like – it is equally important that the thinking behind what has gone into the model is done, and then ensuring that what is presented reflects this thinking. Ultimately a model is the financial articulation of your strategy so if you can relate each key element of your model all the way back to a strategic decision, that then helps stakeholders, a user and management to understand why the output at the end of the model is as it is. For each financial or strategic decision in the business’ growth, the impact of this decision across all three statements needs to be considered.
The importance of working capital
A good example of this is working capital. Every business must be able to deliver the financial performance of the business, meaning that there needs to be a baseline level of working capital built in.
A core functionality of models both now and going forward must have working capital sorted, as, if you understand that then you know what’s going on around it and you may be able to leverage working capital to fit you. This could result in not needing to raise as much debt or equity funding for growth. It also makes the business easier to fund and easier to manage as all parties know when the peaks and troughs occur. Alternatively, it may identify whether a particular transaction is even feasible, as the business may simply not have the cash. By understanding the business’ exposure to things going on externally and how it needs to respond and then modelling it correctly, the model can both function as a reliable bedrock and deal with multiple scenarios and outcomes for a proposed transaction.
All of that said, users of financial models do need to understand how the numbers and the overall picture were reached, and if what they receive is unstructured or overcomplicated, then they are not going to have much confidence in the relevancy of the outputs. Simplicity in presentation and a logical flow are key – reviewers build their confidence when they can peel back each of the layers of the model, going from the dashboard back to the outputs, back to the calculations, back to the inputs logically without jumping all over the place.
This article was written by Nathan Young, Strategic Corporate Finance Manager at Price Bailey. If you are looking for support in building a financial model for your business or transaction, then please get in touch with one of the team using the form below.
We always recommend that you seek advice from a suitably qualified adviser before taking any action. The information in this article only serves as a guide and no responsibility for loss occasioned by any person acting or refraining from action as a result of this material can be accepted by the authors or the firm.
For more insight, events and webinars, sign up to the Price Bailey mailing list…
We can help
Contact us today to find out more about how we can help you