By Levi Bailey, 1 January 2016.

Financial Modelling Excellence

This page describes qualities of financial modelling excellence that I believe are widely agreed upon within the profession. Comment on LinkedIn (you must be a member of the "Financial Modelling in Excel" LinkedIn group) or Reddit.

Financial modelling excellence means reliably producing models that:

  1. Enable useful analysis;
  2. Carry a low risk of error;
  3. Are quick and easy to understand;
  4. Are quick, easy and nice to use;
  5. Are quick and easy to change;
  6. Are quick and easy to create; and
  7. Are quick and easy to review.

The list above is broadly ordered with more important qualities closer to the top. The relative importance of each quality is fairly stable even when considering models that are intended for different purposes because requirements frequently change. It is subject to personal preference.

These qualities represent competing priorities in many cases. Understanding the priorities allows us to balance them efficiently and to evaluate alternative modelling approaches.

The list above will now be described in more detail.

1. Enable useful analysis

Models are generally produced with particular goals in mind and should be capable of meeting those goals. It is also desirable for models to consider alternative sensitivities and scenarios, and enable users to understand what is causing results to arise.

2. Carry a low risk of error

The risk of error should be minimised. If an error does occur, the user should be immediately aware of it.

3. Quick and easy to understand

Users should be able to find and fully understand model inputs and outputs that they are interested in. This includes finding and interpreting items, and is relevant at the level of detail of interest (perhaps an individual element or in aggregate). Users should also be able to trace the relationships between inputs and outputs.

4. Quick, easy and nice to use

It should be simple for users to do the things that they want and they should be confident doing so. Models should respond quickly. User experience is important. Aesthetics matter.

5. Quick and easy to change

It should be quick and easy to adjust a model when requirements change, and to make improvements. Updates should not make models worse in any way.

6. Quick and easy to create

This is self-explanatory.

7. Quick and easy to review

Models should facilitate review. This includes sanity checks from a people who are familiar with the project, narrow reviews by specialists such as tax advisers, and rigorous forms of review including third-party audits.

A final consideration: Quick and easy for who?

Is it important for a model to be quick and easy for all users to work with, or are we really only interested in an analyst who has advanced Excel skills and thoroughly understands the subject matter? Or something in between?

The preceding discussion is silent on this point because practitioners have a wide variety of reasonable opinions. Some modellers make extensive use of Power BI features and expect future users to be comfortable doing the same, while some auditors do not even understand PivotTables (although it is questionable whether this lack of understanding is reasonable for auditors).

My opinion: Within reason, I like models that are easy for all users to understand, use and change - even those who are unfamiliar with the subject matter and have limited Excel skills. It is usually possible to achieve this without any significant drawbacks, and as such it is usually worthwhile doing so. It is reasonable to expect any given analyst to think in a fairly logical way, to be familiar with basic Excel features and have some ability to learn new things on their own. There are times when it makes sense to use more sophisticated features because they bring benefits that outweigh the reduction in accessibility, and in these cases we should take particular care to ensure that the workings are clear.