fin-modeling
Compare original and translation side by side
🇺🇸
Original
English🇨🇳
Translation
ChineseThree-Statement Financial Modeling
三表财务建模
Framework
框架
IRON LAW: The Three Statements Must Balance
Assets = Liabilities + Equity (Balance Sheet identity)
Net Income flows from IS to BS (retained earnings)
Cash flow bridges IS and BS through working capital and capex
If your model doesn't balance, there's an error. Check the cash line on
the BS against the ending cash on the CF statement. They MUST match.IRON LAW: The Three Statements Must Balance
Assets = Liabilities + Equity (Balance Sheet identity)
Net Income flows from IS to BS (retained earnings)
Cash flow bridges IS and BS through working capital and capex
If your model doesn't balance, there's an error. Check the cash line on
the BS against the ending cash on the CF statement. They MUST match.Model Architecture
模型架构
[Assumptions Page] → drives everything
↓
[Income Statement] → Revenue, costs, taxes → Net Income
↓
[Balance Sheet] → Assets, liabilities, equity → must balance
↓
[Cash Flow Statement] → Start cash + Operating + Investing + Financing = End cash
↓
[Outputs: DCF, Returns, Scenarios][Assumptions Page] → drives everything
↓
[Income Statement] → Revenue, costs, taxes → Net Income
↓
[Balance Sheet] → Assets, liabilities, equity → must balance
↓
[Cash Flow Statement] → Start cash + Operating + Investing + Financing = End cash
↓
[Outputs: DCF, Returns, Scenarios]Revenue Forecasting Methods
收入预测方法
| Method | How | Best For |
|---|---|---|
| Top-down | Market size × market share × price | New markets, macro-driven |
| Bottom-up | Units × price, or customers × ARPU | Established products, SaaS |
| Run-rate | Current monthly × 12, adjusted for growth | Near-term projections |
| Cohort-based | New cohort revenue + existing cohort retention | Subscription businesses |
| 方法 | 操作方式 | 适用场景 |
|---|---|---|
| Top-down | 市场规模 × 市场份额 × 价格 | 新市场、宏观驱动型业务 |
| Bottom-up | 销量 × 单价，或客户数 × ARPU | 成熟产品、SaaS业务 |
| Run-rate | 当前月度数据 ×12，根据增长情况调整 | 短期预测 |
| Cohort-based | 新客户群体收入 + 现有客户群体留存收入 | 订阅制业务 |
Key Assumptions to Document
需记录的关键假设
| Category | Assumptions |
|---|---|
| Revenue | Growth rate, pricing, volume, churn (if subscription) |
| COGS | Gross margin trajectory, input cost inflation |
| OpEx | Headcount plan, salary inflation, marketing as % of revenue |
| Working Capital | DSO (days sales outstanding), DPO (days payable), DIO (days inventory) |
| CapEx | Capital expenditure as % of revenue or specific projects |
| Tax | Effective tax rate, tax loss carryforwards |
| Financing | Debt schedule, interest rates, equity raises |
| 类别 | 假设内容 |
|---|---|
| Revenue | 增长率、定价、销量、客户流失率（若为订阅制） |
| COGS | 毛利率走势、投入成本通胀率 |
| OpEx | 人员规划、薪资通胀率、营销费用占收入比例 |
| Working Capital | DSO（应收账款周转天数）、DPO（应付账款周转天数）、DIO（存货周转天数） |
| CapEx | 资本支出占收入比例或特定项目支出 |
| Tax | 有效税率、税损结转 |
| Financing | 债务计划、利率、股权融资 |
Building Steps
搭建步骤
Phase 1: Assumptions
- Document all assumptions on a dedicated page
- Color code: blue = input, black = formula, green = linked from another sheet
- Each assumption must have a source or rationale
Phase 2: Income Statement
4. Build revenue line from assumptions
5. COGS and gross profit
6. Operating expenses by category
7. EBITDA, depreciation, EBIT
8. Interest, taxes, net income
Phase 3: Balance Sheet
9. Working capital items from IS drivers (DSO × Revenue/365, etc.)
10. Fixed assets: prior period + capex - depreciation
11. Debt schedule: prior period + new borrowing - repayment
12. Equity: prior period + net income - dividends + equity raises
13. CHECK: Assets = Liabilities + Equity
Phase 4: Cash Flow
14. Start with net income
15. Add back non-cash items (depreciation, amortization)
16. Working capital changes (from BS period-over-period)
17. CapEx (investing)
18. Debt and equity changes (financing)
19. CHECK: Ending cash = BS cash line
Phase 5: Scenarios
20. Base case (most likely assumptions)
21. Bull case (optimistic — higher growth, better margins)
22. Bear case (pessimistic — lower growth, margin pressure)
阶段1：假设设定
- 在单独页面记录所有假设
- 颜色编码：蓝色=输入项，黑色=公式，绿色=链接自其他工作表
- 每个假设都必须有来源或依据
阶段2：Income Statement
4. 根据假设构建收入项
5. 计算COGS和毛利润
6. 按类别划分运营费用
7. 计算EBITDA、折旧、EBIT
8. 计算利息、税费、净利润
阶段3：Balance Sheet
9. 根据Income Statement驱动因素计算营运资金项目（如DSO × 收入/365等）
10. 固定资产：上期余额 + 资本支出 - 折旧
11. 债务计划：上期余额 + 新增借款 - 还款
12. 权益：上期余额 + 净利润 - 股息 + 股权融资
13. 检查：资产 = 负债 + 权益
阶段4：Cash Flow Statement
14. 以净利润为起点
15. 加回非现金项目（折旧、摊销）
16. 营运资金变动（来自资产负债表期间差额）
17. 资本支出（投资活动）
18. 债务与权益变动（融资活动）
19. 检查：期末现金 = 资产负债表中的现金项目
阶段5：情景分析
20. 基准情景（最可能的假设）
21. 乐观情景（增长更高、利润率更好）
22. 悲观情景（增长更低、利润率承压）
Output Format
输出格式
markdown
undefinedmarkdown
undefinedFinancial Model: {Company} — {Projection Period}
Financial Model: {Company} — {Projection Period}
Key Assumptions
Key Assumptions
| Assumption | Y1 | Y2 | Y3 | Y4 | Y5 | Source |
|---|---|---|---|---|---|---|
| Revenue growth | {%} | {%} | {%} | {%} | {%} | {basis} |
| Gross margin | {%} | ... | ... | ... | ... | ... |
| OpEx growth | {%} | ... | ... | ... | ... | ... |
| Assumption | Y1 | Y2 | Y3 | Y4 | Y5 | Source |
|---|---|---|---|---|---|---|
| Revenue growth | {%} | {%} | {%} | {%} | {%} | {basis} |
| Gross margin | {%} | ... | ... | ... | ... | ... |
| OpEx growth | {%} | ... | ... | ... | ... | ... |
Projected Income Statement
Projected Income Statement
| Y1 | Y2 | Y3 | Y4 | Y5 | |
|---|---|---|---|---|---|
| Revenue | ${X} | ... | ... | ... | ... |
| Gross Profit | ${X} | ... | ... | ... | ... |
| EBITDA | ${X} | ... | ... | ... | ... |
| Net Income | ${X} | ... | ... | ... | ... |
| Y1 | Y2 | Y3 | Y4 | Y5 | |
|---|---|---|---|---|---|
| Revenue | ${X} | ... | ... | ... | ... |
| Gross Profit | ${X} | ... | ... | ... | ... |
| EBITDA | ${X} | ... | ... | ... | ... |
| Net Income | ${X} | ... | ... | ... | ... |
Scenario Comparison
Scenario Comparison
| Metric (Y5) | Bear | Base | Bull |
|---|---|---|---|
| Revenue | ${X} | ${X} | ${X} |
| Net Income | ${X} | ${X} | ${X} |
| FCF | ${X} | ${X} | ${X} |
| Metric (Y5) | Bear | Base | Bull |
|---|---|---|---|
| Revenue | ${X} | ${X} | ${X} |
| Net Income | ${X} | ${X} | ${X} |
| FCF | ${X} | ${X} | ${X} |
Balance Check
Balance Check
- BS balances: ✓/✗
- CF ending cash = BS cash: ✓/✗
undefined- BS balances: ✓/✗
- CF ending cash = BS cash: ✓/✗
undefinedGotchas
注意事项
- Revenue is the most sensitive assumption: A 2% difference in growth rate compounds enormously over 5 years. Always present a range, not a point estimate.
- Working capital is often forgotten: Fast-growing companies consume cash in working capital (building inventory, extending credit). A profitable company can run out of cash if working capital isn't modeled.
- Circular references with interest: Interest expense depends on debt balance, which depends on cash (which may require more debt). Break the circularity with an iterative calculation or prior-period debt balance.
- Granularity should match certainty: Model Year 1 monthly, Year 2 quarterly, Years 3-5 annually. Detailed monthly projections for Year 5 are false precision.
- Assumptions page is the most important page: Nobody should need to dig into formulas to understand what drives your model. All assumptions visible, documented, and changeable in one place.
- 收入是最敏感的假设：增长率2%的差异在5年内会产生巨大的复利效应。始终提供范围值，而非单点估计。
- 营运资金常被忽略：快速增长的公司会在营运资金上消耗现金（如备货、延长信用期）。若未对营运资金建模，盈利的公司也可能出现现金短缺。
- 利息的循环引用：利息费用取决于债务余额，而债务余额又取决于现金（可能需要更多债务）。使用迭代计算或上期债务余额来打破循环。
- 颗粒度应与确定性匹配：第1年按月度建模，第2年按季度，第3-5年按年度。对第5年做详细月度预测属于虚假精准。
- 假设页面是最重要的页面：任何人无需深入查看公式即可理解模型的驱动因素。所有假设都应在同一位置可见、可记录、可修改。
References
参考资料
- For DCF valuation built on the three-statement model, see the biz-dcf skill
- For spreadsheet best practices, see
references/modeling-best-practices.md
- 基于三表模型的DCF估值，请参考biz-dcf技能
- 电子表格最佳实践，请查看
references/modeling-best-practices.md