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:

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:

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:

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:

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.

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.

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.

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.

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

Cross-Statement Integrity Checks

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

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