Five Financial Models Tips to Make Models Easier to Understand, Use and Audit

Financial models can be scary and even daunting – especially when they haven’t been built by you!  Maybe you are a financier or model auditor trying to figure out what a model is trying to tell you.  Using the 5 financial model tips outlined in this article, we can all make 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

Financial Modelling Flags in Excel
Flags are a single row that represent when an asset is in construction or operations, when a dividend or debt repayment is due to be made, when interest is being capitalised or paid etc. This is especially useful for project finance models!

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 being 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

Assumptions Excel Financial Modelling

When reviewing a financial model against term sheets, EPC contracts and other long documents, having one worksheet where all of 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 that no-one spends hours looking for key assumptions!

Financial Model Tip 3: Useful graphs to visualise outputs

Graphs to visualise outputs

Being humans, we tend to assess large amounts of data much more easily 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 even needs to look at the numbers to know that the financial model is saying!

DSCR vs min DSCR financial model

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:

DSCR vs min DSCR financial model2

Financial Model Tip 4: Checks

error checks financial model
The most commonly used error checks are the ones checking whether the balance sheet is balancing.  That said, I regularly use checks for everything – if debt and the DSRA is sized appropriately if ratios aren’t being breached, if the DSRA is not being used, if interest is being capitalised where it shouldn’t be, if debt amortises correctly.  Checks can be used for anything in the model that may cause a potential error – use them wisely!

This financial model tip ensures that the model is accurate.

Financial Model Tip 5: Named Ranges

A controversial one for use – it does make using models easier, provided the modeller manages named ranges (and deletes obsolete ones!)) appropriately.  The biggest problem here is that when rows or columns are deleted (or even cell values), the names remain.  So a good financial modeller continually checks for obsolete named cells and ranges.  That said, when managed well, named ranges and cells enable the quick analysis of a model with multiple worksheets and inputs.

And 3 additional financial model tips

financial model cell styles excel
  1. A legend:  A legend ties into tips 2 and 3 on this sub-list, and show 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 own built-in styles (found by typing Alt -> h -> j), however you can also create your own cell styles if you don’t like these.  As long as you are consistent about the implementation of 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 read why financial models matter!

Good luck and happy financial modelling!


Matthew Bernath Financial Modeller
Leave a Reply

Your email address will not be published. Required fields are marked *