Five Financial Models Tips to Make Models Easier to Understand, Use and Audit – The Financial Modelling Podcast (2024)

Financial models can be scary and even daunting – especially when you haven’t built them! Maybe you are a financier or model auditor trying to figure out what a model is trying to tell you. Using the five financial model tips outlined in this article, we can make the financial models we build easier to understand, use and be audited (a crucial step in obtaining funding). Financial models don’t have to be the complex beasts they are sometimes made out to be! Follow these five financial model tips to modelling glory.

Financial Model Tip 1: Flags

A flag can be used to represent anything that changes over time! A simple 1 or 0 (with some conditional formatting) can then be used in other calculations, complex formulas can just multiply by the flag to ensure they are used in the appropriate periods.

This is one financial model tip that makes a big impact!

Financial Model Tip 2: Inputs all on one worksheet

When reviewing a financial model against term sheets, EPC contracts and other long documents, having one worksheet where all the inputs can be found makes life much easier! Even better is when those inputs are segregated according to themes or headings (E.g. Financing, Operations, Construction, Forex, Macro-Economic Inputs etc.). Intelligently structuring the Inputs worksheet is not only a financial modelling tip but a sign of a well-thought-out and strategically built financial model.

This financial model tip ensures no one spends hours looking for key assumptions!

Financial Model Tip 3: Useful graphs to visualise outputs

Humans tend to assess large amounts of data much more quickly if it is presented to us in a pretty picture. Financial models are no different! Graphs illustrating construction sources and uses of funds, ratios vs covenants and cashflows can be very useful for immediately understanding where stresses are in financial models.

This financial model tip means no one needs to look at the numbers to know what the financial model is saying!

Note that the above graph is slightly deceptive as the y-axis does not start at 0! If it did, the gap between the covenanted DSCR and the actual DSCR wouldn’t appear at all:

This financial model tip ensures that the model is accurate.

Financial Model Tip 5: Named Ranges

1. A legend: A legend ties into tips 2 and 3 on this sub-list and shows exactly what all the different cell styles and colour-coded worksheets mean. Having consistent input and output cell styles for a start makes checking a model much easier – a hardcoded value in an output cell is already a good indication that something is wrong!
2. Cell Styles: Excel has its built-in styles (found by typing Alt -> h -> j). However, you can also create your cell styles if you don’t like these. As long as you are consistent about implementing cell styles, your model will look and feel great (yes, financial models have feelings!).
3. Colour-coded worksheets: Taking cell styles to the next level is to colour code worksheets to show which worksheets contain inputs, which worksheets contain calculations, which contain summaries and which contain outputs. This may be a bit OCD – but OCD is the trait of a good financial modeller!

So, there you have some very easy-to-implement financial model tips on making your financial models easier to build, use and check.

Now readwhy financial models matter!

Good luck and happy financial modelling!

Matthew