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.

运行环境与交付链（本系统专用，先读这一段）

officecli 已经预装在沙盒镜像的 /usr/local/bin/officecli，不要联网安装、不要 curl。 bash 工具直接调用即可，自检用 officecli --version。

所有命令都在沙盒里执行，文件一律落在 /workspace/ 下。一次完整作业 = 3 步 + 1 步交付：

# Step 1（仅当用户上传过原始文件）把 artifact 送进沙盒
sandbox_put_artifact(artifact_id="<用户文件的 file_id>", dest_path="/workspace/in.docx")

# Step 2 用 bash 跑 officecli —— 本文件下面讲的全部命令都在这一步里
bash("officecli create /workspace/out.docx")

# Step 3 把成稿从沙盒取出、登记成 artifact，拿到新的 file_id
sandbox_get_artifact(src_path="/workspace/out.docx", name="终稿.docx")

# Step 4（必做）交付给用户
pin_to_workspace(file_ids=["<新 file_id>"])

铁律：

• sandbox_put_artifact = 放进沙盒，sandbox_get_artifact = 从沙盒取出，别搞反；两个路径参数都必须以 /workspace/ 开头。
• 拿到 file_id 却不 pin_to_workspace 就等于没交付，用户看不到文件。多个产物一次性放进同一个列表，别分多次调用。
• 下文示例里的 $FILE 和相对路径，都要换成 /workspace/ 下的绝对路径。
• 下文的可视化质检（view <file> screenshot / view <file> html）依赖沙盒内的 Chromium，镜像已预装；截图失败就按下文 fallback 走 view html，并如实标注「未做视觉验证」，不要谎报 PASS。
• 导出 PDF 不要用 officecli view <file> pdf —— 那个模式依赖本系统没有安装的 exporter 插件，必然失败。改用沙盒里的 LibreOffice： soffice --headless --convert-to pdf --outdir /workspace /workspace/out.docx。.xlsx / .pptx 换掉扩展名同理，但要沙盒里装了 LibreOffice Calc / Impress 才转得动；转换报错就如实告诉用户 PDF 没导出成功、主产物仍是原文件，不要谎报已导出。

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.

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.

Step 4 — Balance Sheet rows (all formulas). Assets = Cash + AR + Inventory + Net PP&E. Liab = AP + Debt. Equity = OpeningEquity + RetainedEarnings. Working-capital rows use Days assumptions: AR = Revenue × DaysReceivable / 365. Net PP&E rolls forward: Beg + CapEx − Depreciation. BS.Cash is NOT an independent plug — it MUST equal 'Cash Flow'!B<ending-cash-row> (populated in Step 5).

Retained Earnings — live formula every period. BS.RE(t) = BS.RE(t-1) + 'P&L'!NI(t) − Dividends(t). Hardcoded RE rounds to whole dollar → BS shows ±$1 off every period (CFO reads "model doesn't balance"). For Y1 Historical RE (no prior NI), compute via BS identity as a live formula: BS!RE_Y1 = TotalAssets − TotalLiabilities − PaidInCapital. Blue-font + classic comment on the Y1 cell; Y2..Y5 stay NI-driven.

Step 5 — Cash Flow rows (all formulas). Operating: NI + D&A − ΔWorkingCapital. Investing: −CapEx. Financing: ΔDebt − Dividends. Ending Cash = Opening + Operating + Investing + Financing. Year 2+ Opening Cash = prior period Ending Cash — self-chain on the same sheet: C17 = B19, D17 = C19, E17 = D19. The Y1 OpeningCash is an Assumptions input.

Step 6 — Balance check + cash reconciliation rows (hard delivery checks). Row-map for this example: Balance Sheet: B10=Total Assets, B15=Total Liab, B17=Total Equity, B18=Balance Check; Cash Flow: B5=BS.Cash (cross-sheet anchor), B19=CF.Ending Cash, B21=CF-BS Cash Recon. Substitute your layout's rows — the logic is the check, not the cell addresses.

cat <<'EOF' | officecli batch "$FILE"
[
  {"command":"set","path":"/Balance Sheet/B18","props":{"formula":"IF(ABS(B10-B15-B17)<0.01,\"OK\",\"IMBALANCED: \"&ROUND(B10-B15-B17,0))","bold":"true","font.color":"000000"}},
  {"command":"set","path":"/Cash Flow/B21","props":{"formula":"IF(ABS(B19-'Balance Sheet'!B5)<0.01,\"OK\",\"CF != BS CASH: \"&ROUND(B19-'Balance Sheet'!B5,0))","bold":"true"}}
]
EOF

Replicate across columns C/D/E. Apply red fill (fill=FFC7CE) conditionally via type=containsText --prop text=IMBALANCED or text="CF !=". Gate 4 queries these rows and refuses delivery on any IMBALANCED.

Step 7 — cache refresh + format pass. Re-set every summary cell on Summary, every balance-check / recon cell, and every cross-sheet reference on BS / CF (non-resident, single batch per sheet). Apply column widths (col[A]=28, col[B:E]=15), numberformat='$#,##0;($#,##0);"-"' on all dollar rows, header fills (fill=1F3864, font.color=FFFFFF, bold=true) on section-header rows (REVENUE / COGS / ASSETS / LIABILITIES). Header fill must cover A:E, not just the label cell (→ xlsx v2 §visual floor).

Step 8 — Summary / Dashboard KPIs + charts. Minimum 4 KPIs: Revenue 27E, EBITDA Margin 27E, Ending Cash 27E, Net Income CAGR — each a formula referencing a statement cell, green font.

Minimum 3 charts on any Dashboard delivered to a board / executive audience — one chart is a draft, three is a deliverable. Pre-populate Summary!A10:E13 with Gross Margin / EBITDA Margin / NI Margin ratio rows (formulas referencing P&L) before adding the margin chart.

# (1) Top-line trend (Revenue + EBITDA).
officecli add "$FILE" /Summary --type chart --prop chartType=column --prop dataRange='P&L!A2:E5' --prop title='Revenue & EBITDA' --prop width=14cm --prop height=8cm
# (2) Margin trend (Gross / EBITDA / NI margin).
officecli add "$FILE" /Summary --type chart --prop chartType=line --prop dataRange='Summary!A10:E13' --prop title='Margin trend' --prop width=14cm --prop height=8cm
# (3) Cash trajectory (Ending Cash ± Runway).
officecli add "$FILE" /Summary --type chart --prop chartType=area --prop dataRange='Cash Flow!A19:E19' --prop title='Ending cash' --prop width=14cm --prop height=8cm

Verification (run all three):

# Balance check every period must say OK (range get → cells under .data.results[0].children[])
officecli get "$FILE" "/Balance Sheet/B18:E18" --json | jq '.data.results[0].children[] | .format.cachedValue // .text'
# Cash recon every period must say OK
officecli get "$FILE" "/Cash Flow/B21:E21" --json | jq '.data.results[0].children[] | .format.cachedValue // .text'
# Summary KPIs are plausible numbers, not null
officecli get "$FILE" "/Summary/B2:B5" --json | jq '.data.results[0].children[].format.cachedValue'

Recipe B — DCF valuation

What this recipe produces. Sheets: Assumptions, FCF (10-year forecast), WACC (panel), DCF (NPV + TV + equity bridge), Sensitivity (2-axis grid). Output: Implied Equity Value + Implied Per-Share, with a WACC × g sensitivity.

Build order. Assumptions → FCF → WACC → DCF → Sensitivity.

Step 1 — named ranges for key drivers. DCF's readability depends on names. Every formula below uses WACC, TaxRate, g — not $B$6:

cat <<'EOF' | officecli batch "$FILE"
[
  {"command":"add","parent":"/","type":"namedrange","props":{"name":"WACC","ref":"WACC!$B$12"}},
  {"command":"add","parent":"/","type":"namedrange","props":{"name":"TaxRate","ref":"Assumptions!$B$8"}},
  {"command":"add","parent":"/","type":"namedrange","props":{"name":"TerminalGrowth","ref":"Assumptions!$B$15"}},
  {"command":"add","parent":"/","type":"namedrange","props":{"name":"NetDebt","ref":"Assumptions!$B$20"}},
  {"command":"add","parent":"/","type":"namedrange","props":{"name":"SharesOut","ref":"Assumptions!$B$21"}}
]
EOF

Step 2 — FCF build (10 years). Columns B:K = Y1..Y10. Rows: Revenue (from growth) / EBIT (revenue × margin) / EBIT × (1 − TaxRate) (NOPAT) / + D&A / − CapEx / − ΔNWC / = FCF. Use Assumptions-driven ratios (CapEx = Revenue × CapExRatio). All cells formulas, black font, numFmt='$#,##0;($#,##0);"-"'.

Step 3 — WACC panel. On WACC sheet, an 8-row panel: Risk-free rate / Equity risk premium / Beta / Cost of equity (=Rf + β×ERP) / Pre-tax debt cost / After-tax debt cost (=×(1−TaxRate)) / Equity weight / Debt weight / WACC (=We×Re + Wd×Rd_after_tax). Inputs blue; derived rows black.

Step 4 — Terminal value + NPV + equity bridge. Row-map: DCF: B/C 3=TV, 4=PV explicit FCF, 5=PV terminal, 6=EV, 7=Net Debt, 8=Equity Value, 9=Per-Share; FCF: row 2 = periods (1..10), row 11 = FCF, B:K = Y1..Y10. Substitute your rows. Notes column cells use {"value":"text"}, never {"formula":"..."} — formula-style prose yields #NAME? on open (see callout after Recipe C Step 5). On DCF sheet:

cat <<'EOF' | officecli batch "$FILE"
[
  {"command":"set","path":"/DCF/B3","props":{"value":"Terminal value (Gordon growth)"}},
  {"command":"set","path":"/DCF/C3","props":{"formula":"FCF!K11*(1+TerminalGrowth)/(WACC-TerminalGrowth)","font.color":"008000","numberformat":"$#,##0;($#,##0);\"-\""}},
  {"command":"set","path":"/DCF/B4","props":{"value":"PV of explicit-period FCF (10 yr)"}},
  {"command":"set","path":"/DCF/C4","props":{"formula":"SUMPRODUCT(FCF!B11:K11/(1+WACC)^FCF!B2:K2)","font.color":"008000","numberformat":"$#,##0;($#,##0);\"-\""}},
  {"command":"set","path":"/DCF/B5","props":{"value":"PV of terminal value"}},
  {"command":"set","path":"/DCF/C5","props":{"formula":"C3/(1+WACC)^10","numberformat":"$#,##0;($#,##0);\"-\""}},
  {"command":"set","path":"/DCF/B6","props":{"value":"Enterprise value"}},
  {"command":"set","path":"/DCF/C6","props":{"formula":"C4+C5","bold":"true","numberformat":"$#,##0;($#,##0);\"-\""}},
  {"command":"set","path":"/DCF/B7","props":{"value":"Less: Net debt"}},
  {"command":"set","path":"/DCF/C7","props":{"formula":"-NetDebt","font.color":"008000","numberformat":"$#,##0;($#,##0);\"-\""}},
  {"command":"set","path":"/DCF/B8","props":{"value":"Equity value"}},
  {"command":"set","path":"/DCF/C8","props":{"formula":"C6+C7","bold":"true","font.color":"000000","numberformat":"$#,##0;($#,##0);\"-\""}},
  {"command":"set","path":"/DCF/B9","props":{"value":"Implied per-share"}},
  {"command":"set","path":"/DCF/C9","props":{"formula":"C8/SharesOut","bold":"true","numberformat":"$0.00"}}
]
EOF

NPV vs SUMPRODUCT — both cache correctly. The evaluator computes NPV(rate, cross_sheet_range) and caches the right value (verify with a readback). SUMPRODUCT(values/(1+rate)^periods) is the algebraic equivalent (period row FCF!B2:K2 = 1..10 is a one-time setup); it is shown here as an OPTIONAL audit-readability choice — the explicit discounting series is sometimes easier for a reviewer to trace, not a cache workaround. Either form is fine. For irregular dates, XNPV(rate, values, dates) likewise caches; the SUMPRODUCT equivalent is SUMPRODUCT(values/(1+rate)^((dates-base_date)/365)).

Step 5 — 2-axis sensitivity grid (WACC × g). 5×5 grid. Rows = WACC values 7.5% ... 11.5%, cols = g values 1.5% ... 3.5%. Each cell = one self-contained formula re-running the DCF with the grid's WACC and g substituted. Template:

# Cell D14 (first data cell, grid anchor at C14 = WACC label, C15 = first WACC value)
# Substitute $D$13 (this cell's g) and $C15 (this cell's WACC) into a replicated EV + equity formula.
cat <<'EOF' | officecli batch "$FILE"
[
  {"command":"set","path":"/Sensitivity/D15","props":{"formula":"(NPV($C15,FCF!$B$11:$K$11)+(FCF!$K$11*(1+D$14)/($C15-D$14))/(1+$C15)^10+(-NetDebt))/SharesOut","numberformat":"$0.00"}}
]
EOF

Copy the formula across D15:H19 (5×5 grid). Row 14 carries g values (blue input); column C carries WACC values (blue input). Row 13 and column B carry labels. Apply 3-color gradient CF for quick-read (green = upside, red = downside):

officecli add "$FILE" /Sensitivity --type conditionalformatting \
  --prop type=colorScale --prop ref=D15:H19

No Excel Data Tables. Excel's native /Data/Table 2-variable table is not reliably supported via the CLI — each grid cell MUST be an explicit formula. Copy the template, do not try Data Table input cells.

Verification.

officecli get "$FILE" "/DCF/C8" --json | jq '.data.results[0].format.cachedValue'   # equity value, plausible $
officecli get "$FILE" "/DCF/C9" --json | jq '.data.results[0].format.cachedValue'   # per-share, in $XX.XX range
officecli get "$FILE" "/Sensitivity/F17" --json | jq '.data.results[0].format.cachedValue'   # grid center cell, plausible

If C8 or C9 come back null or show the #OCLI_NOTEVAL! sentinel, re-set them (non-resident) — see §Build-order & cache-drift.

Recipe C — LBO model

What this recipe produces. Sheets: Assumptions, S&U (Sources & Uses), Debt (multi-tranche schedule), P&L (5-yr), CF, Exit / Returns. Outputs: MOIC, IRR, and a 4-tier returns waterfall. LBO is the stress test — expect circular refs (interest ↔ cash), deepest cross-sheet chains, and the heaviest use of named ranges.

Build order. Assumptions → S&U → P&L → Debt → CF → Exit → Returns. P&L before Debt (debt interest depends on P&L EBIT for coverage checks); Debt before CF (CF uses interest + principal amortization). Enable calc.iterate before Step 5.

Step 1 — Sources & Uses (balance required, every fee line itemized).

Uses    = Purchase_EV (EntryEBITDA × EntryMultiple) + Transaction_fees (Purchase_EV × TxnFeePct, typ 1.5–2.5%)
        + Financing_fees ((Senior + Mezz) × FinFeePct, typ 1–3%) + Refinanced_debt
Sources = Senior_TLB + Mezz + Revolver_drawn + Sponsor_equity

Sponsor equity — pick one, never both. (a) Stated: Sponsor_equity = Assumptions!SponsorEquity, then scale senior/mezz so Sources = Uses (fees absorbed by debt, not a silent plug). (b) Solved: Sponsor_equity = Uses − Senior − Mezz − Revolver − Refinanced, label "Sponsor Equity (solved)", no standalone Assumptions ref. Hardcoded SponsorEquity PLUS a =Uses − Senior − Mezz plug guarantees silent fee absorption — stated $140M vs plug $194.67M = $54.67M unaccounted fees, CFO rejection on sight.

# Sources = Uses hard check.
officecli set "$FILE" /S&U/B12 --prop formula='IF(ABS(SUM(B4:B7)-SUM(B9:B11))<1,"BALANCED","S&U IMBALANCE: "&ROUND(SUM(B4:B7)-SUM(B9:B11),0))' --prop bold=true

# Stated-vs-plug consistency (Gate 4 addendum; only run if you chose pattern (a)).
STATED=$(officecli get "$FILE" /Assumptions/B12 --json | jq -r '.data.results[0].format.cachedValue // "null"')
PLUGGED=$(officecli get "$FILE" /S&U/B10 --json | jq -r '.data.results[0].format.cachedValue // "null"')   # B10 = sponsor-equity row on S&U
DELTA=$(python3 -c "print(abs(float('$STATED') - float('$PLUGGED')))" 2>/dev/null || echo 99999)
python3 -c "import sys; sys.exit(0 if float('$DELTA') <= 1 else 1)" && echo "S&U sponsor OK (stated=$STATED plug=$PLUGGED)" || { echo "REJECT Gate 4 S&U: stated $STATED ≠ plug $PLUGGED (Δ=$DELTA) — fees silently absorbed"; exit 1; }

Every non-sponsor line on S&U is a blue Assumptions input (target EBITDA, entry multiple, fee %s) or a derived formula. No hardcoded Uses / Sources numbers.

Step 2 — Debt schedule (multi-tranche). One row per tranche per year. Columns: BeginningBalance / Mandatory amortization / Cash sweep / EndingBalance / AverageBalance / InterestExpense. Senior TLB: 1% mandatory amortization + all excess cash to sweep. Mezz: 0% amortization, interest-only cash-pay. Row-map for this example (senior TLB tranche, year 2 column C): C4=Beginning Balance, C5=Mandatory Amort, C6=Ending Balance, C7=Cash Sweep, C8=Average Balance, C9=Interest Expense. CF!C20 = free cash available to sweep (year-2 ending cash pre-sweep on CF sheet). Substitute your tranche row block per layout.

# year 2 senior TLB
cat <<'EOF' | officecli batch "$FILE"
[
  {"command":"set","path":"/Debt/C4","props":{"formula":"B6"}},
  {"command":"set","path":"/Debt/C5","props":{"formula":"-C4*Assumptions!$B$30","numberformat":"$#,##0;($#,##0);\"-\""}},
  {"command":"set","path":"/Debt/C6","props":{"formula":"C4+C5+C7","numberformat":"$#,##0;($#,##0);\"-\""}},
  {"command":"set","path":"/Debt/C7","props":{"formula":"-MIN(-CF!C20,C4+C5)"}},
  {"command":"set","path":"/Debt/C8","props":{"formula":"(C4+C6)/2","numberformat":"$#,##0;($#,##0);\"-\""}},
  {"command":"set","path":"/Debt/C9","props":{"formula":"-C8*Assumptions!$B$31","numberformat":"$#,##0;($#,##0);\"-\""}}
]
EOF
# Add the sweep-rule comment as a classic comment (comment is NOT a cell prop — separate --type comment).
officecli add "$FILE" /Debt --type comment --prop ref=C7 --prop text='cash sweep capped at available cash and remaining tranche balance'

Revolver capacity cap. If your deal uses a revolver tranche, the revolver balance each period is bounded by the commitment ceiling:

Revolver_Balance = MIN(Assumptions!RevolverCapacity, MAX(0, prior_revolver + draw − paydown))

Without the MIN(capacity, ...) outer, a shortfall quarter silently over-draws the facility.

Adjust row indices to your layout. Repeat for each tranche (senior / mezz / revolver) and each year.

Step 3 — P&L (5-year) + interest from Debt. P&L interest row pulls from Debt: Interest = 'Debt'!TotalInterestRowY<N>. This creates the circular reference: Interest → NI → CF → Cash Sweep → Debt balance → Interest.

Write-order warning. calc.iterate=true governs recalculation, not write-phase. Appending the closing leg of a cross-sheet ring to a file that already contains the ring deadlocks the engine at 100% CPU regardless of iterate. For complex rings (multi-tranche LBO, revolver + TLB + mezz), use §Write-order surgery below (de-ring → write downstream → re-ring). Enable calc.iterate=true BEFORE writing ring formulas:

officecli set "$FILE" / --prop calc.iterate=true --prop calc.iterateCount=100 --prop calc.iterateDelta=0.001

iterate converges via successive approximation for naturally-dampening loops (higher interest → less cash → less sweep → higher balance, bounded by EBIT). #REF! or divergent values = pause; fix algebra, do not raise iterateCount to 1000.

Step 4 — CF + cash sweep. Ending cash = Opening + CFO − CapEx − Mandatory amort − Cash sweep. Cash sweep = MIN(freeCashAfterCapEx, seniorDebtBalance + seniorMandatoryAmort). The MIN cap prevents swept-below-zero.

Step 5 — Exit + Returns. Row-map: Exit: B3=Exit EV, B4=Less: remaining debt, B5=Exit equity to sponsor; Returns: B3=MOIC, B4=IRR.

# Values/formulas — single non-resident batch.
cat <<'EOF' | officecli batch "$FILE"
[
  {"command":"set","path":"/Exit/B3","props":{"formula":"'P&L'!F8*Assumptions!$B$25","numberformat":"$#,##0;($#,##0);\"-\""}},
  {"command":"set","path":"/Exit/B4","props":{"formula":"-('Debt'!F6+'Debt'!F13)","font.color":"008000","numberformat":"$#,##0;($#,##0);\"-\""}},
  {"command":"set","path":"/Exit/B5","props":{"formula":"B3+B4","bold":"true","numberformat":"$#,##0;($#,##0);\"-\""}},
  {"command":"set","path":"/Returns/B3","props":{"formula":"'Exit'!B5/('S&U'!B9)","numberformat":"0.00\"x\""}},
  {"command":"set","path":"/Returns/B4","props":{"formula":"IRR({-'S&U'!B9,0,0,0,0,'Exit'!B5})","numberformat":"0.0%"}}
]
EOF
# Classic comments — one --type comment per anchor cell.
officecli add "$FILE" /Exit --type comment --prop ref=B3 --prop text='Exit EV = Y5 EBITDA × exit multiple'
officecli add "$FILE" /Returns --type comment --prop ref=B3 --prop text='MOIC = exit equity / sponsor equity'
officecli add "$FILE" /Returns --type comment --prop ref=B4 --prop text='IRR — 5-yr, entry + exit only; use XIRR for mid-year dividends'

Callout — labels: comment element vs Notes column vs formula (three distinct mechanics).

• Hover tooltip → officecli add ... --type comment --prop ref=<cell> --prop text='...'. The comment key is NOT a valid prop on set cell (not in officecli help xlsx cell) — it silently drops when embedded inside a set cell props dict. Use the dedicated element.
• Visible text in an adjacent Notes column → {"command":"set","path":"/DCF/D3","props":{"value":"TV = FCF × (1+g) / (WACC−g)"}} — value, not formula, plain quoted string.
• Formula-style prose written as a real formula → NEVER. {"formula":"FCF10*(1+g)/(WACC-g)"} produces #NAME? in Excel (FCF10, g, WACC are unbound identifiers in that cell context).

For mid-year dividends or partial exits, use XIRR({cashflows}, {dates}) instead of IRR.

Step 6 — Returns waterfall (optional, 4-tier LP/GP). Tiers: (1) LP preferred return 8% ; (2) GP catch-up to 20% ; (3) 80/20 split above hurdle ; (4) 100% to LP on loss. Each tier is a MAX(0, MIN(...)) clamp. See §Sensitivity & scenarios for the general grid pattern.

Verification.

officecli get "$FILE" /S&U/B12 --json | jq '.data.results[0].format.cachedValue // .data.results[0].text'   # must say BALANCED
officecli get "$FILE" /Returns/B3 --json | jq '.data.results[0].format.cachedValue'                # MOIC, expect 2.0x-4.0x typical
officecli get "$FILE" /Returns/B4 --json | jq '.data.results[0].format.cachedValue'                # IRR, expect 0.15-0.30 typical
# Iterate converged?
officecli query "$FILE" 'cell:contains("#REF!")' --json | jq '.data.results | length'   # must be 0

Sensitivity & scenarios

Three patterns, pick one:

• (a) Base / Upside / Downside columns on Assumptions — side-by-side scenarios, dropdown-less switch via an "Active" column + INDEX/MATCH.
• (b) Dropdown + INDEX/MATCH switch — one validation dropdown on Summary drives every driver via INDEX(Base:Downside, MATCH(Dropdown, ScenLabels, 0)).
• (c) 2-axis sensitivity grid — 5×5 or 7×7, one self-contained formula per cell, row/col headers are the two drivers. See Recipe B Step 5 for WACC × g.

Mixing (a)+(b) creates circular input (scenario picked by dropdown AND overwritten by Active column) — pick one.

Grid rule: each cell substitutes row-driver and col-driver into a self-contained copy of the output formula. Cannot reference the WACC named range (that's the panel) — reference the grid's axis cell.

Dropdown scenario switch. One validation dropdown on Summary drives every Assumptions row:

cat <<'EOF' | officecli batch "$FILE"
[
  {"command":"add","parent":"/Summary","type":"validation","props":{"sqref":"B1","type":"list","formula1":"Base,Upside,Downside"}},
  {"command":"set","path":"/Assumptions/B5","props":{"formula":"INDEX(C5:E5,MATCH(Summary!$B$1,$C$4:$E$4,0))"}}
]
EOF
# If you want a hover tooltip on B5, add it separately:
officecli add "$FILE" /Assumptions --type comment --prop ref=B5 --prop text='Revenue growth — picked by Summary!B1 scenario dropdown'

Every Assumptions driver row gets the same INDEX/MATCH. Base / Upside / Downside columns on C:E stay blue (hardcoded scenario inputs).

Football-field chart pattern (DCF valuation summary). Horizontal Low→High bars for 3–5 valuation methods (DCF base, DCF bear, Trading comps, Precedent txns, LBO floor) stacked vertically. On a Football sheet: col A = method label, col B = Low $, col C = High $, col D = =C−B (width). Chart as a stacked bar with column B as an invisible first series (white/no-fill) and column D as the visible series — dataRange=Football!A3:D7, chartType=bar. Excel reads this as a floating bar per method.

Financial function patterns

Terse reference — not a finance textbook. If you don't know what these do, pause and ask the user.

Evaluator coverage — verify, don't preemptively hardcode. The CLI evaluator computes the finance functions this skill uses — NPV / XNPV / IRR / XIRR, array-literal formulas (IRR({...})), and SUMPRODUCT(1/COUNTIF(range,range)) distinct-count — and caches the correct value with evaluated:true. There is no NPV→0 rewrite obligation and no distinct-count 1/N trap. The honest rule: build the formula you mean, then verify the cached value with a readback (get ... --json | jq '.data.results[0].format.cachedValue'). Only if a specific cell genuinely comes back with the #OCLI_NOTEVAL! sentinel (formula written before its inputs, or an unsupported function) should you fall back — first re-set non-resident after close; if it still won't evaluate, hardcode the computed value with a blue font and a classic comment via officecli add "$FILE" /Sheet --type comment --prop ref=<cell> --prop text='cached valuation; refreshes on open in Excel — do not edit', and disclose in delivery notes.

Circular references & iterative calc

Enable calc.iterate ONLY when circularity is algebraically justified: Interest ↔ Cash (LBO revolver / cash sweep), Tax shield ↔ NI (rare — most 3-statement models compute interest before tax and avoid), Revolver plug ↔ Ending cash (corporate cash waterfall with min-cash).

officecli set "$FILE" / --prop calc.iterate=true --prop calc.iterateCount=100 --prop calc.iterateDelta=0.001

iterateCount=100 / iterateDelta=0.001 are Excel defaults, fine for naturally dampening loops.

Write-order surgery (de-ring → write downstream → re-ring)

calc.iterate controls recalc, not write-phase. Appending the closing leg of an already-wired cross-sheet ring (Debt.Interest ↔ CF.Cash ↔ Debt.CashSweep) deadlocks at 100% CPU; view html / get also hang on a non-converged ring.

3-step playbook:

1. De-ring — write Debt with the 10–20 ring cells set to literal 0 (e.g. C7=0, not =-MIN(...)). Removes the ring.
2. Write downstream — build all non-circular chains (P&L, CF, Exit, Returns, Summary, grid) non-resident, one heredoc per sheet. Everything caches against the zeroed cells.
3. Re-ring — close all residents, re-set each circular cell with its real formula, one set per cell, non-resident.

Acceptance. get /Debt/C7 --json | jq '.data.results[0].format.cachedValue' returns non-zero non-null. If a cell still deadlocks, leave =0 + classic comment "circular; recalculates in Excel on F9", flag at delivery. Never paper over with iterateCount=1000.

Do NOT use iterate as a band-aid for #REF! / divergent values. Raising iterateCount to 1000 hides the bug and ships a plausibly-wrong value; validate does not catch it. Break the loop algebraically (e.g. interest on opening balance only, not average).

Verify convergence. Read the loop cell, bump a driving assumption and back, re-read — values must match:

V1=$(officecli get "$FILE" /Debt/C9 --json | jq '.data.results[0].format.cachedValue')
officecli set "$FILE" /Assumptions/B31 --prop value=0.085
officecli set "$FILE" /Assumptions/B31 --prop value=0.0845
V2=$(officecli get "$FILE" /Debt/C9 --json | jq '.data.results[0].format.cachedValue')
[ "$V1" = "$V2" ] && echo "Iterate converged" || echo "WARN: drift V1=$V1 V2=$V2 — tighten iterateDelta or check algebra"

Audit & Delivery Gate

Assume there are problems. First build is almost never correct. Run every gate below; every check must print its success line. validate passing is not delivery — the model can pass schema and still be wrong by a factor of 10.

Gates 1–3 — inherited from xlsx v2 verbatim

→ see xlsx v2 §QA minimum cycle (Gates 1–3 cover view issues, error-cell query, validate after close). Run them first, exactly as written in xlsx v2. No financial-model-specific tweaks.

Gate 4 — statement integrity (3-statement & LBO)

Balance-check and cash-reconciliation rows produced by Recipe A / C must show OK / BALANCED every period. query the check rows and refuse on any IMBALANCED / CF !=:

BS_FAIL=$(officecli query "$FILE" 'cell:contains("IMBALANCED")' --json | jq '.data.results | length')
CF_FAIL=$(officecli query "$FILE" 'cell:contains("CF !=")' --json | jq '.data.results | length')
SU_FAIL=$(officecli query "$FILE" 'cell:contains("S&U IMBALANCE")' --json | jq '.data.results | length')
if [ "$BS_FAIL" -eq 0 ] && [ "$CF_FAIL" -eq 0 ] && [

…(truncated)