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 pinned to installed version — when this skill and help disagree, help wins. Every --prop X= below was verified against officecli help xlsx on v1.0.63.

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. NPV / IRR / XNPV caching 0 ships a wrong number to non-recalculating readers. 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):

# Calc zone: zero numeric hardcodes allowed. NOTE: `:not(:has(formula))` pseudo doesn't filter on v1.0.63+ — filter via jq on .format.formula == null.
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 ''

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 .format.cachedValue returns non-zero non-null. null ≠ 0: null means Excel will compute on open (OK for delivery); 0 is a cached lie. If 0 persists: close residents, re-set; still 0 → 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 ` (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.

```bash
cat /dev/null || echo 99999)
python3 -c "import sys; sys.exit(0 if float('$DELTA') `. 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:

```bash
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  --prop text='...'`. The **`comment` key is NOT a valid prop on `set cell`** (not in `officecli help xlsx cell` on v1.0.63) — 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.**

```bash
officecli get "$FILE" /S&U/B12 --json | jq '.data.value // .data.cachedValue'   # must say BALANCED
officecli get "$FILE" /Returns/B3 --json | jq .format.cachedValue                # MOIC, expect 2.0x-4.0x typical
officecli get "$FILE" /Returns/B4 --json | jq .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

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: zhu1090093659
• Source: zhu1090093659/deepseek-pp
• License: Apache-2.0
• Homepage: https://chromewebstore.google.com/detail/deepseek++/kdmpkkahkhdmdhfkdihkopikgcocbpbf?hl=zh-CN&authuser=0

Install and usage instructions live in the source repository linked above.