officecli-financial-model

# OfficeCLI Financial-Model Skill **This skill is a scene layer on top of `officecli-xlsx`.** Every xlsx hard rule — shell quoting, incremental execution, Help-First Rule, visual delivery floor, CFO 4-color code (blue input / black formula / green cross-sheet / yellow-fill assumption), number-format standards (years as text, zero as `-`, `%` one decimal, negatives in parens), assumption-cell discipline, CSV batch import, chart data-feed forms (a/b/c), the 5-gate Delivery cycle, cache-drift guidance, Known Issues (the cross-sheet `!` trap, batch + resident for formulas, renderer caveats) — is **inherited, not re-taught**. This file adds only what a **financial model** requires on top: three-zone architecture, 3 model-type recipes (3-statement / DCF / LBO), sensitivity + scenario protocols, financial-function patterns, circular-reference discipline, and model-specific Delivery Gates 4–6. When the xlsx base rules cover it, the text here says `→ see xlsx v2 §X`. Read `skills/officecli-xlsx/SKILL.md` first if you have not. ## Setup If `officecli` is missing: - **macOS / Linux**: `curl -fsSL https://d.officecli.ai/install.sh | bash` - **Windows (PowerShell)**: `irm https://d.officecli.ai/install.ps1 | iex` Verify with `officecli --version` (open a new terminal if PATH hasn't picked up). If install fails, download a binary from https://github.com/iOfficeAI/OfficeCLI/releases. ## Help-First Rule This skill teaches what a financial model requires, not every CLI flag. When a prop name / alias / enum is uncertain, consult help BEFORE guessing: `officecli help xlsx [element] [--json]`. Help is authoritative for the installed version — when this skill and help disagree, **help wins**. Every `--prop X=` below was verified against `officecli help xlsx <element>`. ## Mental Model & Inheritance **Inherits xlsx v2.** Read `skills/officecli-xlsx/SKILL.md` first. This skill assumes you know `create` / `open` / `close`, `set` values/formulas, `batch` heredocs for cross-sheet formulas, `/SheetName/A1` paths, named ranges, the 5-gate Delivery cycle, the cross-sheet `!` trap, and that **cross-sheet formulas go non-resident (single batch OR individual `set`), never batch-while-resident**. ## Shell & Execution Discipline Shell quoting, incremental execution, `$FILE` convention → see xlsx v2 §Shell & Execution Discipline. Same rules: quote every `[N]` path, single-quote any prop containing `$` (every number format here — `$#,##0;($#,##0);"-"` — needs single quotes), no hand-written `\$`/`\t`/`\n`, one command at a time. Examples below use `$FILE` (`FILE="model.xlsx"`). ## Core Principles (identity) A financial model is an xlsx with a **decision-grade, formula-driven layer**: every output traces an unbroken chain to blue-font assumptions, every statement balances every period, every valuation is re-auditable. Eight deltas on top of a general xlsx: 1. **Three-zone architecture mandatory:** Inputs → Calc → Outputs. Collapsing zones → unauditable. 2. **Assumptions live in cells, never inside formulas.** `=B5*(1+Assumptions!GrowthRate)`, never `=B5*1.05`. 3. **Statements balance every period.** `Assets − Liab − Equity = 0`, `CF.EndingCash = BS.Cash`. Gate 4 fails on `IMBALANCED`. 4. **Hardcodes audited.** Calc sheets carry zero hardcoded numbers; Gate 6 counts. 5. **Sensitivity / scenario is first-class.** 2-axis grid, dropdown `INDEX/MATCH` switch, or Base/Upside/Downside cols. Excel Data Tables not reliably supported — manual grids only. 6. **Cached values on valuation cells load-bearing.** A valuation cell that ships with no cached result (or the `#OCLI_NOTEVAL!` sentinel) sends a blank/wrong number to non-recalculating readers. The evaluator now computes NPV / XNPV / IRR / XIRR; verify the cached value with a readback. Gate 5 spot-checks. 7. **Circularity is a design choice.** Legitimate rings (interest ↔ cash, revolver plug ↔ ending cash) use `calc.iterate=true`. Accidental circularity is broken algebra — never papered with `iterate`. 8. **Named ranges for ≥ 3-use assumptions.** `WACC`, `TaxRate`, `TerminalGrowth`, `ExitMultiple`, `ChurnRate`. Declared-unused names are dead decoration — Gate 6 flags. ### Reverse handoff — when to go BACK to xlsx base Stay in **xlsx base** for: budget trackers, CSV-to-report dumps, operational KPI sheets, simple templates, cap tables without forecast logic. Use **this skill** only when the ask mentions: 3-statement / DCF / WACC / NPV / TV / LBO / debt schedule / MOIC / IRR / unit economics / ARR roll-forward / sensitivity grid / scenario switch / pro forma. ## Three-zone architecture (hard rule) Every model in this skill builds on three zones. **Name them, tab-color them, and enforce them with executable audits.** Breaking the zone rule is the single most common cause of an unauditable model. | Zone | Sheet names (convention) | Tab color | Content | Hardcodes | Formulas | |---|---|---|---|---|---| | **Inputs** | `Assumptions`, `Inputs`, `Drivers` | Yellow `FFC000` | Raw drivers: growth rates, margins, tax, WACC, FTE, pricing, working-capital days | Blue `0000FF` on every cell | Allowed only for derived assumptions (e.g. `=MonthlyARPU*12`) | | **Calc** | `P&L`, `Balance Sheet`, `Cash Flow`, `DCF`, `Debt`, `ARR` | Blue `4472C4` | All derivations and statements | **Zero** (enforced by Gate 6) | Black `000000` for same-sheet, green `008000` for cross-sheet | | **Outputs** | `Summary`, `Dashboard`, `Sensitivity`, `Returns` | Green `70AD47` | KPIs, sensitivity grids, charts, returns waterfall | Only for labels (non-numeric); Gate 6 counts numeric hardcodes → 0 | Black / green per above | **Build order is cross-zone-aware.** Assumptions first, then Calc bottom-up on the dependency chain (`IS → BS → CF` for 3-statement; `FCF → WACC → NPV` for DCF), then Outputs last. Building Outputs first caches `0` everywhere and downstream inherits zeros. **Executable zone audit** (run before Gate 4): ```bash # Calc zone: zero numeric hardcodes allowed. `cell:not(:has(formula))` selects the literal (non-formula) cells; `cell:has(formula)` selects the formula cells. HARDCODE=$(officecli query "$FILE" 'cell[type=Number]' --json | jq '[.data.results[] | select(.format.formula == null) | select(.path | test("/(P&L|Balance Sheet|Cash Flow|DCF|Debt|ARR)/"))] | length') [ "$HARDCODE" -eq 0 ] && echo "Zone audit OK" || { echo "REJECT: $HARDCODE hardcoded numeric cells on Calc sheets — move to Assumptions"; exit 1; } # Assumptions zone: should be non-zero. INPUTS=$(officecli query "$FILE" '/Assumptions/cell[type=Number]' --json | jq '[.data.results[] | select(.format.formula == null)] | length') [ "$INPUTS" -ge 5 ] && echo "Assumptions has $INPUTS hardcoded drivers" || echo "WARN: Assumptions has only $INPUTS inputs" ``` ## Print delivery (board / IC / LP) When the ask contains "print" / "一页" / "董事会" / "投资人" / "IC memo" / "LP update", the print pipeline must emit **only** the Outputs zone. Two artefacts: ```bash # 1. Print_Area scoped to the Outputs sheet (Summary or Dashboard). officecli add "$FILE" / --type namedrange --prop name=_xlnm.Print_Area --prop scope=Summary --prop 'refersTo=Summary!$A$1:$H$40' # 2. Hide every non-Outputs sheet — Print_Area scope alone does NOT stop the print pipeline from emitting every visible sheet. for S in Assumptions 'P&L' 'Balance Sheet' 'Cash Flow' DCF WACC Debt FCF 'S&U' Exit Returns; do officecli raw-set "$FILE" /workbook --xpath "//x:sheet[@name='$S']" --action setattr --xml "state=hidden" || true done # 3. fit-to-page landscape on Outputs sheet. officecli raw-set "$FILE" /Summary --xpath "//x:worksheet" --action prepend --xml '<sheetPr xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><pageSetUpPr fitToPage="1"/></sheetPr>' ``` Delete any `Print_Area` set on Calc sheets — conflicting scopes emit multi-page output with Assumptions / statement sheets leaking. ## Build-order & cache-drift rule (critical for 3-statement) Three facts cause silent wrong numbers: (1) new formulas ship without cached values — Excel recomputes on open, HTML preview / older viewers do not; (2) downstream written in the same sequence as upstream caches `0` from upstream's pre-cache state; (3) cross-sheet `batch` while resident is open deadlocks at 3–5 ops. **Discipline (every recipe):** - Build order follows the data chain: `P&L → BS → CF` (3-statement); `FCF → WACC → NPV → Sensitivity` (DCF); `S&U → Debt → P&L → CF → Returns` (LBO). - After the cross-sheet chain, **cache-refresh pass:** re-issue `set` on every summary / valuation / balance-check cell, non-resident. - Spot-check: `officecli get "$FILE" /Summary/B2 --json | jq '.data.results[0].format.cachedValue'` returns a plausible non-null value. `null` means Excel will compute on open (OK for delivery). If a cell shows the `#OCLI_NOTEVAL!` sentinel: close residents, re-set; still unevaluated → cache-fallback (§Financial function patterns). ## Recipes — three model types Each recipe below is **runnable skeleton, not finance theory**. Substitute numbers; don't restructure. All recipes assume `FILE="model.xlsx"` is set and you have run `officecli create "$FILE"` + `officecli open "$FILE"`. Close with `officecli close "$FILE"` at the end. ### Recipe A — 3-statement model (P&L + BS + CF) **What this recipe produces.** 4 sheets: `Assumptions`, `P&L`, `Balance Sheet`, `Cash Flow`, plus `Summary`. Year columns 2024A · 2025E · 2026E · 2027E. Balance-check row on BS; cash-reconciliation row on CF. Every statement row = formula → Assumptions. **Build order (MANDATORY).** `Assumptions → P&L → Balance Sheet → Cash Flow → Summary`. Do NOT build BS before P&L — `RetainedEarnings` depends on `NI`. Do NOT build CF before BS — `CF.OpeningCash = prior period CF.EndingCash` self-chain requires BS cash anchored for Y1. The skill's Gate 4 balance check fails silently if order is wrong. **Step 1 — sheets + tab colors + freeze panes.** ```bash officecli add "$FILE" / --type sheet --prop name=Assumptions --prop tabColor=FFC000 officecli add "$FILE" / --type sheet --prop name=P&L --prop tabColor=4472C4 officecli add "$FILE" / --type sheet --prop name='Balance Sheet' --prop tabColor=4472C4 officecli add "$FILE" / --type sheet --prop name='Cash Flow' --prop tabColor=4472C4 officecli add "$FILE" / --type sheet --prop name=Summary --prop tabColor=70AD47 officecli set "$FILE" /Assumptions --prop freeze=B2 officecli set "$FILE" /P&L --prop freeze=B3 officecli set "$FILE" "/Balance Sheet" --prop freeze=B3 officecli set "$FILE" "/Cash Flow" --prop freeze=B3 ``` **Step 2 — assumptions (blue, yellow-fill on key drivers).** Year headers row 2, labels down col A, blue numeric inputs on B:E. Drivers: `RevenueGrowth`, `GrossMargin`, `OpExRatio`, `TaxRate`, `DaysReceivable/Inventory/Payable`, `CapExRatio`, `DepreciationYears`. `font.color=0000FF` on B:E. Yellow-fill (`fill=FFFF00`) the 3–5 scenario-switched drivers. **Declare named ranges for ≥3-use drivers and reference them** (`StartingARR`, `TaxRate`, `OpeningCash`, `GrowthRate`, `GrossMargin`). Formulas: `=StartingARR` not `=Assumptions!B4`; `=EBT*TaxRate` not `=EBT*Assumptions!B8`. Declared-unused names = dead decoration, Gate 6 rejects. **Step 3 — P&L rows (all formulas).** Rows: `Revenue` / `COGS` / `Gross Profit` / `OpEx` / `EBITDA` / `D&A` / `EBIT` / `Interest` / `EBT` / `Tax` / `Net Income`. Every row = formula referencing `Assumptions` or prior-row cells. Example revenue-side block — **substitute your row numbers**. Row-map for this example: `B3=Revenue, B4=COGS, B5=Gross Profit, B7=OpEx, B9=EBITDA, B10=EBIT, B15=Net Income`. Submit as single non-resident batch: ```bash cat <<'EOF' | officecli batch "$FILE" [ {"command":"set","path":"/P&L/B3","props":{"formula":"Assumptions!B5","font.color":"008000"}}, {"command":"set","path":"/P&L/C3","props":{"formula":"B3*(1+Assumptions!C6)"}}, {"command":"set","path":"/P&L/D3","props":{"formula":"C3*(1+Assumptions!D6)"}}, {"command":"set","path":"/P&L/E3","props":{"formula":"D3*(1+Assumptions!E6)"}}, {"command":"set","path":"/P&L/B4","props":{"formula":"-B3*(1-Assumptions!B7)"}}, {"command":"set","path":"/P&L/B5","props":{"formula":"B3+B4"}} ] EOF ``` Assumptions refs (`B5`, `C6`, `B7`) are also placeholder rows — better: **define named ranges** for each driver (Step 2) so formulas read `=StartingRevenue*(1+RevenueGrowth_Y2)` regardless of row layout. Repeat for `OpEx` / `D&A` / `Interest` / `Tax` / `NI`. `font.color=008000` on every cross-sheet-reference cell; same-sheet cells default `000000`. `numFmt='$#,##0;($#,##0);"-"'` on all $ rows.