Repository Public Research preview
1# Modeling standard23A short standard for workbooks that other people must read, check and reuse. Each rule states4what to do, why, and how the auditor verifies it.56## Sheets78| Sheet | Contains | Never contains |9|---|---|---|10| `Doc` | Purpose, version, date, structure, units, sign convention, sources, limits | Calculations |11| `Inputs` | Every assumption: label, value, unit, source | Formulas (except a documented conversion) |12| `Calc` (one or several) | Formulas only, one pattern per row | Typed numbers |13| `Outputs` | References to `Calc`, summaries, the overall check | New logic |14| `Controls` | Reconciliations and the "all checks pass" cell | Typed TRUE / 0 |1516Data flows one way: Inputs → Calc → Outputs. A formula in `Calc` may read `Inputs` and earlier17rows of `Calc`; `Outputs` reads `Calc`; nothing reads `Outputs` except `Controls`.1819## Rules2021### 1. One input, one cell2223Each assumption appears once, in `Inputs`, with a label on its left and a unit on its right.24Formulas refer to it absolutely (`Inputs!$B$3`) or by name (`Growth`).2526Why: a reader changes one cell and the whole model follows; a reviewer reads one sheet to know27every assumption.28Check: `hardcoded` lists every number inside a formula. `0` and `1` are ignored by default29(`--ignore 0,1,12,100` to widen), and the digits argument of `ROUND` is never reported.3031### 2. One formula per row3233Write the formula in the first period and copy it across. In R1C1 terms every cell of the row is34then the same formula; that is exactly what `consistency` compares.3536Allowed exception: the first cell of a recurrence (`=Inputs!$B$4` then `=B2*(1+Growth)`); the37auditor recognises it. A total at the end of the row is also recognised.3839Never: type a number over one cell "just for this year". If an override is needed, add an40override row in `Inputs` (blank = no override) and write `=IF(Inputs!C9="",C5*Growth,Inputs!C9)`41in every period.4243### 3. No number without a unit, no sign without a convention4445Write the unit beside each input and in each row label where it changes (`Revenue (k)`), and state46the sign convention once in `Doc`. The two usual conventions:4748- *Natural signs*: costs are positive numbers, formulas subtract them (`=Revenue-Costs`).49- *Cash-flow signs*: outflows are negative numbers, formulas add everything (`=SUM(...)`).5051Pick one for the whole workbook. Mixed conventions are the commonest source of sign errors.5253Rates are stored as decimals (0.05) and formatted as percentages; never store 5 and divide by54100 in formulas.5556### 4. Controls that can fail5758A control compares two figures obtained by different routes:5960| Control | Left | Right | Difference | OK |61|---|---|---|---|---|62| Margin = revenue − costs | `=Calc!G5` | `=Calc!G3-Calc!G4` | `=B2-C2` | `=ABS(D2)<Inputs!$B$7` |63| Balance sheet balances | total assets | liabilities + equity | … | … |64| Detail ties to summary | `=Outputs!B1` | `=SUM(Calc!B3:F3)` | … | … |65| Opening + movements = closing | … | … | … | … |6667Then one cell `=AND(E2:E9)` on a row labelled "All checks pass", repeated on `Outputs`.6869- The tolerance is an input, not a number in the formula.70- Every control is a formula. `controls` reports typed ones.71- The overall cell must cover every row. `controls` reports an overall cell that says TRUE while72 a row fails.73- A control that compares a figure with itself (same cells on both sides) proves nothing.7475The auditor finds controls on a sheet whose name contains *control*, *check*, *reconciliation*76or *validation*, in defined names starting with `chk`, `check`, `ctrl`, `control`, `test` or77`ok`, or in ranges you pass (`--ranges "Summary!H2:H9"`). It reads a boolean, an `OK` / `FAIL`78text, or a number in a column headed *Difference* (also *diff*, *delta*, *variance*, *écart*).7980### 5. Names8182Name what a reader will meet often: key inputs, the tolerance. A name refers to one cell or one83range (`Inputs!$B$6`), never to a formula or a constant: those hide logic where nobody looks.84Workbook scope unless two sheets genuinely need the same name.8586### 6. Things to avoid8788- Circular references. If the economics are circular (interest on average debt), break the loop89 with an explicit iteration row or a documented approximation rather than iterative calculation.90- `OFFSET`, `INDIRECT`: they hide dependencies from every tracing tool, this one included.91- `IFERROR` around whole formulas: it turns a visible error into a silent wrong number. Test the92 specific condition instead (`=IF(B1=0,0,B2/B1)`).93- Merged cells inside calculation blocks; hidden rows with live formulas; links to other files.94- Formatting numbers as text; storing a date as text.9596### 7. Version stamp9798`Doc!B2` holds a version (`1.3.0`) and `Doc!B3` a date. Change them with every delivered99revision and keep a short change list below. A reviewer's report can then name what it reviewed.100101## Skeleton with openpyxl102103openpyxl writes formulas but cannot calculate them: the file has no results until Excel or104LibreOffice opens and saves it. Recalculate, then audit the recalculated copy.105106```python107from openpyxl import Workbook108from openpyxl.workbook.defined_name import DefinedName109110wb = Workbook()111doc = wb.active; doc.title = "Doc"112for row in [("Model", "Five-year margin model"), ("Version", "1.0.0"),113 ("Units", "Currency units; rates as decimals; costs positive"),114 ("Structure", "Inputs -> Calc -> Outputs; Controls reconciles")]:115 doc.append(row)116117inp = wb.create_sheet("Inputs")118inp.append(("Assumption", "Value", "Unit"))119for row in [("Unit price", 100, "currency"), ("Growth", 0.05, "rate per year"),120 ("Units year 1", 1000, "units"), ("Cost ratio", 0.6, "share of revenue"),121 ("Tolerance", 0.005, "currency")]:122 inp.append(row)123wb.defined_names["Growth"] = DefinedName("Growth", attr_text="Inputs!$B$3")124125calc = wb.create_sheet("Calc")126calc.append(["Line"] + [f"Year {i}" for i in range(1, 6)] + ["Total"])127cols = "BCDEF"128calc["A2"], calc["A3"], calc["A4"], calc["A5"] = "Units", "Revenue", "Costs", "Margin"129for i, c in enumerate(cols):130 calc[f"{c}2"] = "=Inputs!$B$4" if i == 0 else f"={cols[i-1]}2*(1+Growth)"131 calc[f"{c}3"] = f"={c}2*Inputs!$B$2"132 calc[f"{c}4"] = f"={c}3*Inputs!$B$5"133 calc[f"{c}5"] = f"={c}3-{c}4"134for r in (3, 4, 5):135 calc[f"G{r}"] = f"=SUM(B{r}:F{r})"136137out = wb.create_sheet("Outputs")138out.append(("Total revenue", "=Calc!G3"))139out.append(("Total margin", "=Calc!G5"))140out.append(("All checks pass", "=Controls!E4"))141142ctl = wb.create_sheet("Controls")143ctl.append(("Control", "Left", "Right", "Difference", "OK"))144ctl.append(("Margin = revenue - costs", "=Calc!G5", "=Calc!G3-Calc!G4", "=B2-C2", "=ABS(D2)<Inputs!$B$6"))145ctl.append(("Outputs tie to Calc", "=Outputs!B1", "=SUM(Calc!B3:F3)", "=B3-C3", "=ABS(D3)<Inputs!$B$6"))146ctl.append(("All checks pass", None, None, None, "=AND(E2:E3)"))147wb.save("model.xlsx")148```149150```bash151soffice --headless --convert-to xlsx --outdir recalculated model.xlsx152node scripts/xlsx.mjs report recalculated/model.xlsx153```154155Expected: no error, no circular reference, no constant inside a formula, no broken pattern,156two controls passing, the overall cell TRUE and consistent.157