Modeling Best Practices

Post date: Oct 7, 2016 1:50:18 PM

Here is a BXL modelling best practices overview.

NOTE! These methods add value even when modeler and user are the same person.

• 1. Use a simplified SDLC for spreadsheets: Development > Testing > Production.

     • Development is where ALL changes happen and is restricted to the developer/modeler

     • Testing is performed by a skilled auditor who is NOT the developer

     • Production is where end users work with the workbook. Changes to calculations are not allowed. If changes are needed, they must go back to Development

  2. Use a simplified Version Control process.

     • New models are initially saved to the Development directory

     • The modeler may copy versions to the Development directory’s Previous Versions subdirectory for reference

     • After passing testing, copy tested models to:

       • The Development directory’s Production subdirectory for backup.

       • A read only Production directory users can access.

     • If the model is replacing a model in a Production directory, move the old model from Production directory to the Production directory’s Previous Versions subdirectory.

     • If the model exists in a Previous Versions subdirectory replace it. Use a network backup solution that preserves directory snapshots to facilitate retrieving versions from specific dates/times

  3. To reduce risk of errors focus on reducing points of failure.

     • Every changed cell is a point of failure opportunity. Reducing the number of changed cells is the first step towards reducing risk (See 10 Rational Risk Measures)

       • Use PivotTables instead of formula blocks

       • Use Power Query to link to external data or join data instead of formulas

       • Use helper columns ONLY when a non-standard formula complexity exceeds our complexity threshold (any combination of 4 functions, operators, or nesting) and can be logically decomposed.

     • Every distinct formula must be tested. Every test is a point of failure opportunity. Minimize testing requirements by reducing distinct formula count. One method for reducing formula count is to enforce formula consistency in columns by using first period tests instead of different formulas for first periods.

• • 1. Example: =IF([@Period]=1, Financed_Amount, _Prior [Balance])

• 4. NOTE! Adding cross checking formulas adds points of failure. They may be helpful during development, but once formulas are verified correct, cross check formulas become redundant and unnecessary.

  5. To reduce errors reuse pretested standard formulas and templates.

     • Store pretested standard formulas in a catalog modelers can copy from eliminating errors associated with crafting formulas from scratch (See Formula Catalog)

     • Create templates from previous projects for various types of models/apps. Templates contain formulas, values, and structure which speed development and eliminate many errors of omission.

  6. To reduce formula errors as well as testing/auditing/review errors use self-documenting formulas.

     • Improving transparency reduces misunderstanding. I measure transparency by counting the number steps required to find a formulas parameter values and labels.

     • Avoid cell references where possible because:

       • Names and Structured References ARE labels and thus, are more transparent than cell references because they eliminate all steps to find labels (See Names are Good).

       • Cell references can be mistyped without generating visible errors. Names and Structured References generate impossible to ignore errors when mistyped.

       • Excel automatically changes dependent formulas when Names or Structured References are changed.

     • Avoid constants in formulas. Instead use Names to add a label to that formula’s parameter.

     • Add Units of Measure (UOM) label as well as a basic label to all numeric values.

     • Create names using XL’s Create from Selection feature to ensure all values are labelled and that their label matches their name (See Modeling Helper Add-in).

  7. To reduce testing/auditing/review errors use tool-tip documentation to further document formulas by adding “Allow: Any Value” data validation with Input Messages (See Modeling Helper Add-in).

  8. To reduce data entry errors:

     • Use Data Validation

     • Use INPUT Style to unlock entry cells and visually designate where entries can be made

     • Use Worksheet Protection to prevent changes to all other cells

     • Segregate entry cells to their own worksheet AWAY from calculations

     • Instead of entering data, download data whenever possible

     • Hide ‘technical’ worksheets from users in production to keep them focused in inputs and outputs.