Foundation
We teach a set of guidelines providing standardization and manifold useful attributes to spreadsheet models:
- fluent development
- neatness & auditability
- suppleness & redundancy prevention
- major quality improvements and transparency
- minimization of out of hand error pass
Shape Up
- maximize presetting and automation
- maximize neatness, transparency and simplicity
- employment of the right technique and tool for each job (formula, table or pivot? Xlookup() or Merged Tables? Power Query or Power Pivot?)
Modular Design
One sheet performs one task only:
- “Inputs” sheets – introduce & deposit the raw data
- “Workings” sheets – model the data
- “Outputs” sheets – collect the modelling results in reports
Inputs Sheets
They contain only:
- Data
- Source Tables
- Controls
- Indexes
- Documentation/Instructions
Outputs Sheets (via links to Workings)
They contain only:
- Reports
- Charts
- Dashboards
Workings (calculations)
- use simple formulas (if complex formulae are a must, then, document them)
- no repeated/redundant calculations (a calculation should be performed only once, with dependent calculations referring back to it)
- left to right consistency, same formula all across one row (where applicable)
- up to down consistency, same formula all across one column (where applicable)
(More on spreadsheet modelling standards at: www.ssrb.org)