Modeling standard
A short standard for workbooks that other people must read, check and reuse. Each rule states what to do, why, and how the auditor verifies it.
Sheets
| Sheet | Contains | Never contains |
|---|---|---|
Doc |
Purpose, version, date, structure, units, sign convention, sources, limits | Calculations |
Inputs |
Every assumption: label, value, unit, source | Formulas (except a documented conversion) |
Calc (one or several) |
Formulas only, one pattern per row | Typed numbers |
Outputs |
References to Calc, summaries, the overall check |
New logic |
Controls |
Reconciliations and the "all checks pass" cell | Typed TRUE / 0 |
Data flows one way: Inputs → Calc → Outputs. A formula in Calc may read Inputs and earlier
rows of Calc; Outputs reads Calc; nothing reads Outputs except Controls.
Rules
1. One input, one cell
Each assumption appears once, in Inputs, with a label on its left and a unit on its right.
Formulas refer to it absolutely (Inputs!$B$3) or by name (Growth).
Why: a reader changes one cell and the whole model follows; a reviewer reads one sheet to know
every assumption.
Check: hardcoded lists every number inside a formula. 0 and 1 are ignored by default
(--ignore 0,1,12,100 to widen), and the digits argument of ROUND is never reported.
2. One formula per row
Write the formula in the first period and copy it across. In R1C1 terms every cell of the row is
then the same formula; that is exactly what consistency compares.
Allowed exception: the first cell of a recurrence (=Inputs!$B$4 then =B2*(1+Growth)); the
auditor recognises it. A total at the end of the row is also recognised.
Never: type a number over one cell "just for this year". If an override is needed, add an
override row in Inputs (blank = no override) and write =IF(Inputs!C9="",C5*Growth,Inputs!C9)
in every period.
3. No number without a unit, no sign without a convention
Write the unit beside each input and in each row label where it changes (Revenue (k)), and state
the sign convention once in Doc. The two usual conventions:
- Natural signs: costs are positive numbers, formulas subtract them (
=Revenue-Costs). - Cash-flow signs: outflows are negative numbers, formulas add everything (
=SUM(...)).
Pick one for the whole workbook. Mixed conventions are the commonest source of sign errors.
Rates are stored as decimals (0.05) and formatted as percentages; never store 5 and divide by 100 in formulas.
4. Controls that can fail
A control compares two figures obtained by different routes:
| Control | Left | Right | Difference | OK |
|---|---|---|---|---|
| Margin = revenue − costs | =Calc!G5 |
=Calc!G3-Calc!G4 |
=B2-C2 |
=ABS(D2)<Inputs!$B$7 |
| Balance sheet balances | total assets | liabilities + equity | … | … |
| Detail ties to summary | =Outputs!B1 |
=SUM(Calc!B3:F3) |
… | … |
| Opening + movements = closing | … | … | … | … |
Then one cell =AND(E2:E9) on a row labelled "All checks pass", repeated on Outputs.
- The tolerance is an input, not a number in the formula.
- Every control is a formula.
controlsreports typed ones. - The overall cell must cover every row.
controlsreports an overall cell that says TRUE while a row fails. - A control that compares a figure with itself (same cells on both sides) proves nothing.
The auditor finds controls on a sheet whose name contains control, check, reconciliation
or validation, in defined names starting with chk, check, ctrl, control, test or
ok, or in ranges you pass (--ranges "Summary!H2:H9"). It reads a boolean, an OK / FAIL
text, or a number in a column headed Difference (also diff, delta, variance, écart).
5. Names
Name what a reader will meet often: key inputs, the tolerance. A name refers to one cell or one
range (Inputs!$B$6), never to a formula or a constant: those hide logic where nobody looks.
Workbook scope unless two sheets genuinely need the same name.
6. Things to avoid
- Circular references. If the economics are circular (interest on average debt), break the loop with an explicit iteration row or a documented approximation rather than iterative calculation.
OFFSET,INDIRECT: they hide dependencies from every tracing tool, this one included.IFERRORaround whole formulas: it turns a visible error into a silent wrong number. Test the specific condition instead (=IF(B1=0,0,B2/B1)).- Merged cells inside calculation blocks; hidden rows with live formulas; links to other files.
- Formatting numbers as text; storing a date as text.
7. Version stamp
Doc!B2 holds a version (1.3.0) and Doc!B3 a date. Change them with every delivered
revision and keep a short change list below. A reviewer's report can then name what it reviewed.
Skeleton with openpyxl
openpyxl writes formulas but cannot calculate them: the file has no results until Excel or LibreOffice opens and saves it. Recalculate, then audit the recalculated copy.
from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName
wb = Workbook()
doc = wb.active; doc.title = "Doc"
for row in [("Model", "Five-year margin model"), ("Version", "1.0.0"),
("Units", "Currency units; rates as decimals; costs positive"),
("Structure", "Inputs -> Calc -> Outputs; Controls reconciles")]:
doc.append(row)
inp = wb.create_sheet("Inputs")
inp.append(("Assumption", "Value", "Unit"))
for row in [("Unit price", 100, "currency"), ("Growth", 0.05, "rate per year"),
("Units year 1", 1000, "units"), ("Cost ratio", 0.6, "share of revenue"),
("Tolerance", 0.005, "currency")]:
inp.append(row)
wb.defined_names["Growth"] = DefinedName("Growth", attr_text="Inputs!$B$3")
calc = wb.create_sheet("Calc")
calc.append(["Line"] + [f"Year {i}" for i in range(1, 6)] + ["Total"])
cols = "BCDEF"
calc["A2"], calc["A3"], calc["A4"], calc["A5"] = "Units", "Revenue", "Costs", "Margin"
for i, c in enumerate(cols):
calc[f"{c}2"] = "=Inputs!$B$4" if i == 0 else f"={cols[i-1]}2*(1+Growth)"
calc[f"{c}3"] = f"={c}2*Inputs!$B$2"
calc[f"{c}4"] = f"={c}3*Inputs!$B$5"
calc[f"{c}5"] = f"={c}3-{c}4"
for r in (3, 4, 5):
calc[f"G{r}"] = f"=SUM(B{r}:F{r})"
out = wb.create_sheet("Outputs")
out.append(("Total revenue", "=Calc!G3"))
out.append(("Total margin", "=Calc!G5"))
out.append(("All checks pass", "=Controls!E4"))
ctl = wb.create_sheet("Controls")
ctl.append(("Control", "Left", "Right", "Difference", "OK"))
ctl.append(("Margin = revenue - costs", "=Calc!G5", "=Calc!G3-Calc!G4", "=B2-C2", "=ABS(D2)<Inputs!$B$6"))
ctl.append(("Outputs tie to Calc", "=Outputs!B1", "=SUM(Calc!B3:F3)", "=B3-C3", "=ABS(D3)<Inputs!$B$6"))
ctl.append(("All checks pass", None, None, None, "=AND(E2:E3)"))
wb.save("model.xlsx")soffice --headless --convert-to xlsx --outdir recalculated model.xlsx
node scripts/xlsx.mjs report recalculated/model.xlsxExpected: no error, no circular reference, no constant inside a formula, no broken pattern, two controls passing, the overall cell TRUE and consistent.