officecli-financial-model

name officecli-financial-model description Use this skill when the user wants to build a financial model — 3-statement model, DCF valuation, LBO, SaaS unit economics, sensitivity / scenario analysis, debt schedule, or fundraising projections — in Excel. Trigger on: 'financial model', '3-statement model', 'P&L + BS + CF', 'DCF', 'WACC', 'NPV', 'terminal value', 'LBO', 'debt schedule', 'cash sweep', 'MOIC', 'IRR / XIRR', 'sensitivity table', 'scenario analysis', 'ARR model', 'unit economics', 'CAC / LTV', 'cap table forecast'. Output is a single formula-driven .xlsx. This skill is a scene layer on top of officecli-xlsx — it inherits every xlsx v2 rule (4-color code, visual floor, number formats, cache-drift, Known Issues, Delivery Gate minimum cycle). DO NOT invoke for a simple budget tracker, CSV dump, or operational KPI sheet — route those to officecli-xlsx base. 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: Three-zone architecture mandatory: Inputs → Calc → Outputs. Collapsing zones → unauditable. Assumptions live in cells, never inside formulas. =B5*(1+Assumptions!GrowthRate) , never =B5*1.05 . Statements balance every period. Assets − Liab − Equity = 0 , CF.EndingCash = BS.Cash . Gate 4 fails on IMBALANCED . Hardcodes audited. Calc sheets carry zero hardcoded numbers; Gate 6 counts. 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. 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. 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 . 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): # 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: # 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. 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: 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.