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:
- 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.