SPB Git

Repository Public Research preview

modeling-standard.md 7.0 KB

# 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. controls reports typed ones.
  • The overall cell must cover every row. controls reports 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.
  • IFERROR around 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.

python
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")
bash
soffice --headless --convert-to xlsx --outdir recalculated model.xlsx
node scripts/xlsx.mjs report recalculated/model.xlsx

Expected: no error, no circular reference, no constant inside a formula, no broken pattern, two controls passing, the overall cell TRUE and consistent.

Files main
Menu

Theme