> ## Documentation Index
> Fetch the complete documentation index at: https://financecontext.com/llms.txt
> Use this file to discover all available pages before exploring further.
# 3-Statement Model Skill
> Learn the three financial statements -- Income Statement, Balance Sheet, and Cash Flow Statement -- how they link together, why modeling them matters, and how to build integrated models.
# 3-Statement Model Skill
## What is a 3-Statement Model?
Every public company reports three core financial statements: the **Income Statement** (how much the company earned), the **Balance Sheet** (what the company owns and owes), and the **Cash Flow Statement** (how cash moved in and out). A 3-statement model is an integrated Excel framework that connects all three statements so that changing one assumption -- like revenue growth -- automatically flows through to every other statement.
Think of the three statements as three lenses on the same business:
* The **Income Statement** (also called the P\&L -- Profit and Loss) shows the company's economic performance over a period. Did the company make money? How much revenue came in, and how much did it cost to generate that revenue? The bottom line is **Net Income** -- what the company earned after all expenses, interest, and taxes.
* The **Balance Sheet** is a snapshot of what the company owns (**Assets**) and what it owes (**Liabilities**) at a specific point in time. The difference is **Shareholders' Equity** -- the residual value belonging to owners. The fundamental accounting equation is: Assets = Liabilities + Equity. This equation must always hold true.
* The **Cash Flow Statement** reconciles the gap between earnings and cash. A company can report \$100M in net income but actually have less cash at the end of the year than the beginning -- because it invested in equipment, paid down debt, or had customers who have not paid yet. The Cash Flow Statement explains where the cash actually went.
**Quick numeric example:** Suppose RetailCo has Year 1 net income of $50M but also spent $30M on new equipment (CapEx), had customers owe an additional $15M at year end (increase in Accounts Receivable), and recognized $20M of depreciation (non-cash expense). The Cash Flow Statement reconciles this: $50M net income + $20M D\&A add-back - $15M increase in AR - $30M CapEx = \*\*$25M** of actual cash generated. The Income Statement says $50M in profit; the Cash Flow Statement says only \$25M actually showed up in the bank account.
The power of a 3-statement model is that these three statements are mathematically linked. Net Income from the IS feeds into Retained Earnings on the BS and starts the Cash Flow Statement. Depreciation from the IS adds back on the CF (because it is a non-cash expense) and reduces PP\&E on the BS. Debt payments on the CF reduce the debt balance on the BS. When you change revenue growth, the model automatically recalculates expenses, taxes, net income, retained earnings, working capital, cash flow, and ending cash.
## Detailed Worked Example
Let us build a simplified 3-statement model for **RetailCo**, a fictional retail company, for one projection year.
**Historical Data (Year 0 Actuals):**
* Revenue: \$1,000M
* COGS: \$600M (60% of revenue)
* SG\&A: \$200M (20% of revenue)
* D\&A: \$50M (5% of revenue)
* Interest Expense: \$20M
* Tax Rate: 25%
* Beginning PP\&E: \$400M
* Beginning Total Debt: \$300M
* Beginning Retained Earnings: \$250M
* Beginning Cash: \$80M
* Beginning AR: $100M; Inventory: $120M; AP: \$80M
**Projection Assumptions (Year 1):**
* Revenue growth: 8%
* COGS: 59% of revenue (margin improvement)
* SG\&A: 19% of revenue (operating leverage)
* D\&A: 5% of revenue
* CapEx: 7% of revenue
* DSO stays at 36.5 days; DIO stays at 73 days; DPO stays at 48.7 days
* Debt repayment: \$30M mandatory amortization
* No new equity issuance; No dividends
```
Revenue = $1,000M x 1.08 = $1,080.0M
(-) COGS = $1,080M x 59% = $637.2M
= Gross Profit = $442.8M
Gross Margin = 41.0%
(-) SG&A = $1,080M x 19% = $205.2M
(-) D&A = $1,080M x 5% = $54.0M
= EBIT = $183.6M
EBIT Margin = 17.0%
(-) Interest Expense (on beginning debt $300M) = $20.0M
= EBT (Earnings Before Tax) = $163.6M
(-) Taxes = $163.6M x 25% = $40.9M
= Net Income = $122.7M
Net Margin = 11.4%
```
Working capital items are projected using days outstanding ratios:
```
Accounts Receivable = Revenue / 365 x DSO
= $1,080M / 365 x 36.5 = $108.0M
Change in AR = $108.0M - $100.0M = +$8.0M (use of cash)
Inventory = COGS / 365 x DIO
= $637.2M / 365 x 73 = $127.4M
Change in Inventory = $127.4M - $120.0M = +$7.4M (use of cash)
Accounts Payable = COGS / 365 x DPO
= $637.2M / 365 x 48.7 = $85.0M
Change in AP = $85.0M - $80.0M = +$5.0M (source of cash)
Total Change in NWC = -$8.0M - $7.4M + $5.0M = -$10.4M (net use of cash)
```
**Sign convention check:** Increase in AR and Inventory are uses of cash (negative on CF). Increase in AP is a source of cash (positive on CF). Growing companies typically consume working capital.
```
CASH FLOW FROM OPERATIONS (CFO):
Net Income = $122.7M
(+) D&A (non-cash add-back) = $54.0M
(-) Increase in Accounts Receivable = ($8.0M)
(-) Increase in Inventory = ($7.4M)
(+) Increase in Accounts Payable = $5.0M
= Cash from Operations = $166.3M
CASH FLOW FROM INVESTING (CFI):
(-) Capital Expenditures (7% of revenue) = ($75.6M)
= Cash from Investing = ($75.6M)
CASH FLOW FROM FINANCING (CFF):
(-) Debt Repayment = ($30.0M)
= Cash from Financing = ($30.0M)
Net Change in Cash = $166.3M + ($75.6M) + ($30.0M) = $60.7M
Beginning Cash = $80.0M
Ending Cash = $140.7M
```
```
ASSETS:
Cash = $80.0M + $60.7M = $140.7M
Accounts Receivable = $100.0M + $8.0M = $108.0M
Inventory = $120.0M + $7.4M = $127.4M
Total Current Assets = $376.1M
PP&E = $400.0M + $75.6M CapEx - $54.0M D&A = $421.6M
Total Assets = $797.7M
LIABILITIES:
Accounts Payable = $80.0M + $5.0M = $85.0M
Total Current Liabilities = $85.0M
Long-Term Debt = $300.0M - $30.0M = $270.0M
Total Liabilities = $355.0M
EQUITY:
Common Stock/APIC = $120.0M
Retained Earnings = $250.0M + $122.7M NI = $372.7M
Total Equity = $492.7M
Total Liabilities + Equity = $847.7M
```
**WAIT** -- Total Assets ($797.7M) does not equal Total Liabilities + Equity ($847.7M). There is a \$50M gap. Let me trace the error...
The issue is that I set Common Stock at $120.0M but need to verify: Beginning Total Equity = Beginning Assets - Beginning Liabilities. Beginning Assets = $80 + $100 + $120 + $400 = $700M. Beginning Liabilities = $80 + $300 = $380M. Beginning Equity = $320M, so Common Stock = $320M - $250M RE = \$70M.
**Corrected:**
```
Common Stock/APIC = $70.0M
Retained Earnings = $372.7M
Total Equity = $442.7M
Total Liabilities + Equity = $355.0M + $442.7M = $797.7M
```
**Balance check:** Assets ($797.7M) = Liabilities + Equity ($797.7M). The balance sheet balances.
| Check | Formula | Result |
| ------------ | ----------------------------------------- | ----------------------------------- |
| BS Balance | Assets - L - E | $797.7M - $797.7M = \$0 |
| Cash Tie-Out | CF Ending Cash - BS Cash | $140.7M - $140.7M = \$0 |
| NI Link | IS Net Income - CF Starting NI | $122.7M - $122.7M = \$0 |
| RE Roll | Prior RE + NI - Dividends - Ending RE | $250 + $122.7 - $0 - $372.7 = \$0 |
| PP\&E Roll | Prior PP\&E + CapEx - D\&A - Ending PP\&E | $400 + $75.6 - $54.0 - $421.6 = \$0 |
| Debt Roll | Prior Debt - Repayment - Ending Debt | $300 - $30 - $270 = $0 |
All six integrity checks pass. The model is internally consistent.
## Why It Matters
The 3-statement model is the foundation upon which all other financial analysis is built:
* **DCF models** rely on projected free cash flows, which come from the 3-statement model
* **LBO models** need operating projections and cash flow for debt paydown -- both from the 3-statement model
* **Credit analysis** requires projected leverage ratios and interest coverage -- derived from the 3-statement model
* **Scenario planning** uses the integrated model to test "what if" questions: What if revenue grows 5% instead of 10%? What if margins compress? What if interest rates rise?
If your 3-statement model has errors -- a balance sheet that does not balance, a cash flow that does not tie to the balance sheet -- then every analysis built on top of it is unreliable. This is why model integrity checks are so critical.
## Key Concepts
| Term | Definition | Why It Matters |
| --------------------- | -------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------- |
| **Revenue** | Total sales or income generated by the company's business activities. | The top line -- everything else in the model flows from revenue assumptions. |
| **COGS** | Cost of Goods Sold. Direct costs of producing goods or services. | Revenue minus COGS = Gross Profit. This measures production efficiency. |
| **EBITDA** | Earnings Before Interest, Taxes, Depreciation, and Amortization. | The most common measure of operating profitability, used in virtually every valuation multiple. |
| **Net Income** | The bottom line -- revenue minus all expenses, interest, and taxes. | Links to Retained Earnings on the BS and starts the CF statement. |
| **Working Capital** | Current Assets minus Current Liabilities. Includes Accounts Receivable, Inventory, and Accounts Payable. | Changes in working capital consume or release cash. Growing companies often consume working capital. |
| **D\&A** | Depreciation and Amortization. Non-cash expenses that spread the cost of assets over their useful life. | Reduces earnings but does not consume cash. Added back on the Cash Flow Statement. |
| **CapEx** | Capital Expenditures. Money spent on long-term assets like equipment and buildings. | Consumes cash but does not appear as an expense on the IS (it is capitalized on the BS). |
| **Retained Earnings** | Cumulative net income minus cumulative dividends. | The link between the IS and BS: Prior RE + Net Income - Dividends = Current RE. |
| **Free Cash Flow** | Cash generated by operations minus capital expenditures. | The cash available to distribute to investors or reinvest in the business. |
| **DSO/DIO/DPO** | Days Sales Outstanding, Days Inventory Outstanding, Days Payable Outstanding. | Key drivers of working capital. DSO = AR / Revenue x 365. DIO = Inventory / COGS x 365. DPO = AP / COGS x 365. |
## How It Works
**Triggers when:** asked to fill in, complete, or populate a 3-statement model template, complete a partially filled IS/BS/CF framework, or link integrated financial statements.
**Formulas over hardcodes -- non-negotiable.** Every projection cell, roll-forward, linkage, and subtotal MUST be an Excel formula. The ONLY cells with hardcoded numbers are historical actuals and assumption drivers.
### Environment: Office JS vs Python/openpyxl
Use Office JS directly. Write formulas via `range.formulas = [["=D14*(1+Assumptions!$B$5)"]]` -- never `range.values` for derived cells. No separate recalc; Excel computes natively.
**Merged cell pitfall:** Do NOT call `.merge()` then set `.values` on the merged range. Instead write value to top-left cell alone, then merge and format the full range.
Use Python/openpyxl. Write formula strings (`ws["D15"] = "=D14*(1+Assumptions!$B$5)"`), then run `recalc.py` before delivery.
### Core Linkages (Must Always Hold)
These are the mathematical connections that make the model "integrated." If any of these break, the model has an error:
| Check | Formula | Expected Result |
| --------------------- | -------------------------------------------------------- | --------------- |
| Balance Sheet Balance | Assets - Liabilities - Equity | = 0 |
| Cash Tie-Out | CF Ending Cash - BS Cash | = 0 |
| Net Income Link | IS Net Income - CF Starting Net Income | = 0 |
| Retained Earnings | Prior RE + NI - Dividends - BS Ending RE | = 0 |
| Equity Financing | Change in Common Stock/APIC (BS) - Equity Issuance (CFF) | = 0 |
### Sign Convention Reference
Getting signs right is one of the trickiest parts of 3-statement modeling:
| Statement | Item | Sign Convention | Why |
| --------- | --------------------- | ------------------------- | -------------------------------------------------------------- |
| CFO | D\&A, SBC | Positive (add-back) | Non-cash expenses that reduced Net Income but did not use cash |
| CFO | Increase in AR | Negative (use of cash) | Revenue was recognized but cash was not collected yet |
| CFO | Increase in AP | Positive (source of cash) | Expenses were recognized but not yet paid |
| CFO | Increase in Inventory | Negative (use of cash) | Cash was spent on inventory not yet sold |
| CFI | CapEx | Negative | Cash spent on long-term assets |
| CFF | Debt issuance | Positive | Cash received from borrowing |
| CFF | Debt repayments | Negative | Cash used to pay back debt |
| CFF | Dividends | Negative | Cash distributed to shareholders |
### Scenario Analysis
The model supports Bear/Base/Bull scenario analysis via a toggle:
| Scenario | Description |
| ------------- | ------------------------------------------ |
| Base Case | Management guidance or consensus estimates |
| Upside Case | Above-guidance growth, margin expansion |
| Downside Case | Below-trend growth, margin compression |
Use CHOOSE or INDEX/MATCH formulas in the Assumptions tab to switch between scenarios:
```
=CHOOSE($B$3, Bear_Growth, Base_Growth, Bull_Growth)
```
Where `$B$3` is the scenario selector cell (1 = Bear, 2 = Base, 3 = Bull).
### Step-by-Step User Verification
Show the user which tabs and sections you have identified. Confirm before touching any cells.
Show the historical block and confirm values and periods match source data.
Run subtotal checks. Show the projected IS and confirm before moving to BS.
Show the balance check (Assets = L+E) for every period. Confirm before moving to CF.
Show the cash tie-out (CF ending cash = BS cash). Confirm before finalizing.
### Quality Checks by Statement
**Income Statement:**
* Revenue figures match source data for historical periods
* All expense line items sum to reported totals
* Subtotals (Gross Profit, EBIT, EBT, Net Income) calculate correctly
* Tax calculation handles losses correctly (use MAX function: `=MAX(0, EBT) * Tax Rate`)
**Balance Sheet:**
* Assets = Liabilities + Equity for every period
* Cash balance matches Cash Flow Statement ending cash
* Retained Earnings rolls forward correctly
* Debt balances tie to debt schedule
**Cash Flow Statement:**
* Net Income at top of CFO matches IS Net Income
* Non-cash add-backs (D\&A, SBC) tie to source schedules
* Working capital changes have correct signs
* CapEx ties to PP\&E schedule
* Ending Cash matches Balance Sheet Cash
### Formatting Standards
| Element | Fill | Font |
| --------------------------------------------- | ----------------------------- | --------------- |
| Section headers (IS / BS / CF titles) | Dark blue `#1F4E79` | White bold |
| Column headers (FY2024A, FY2025E, etc.) | Light blue `#D9E1F2` | Black bold |
| Input cells (historicals, assumption drivers) | Light grey `#F2F2F2` or white | Blue `#0000FF` |
| Formula cells | White | Black |
| Cross-tab links | White | Green `#008000` |
| Check rows / key totals | Medium blue `#BDD7EE` | Black bold |
### Circular Reference Handling
Interest expense creates circularity: Interest depends on debt balance, which depends on cash flow, which depends on interest.
**Two approaches:**
1. **Beginning balance method (preferred):** Calculate interest on beginning-of-period debt. This eliminates the circular reference entirely.
2. **Iterative calculation:** Enable iterative calculation in Excel (File > Options > Formulas > Enable iterative calculation). Set maximum iterations to 100, maximum change to 0.001.
## Common Mistakes
**The mistake:** Total Assets does not equal Total Liabilities + Equity for one or more periods.
**Why it happens:** The most common cause is a broken Retained Earnings roll-forward. Other causes include missing a cash flow item, incorrect PP\&E roll-forward, or a broken debt schedule link.
**The fix:** Add a check row: `=Assets - Liabilities - Equity`. If the result is not zero, trace the gap period by period. Start with the first period that shows an imbalance. Check: (1) RE roll-forward, (2) Cash tie-out, (3) PP\&E roll-forward, (4) Debt balance links. The error is almost always in one of these four areas.
**The mistake:** An increase in Accounts Receivable is shown as a positive cash flow (source of cash) when it should be negative (use of cash).
**Why it happens:** It is counterintuitive. AR increased, which sounds positive, but it means customers owe you more money that has not arrived yet -- cash has not been collected. This is the single most common error in 3-statement models.
**The fix:** Memorize the rules: Increase in current asset = use of cash = negative on CF. Increase in current liability = source of cash = positive on CF. Test with logic: "If my customers owe me more money, do I have more or less cash?" Less cash -- so it must be negative.
**The mistake:** CF Ending Cash and BS Cash show different values for the same period.
**Why it happens:** A cash flow item is missing, or the BS Cash cell uses a hardcoded value instead of linking to the CF ending cash. Another cause: the CF statement computes change in cash correctly, but beginning cash is wrong.
**The fix:** Ensure BS Cash = CF Ending Cash via a direct cell reference (not a copy of the value). Then verify: CF Beginning Cash = Prior Period BS Cash. If these links are correct and cash still does not tie, there is a missing cash flow item -- check for items that affect the BS but are not on the CF (e.g., equity issuances, asset disposals).
**The mistake:** Interest expense depends on debt balance, which depends on cash flow, which depends on interest. Excel shows a circular reference warning and all affected cells show zero or an error.
**Why it happens:** If interest is calculated on the average debt balance (beginning + ending) / 2, the ending balance depends on the cash flow, which includes interest -- creating a loop.
**The fix:** Use beginning-of-period debt balance for interest calculations. This eliminates the circularity because the beginning balance is known from the prior period and does not depend on current-period cash flows. If the model requires average balances, enable iterative calculation and add a circuit breaker toggle.
**The mistake:** D\&A on the Income Statement is $50M, but D&A added back on the Cash Flow Statement is $45M, and the PP\&E schedule uses yet another number.
**Why it happens:** D\&A was hardcoded in multiple places instead of being calculated once and referenced everywhere. When someone updated the IS, they forgot to update the CF and PP\&E schedule.
**The fix:** Calculate D\&A in one place (typically the PP\&E schedule or the Assumptions tab) and reference that cell from the IS, CF, and PP\&E roll-forward. One source, three references. If D\&A is different on the IS versus the CF, the balance sheet will not balance.
**The mistake:** Beginning PP\&E + CapEx - D\&A does not equal Ending PP\&E.
**Why it happens:** CapEx on the CF does not match CapEx in the PP\&E schedule, or depreciation was calculated incorrectly. Sometimes asset disposals or write-downs are missing.
**The fix:** Build the PP\&E schedule as: Beginning PP\&E + CapEx - Depreciation - Disposals = Ending PP\&E. Ensure the CapEx used here is the same cell referenced on the CF statement. Ending PP\&E should be a formula, not a hardcoded value.
**The mistake:** The model shows dividends of $80M when net income is only $50M, causing retained earnings to decline without any documented rationale.
**Why it happens:** The dividend assumption was set as a fixed dollar amount without checking it against projected earnings.
**The fix:** Either cap dividends at net income (`=MIN(Dividend_Target, Net_Income)`) or add a payout ratio assumption (`=Net_Income x Payout_Ratio`). If the company intentionally pays dividends exceeding earnings (drawing down retained earnings), add a cell comment explaining the rationale.
**The mistake:** Year 2 revenue is typed as \$1,166M instead of being calculated as `=Year1_Revenue * (1 + Growth_Rate)`.
**Why it happens:** The analyst computed the number mentally or in a calculator and typed it in, intending to add the formula later but forgetting.
**The fix:** Every projection cell must be a formula. Run a systematic check: for each projection column, verify that no cell contains a hardcoded number that should be a formula. Hardcodes in projection columns are silent bugs -- the number looks right today but will not update when assumptions change.
**The mistake:** When EBT is negative (a loss), the model calculates a negative tax expense (tax benefit), which may not be appropriate depending on the company's tax position.
**Why it happens:** The formula `=EBT * Tax_Rate` mechanically produces a negative tax when EBT is negative.
**The fix:** Use `=MAX(0, EBT) * Tax_Rate` if the company cannot recognize tax benefits (e.g., it has no deferred tax assets). If the company can carry losses forward, build an NOL schedule: losses create NOL balances that offset future taxable income. The NOL schedule prevents tax on income up to the cumulative loss carryforward.
**The mistake:** Year 2's COGS formula references a different margin assumption than Years 3-5, creating a discontinuity that does not reflect the intended projection.
**Why it happens:** The analyst built Year 2 manually, then copied the formula to Years 3-5 but forgot to update the Year 2 formula when changing the approach.
**The fix:** Use Excel's Ctrl+\ shortcut to highlight cells with different formulas in a row. Every projection column should use the same formula structure. If Year 2 intentionally differs (e.g., a step-function margin change), document the rationale in a cell comment.
## Daily Workflow
A company you cover just reported Q3 earnings that beat estimates. You need to update your 3-statement model with the actual Q3 results and revise projections.
**Workflow:**
1. Update the historical column with Q3 actual results (revenue, margins, EPS, working capital, CapEx)
2. Compare actuals to your prior estimates -- where did the model miss?
3. Revise projection assumptions for the remaining quarters and out-years
4. Recalculate the full model -- IS, BS, CF all update through formulas
5. Run integrity checks: BS balance, cash tie-out, RE roll-forward
6. Extract updated metrics for your DCF and comps models
7. Draft the earnings note with the revised estimates and updated price target
Your team is initiating coverage on a mid-cap industrial company. You need to build a 3-statement model from scratch using 3 years of historical data and 5 years of projections.
**Workflow:**
1. Pull 3 years of historical financials from 10-K filings (revenue, COGS, SG\&A, D\&A, interest, taxes, all BS items, CF items)
2. Build the Assumptions tab with projection drivers (growth rates, margin assumptions, working capital days, CapEx intensity)
3. Build the IS projection: revenue build, cost structure, EBIT, interest, taxes, net income
4. Build the BS: working capital (AR, Inventory, AP driven by DSO/DIO/DPO), PP\&E (roll-forward from CapEx and D\&A), debt (roll-forward from repayments), RE (roll-forward from NI)
5. Build the CF: start with NI, add backs, working capital changes, CapEx, debt changes
6. Run integrity checks for every period
7. Add scenario toggle (Bear/Base/Bull) using CHOOSE functions
8. Hand off to the senior analyst for review and approval
The leveraged finance team needs to evaluate whether a BB-rated issuer can maintain covenant compliance over the next 3 years given rising interest rates and slowing growth.
**Workflow:**
1. Update the model with the latest quarterly actuals
2. Build three interest rate scenarios: base (current SOFR + spread), stress (+200bps), severe stress (+400bps)
3. Model the impact on interest expense, net income, and cash flow
4. Calculate key credit metrics for each scenario: Debt/EBITDA, Interest Coverage, Fixed Charge Coverage, FCF/Debt
5. Compare metrics against covenant thresholds
6. Identify the breakeven assumptions: at what growth rate and interest rate does the company breach covenants?
7. Present a traffic-light summary (green/yellow/red) for each metric and scenario
## Practice Exercise
**Scenario:** Build a 2-year projection for TechGrowth Inc., a high-growth software company.
**Given Historical Data (Year 0):**
* Revenue: \$300M
* COGS: \$90M (30% of revenue)
* S\&M: \$105M (35% of revenue)
* R\&D: \$60M (20% of revenue)
* G\&A: \$24M (8% of revenue)
* D\&A: \$15M (5% of revenue)
* Interest Expense: \$5M
* Tax Rate: 22%
* Cash: $150M; AR: $45M; PP\&E: \$100M
* Total Debt: $80M; AP: $30M
* Retained Earnings: $100M; Common Stock/APIC: $85M
**Projection Assumptions:**
* Year 1: Revenue growth 20%, Year 2: Revenue growth 15%
* COGS: stable at 30%
* S\&M: 33% Year 1, 31% Year 2 (operating leverage)
* R\&D: stable at 20%
* G\&A: 7.5% Year 1, 7% Year 2
* D\&A: 5% of revenue; CapEx: 7% of revenue
* DSO: 55 days; DPO: 36.5 days
* Stock-based compensation: $8M Year 1, $10M Year 2 (non-cash, add to APIC on BS)
* Debt repayment: \$10M/year
* No dividends
**Your tasks:**
1. Build the full Income Statement for Years 1-2 (include SBC as an operating expense below G\&A)
2. Calculate working capital changes (remember: DSO and DPO drive AR and AP)
3. Build the Cash Flow Statement (remember to add back both D\&A and SBC as non-cash items)
4. Build the Balance Sheet (remember: SBC increases APIC on the equity side)
5. Run all five integrity checks (BS balance, cash tie, NI link, RE roll, PP\&E roll)
6. Calculate key metrics: EBITDA margin, FCF margin, Debt/EBITDA
Watch the SBC treatment carefully. SBC is an operating expense on the IS (reducing Net Income), a non-cash add-back on the CF (like D\&A), and it increases APIC on the BS equity section. If you forget the APIC increase, the balance sheet will not balance by exactly the amount of cumulative SBC.
## How to Add to Your Local Context
```bash theme={null}
claude plugin install financial-analysis@financial-services-plugins
```
Edit `skills/3-statement-model/SKILL.md`:
```markdown theme={null}
## Firm Template Conventions
- Our standard template is in Egnyte at /templates/3-Stmt_Template_v5.xlsx
- Historical columns use suffix "A" (FY2024A), projected use "E" (FY2025E)
- All figures in USD millions unless otherwise noted
- Working capital schedule is on a separate tab ("WC")
```
```markdown theme={null}
## Projection Conventions
- Revenue: use segment-level build (not top-line growth rate)
- COGS: model as % of revenue
- SG&A: model as $ amount with growth rate (shows leverage)
- D&A: model from PP&E schedule, not as % of revenue
```
Edit `.mcp.json` for your preferred data providers to pull historical financials.
## Common Pitfalls
These are the most frequent causes of broken 3-statement models:
* **Balance sheet does not balance** -- If Assets does not equal Liabilities + Equity, nothing else matters until this is fixed. The most common cause is a broken Retained Earnings roll-forward.
* **Working capital sign errors** -- An increase in Accounts Receivable is a use of cash (negative on CF). This is counterintuitive and the most common error in 3-statement models.
* **Cash does not tie** -- CF Ending Cash must equal BS Cash for every period. If they diverge, there is a missing cash flow item.
* **Circular references from interest** -- Interest expense depends on debt balance, which depends on cash flow, which depends on interest. Enable iterative calculation or use beginning balances for interest.
* **Depreciation mismatch** -- D\&A on the CF should match D\&A on the IS and should be consistent with the PP\&E schedule on the BS.
* **SBC treatment** -- SBC reduces NI on the IS, is added back on the CF (non-cash), and increases APIC on the BS. Missing the APIC increase causes a balance sheet gap equal to cumulative SBC.