# 3 Statement Model

> Builds fully linked income-statement, balance-sheet, and cash-flow workbooks in openpyxl with working-capital, D&A, debt, and NOL schedules plus cash/RE tie-outs. Use for filling 3-statement templates or debugging a BS that will not balance. Do not use for DCF valuation or LBO debt waterfalls.

- Skill: `kayforkind/3-statement-model` (Agent Skill, multi-file: 4 files)
- Install (CLI): `npx skillmds@latest add kayforkind/3-statement-model`
- Raw SKILL.md: https://api.skillmd.com/api/skills/kayforkind/3-statement-model/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- License: Apache-2.0
- Author: Kayforkind (https://skillmd.com/u/kayforkind)
- Updated: 2026-09-09
- Page: https://skillmd.com/skills/kayforkind/3-statement-model

---


## Overview

This skill produces fully-integrated 3-statement financial models as `.xlsx` files on disk using headless openpyxl. It covers template analysis, historical data population, projection formulas, supporting schedules (working capital, D&A, debt, NOL), cross-statement integrity checks, scenario toggles, and audit dashboards.

**Environment:** Headless openpyxl producing `.xlsx` on disk. Follow the `excel-author` skill's conventions for cell coloring, formulas, named ranges, and sensitivity tables. Recalculate before delivery by opening the workbook in Excel (full calculate + save). This folder does not ship a recalc helper.

Windows host is primary (PowerShell). Adjust path separators for macOS/Linux as needed.

## When to Use

- User asks to build or complete an integrated 3-statement financial model (IS + BS + CF)
- User provides a template `.xlsx` and asks to populate it with historicals and projections
- User needs working capital schedules, D&A roll-forwards, debt schedules, or NOL schedules linked to main statements
- User wants scenario analysis (Base / Upside / Downside) with a toggle
- User needs cross-statement integrity checks (balance check, cash tie-out, RE roll-forward)
- User asks to debug a broken model where BS doesn't balance or cash doesn't tie

**Trigger keywords:** 3-statement, integrated model, income statement, balance sheet, cash flow, working capital schedule, D&A roll-forward, debt schedule, NOL, balance check, cash tie-out, scenario toggle, financial model template

## Prerequisites

- `excel-author` skill installed (for cell coloring conventions, formula patterns, recalc script)
- Python with openpyxl available
- Template `.xlsx` file (if user provides one) or ability to build from scratch
- Historical financial data (from SEC filings, user-provided, or MCP data source)

**Reference files** (load when needed):
- `references/formulas.md` — Load when writing projection formulas, cross-statement linkages, or building the checks/audit tab. Contains all formula details for core linkages.
- `references/sec-filings.md` — Load ONLY when populating templates with public company data from SEC filings (10-K, 10-Q). Contains extraction guidance for EDGAR.

## Procedure

### Step 1: Analyze the Template Structure

Before entering any data, thoroughly review the template to understand its architecture.

**Identify tabs and their contents:**

| Common Tab Names | Contents to Look For |
|------------------|----------------------|
| IS, P&L, Income Statement | Income Statement |
| BS, Balance Sheet | Balance Sheet |
| CF, CFS, Cash Flow | Cash Flow Statement |
| WC, Working Capital | Working Capital Schedule |
| DA, D&A, Depreciation, PP&E | Depreciation & Amortization Schedule |
| Debt, Debt Schedule | Debt Schedule |
| NOL, Tax, DTA | Net Operating Loss Schedule |
| Assumptions, Inputs, Drivers | Driver assumptions and inputs |
| Checks, Audit, Validation | Error-checking dashboard |

**Template Review Checklist:**
1. Identify which tabs exist (not all templates include every schedule)
2. Note any template-specific tabs not listed above
3. Understand tab dependencies (e.g., which schedules feed into the main statements)
4. Locate input cells vs. formula cells on each tab

**Identify row structure:**
- Locate the model title at top of each tab
- Identify section headers and their visual separation
- Find the units row indicating $ millions, %, x, etc.
- Note column headers distinguishing Actuals vs. Estimates periods
- Confirm period labels (e.g., FY2024A, FY2025E)
- Identify input cells vs. formula cells (typically distinguished by font color)

**Identify column structure:**
- Confirm line item labels in leftmost column
- Verify historical years precede projection years
- Note the visual border separating historical from projected periods
- Check for consistent column order across all tabs

**Review named ranges:**
- Check existing named ranges (Formulas → Name Manager in Excel, or via openpyxl `wb.defined_names`)
- Common named ranges: Revenue growth rates, cost percentages, key outputs (Net Income, EBITDA, Total Debt, Cash), scenario selector cell
- Ensure inputs are entered in cells that feed into these named ranges

**Map the template's flow:**
- Identify which tabs feed into others (e.g., Assumptions → IS → BS → CF)
- Note any supporting schedules and their linkages to main statements
- Document the template's specific line items and structure before populating

> **VERIFY WITH USER:** After mapping the template, show the user which tabs/sections you've identified and confirm before touching any cells.

### Step 2: Populate Historical Data

**Golden Rules for Data Entry:**

| Rule | Description |
|------|-------------|
| Only edit input cells | Never overwrite cells containing formulas unless intentionally replacing the formula |
| Preserve cell references | When copying data, use Paste Values (Ctrl+Shift+V) to avoid overwriting formulas with source formatting |
| Match the template's units | Verify if template uses thousands, millions, or actual values before entering data |
| Respect sign conventions | Follow the template's existing sign convention (e.g., expenses as positive or negative) |
| Check for circular references | If the template uses iterative calculations, ensure Enable Iterative Calculation is turned on |

**Safe data entry process:**
1. Identify the exact cells designated for input (usually highlighted or labeled)
2. Enter historical data first, then verify formulas are calculating correctly for those periods
3. Enter assumption drivers that feed forecast calculations
4. Review calculated outputs to confirm formulas are working as intended
5. If a formula cell must be modified, document the original formula before making changes

**Handling pre-built formulas:**
- If formulas reference cells you haven't populated yet, expect temporary errors (#REF!, #DIV/0!) until all inputs are complete
- When formulas produce unexpected results, trace precedents to identify missing or incorrect inputs
- Never delete rows/columns without checking for formula dependencies across all tabs

> **VERIFY WITH USER:** After populating historicals, show the user the historical block and confirm values/periods match source data.

### Step 3: Build Income Statement Projections

**CRITICAL — Formulas over hardcodes (non-negotiable):**
- Every projection cell, roll-forward, linkage, and subtotal MUST be an Excel formula — never a pre-computed value
- When using Python/openpyxl: write formula strings (`ws["D15"] = "=D14*(1+Assumptions!$B$5)"`), NOT computed results (`ws["D15"] = 12500`)
- The ONLY cells that should contain hardcoded numbers are: (1) historical actuals, (2) assumption drivers in the Assumptions tab
- If you find yourself computing a value in Python and writing the result to a cell — STOP. Write the formula instead.
- Why: the model must flex when scenarios toggle or assumptions change. Hardcodes break every downstream integrity check silently.

**Projection period:**
- Templates typically project 5 years forward from last historical year
- Verify historical (A) vs. projected (E) columns are clearly separated
- Confirm columns use fiscal year notation (e.g., FY2024A, FY2025E)

**IS Quality Checks:**
- 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 logic is appropriate (handles losses correctly)
- Forecast drivers reference assumptions tab (no hardcodes)
- Period-over-period changes are directionally reasonable

> **VERIFY WITH USER:** After building IS projections, run the subtotal checks, show the user the projected IS, confirm before moving to BS.

### Step 4: Build Balance Sheet Projections

**BS Quality Checks:**
- Assets = Liabilities + Equity for every period (primary check)
- Cash balance matches Cash Flow Statement ending cash
- Working capital accounts tie to supporting schedules (if applicable)
- Retained Earnings rolls forward correctly: Prior RE + Net Income - Dividends +/- Adjustments = Ending RE
- Debt balances tie to debt schedule (if applicable)
- All balance sheet items have appropriate signs (assets positive, most liabilities positive)

> **VERIFY WITH USER:** After building BS, show the user the balance check (Assets = L+E) for every period, confirm before moving to CF.

### Step 5: Build Cash Flow Statement Projections

**CF Quality Checks:**
- Net Income at top of CFO matches Income Statement Net Income
- Non-cash add-backs (D&A, SBC, etc.) tie to their source schedules/statements
- Working capital changes have correct signs (increase in asset = use of cash = negative)
- CapEx ties to PP&E schedule or fixed asset roll-forward
- Financing activities tie to changes in debt and equity accounts on BS
- Ending Cash matches Balance Sheet Cash
- Beginning Cash equals prior period Ending Cash

**Sign Convention Reference:**

| Statement | Item | Sign Convention |
|-----------|------|-----------------|
| CFO | D&A, SBC | Positive (add-back) |
| CFO | ΔAR (increase) | Negative (use of cash) |
| CFO | ΔAP (increase) | Positive (source of cash) |
| CFI | CapEx | Negative |
| CFF | Debt issuance | Positive |
| CFF | Debt repayments | Negative |
| CFF | Dividends | Negative |

> **VERIFY WITH USER:** After building CF, show the user the cash tie-out (CF ending cash = BS cash), confirm before finalizing.

### Step 6: Build Supporting Schedules

**Working Capital Schedule:**
- AR, Inventory, AP tie to BS
- DSO, DIO, DPO reasonability checks (flag if outside normal ranges)
- Opening balances equal prior period closing balances

**D&A / PP&E Schedule:**
- Roll-forward logic: Beginning PP&E + CapEx - Disposals = Ending PP&E
- D&A ties to IS and CF (as non-cash add-back)
- CapEx ties to CF (investing activities)

**Debt Schedule:**
- Total Debt ties to BS (Current + LT Debt)
- Interest calculation ties to IS
- Roll-forward: Beginning Debt + Issuances - Repayments = Ending Debt

**NOL Schedule:**
- Beginning NOL (Year 1 / Formation) = 0 (new business starts with zero NOL)
- NOL increases only when EBT < 0 (losses must be realized to generate NOL)
- DTA ties to BS (NOL Schedule DTA = BS Deferred Tax Asset)
- NOL utilization ≤ 80% of EBT (post-2017 federal limitation)
- NOL balance is non-negative (cannot utilize more than available)
- NOL generated only when EBT < 0
- Tax expense = 0 when taxable income ≤ 0

**Equity Financing:**
- Equity issuance proceeds tie to BS Common Stock/APIC increase
- Cash increase from equity = Equity account increase (must balance)
- Equity Raise Tie-Out: ΔCommon Stock/APIC (BS) = Equity Issuance (CFF) (must = 0)
- Year 0 Equity Tie-Out: Equity Raised (Year 0) = Beginning Equity Capital (Year 1)

### Step 7: Cross-Statement Integrity Checks

After validating individual sheets, confirm the three statements are properly integrated:

| Check | Formula | Expected Result |
|-------|---------|-----------------|
| Balance Sheet Balance | Assets - Liabilities - Equity | = 0 |
| Cash Tie-Out | CF Ending Cash - BS Cash | = 0 |
| Cash Monthly vs Annual | Closing Cash (Monthly) - Closing Cash (Annual) | = 0 |
| Net Income Link | IS Net Income - CF Starting Net Income | = 0 |
| Retained Earnings | Prior RE + NI + SBC - Dividends - BS Ending RE | = 0 |
| Equity Financing | ΔCommon Stock/APIC (BS) - Equity Issuance (CFF) | = 0 |
| Year 0 Equity | Equity Raised (Year 0) - Beginning Equity Capital (Year 1) | = 0 |

Load `references/formulas.md` for all formula details when building the Checks/Audit tab.

### Step 8: Scenario Analysis (Base / Upside / Downside)

Use a scenario toggle (dropdown) in the Assumptions tab with CHOOSE or INDEX/MATCH formulas.

| Scenario | Description |
|----------|-------------|
| Base Case | Management guidance or consensus estimates |
| Upside Case | Above-guidance growth, margin expansion |
| Downside Case | Below-trend growth, margin compression |

**Key Drivers to Sensitize:** Revenue growth, Gross margin, SG&A %, DSO/DIO/DPO, CapEx %, Interest rate, Tax rate.

**Scenario Audit Checks:**
- Toggle switches all statements
- BS balances in all scenarios
- Cash ties out in all scenarios
- Hierarchy holds: Upside > Base > Downside for NI, EBITDA, FCF, margins
- Credit metrics: Upside < Base < Downside for leverage (inverted — lower is better)

### Step 9: Margin Analysis (Optional — Only if Prompted)

> Only perform margin analysis if prompted by the user or if the template explicitly requires it. If no prompt is given, skip this section.

| Margin | Formula | What It Measures |
|--------|---------|------------------|
| Gross Margin | Gross Profit / Revenue | Pricing power, production efficiency |
| EBITDA Margin | EBITDA / Revenue | Core operating profitability |
| EBIT Margin | EBIT / Revenue | Operating profitability after D&A |
| Net Income Margin | Net Income / Revenue | Bottom-line profitability |

Display margin percentages directly below each profit line item on the IS tab.

### Step 10: Credit Metrics (Optional — Only if Prompted)

> Only perform credit analysis if prompted by the user or if the template explicitly requires it. If no prompt is given, skip this section.

| Metric | Formula | What It Measures |
|--------|---------|------------------|
| Total Debt / EBITDA | Total Debt / LTM EBITDA | Leverage multiple |
| Net Debt / EBITDA | (Total Debt - Cash) / LTM EBITDA | Leverage net of cash |
| Interest Coverage | EBITDA / Interest Expense | Ability to service debt |
| Debt / Total Cap | Total Debt / (Total Debt + Equity) | Capital structure |
| Debt / Equity | Total Debt / Total Equity | Financial leverage |
| Current Ratio | Current Assets / Current Liabilities | Short-term liquidity |
| Quick Ratio | (Current Assets - Inventory) / Current Liabilities | Immediate liquidity |

**Credit Metric Hierarchy Checks:**
- Leverage: Upside < Base < Downside (lower is better)
- Coverage: Upside > Base > Downside (higher is better)
- Liquidity: Upside > Base > Downside (higher is better)

If debt covenants are known, add explicit compliance checks comparing actual metrics to covenant thresholds.

### Step 11: Build Audit/Checks Dashboard

Consolidate all validation checks into the Checks/Audit tab:

**Check Categories:**

1. **Currency Consistency** — Currency identified and documented in Assumptions; all tabs use consistent currency symbol and scale; units row matches model currency
2. **Balance Sheet Integrity** — Assets = Liabilities + Equity for each period
3. **Cash Flow Integrity** — Cash ties to BS; monthly vs annual cash; NI ties to IS; D&A ties to schedule; SBC ties to IS; ΔAR, ΔInventory, ΔAP tie to WC schedule; CapEx ties to DA schedule
4. **Retained Earnings** — Prior RE + NI + SBC - Dividends = Ending RE; show component breakdown for debugging
5. **Working Capital** — AR, Inventory, AP tie to BS; DSO, DIO, DPO reasonability checks
6. **Debt Schedule** — Total Debt ties to BS; Interest calculation ties to IS
7. **Equity Financing** — Equity issuance ties to BS Common Stock/APIC increase; Year 0 equity tie-out
8. **NOL Schedule** — Beginning NOL = 0; NOL increases only on losses; DTA ties to BS; NOL utilization ≤ 80% of EBT; non-negative balance
9. **Scenario Hierarchy** — Absolute metrics: Upside > Base > Downside; Margins: Upside > Base > Downside; Credit metrics: Upside < Base < Downside for leverage
10. **Formula Integrity** — COGS, S&M, G&A, R&D, SBC driven by % of Revenue; consistent formulas across projection years; no #REF!, #DIV/0!, #VALUE! errors
11. **Credit Metric Thresholds** — Flag metrics as Green/Yellow/Red based on covenant thresholds

**Master Check Formula:**
- If all sections pass → "✓ ALL CHECKS PASS"
- If any section fails → "✗ ERRORS DETECTED - REVIEW BELOW"

### Step 12: Final Review and Recalculate

1. Toggle through all scenarios to verify checks pass in each case
2. Review all #REF!, #DIV/0!, #VALUE!, and #NAME? errors and resolve or document
3. Confirm all input cells have been populated (search for placeholder values)
4. Verify units are consistent across all tabs
5. Save a clean version before making any additional modifications
6. Recalculate before delivery: open the `.xlsx` in Excel, force a full calculate, and save. This folder does not ship a recalc helper.

## Formatting — Professional Blue/Grey Palette

**Keep colors minimal.** Use only blues and greys for cell fills. Do NOT introduce greens, yellows, oranges, or multiple accent colors — a clean model uses restraint.

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

**That's 3 blues + 1 grey + white.** If the template has its own color scheme, follow the template instead.

Font color signals *what* a cell is (input/formula/link). Fill color signals *where* you are (header/data/check).

## Data Sources — MCP First, Web Fallback

- **If you have any structured financial-data MCP configured** (Hermes supports MCP — see `native-mcp` skill), prefer it for point-in-time comps, precedent transactions, and filings.
- **Otherwise**, fall back to:
  - `web_search` / `web_extract` against SEC EDGAR (`https://www.sec.gov/cgi-bin/browse-edgar`) for US filings
  - Company IR pages for press releases, earnings decks
  - `browser_navigate` for interactive data portals
  - User-provided data (explicitly ask when the context doesn't have it)
- **Never fabricate.** If a multiple, precedent, or filing number can't be sourced, flag the cell as `[UNSOURCED]` and surface it to the user.

For SEC filings extraction guidance, load `references/sec-filings.md`.

## Pitfalls

### Hardcodes Break Models Silently
- **Pitfall:** Computing a value in Python and writing the result to a cell instead of writing the formula.
- **Impact:** The model won't flex when scenarios toggle or assumptions change. Every downstream integrity check breaks silently.
- **Fix:** Always write formula strings in openpyxl. The ONLY hardcoded numbers should be historical actuals and assumption drivers.

### Circular Reference from Interest Expense
- **Pitfall:** Interest → Net Income → Cash → Debt Balance → Interest creates a circular reference.
- **Fix:** Enable iterative calculation in Excel: File → Options → Formulas → Enable iterative calculation. Set maximum iterations to 100, maximum change to 0.001. Add a circuit breaker toggle in Assumptions tab.

### Mixed Absolute/Relative References
- **Pitfall:** Incorrect reference types cause wrong results when formulas are copied across periods.
- **Fix:** Use `$` for absolute references to assumption cells (e.g., `Assumptions!$B$5`), relative references for period-to-period calculations.

### Deleting Rows/Columns Without Checking Dependencies
- **Pitfall:** Deleting a row or column breaks formulas across all tabs that reference it.
- **Fix:** Never delete rows/columns without checking for formula dependencies across all tabs. Use Trace Dependents first.

### Sign Convention Errors in Cash Flow
- **Pitfall:** Increase in an asset (e.g., AR) shown as positive instead of negative (use of cash).
- **Fix:** Follow the sign convention table above. Increase in asset = use of cash = negative. Increase in liability = source of cash = positive.

### NOL Utilization Exceeding 80% Limit
- **Pitfall:** Utilizing more than 80% of EBT for NOL carryforward (post-2017 federal limitation).
- **Fix:** Cap NOL utilization at 80% of EBT. Add an explicit check in the NOL schedule.

### Populating Entire Model End-to-End Without User Checkpoints
- **Pitfall:** Building the complete model and presenting it at once, only to find fundamental errors in the IS that cascade through BS and CF.
- **Fix:** Break at each statement. Show the work. Catch errors early. Follow the verify-with-user checkpoints after each step.

### Inconsistent Units Across Tabs
- **Pitfall:** One tab uses thousands, another uses millions, creating order-of-magnitude errors.
- **Fix:** Verify units before entering data. Check the units row on every tab. Document the model's currency and scale in Assumptions.

## Verification

### Formula Integrity Checks

| Check Type | Method |
|------------|--------|
| Trace precedents | Select a formula cell → Formulas → Trace Precedents to verify it references correct inputs |
| Trace dependents | Verify key inputs flow to expected output cells |
| Evaluate formula | Use Formulas → Evaluate Formula to step through complex calculations |
| Check for hardcodes | Projection formulas should reference assumptions, not contain hardcoded values |
| Test with known values | Input simple test values to verify formulas produce expected results |
| Cross-tab consistency | Ensure the same formula logic applies across all projection periods |
| Find inconsistent formulas | Use Ctrl+\ to find differences across columns |

### Cross-Statement Integrity Checks

Run these checks after model completion. All must equal zero:

| 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 + SBC - Dividends - BS_Ending_RE` | 0 |
| Equity Financing | `=ΔCommon_Stock_APIC - Equity_Issuance_CFF` | 0 |

### Recalculation Verification

Open the output `.xlsx` in Excel, force a full calculate, save, and confirm it opens without formula errors. This folder does not ship a recalc helper.

### Quick Debug Workflow

When Master Status shows errors:
1. Scroll to find red-highlighted sections on the Checks tab
2. Identify which check category has failures
3. Navigate to source tab to investigate
4. Fix the underlying issue
5. Return to Checks tab to verify resolution
6. Re-open in Excel and force a full calculate

### Final Delivery Checklist

- [ ] All historical data matches source documents
- [ ] All projection cells are formulas (no hardcodes)
- [ ] BS balances for every period (Assets = L + E)
- [ ] CF ending cash = BS cash for every period
- [ ] RE roll-forward ties for every period
- [ ] All scenarios toggle correctly and pass checks
- [ ] No #REF!, #DIV/0!, #VALUE!, #NAME? errors
- [ ] Units consistent across all tabs
- [ ] Workbook fully recalculates and opens in Excel
- [ ] All `[UNSOURCED]` cells flagged to user

## Related Skills

- `excel-author` — Cell coloring conventions, formula patterns, recalc script, named ranges, sensitivity tables
- `dcf-model` — DCF valuation built on top of 3-statement model outputs
- `lbo-model` — LBO transaction model with debt waterfall
- `pptx-author` — Presentation output of model results

## Attribution

Adapted from Anthropic's Claude for Financial Services plugin suite (Apache-2.0). The Office-JS / Cowork live-Excel paths have been removed; this version targets headless openpyxl via the `excel-author` skill's conventions. Original: https://github.com/anthropics/financial-services