Financial model review – try this at home!

If you are not in a position to engage an independent model audit company to perform an independent financial model review then the following check-list may be helpful. I do not attempt to delve into high level analytical checks but rather strictly focus on algebraic and structural errors that can be easily detected and therefore avoided.

Inconsistent formulae across rows

A solid financial model should have the formulae copied across all columns. Not having consistent formulae significantly increases the risk of error in your model. Even though on face value the model may be right, the inconsistent formula makes it very hard for anyone else to update assumptions or amend the model.

The most efficient way of detecting inconsistent formulae across rows is to purchase a financial model review add-in. There are a number of different products on the market and they all have their strengths and weaknesses. I would recommend downloading a free trial before purchase to make sure that that particular add-in works for you.

If you don’t want to purchase a model review add-in then you can explore the powerful, but often overlooked, built-in Excel functions

• ‘Trace all dependents’ (CTRL + SHIFT + ])
• Trace all precedents (CTRL + SHIFT + [)
• Row or Column Differences (CTRL + SHIFT + \) and (CTRL + \)

These functions are also accessible via F5 (Goto) → Special.

Complex formulae that are hard to understand

Overly complex formulae with nested IF, OFFSET, INDEX, MMULTS, ISERROR, etc, and other functions, etc, significantly increases the risk of an error in your model. This can be easily avoided by taking advantage of the many rows available splitting the formula into smaller, manageable parts.

The following is an example of what not to do:

=IF(G$4<=OpStartDate,0,SUMIF(Operations!$D$4:$AO$4,EOMONTH(G$4,IF(MOD(MONTH(G$4),3)=0,0,3-MOD(MONTH(G$4),3))),Operations!$D163:$AO163))/3

Check for unused assumptions

One very simple check to perform is to make sure that all assumptions in your assumption sheet have been used in calculations. If there are unused assumptions then there is an increased probability that you have mistakenly linked in an incorrect assumptions to flow into the model calculations.
To perform this test you could either use a spreadsheet review add-in or Excel’s built-in tracing functionality.
Sums that exclude lines that should have been included

It may sound unlikely, but one of the most common errors is SUM-functions that do not sum the right rows. Simply work through your SUM-functions to make sure that they have been coded correctly.

Review timing errors

A very efficient way of finding errors in your timing section is to simply change the timing assumptions and check it against changes in graphical representations of the model. Any unexpected spikes, troughs or unreasonable movements in outputs should be investigated. Experience has shown that Timing is a common source of errors and it is recommended that close attention to be paid to model Timing.

Mix of assumptions and calculations

A good model should have all assumptions located in a dedicated section of the model separate to the calculations. Financial model review software will have the functionality to distinguish inputs as opposed to calculations, but you can always use F5 → Special → Formulas → Numbers if you do not have one.

COMMENTS