Wrapping up the mechanical Review

In the previous episodes of Financial Model Detective, we’ve seen some techniques that would enable you to check if a financial model is structured, flexible and transparent.

You can use the same techniques to check for most of the issues included in the financial model review checklist. I think most of these issues are self-explanatory, but if you want me to expand on any of the below issues let me know and I will make a new episode based on your requested issues.

The verdict

After you have performed the preliminary review, you should decide as early as possible whether you want to work with the inherited model or to build your own model instead.

Here are some of the typical conclusions:

1. Model is not structured

Evidence:

  • Inputs are not separated from calculations and output
  • Hard-coded parameters that are subject to change
  • Long formulas
  • Non-conformity in column structure between worksheets
  • Links to external files
  • Inconsistent formulas across rows
  • Model contains circular reference(s)
  • Missing design and color-code specification.

The verdict: For the crime of building a badly-structured model, the financial model is sentenced to restructuring.

2. Simple tool for a complex transaction

Evidence:

  • Does not meet the objective of the model
  • Does not reflect the realities of the project
  • Model can not accommodate expected future changes
  • No programming of timelines
  • Limited number of years projected.

The verdict: The financial model needs to be restructured to meet its objective and to reflect the realities on the ground.

Amendments:

Keep in mind that simplicity is a relative term and must be considered in connection with the level of knowledge of the recipient. For example, a standard financial model might be labeled as complex by someone who has little knowledge of Excel and financial modelling. On the other hand, the same model reviewed by a spreadsheet engineer might be considered too simplistic.

The level of details and complexity also depend on the objective of the financial model. For example, a single pager and simple but structured spreadsheet can be sufficient for the purpose of preliminary project screening.

3. Black box

Evidence:

  • The size of the file is tool large
  • Long waiting time to run the model
  • Excessive use of IF statements (long formulae)
  • Excessive use of copy-and-paste macros
  • Model contains password-protected sheets or codes.

Verdict: The financial model is found guilty of unnecessary complexity. In the case of password-protected spreadsheets, if the password can not be recovered then the financial model needs to be rebuilt from scratch.

4. Model is standard and need minor adjustments

Evidence:

  • Inputs are clearly defined and stated in the financial model in a structured manner
  • Model is easy to audit and understand
  • Key outputs are presented effectively
  • Model allows sensitivity and risk analysis.

The verdict: The financial model is in line with best practice financial modelling standards and can be used during the appraisal of the project.