Here are the top ten checks I would recommend if you receive a financial model and need to get an initial feel for it, without doing a complete review:
1. Check the calculation method
If iteration is switched on, the model will probably have circular references and possibly material errors.
If the calculation method is on manual or automatic except for data tables, make sure the model is regularly calculated (F9).
For further details on circular references, please see my earlier blog.
2. Check to see if there are any macros and why they are there
A model containing macros should explain their purpose. Some macros may be perfectly valid, but others may be there because the modeller failed to solve a circular reference (copy paste iterative macros).
For further details on macros, please see my earlier blog.
3. Check for sensitivities
Check to see if sensitivities have been set up.
If not, then set up (as a minimum) a Stress Case in addition to the Base Case.
4. Look at the Cashflow
Run the Base Case and Stress Case and check to see if the cashflows make sense. Perhaps print it out as this can make it easier to absorb information.
5. Check the checks
Sense checks should have been included, for example:
- Does the Balance Sheet balance?
- Is cash always positive?
- Do funding sources and uses balance?
- Is debt fully repaid?
- Are assets depreciated to zero?
Check that these checks make sense for both the Base Case and Stress Case.
6. Check that formulae on time-based sheets are copied across
This can sometimes help to identify rogue formulae.
To do this use F5 special, row differences.
7. Check for inputs
This can sometimes help to identify unintentional hard-wired numbers (I once heard of a model that accidentally had an input as an IRR result).
To do this use F5 special, constants then take out text, logicals and errors. Put a background colour on to help identify the input cells.
8. Check for formulae referring to empty cells
To do this go to File -> Options -> Formulas and include this little-used check in the error-checking rules.
9. Check for hidden areas
To do this go through Home -> Format -> Visibility.
10. Run basic ratio tests
Test the ratios to see what happens when Available Cashflow = Debt Service. Of course, in this case, the ratios should equal 1.00.
For further details of ratio tests, please see my earlier blog.