Some Best Practice Principles in Excel Modeling

This blog briefly posts some fairly standard “best practice principles” in Excel modeling. 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 Lumivero'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” formulas – 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, color-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

This blog briefly posts some fairly standard “best practice principles” in Excel modeling. 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 Lumivero'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” formulas – 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, color-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