---
name: OfficeCLI Financial Model
slug: officecli-financial-model
category: Data
description: OfficeCLI Financial Model builds formula-driven financial models in Excel, including 3-statement models, DCF valuations, LBOs, SaaS unit economics, and sensitivity/scenario analysis. Use it when a request mentions financial model, DCF, WACC, LBO, ARR, or similar and the output should be a single .xlsx.
github: "https://github.com/iOfficeAI/OfficeCLI/tree/main/skills/officecli-financial-model"
language: "C#"
stars: 28226
forks: 1910
install: "npx degit https://github.com/iOfficeAI/OfficeCLI/tree/main/skills/officecli-financial-model ~/.claude/skills/officecli-financial-model"
installs_to: ~/.claude/skills/officecli-financial-model
source_path: skills/officecli-financial-model/SKILL.md
collection_size: 12
category_size: 668
collection_url: "https://dirskills.com/collections/iOfficeAI/OfficeCLI"
added: 2026-08-14T07:11:51.364Z
last_synced: 2026-08-14T07:11:51.364Z
canonical_url: "https://dirskills.com/skills/officecli-financial-model"
---

# OfficeCLI Financial Model

OfficeCLI Financial Model builds formula-driven financial models in Excel, including 3-statement models, DCF valuations, LBOs, SaaS unit economics, and sensitivity/scenario analysis. Use it when a request mentions financial model, DCF, WACC, LBO, ARR, or similar and the output should be a single .xlsx.

**Install:**

```bash
npx degit https://github.com/iOfficeAI/OfficeCLI/tree/main/skills/officecli-financial-model ~/.claude/skills/officecli-financial-model
```

## README

# 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.

**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.

```bash
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.

```bash
# (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):**

```bash
# 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`:

```bash
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:

```bash
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);\"-\""}},