Some Best Practice Principles in Excel Modelling

This blog briefly posts some fairly standard (but not fully accepted, and more often simply not implemented!) “best practice principles” in Excel modelling. A later blog discusses a related topic as to whether risk modelling (when building Monte Carlo simulation models using @RISK in Microsoft Excel) requires the same (or a modified) set of principles.

The following principles are generally to be applied to Excel models (in fact, in practice, many of these may need to be varied or interpreted in a slightly different way than it might seem at first; my book Financial Modelling in Practice discusses some of these issues):

  • The model should be objectives driven, that is, it supports the decision-making situation; its structure and allowed sensitivities should be aligned to the decisions that will be taken with it
  • It should be kept “as simple as possible, but no simpler” (to paraphrase Einstein)
  • Error-checks should be built in. For example, the same quantity could be calculated in two different ways and the difference between the calculations (which should always be zero) could be set as an output. Excel DataTables (or Palisade’s TopRank) could be used to check that the error is always zero under a wide range of input scenarios (when using DataTables, conditional formatting of the cells can be used to highlight any non-zero values)
  • It should have a modular structure with related calculations kept as close together as possible
  • It should be compact, with no linked workbooks, and as few worksheets as possible; the total length of all audit trails in the model should be minimized.
  • There should be a clear logical flow (usually left-to-right, top-to-bottom), with no “mixed” formulae – every numerical quantity is either a number or a calculation
  • There should be short, transparent calculation steps (that can be understood within a short space of time)
  • Formatting should be used to highlight the structure and flow of the model (e.g. borders around the modules, bold text, colour-coding, shading, “significant figure’ rule for the number of decimals etc)
  • There should be no circular references (some very limited exceptions apply)
  • Named ranges should be used highly selectively but not excessively
  • Adequate documentation should be provided: key assumptions, limitations or key restrictions on the logic

Many of these issues apply in risk modelling with @RISK (risk analysis using Monte Carlo simulation software add-in for Microsoft Excel, for decision making under uncertainty), but some additional points may require consideration, as discussed in another posting.

Dr. Michael Rees
Director of Training and Consulting

4 comments

  1. It’s great to see a summary of good design practices for Excel. It’s been four or so years since I’d seen anything similar. I’d come to the conclusion about fewer tabs being better. And you’ve hit on a tip that I use routinely — calculate something twice to double check that everything’s been captured. I also like to differentiate cell colors — green with black text for input and white with blue text for calculation. I also find that data validation keeps people from making unfortunate entries (eg, if values

  2. There is an extensive discussion about named ranges in my book "Financial Modeling in Practice" (the most extensive anywhere in the modelling literature).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s