Build Engine — shared mechanics for every model

Read this once per build. The family references give each model's distinctive tabs, formulas, and checks; this file gives the machinery they all share, so they don't repeat it.

Tab architecture

Every model uses a small set of tabs in this order:

1. Cover — title, one-line purpose, build date, the colour legend, any toggle (scenario / circuit-breaker / waterfall type), and a named-range index. Often echoes the headline output.
2. Assumptions (a.k.a. Inputs / Drivers) — the only place hardcoded blue inputs live. One labelled cell per driver, units in an adjacent column.
3. Calculation tabs — one or more, reading from Assumptions. Periods run across columns; line items down rows.
4. Outputs / Summary — the answer: valuation, returns, ratios, ranges.
5. Checks — tie-outs and sanity flags, ending in a single master PASS/FAIL.

Freeze header rows on data tabs. Put periods across columns with a clear period-0 (entry / historical / latest actual) and projection columns following.

Cell colour convention

• Blue font = hardcoded input. Lives only on Assumptions and Cover.
• Black font = formula computed within the same tab.
• Green font = link pulling a value from another tab (=OtherTab!Cell).

The colours are the audit trail: a reviewer should see at a glance which cells are typed and which are derived. Never type a number where a scenario or another tab should drive it.

No hardcoded constants

Every assumption is its own blue cell and is referenced, never embedded. =Revenue*0.25 is wrong; put the 25% tax rate on Assumptions and write =Revenue*TaxRate. This is what lets the whole model re-solve when one input changes, and it is the first thing the QA gate checks.

Named ranges

Name the major drivers (WACC, TaxRate, Hurdle, Carry, Scenario, EBITDA, Commitment, …) with openpyxl's DefinedName so cross-tab formulas read in English (=EquityWeight*CostEquity+DebtWeight*AfterTaxKd) rather than in opaque cell addresses. List them in the Cover's named-range index.

Scenario toggle (Base / Bull / Bear)

Where a model carries cases:

• Store three input columns (Base, Bull, Bear) per driver, plus a single selector cell (1/2/3), named scn or Scenario, with a data-validation dropdown.
• The active value pulls with =CHOOSE(scn, Base, Bull, Bear) or =INDEX(Base:Bear, scn). CHOOSE is explicit for three fixed cases; INDEX scales if cases may grow.
• For a side-by-side comparison that shows all three at once regardless of the selector, replicate the few output formulas three times, each fed directly by the Base, Bull, or Bear column (parallel calc) — see references/analysis-and-decision.md (scenario manager) for the full pattern.
• Directional sanity: Bull should not beat Base downward and Bear should not beat Base upward on the headline metric; a violation usually means two case columns are swapped.

Circularity (interest ↔ cash ↔ debt)

Debt models create a loop: interest reduces cash, cash drives the sweep, the sweep changes balances, and balances change interest. Handle it one of two ways, and document the choice on the Cover:

• Circuit-breaker = 1: compute interest on the beginning balance. No circularity, deterministic, clean first calc. Default for delivery.
• Circuit-breaker = 0: compute interest on the average of beginning and ending balance (more precise), which is circular — enable iterative calculation in the recalc engine.

The same pattern appears wherever an output feeds back into its own driver (e.g. a management ratchet keyed to the sponsor MOIC it helps determine — break it by keying on the pre-ratchet value).

openpyxl build notes

• Write every derived cell as a formula string (ws["B5"] = "=XIRR(Flows,Dates,0.1)"). Never assign the precomputed number where a formula belongs.
• Sampled data (Monte Carlo trial draws, a numpy-computed fallback grid) is written as plain values — it is data, not a derived cell — and kept visually distinct with a "computed snapshot" header.
• Create named ranges with DefinedName at workbook scope.
• Apply numFmt and column widths per column, not per cell, to keep the file small.
• Charts: build with openpyxl.chart (BarChart for columns/bars/waterfalls/histograms). For floating-bar charts (football field, waterfall bridge) use a stacked bar with an invisible base series (set the base series fill and border to none) so only the visible range shows. Apply colour scales with openpyxl.formatting.rule.ColorScaleRule.
• Data-validation dropdowns: DataValidation(type="list", formula1='"1,2,3"'), then ws.add_data_validation(dv) and dv.add(ws["B2"]).

Native Data Tables vs code-computed grids

Excel's What-If Data Table ({=TABLE(row_input, col_input)}) is the native way to build a sensitivity grid, but openpyxl can only write the array text — it cannot populate it until a real engine recalculates. Two robust patterns, often built together:

• Native Data Table — write the =TABLE(...) structure and the live corner cell =Model!Output; proves the grid is live after recalc.
• Code-computed grid — re-evaluate the model across every input combination in Python and write the resulting values as a labelled matrix; guarantees populated numbers even before the user opens Excel.

Keep both when generating via code: the native table for liveness, the computed matrix for certainty. Detail in references/analysis-and-decision.md.

Recalc-and-verify loop (mandatory before delivery)

1. Save with openpyxl (formula text only; values are blank).
2. Recalculate in a real engine headlessly — e.g. libreoffice --headless --convert-to xlsx — so formulas, cross-sheet links, =TABLE(...), XIRR, NORM.INV/RAND, and chart source ranges populate. Enable iterative calculation first if the model averages balances.
3. Reload and scan every cell for #REF!, #DIV/0!, #VALUE!, #NAME?.
4. Confirm the model's structural tie-out is zero and the Checks tab master flag reads PASS.
5. If anything fails, trace it (a common cause: a missing named range, a zero denominator, or an XIRR with no guess / single-sign flows), fix, and loop from step 2.
6. Deliver only when errors are zero and all checks PASS.

Formatting standards

Quantity	Format
Currency ($mm), negatives in parentheses	#,##0;(#,##0) or #,##0.0
Multiples / leverage / MOIC	0.0"x"
Percentages, growth, IRR, headroom	0.0%
Per-share values	#,##0.00
Discount factors	0.000
EPS	0.00

Use conditional formatting to shade FAIL flags red and to colour-scale sensitivity grids. Highlight the scenario selector cell with a fill and border so the single control cell is obvious.

Checks discipline

Each model's reference lists its specific checks. They always include: the structural identity tie-out (= 0), no divide-by-zero (guard denominators with =IF(denom=0,"n/m",ratio)), non-negativity where required, and a master flag =IF(SUMPRODUCT(--(checks fail))=0,"PASS","FAIL") or =IF(COUNTIF(flags,"FAIL")=0,"PASS","FAIL"). Spot-check at least one ratio or tier by recomputing it independently as an extra tie.

Integrity rules

• No invented benchmarks, statistics, testimonials, or sources.
• Generic placeholder names only (Peer A, Segment A, Acquirer A) — never real company names in illustrative builds.
• Label every example figure as hypothetical.