Financial Model Skill
Build formula-driven, multi-sheet financial models from scratch in Excel. Every number on every statement sheet is a formula referencing the Assumptions sheet. Output is a single .xlsx file with interconnected sheets -- Income Statement, Balance Sheet, Cash Flow Statement, and optional valuation or scenario analysis sheets.
BEFORE YOU START (CRITICAL)
Every time before using officecli, run this check:
CODEBLOCK0
Use When
- - User provides assumptions and asks for financial projections or a financial model
- User asks for an "income statement", "balance sheet", or "cash flow statement"
- User asks for DCF valuation, WACC calculation, or discounted cash flow analysis
- User asks for a cap table, waterfall analysis, or fundraising model
- User asks for scenario analysis, sensitivity table, debt schedule, or break-even model
Do NOT Use When
| User Request | Correct Skill |
|---|
| CSV data to dashboard / charts | officecli-data-dashboard |
| Edit/modify an existing .xlsx |
officecli-xlsx (editing.md) |
| KPI dashboard or metrics summary | officecli-data-dashboard |
| 1-2 sheet visualization from existing data | officecli-data-dashboard |
| Word document or PowerPoint | officecli-docx / officecli-pitch-deck |
What This Skill Produces
A single .xlsx file with 4-10 interconnected sheets:
| Sheet Type | Purpose | Key Characteristic |
|---|
| Assumptions | All hardcoded inputs in one place | Blue font (0000FF) on every input cell |
| Income Statement |
Revenue through Net Income | All rows are formulas referencing Assumptions |
| Balance Sheet | Assets, Liabilities, Equity | Must balance every period; includes check row |
| Cash Flow Statement | Operating, Investing, Financing | Ending Cash must equal BS Cash |
| DCF / Valuation | WACC, FCF, Terminal Value, Equity Value | Named ranges for key inputs |
| Sensitivity Table | 2-variable grid of implied values | Each cell is a self-contained formula |
| Scenarios | Dropdown-driven Base/Bull/Bear | IF/INDEX formulas reference dropdown |
| Error Checks | Balance, cash reconciliation, ISERROR scan | "ALL CLEAR" or "ERRORS FOUND" summary |
| Dashboard / Charts | Visual summary of model outputs | Charts use cell range references |
ALL values on statement sheets are formulas. The only hardcoded numbers are on the Assumptions sheet.
Core Concepts
- - Assumptions-First Architecture -- ALL hardcoded inputs go on the Assumptions sheet. Every other sheet references Assumptions. Changing one assumption recalculates the entire model.
- Financial Color Coding --
font.color=0000FF (blue) for inputs, font.color=000000 (black) for formulas, font.color=008000 (green) for cross-sheet references. Non-negotiable convention. - Formula Chain Integrity -- Every derived value traces back to the Assumptions sheet through an unbroken chain of formula references.
- Error Checking -- Balance checks (Assets = Liabilities + Equity), cash reconciliation (CF ending cash = BS cash), and ISERROR scans on every sheet.
- Batch-First Workflow -- Use heredoc batch for ALL multi-cell operations, especially cross-sheet formulas. Verify after each batch.
Workflow Overview
Phase 1: Understand -- Identify model type (3-statement, DCF, cap table, scenario). Determine which sheets are needed and the formula dependency chain.
Phase 2: Plan -- Map sheet structure, column layout (labels + year columns), and cross-sheet formula dependencies. Define the build order.
Phase 3: Build -- Follow the mandatory 10-step build sequence in creating.md Section A.7. Build in layers: structure, assumptions, formulas (IS then BS then CF), formatting, charts, protection, raw-set, validate.
Phase 4: QA -- Run the QA checklist: validate, formula error scan, cross-sheet verification, balance check, cash reconciliation, chart data check.
Phase 5: Deliver -- Deliver the .xlsx file. Note that formulas recalculate on open (fullCalcOnLoad is set).
Quick Reference: Key Warnings
| Warning | Detail |
|---|
Cross-sheet ! escaping | Use heredoc batch for ALL cross-sheet formulas. Verify with officecli get after each batch. |
| Batch size limit |
8-12 operations per batch, non-resident mode. Larger batches have ~33% failure rate. |
| Batch JSON values | ALL values must be strings:
"true" not
true,
"24" not
24 |
| fullCalcOnLoad + iterate | MANDATORY. Always use
//x:definedNames --action insertafter (financial models always have named ranges) |
| Blue inputs / black formulas |
font.color=0000FF on Assumptions inputs,
font.color=000000 on all formula cells |
| Balance sheet must balance | Explicit check formula:
=TotalAssets - TotalLiabilities - TotalEquity must equal 0 |
| Cash reconciliation | CF ending cash must equal BS cash for every period |
| No Excel Data Tables | Sensitivity tables must be manual formula grids. Each cell is an explicit self-contained formula. |
| Number format
$ quoting | Use heredoc batch or single quotes to prevent shell expansion of
$ |
| Named ranges required | Define for all key assumptions (WACC, growth rates, tax rate). Required for auditability. |
| Column widths | No auto-fit. Set explicitly: labels=22-28, numbers=14-18, year headers=12-14 |
| formulacf no font.bold | Use
fill +
font.color only.
font.bold causes validation errors. |
| raw-set ordering | activeTab and calcPr MUST be the absolute last commands |
| BS Cash = CF Ending Cash | BS Cash ALWAYS equals
=Cash Flow!B19, including Year 1. Never use cash-as-plug or reference Assumptions directly. |
| Chart title
$ in shell | Use heredoc batch for chart titles containing
$ to prevent shell expansion. |
Known Issues
| Issue | Workaround |
|---|
| INLINECODE25 escaping in cross-sheet formulas | Always use heredoc batch. Verify with officecli get. |
| Batch failure at scale |
Keep batches to 8-12 ops. Non-resident mode. Retry individually on failure. |
| Cannot rename sheets | Plan sheet names upfront before creation. |
| Sensitivity tables are manual | Each cell needs an explicit formula. No Excel DATA TABLE support. |
| Chart series fixed at creation | Cannot add series later. Plan all series before
add. |
| Formula cached values blank |
view text shows blank for formula cells. This is normal. Set fullCalcOnLoad. |
| Waterfall chart totals | Cannot mark bars as totals programmatically. Use color convention. |
| Circular references | Use
<calcPr iterate="1" ...>. Design model to avoid unnecessary circularity. |
| Chart title
$ stripping | Shell expands
$ in
--prop title. Use heredoc batch for chart titles with
$, or omit
$ from titles. |
Full Guide
Read creating.md and follow it step by step. It contains setup conventions, core financial statement patterns, advanced patterns (DCF, sensitivity, scenarios), chart recipes, QA checklist, and known issues with workarounds.
References
财务模型技能
在Excel中从头构建基于公式、多工作表联动的财务模型。每张报表上的每个数字都是引用假设工作表的公式。输出为单个.xlsx文件，包含相互关联的工作表——利润表、资产负债表、现金流量表，以及可选的价值评估或情景分析工作表。
开始前（关键）
每次使用officecli前，请执行此检查：
bash
if ! command -v officecli &> /dev/null; then
echo 正在安装officecli...
curl -fsSL https://raw.githubusercontent.com/iOfficeAI/OfficeCli/main/install.sh -o /tmp/officecliinstall.sh && bash /tmp/officecliinstall.sh && rm -f /tmp/officecli_install.sh
# Windows: irm https://raw.githubusercontent.com/iOfficeAI/OfficeCli/main/install.ps1 -OutFile $env:TEMP\officecliinstall.ps1; & $env:TEMP\officecliinstall.ps1
else
CURRENT=$(officecli --version 2>&1 | grep -oE [0-9]+\.[0-9]+\.[0-9]+ | head -1)
LATEST=$(curl -fsSL https://api.github.com/repos/iOfficeAI/OfficeCLI/releases/latest | grep tag_name | sed -E s/.v?([0-9.]+)./\1/)
if [ $CURRENT != $LATEST ]; then
echo 正在升级officecli $CURRENT -> $LATEST...
curl -fsSL https://raw.githubusercontent.com/iOfficeAI/OfficeCli/main/install.sh -o /tmp/officecliinstall.sh && bash /tmp/officecliinstall.sh && rm -f /tmp/officecli_install.sh
else
echo officecli $CURRENT 已是最新版本
fi
fi
officecli --version
使用场景
- - 用户提供假设条件并要求生成财务预测或财务模型
- 用户要求生成利润表、资产负债表或现金流量表
- 用户要求进行DCF估值、WACC计算或折现现金流分析
- 用户要求生成股权结构表、瀑布分析或融资模型
- 用户要求进行情景分析、敏感性分析表、债务偿还计划或盈亏平衡模型
不使用场景
| 用户请求 | 正确技能 |
|---|
| CSV数据生成仪表盘/图表 | officecli-data-dashboard |
| 编辑/修改现有.xlsx文件 |
officecli-xlsx (editing.md) |
| KPI仪表盘或指标汇总 | officecli-data-dashboard |
| 基于现有数据生成1-2张可视化图表 | officecli-data-dashboard |
| Word文档或PowerPoint | officecli-docx / officecli-pitch-deck |
本技能产出
单个.xlsx文件，包含4-10张相互关联的工作表：
| 工作表类型 | 用途 | 关键特征 |
|---|
| 假设条件 | 所有硬编码输入集中在一处 | 每个输入单元格使用蓝色字体（0000FF） |
| 利润表 |
从收入到净利润 | 所有行均为引用假设工作表的公式 |
| 资产负债表 | 资产、负债、所有者权益 | 每期必须平衡；包含校验行 |
| 现金流量表 | 经营活动、投资活动、筹资活动 | 期末现金必须等于资产负债表现金 |
| DCF/估值 | WACC、自由现金流、终值、股权价值 | 关键输入使用命名区域 |
| 敏感性分析表 | 双变量隐含值网格 | 每个单元格为独立公式 |
| 情景分析 | 下拉菜单驱动的基础/乐观/悲观 | IF/INDEX公式引用下拉菜单 |
| 错误检查 | 平衡校验、现金对账、ISERROR扫描 | 全部通过或发现错误汇总 |
| 仪表盘/图表 | 模型输出的可视化汇总 | 图表使用单元格区域引用 |
报表工作表上的所有值均为公式。唯一硬编码的数字位于假设工作表上。
核心概念
- - 假设优先架构 -- 所有硬编码输入均放在假设工作表上。其他每个工作表都引用假设工作表。更改一个假设即可重新计算整个模型。
- 财务颜色编码 -- font.color=0000FF（蓝色）用于输入，font.color=000000（黑色）用于公式，font.color=008000（绿色）用于跨工作表引用。此为不可协商的约定。
- 公式链完整性 -- 每个衍生值都通过不间断的公式引用链追溯到假设工作表。
- 错误检查 -- 平衡校验（资产=负债+所有者权益）、现金对账（现金流量表期末现金=资产负债表现金）以及每个工作表上的ISERROR扫描。
- 批量优先工作流 -- 对所有多单元格操作使用heredoc批量处理，特别是跨工作表公式。每次批量处理后进行验证。
工作流程概览
阶段1：理解 -- 确定模型类型（三表联动、DCF、股权结构表、情景分析）。确定需要哪些工作表以及公式依赖链。
阶段2：规划 -- 映射工作表结构、列布局（标签+年份列）以及跨工作表公式依赖关系。定义构建顺序。
阶段3：构建 -- 按照creating.md第A.7节中规定的10步构建顺序执行。分层构建：结构、假设、公式（先利润表，再资产负债表，后现金流量表）、格式、图表、保护、原始设置、验证。
阶段4：质量保证 -- 执行质量保证检查清单：验证、公式错误扫描、跨工作表验证、平衡校验、现金对账、图表数据检查。
阶段5：交付 -- 交付.xlsx文件。注意公式在打开时重新计算（已设置fullCalcOnLoad）。
快速参考：关键警告
| 警告 | 详情 |
|---|
| 跨工作表!转义 | 对所有跨工作表公式使用heredoc批量处理。每次批量处理后使用officecli get验证。 |
| 批量大小限制 |
每批8-12个操作，非驻留模式。较大批次的失败率约为33%。 |
| 批量JSON值 | 所有值必须为字符串：true而非true，24而非24 |
| fullCalcOnLoad + 迭代 | 强制性要求。始终使用//x:definedNames --action insertafter（财务模型始终有命名区域） |
| 蓝色输入/黑色公式 | 假设工作表输入使用font.color=0000FF，所有公式单元格使用font.color=000000 |
| 资产负债表必须平衡 | 显式校验公式：=TotalAssets - TotalLiabilities - TotalEquity必须等于0 |
| 现金对账 | 每期现金流量表期末现金必须等于资产负债表现金 |
| 无Excel数据表 | 敏感性分析表必须为手动公式网格。每个单元格为显式独立公式。 |
| 数字格式$引用 | 使用heredoc批量处理或单引号防止shell对$的扩展 |
| 需要命名区域 | 为所有关键假设（WACC、增长率、税率）定义命名区域。审计性要求。 |
| 列宽 | 不自动调整。显式设置：标签=22-28，数字=14-18，年份标题=12-14 |
| formulacf无font.bold | 仅使用fill+font.color。font.bold会导致验证错误。 |
| raw-set排序 | activeTab和calcPr必须是绝对最后的命令 |
| 资产负债表现金=现金流量表期末现金 | 资产负债表现金始终等于=现金流量表!B19，包括第1年。切勿使用现金作为平衡项或直接引用假设工作表。 |
| 图表标题中的$ | 对包含$的图表标题使用heredoc批量处理，防止shell扩展。 |
已知问题
| 问题 | 解决方法 |
|---|
| 跨工作表公式中的!转义 | 始终使用heredoc批量处理。使用officecli get验证。 |
| 批量处理大规模失败 |
保持每批8-12个操作。非驻留模式。失败时单独重试。 |
| 无法重命名工作表 | 在创建工作表前预先规划工作表名称。 |
| 敏感性分析表为手动 | 每个单元格需要显式公式。不支持Excel数据表功能。 |
| 图表系列在创建时固定 | 之后无法添加系列。在add前规划所有系列。 |
| 公式缓存值为空 | view text显示公式单元格为空。此为正常现象。设置fullCalcOnLoad。 |
| 瀑布图合计 | 无法以编程方式将条形标记为合计。使用颜色约定。 |
| 循环引用 | 使用
。设计模型以避免不必要的循环。 |
| 图表标题$被剥离 | Shell在--prop title中扩展$。对包含$的图表标题使用heredoc批量处理，或从标题中省略
