Building Financial Models That Really Work
Financial models have continuously evolved over the last three decades to become one of the most important financial tools in the modern business world. Virtually, all business decisions have some linkage to a financial model. The models can range from a simple spreadsheet to a complex financial model with multiple inputs and outputs.
An inaccurate model can result in poor business decisions. Despite their growing influence in decision making, it’s surprising to see that a large number of organisations still treat financial models in a relaxed manner.
For example, recent examples of errors we have seen include:
- An incorrect model linkage reduced the projected Earnings Before Interest, Tax, Depreciation and Amortisation (EBITDA) by 15%;
- VLOOKUP function in a feasibility model was incorrectly used resulting in the business making an investment decision they would have rejected otherwise;
- An assumption that was supposed to be a variable was hardcoded in the model resulting in incorrect comparisons across various options that were being considered; and
- An inconsistent formula was applied within an impairment testing model that resulted in an impairment decision when there should have been none.
In our experience, a simple three-step process can result in efficient financial models that serve the purpose and de-risk decision making.
- Build models that are fit for purpose and easy to use;
- Always get a second set of eyes to review; and
- Document changes and review any updates thoroughly.
Build Models That Are Fit for Purpose and Easy to Use
While there is plenty of good literature around building great models, I’ve realised that every good modeller that I worked with, in the last 15 years had four things in common:
- Starts the modelling process by understanding the stakeholders, their objectives, and their ability to work with financial models of varying degree of complexity;
- Creates models that are simple to follow with clear segregation of inputs, calculations and outputs;
- Uses functions that are less prone to errors. For example, using Index and Match over VLOOKUP; and
- Builds models that are easy to review by using multiple tabs, short formulas and error checks whenever possible.
Always Get A Second Set of Eyes to Review
Always get a second set of eyes (possibly a third set on complex models) to review your model. Also, consider using professional review services for models that underpin critical decisions.
Document Changes and Review Updates Thoroughly
A change log tab in the model goes a long way in making sure it stays relevant over time. Also, consider resetting to a fresh model or getting a thorough review once the change log becomes really large.
Our financial modelling team can assist you with the full range of services from preparing the whole model and ensuring that the functionality is suitable (including sensitivities, covenants, headroom calculations etc. and graphical representations), through to sample model checks or just a simple reasonableness review.
During due diligence on projections, we normally spot material errors in a morning, and often within an hour. We can make sure that your model does what it needs to do and that you present the right information for the board, investors and regulators.