officecli-financial-model 精选
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.
安装方式
cp -r skills/officecli-financial-model ~/.claude/skills/officecli-financial-model把该 skill 目录放进客户端 skills 目录。不同客户端的目录可能不同,Claude Code 默认是 ~/.claude/skills/
https://raw.githubusercontent.com/iOfficeAI/OfficeCLI/fb98e593612dee2cec36847666671dba502e60aa/skills/officecli-financial-model/SKILL.md下载后另存为 ~/.claude/skills/officecli-financial-model/SKILL.md。链接锁定采集时的 commit,内容与本站展示一致。
整仓一次性获取:git clone --depth 1 https://github.com/iOfficeAI/OfficeCLI.git
依赖与权限
- 无需 API Key
- 需要联网访问外部服务
- 依赖外部命令:
python3curljqgo - 以上为按正文与 frontmatter 自动推断的信号,仅供参考;安装前建议先阅读原文。
SKILL.md 正文
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=B51.05. - Statements balance every period.
Assets − Liab − Equity = 0,CF.EndingCash = BS.Cash. Gate 4 fails onIMBALANCED. - Hardcodes audited. Calc sheets carry zero hardcoded numbers; Gate 6 counts.
- Sensitivity / scenario is first-class. 2-axis grid, dropdown
INDEX/MATCHswitch, 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 withiterate. - 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
seton 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.nullmeans 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; =EBTTaxRate not =EBTAssumptions!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
质量与来源
- 所属仓库
- iOfficeAI/OfficeCLI
- 仓库星标
- 3.2 万
- 最近更新
- 今天
- 采集时间
- 今天
- commit
- fb98e593612d
- 内容指纹
- sha256:65280bce9537ff3
- 文件路径
- skills/officecli-financial-model/SKILL.md