Some Best Practice Principles in Excel Modelling

Wednesday, March 18, 2009 by DMUU Training Team
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
Bookmark and Share

Comments for Some Best Practice Principles in Excel Modelling

Wednesday, March 25, 2009 by Robin Foster, Avaya:
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
Thursday, March 26, 2009 by Rajesh Sahasrabuddhe:
I understand most of these - but can you expand on the penultimate point on named ranges
Friday, April 3, 2009 by William J McKibbin PhD LLC:
Thanks for the summary list and introduction to your book on financial modeling.
Friday, April 3, 2009 by DMUU Training Team:
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 comment





Captcha