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:
- Identify which tabs exist (not all templates include every schedule)
- Note any template-specific tabs not listed above
- Understand tab dependencies (e.g., which schedules feed into the main statements)
- 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:
- Identify the exact cells designated for input (usually highlighted or labeled)
- Enter historical data first, then verify formulas are calculating correctly for those periods
- Enter assumption drivers that feed forecast calculations
- Review calculated outputs to confirm formulas are working as intended
- 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:
- Currency Consistency — Currency identified and documented in Assumptions; all tabs use consistent currency symbol and scale; units row matches model currency
- Balance Sheet Integrity — Assets = Liabilities + Equity for each period
- 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
- Retained Earnings — Prior RE + NI + SBC - Dividends = Ending RE; show component breakdown for debugging
- Working Capital — AR, Inventory, AP tie to BS; DSO, DIO, DPO reasonability checks
- Debt Schedule — Total Debt ties to BS; Interest calculation ties to IS
- Equity Financing — Equity issuance ties to BS Common Stock/APIC increase; Year 0 equity tie-out
- NOL Schedule — Beginning NOL = 0; NOL increases only on losses; DTA ties to BS; NOL utilization ≤ 80% of EBT; non-negative balance
- Scenario Hierarchy — Absolute metrics: Upside > Base > Downside; Margins: Upside > Base > Downside; Credit metrics: Upside < Base < Downside for leverage
- 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
- 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
- Toggle through all scenarios to verify checks pass in each case
- Review all #REF!, #DIV/0!, #VALUE!, and #NAME? errors and resolve or document
- Confirm all input cells have been populated (search for placeholder values)
- Verify units are consistent across all tabs
- Save a clean version before making any additional modifications
- 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:
- Scroll to find red-highlighted sections on the Checks tab
- Identify which check category has failures
- Navigate to source tab to investigate
- Fix the underlying issue
- Return to Checks tab to verify resolution
- Re-open in Excel and force a full calculate
Final Delivery Checklist
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
1---
2name: 3-statement-model
3description: "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."
4version: 1.0.1
5author: Anthropic (adapted by Nous Research)
6license: Apache-2.0
7platforms: [linux, macos, windows]
8metadata:
9 source: https://skillmd.com/skills/kayforkind/3-statement-model
10 hermes:
11 tags: [finance, three-statement, income-statement, balance-sheet, cash-flow, excel, openpyxl, modeling]
12 related_skills: [excel-author, pptx-author, dcf-model, lbo-model]
13---
14
15## Overview
16
17This 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.
18
19**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.
20
21Windows host is primary (PowerShell). Adjust path separators for macOS/Linux as needed.
22
23## When to Use
24
25- User asks to build or complete an integrated 3-statement financial model (IS + BS + CF)
26- User provides a template `.xlsx` and asks to populate it with historicals and projections
27- User needs working capital schedules, D&A roll-forwards, debt schedules, or NOL schedules linked to main statements
28- User wants scenario analysis (Base / Upside / Downside) with a toggle
29- User needs cross-statement integrity checks (balance check, cash tie-out, RE roll-forward)
30- User asks to debug a broken model where BS doesn't balance or cash doesn't tie
31
32**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
33
34## Prerequisites
35
36- `excel-author` skill installed (for cell coloring conventions, formula patterns, recalc script)
37- Python with openpyxl available
38- Template `.xlsx` file (if user provides one) or ability to build from scratch
39- Historical financial data (from SEC filings, user-provided, or MCP data source)
40
41**Reference files** (load when needed):
42- `references/formulas.md` — Load when writing projection formulas, cross-statement linkages, or building the checks/audit tab. Contains all formula details for core linkages.
43- `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.
44
45## Procedure
46
47### Step 1: Analyze the Template Structure
48
49Before entering any data, thoroughly review the template to understand its architecture.
50
51**Identify tabs and their contents:**
52
53| Common Tab Names | Contents to Look For |
54|------------------|----------------------|
55| IS, P&L, Income Statement | Income Statement |
56| BS, Balance Sheet | Balance Sheet |
57| CF, CFS, Cash Flow | Cash Flow Statement |
58| WC, Working Capital | Working Capital Schedule |
59| DA, D&A, Depreciation, PP&E | Depreciation & Amortization Schedule |
60| Debt, Debt Schedule | Debt Schedule |
61| NOL, Tax, DTA | Net Operating Loss Schedule |
62| Assumptions, Inputs, Drivers | Driver assumptions and inputs |
63| Checks, Audit, Validation | Error-checking dashboard |
64
65**Template Review Checklist:**
661. Identify which tabs exist (not all templates include every schedule)
672. Note any template-specific tabs not listed above
683. Understand tab dependencies (e.g., which schedules feed into the main statements)
694. Locate input cells vs. formula cells on each tab
70
71**Identify row structure:**
72- Locate the model title at top of each tab
73- Identify section headers and their visual separation
74- Find the units row indicating $ millions, %, x, etc.
75- Note column headers distinguishing Actuals vs. Estimates periods
76- Confirm period labels (e.g., FY2024A, FY2025E)
77- Identify input cells vs. formula cells (typically distinguished by font color)
78
79**Identify column structure:**
80- Confirm line item labels in leftmost column
81- Verify historical years precede projection years
82- Note the visual border separating historical from projected periods
83- Check for consistent column order across all tabs
84
85**Review named ranges:**
86- Check existing named ranges (Formulas → Name Manager in Excel, or via openpyxl `wb.defined_names`)
87- Common named ranges: Revenue growth rates, cost percentages, key outputs (Net Income, EBITDA, Total Debt, Cash), scenario selector cell
88- Ensure inputs are entered in cells that feed into these named ranges
89
90**Map the template's flow:**
91- Identify which tabs feed into others (e.g., Assumptions → IS → BS → CF)
92- Note any supporting schedules and their linkages to main statements
93- Document the template's specific line items and structure before populating
94
95> **VERIFY WITH USER:** After mapping the template, show the user which tabs/sections you've identified and confirm before touching any cells.
96
97### Step 2: Populate Historical Data
98
99**Golden Rules for Data Entry:**
100
101| Rule | Description |
102|------|-------------|
103| Only edit input cells | Never overwrite cells containing formulas unless intentionally replacing the formula |
104| Preserve cell references | When copying data, use Paste Values (Ctrl+Shift+V) to avoid overwriting formulas with source formatting |
105| Match the template's units | Verify if template uses thousands, millions, or actual values before entering data |
106| Respect sign conventions | Follow the template's existing sign convention (e.g., expenses as positive or negative) |
107| Check for circular references | If the template uses iterative calculations, ensure Enable Iterative Calculation is turned on |
108
109**Safe data entry process:**
1101. Identify the exact cells designated for input (usually highlighted or labeled)
1112. Enter historical data first, then verify formulas are calculating correctly for those periods
1123. Enter assumption drivers that feed forecast calculations
1134. Review calculated outputs to confirm formulas are working as intended
1145. If a formula cell must be modified, document the original formula before making changes
115
116**Handling pre-built formulas:**
117- If formulas reference cells you haven't populated yet, expect temporary errors (#REF!, #DIV/0!) until all inputs are complete
118- When formulas produce unexpected results, trace precedents to identify missing or incorrect inputs
119- Never delete rows/columns without checking for formula dependencies across all tabs
120
121> **VERIFY WITH USER:** After populating historicals, show the user the historical block and confirm values/periods match source data.
122
123### Step 3: Build Income Statement Projections
124
125**CRITICAL — Formulas over hardcodes (non-negotiable):**
126- Every projection cell, roll-forward, linkage, and subtotal MUST be an Excel formula — never a pre-computed value
127- When using Python/openpyxl: write formula strings (`ws["D15"] = "=D14*(1+Assumptions!$B$5)"`), NOT computed results (`ws["D15"] = 12500`)
128- The ONLY cells that should contain hardcoded numbers are: (1) historical actuals, (2) assumption drivers in the Assumptions tab
129- If you find yourself computing a value in Python and writing the result to a cell — STOP. Write the formula instead.
130- Why: the model must flex when scenarios toggle or assumptions change. Hardcodes break every downstream integrity check silently.
131
132**Projection period:**
133- Templates typically project 5 years forward from last historical year
134- Verify historical (A) vs. projected (E) columns are clearly separated
135- Confirm columns use fiscal year notation (e.g., FY2024A, FY2025E)
136
137**IS Quality Checks:**
138- Revenue figures match source data for historical periods
139- All expense line items sum to reported totals
140- Subtotals (Gross Profit, EBIT, EBT, Net Income) calculate correctly
141- Tax calculation logic is appropriate (handles losses correctly)
142- Forecast drivers reference assumptions tab (no hardcodes)
143- Period-over-period changes are directionally reasonable
144
145> **VERIFY WITH USER:** After building IS projections, run the subtotal checks, show the user the projected IS, confirm before moving to BS.
146
147### Step 4: Build Balance Sheet Projections
148
149**BS Quality Checks:**
150- Assets = Liabilities + Equity for every period (primary check)
151- Cash balance matches Cash Flow Statement ending cash
152- Working capital accounts tie to supporting schedules (if applicable)
153- Retained Earnings rolls forward correctly: Prior RE + Net Income - Dividends +/- Adjustments = Ending RE
154- Debt balances tie to debt schedule (if applicable)
155- All balance sheet items have appropriate signs (assets positive, most liabilities positive)
156
157> **VERIFY WITH USER:** After building BS, show the user the balance check (Assets = L+E) for every period, confirm before moving to CF.
158
159### Step 5: Build Cash Flow Statement Projections
160
161**CF Quality Checks:**
162- Net Income at top of CFO matches Income Statement Net Income
163- Non-cash add-backs (D&A, SBC, etc.) tie to their source schedules/statements
164- Working capital changes have correct signs (increase in asset = use of cash = negative)
165- CapEx ties to PP&E schedule or fixed asset roll-forward
166- Financing activities tie to changes in debt and equity accounts on BS
167- Ending Cash matches Balance Sheet Cash
168- Beginning Cash equals prior period Ending Cash
169
170**Sign Convention Reference:**
171
172| Statement | Item | Sign Convention |
173|-----------|------|-----------------|
174| CFO | D&A, SBC | Positive (add-back) |
175| CFO | ΔAR (increase) | Negative (use of cash) |
176| CFO | ΔAP (increase) | Positive (source of cash) |
177| CFI | CapEx | Negative |
178| CFF | Debt issuance | Positive |
179| CFF | Debt repayments | Negative |
180| CFF | Dividends | Negative |
181
182> **VERIFY WITH USER:** After building CF, show the user the cash tie-out (CF ending cash = BS cash), confirm before finalizing.
183
184### Step 6: Build Supporting Schedules
185
186**Working Capital Schedule:**
187- AR, Inventory, AP tie to BS
188- DSO, DIO, DPO reasonability checks (flag if outside normal ranges)
189- Opening balances equal prior period closing balances
190
191**D&A / PP&E Schedule:**
192- Roll-forward logic: Beginning PP&E + CapEx - Disposals = Ending PP&E
193- D&A ties to IS and CF (as non-cash add-back)
194- CapEx ties to CF (investing activities)
195
196**Debt Schedule:**
197- Total Debt ties to BS (Current + LT Debt)
198- Interest calculation ties to IS
199- Roll-forward: Beginning Debt + Issuances - Repayments = Ending Debt
200
201**NOL Schedule:**
202- Beginning NOL (Year 1 / Formation) = 0 (new business starts with zero NOL)
203- NOL increases only when EBT < 0 (losses must be realized to generate NOL)
204- DTA ties to BS (NOL Schedule DTA = BS Deferred Tax Asset)
205- NOL utilization ≤ 80% of EBT (post-2017 federal limitation)
206- NOL balance is non-negative (cannot utilize more than available)
207- NOL generated only when EBT < 0
208- Tax expense = 0 when taxable income ≤ 0
209
210**Equity Financing:**
211- Equity issuance proceeds tie to BS Common Stock/APIC increase
212- Cash increase from equity = Equity account increase (must balance)
213- Equity Raise Tie-Out: ΔCommon Stock/APIC (BS) = Equity Issuance (CFF) (must = 0)
214- Year 0 Equity Tie-Out: Equity Raised (Year 0) = Beginning Equity Capital (Year 1)
215
216### Step 7: Cross-Statement Integrity Checks
217
218After validating individual sheets, confirm the three statements are properly integrated:
219
220| Check | Formula | Expected Result |
221|-------|---------|-----------------|
222| Balance Sheet Balance | Assets - Liabilities - Equity | = 0 |
223| Cash Tie-Out | CF Ending Cash - BS Cash | = 0 |
224| Cash Monthly vs Annual | Closing Cash (Monthly) - Closing Cash (Annual) | = 0 |
225| Net Income Link | IS Net Income - CF Starting Net Income | = 0 |
226| Retained Earnings | Prior RE + NI + SBC - Dividends - BS Ending RE | = 0 |
227| Equity Financing | ΔCommon Stock/APIC (BS) - Equity Issuance (CFF) | = 0 |
228| Year 0 Equity | Equity Raised (Year 0) - Beginning Equity Capital (Year 1) | = 0 |
229
230Load `references/formulas.md` for all formula details when building the Checks/Audit tab.
231
232### Step 8: Scenario Analysis (Base / Upside / Downside)
233
234Use a scenario toggle (dropdown) in the Assumptions tab with CHOOSE or INDEX/MATCH formulas.
235
236| Scenario | Description |
237|----------|-------------|
238| Base Case | Management guidance or consensus estimates |
239| Upside Case | Above-guidance growth, margin expansion |
240| Downside Case | Below-trend growth, margin compression |
241
242**Key Drivers to Sensitize:** Revenue growth, Gross margin, SG&A %, DSO/DIO/DPO, CapEx %, Interest rate, Tax rate.
243
244**Scenario Audit Checks:**
245- Toggle switches all statements
246- BS balances in all scenarios
247- Cash ties out in all scenarios
248- Hierarchy holds: Upside > Base > Downside for NI, EBITDA, FCF, margins
249- Credit metrics: Upside < Base < Downside for leverage (inverted — lower is better)
250
251### Step 9: Margin Analysis (Optional — Only if Prompted)
252
253> Only perform margin analysis if prompted by the user or if the template explicitly requires it. If no prompt is given, skip this section.
254
255| Margin | Formula | What It Measures |
256|--------|---------|------------------|
257| Gross Margin | Gross Profit / Revenue | Pricing power, production efficiency |
258| EBITDA Margin | EBITDA / Revenue | Core operating profitability |
259| EBIT Margin | EBIT / Revenue | Operating profitability after D&A |
260| Net Income Margin | Net Income / Revenue | Bottom-line profitability |
261
262Display margin percentages directly below each profit line item on the IS tab.
263
264### Step 10: Credit Metrics (Optional — Only if Prompted)
265
266> Only perform credit analysis if prompted by the user or if the template explicitly requires it. If no prompt is given, skip this section.
267
268| Metric | Formula | What It Measures |
269|--------|---------|------------------|
270| Total Debt / EBITDA | Total Debt / LTM EBITDA | Leverage multiple |
271| Net Debt / EBITDA | (Total Debt - Cash) / LTM EBITDA | Leverage net of cash |
272| Interest Coverage | EBITDA / Interest Expense | Ability to service debt |
273| Debt / Total Cap | Total Debt / (Total Debt + Equity) | Capital structure |
274| Debt / Equity | Total Debt / Total Equity | Financial leverage |
275| Current Ratio | Current Assets / Current Liabilities | Short-term liquidity |
276| Quick Ratio | (Current Assets - Inventory) / Current Liabilities | Immediate liquidity |
277
278**Credit Metric Hierarchy Checks:**
279- Leverage: Upside < Base < Downside (lower is better)
280- Coverage: Upside > Base > Downside (higher is better)
281- Liquidity: Upside > Base > Downside (higher is better)
282
283If debt covenants are known, add explicit compliance checks comparing actual metrics to covenant thresholds.
284
285### Step 11: Build Audit/Checks Dashboard
286
287Consolidate all validation checks into the Checks/Audit tab:
288
289**Check Categories:**
290
2911. **Currency Consistency** — Currency identified and documented in Assumptions; all tabs use consistent currency symbol and scale; units row matches model currency
2922. **Balance Sheet Integrity** — Assets = Liabilities + Equity for each period
2933. **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
2944. **Retained Earnings** — Prior RE + NI + SBC - Dividends = Ending RE; show component breakdown for debugging
2955. **Working Capital** — AR, Inventory, AP tie to BS; DSO, DIO, DPO reasonability checks
2966. **Debt Schedule** — Total Debt ties to BS; Interest calculation ties to IS
2977. **Equity Financing** — Equity issuance ties to BS Common Stock/APIC increase; Year 0 equity tie-out
2988. **NOL Schedule** — Beginning NOL = 0; NOL increases only on losses; DTA ties to BS; NOL utilization ≤ 80% of EBT; non-negative balance
2999. **Scenario Hierarchy** — Absolute metrics: Upside > Base > Downside; Margins: Upside > Base > Downside; Credit metrics: Upside < Base < Downside for leverage
30010. **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
30111. **Credit Metric Thresholds** — Flag metrics as Green/Yellow/Red based on covenant thresholds
302
303**Master Check Formula:**
304- If all sections pass → "✓ ALL CHECKS PASS"
305- If any section fails → "✗ ERRORS DETECTED - REVIEW BELOW"
306
307### Step 12: Final Review and Recalculate
308
3091. Toggle through all scenarios to verify checks pass in each case
3102. Review all #REF!, #DIV/0!, #VALUE!, and #NAME? errors and resolve or document
3113. Confirm all input cells have been populated (search for placeholder values)
3124. Verify units are consistent across all tabs
3135. Save a clean version before making any additional modifications
3146. Recalculate before delivery: open the `.xlsx` in Excel, force a full calculate, and save. This folder does not ship a recalc helper.
315
316## Formatting — Professional Blue/Grey Palette
317
318**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.
319
320| Element | Fill | Font |
321|---|---|---|
322| Section headers (IS / BS / CF titles) | Dark blue `#1F4E79` | White bold |
323| Column headers (FY2024A, FY2025E, etc.) | Light blue `#D9E1F2` | Black bold |
324| Input cells (historicals, assumption drivers) | Light grey `#F2F2F2` or white | Blue `#0000FF` |
325| Formula cells | White | Black |
326| Cross-tab links | White | Green `#008000` |
327| Check rows / key totals | Medium blue `#BDD7EE` | Black bold |
328
329**That's 3 blues + 1 grey + white.** If the template has its own color scheme, follow the template instead.
330
331Font color signals *what* a cell is (input/formula/link). Fill color signals *where* you are (header/data/check).
332
333## Data Sources — MCP First, Web Fallback
334
335- **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.
336- **Otherwise**, fall back to:
337 - `web_search` / `web_extract` against SEC EDGAR (`https://www.sec.gov/cgi-bin/browse-edgar`) for US filings
338 - Company IR pages for press releases, earnings decks
339 - `browser_navigate` for interactive data portals
340 - User-provided data (explicitly ask when the context doesn't have it)
341- **Never fabricate.** If a multiple, precedent, or filing number can't be sourced, flag the cell as `[UNSOURCED]` and surface it to the user.
342
343For SEC filings extraction guidance, load `references/sec-filings.md`.
344
345## Pitfalls
346
347### Hardcodes Break Models Silently
348- **Pitfall:** Computing a value in Python and writing the result to a cell instead of writing the formula.
349- **Impact:** The model won't flex when scenarios toggle or assumptions change. Every downstream integrity check breaks silently.
350- **Fix:** Always write formula strings in openpyxl. The ONLY hardcoded numbers should be historical actuals and assumption drivers.
351
352### Circular Reference from Interest Expense
353- **Pitfall:** Interest → Net Income → Cash → Debt Balance → Interest creates a circular reference.
354- **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.
355
356### Mixed Absolute/Relative References
357- **Pitfall:** Incorrect reference types cause wrong results when formulas are copied across periods.
358- **Fix:** Use `$` for absolute references to assumption cells (e.g., `Assumptions!$B$5`), relative references for period-to-period calculations.
359
360### Deleting Rows/Columns Without Checking Dependencies
361- **Pitfall:** Deleting a row or column breaks formulas across all tabs that reference it.
362- **Fix:** Never delete rows/columns without checking for formula dependencies across all tabs. Use Trace Dependents first.
363
364### Sign Convention Errors in Cash Flow
365- **Pitfall:** Increase in an asset (e.g., AR) shown as positive instead of negative (use of cash).
366- **Fix:** Follow the sign convention table above. Increase in asset = use of cash = negative. Increase in liability = source of cash = positive.
367
368### NOL Utilization Exceeding 80% Limit
369- **Pitfall:** Utilizing more than 80% of EBT for NOL carryforward (post-2017 federal limitation).
370- **Fix:** Cap NOL utilization at 80% of EBT. Add an explicit check in the NOL schedule.
371
372### Populating Entire Model End-to-End Without User Checkpoints
373- **Pitfall:** Building the complete model and presenting it at once, only to find fundamental errors in the IS that cascade through BS and CF.
374- **Fix:** Break at each statement. Show the work. Catch errors early. Follow the verify-with-user checkpoints after each step.
375
376### Inconsistent Units Across Tabs
377- **Pitfall:** One tab uses thousands, another uses millions, creating order-of-magnitude errors.
378- **Fix:** Verify units before entering data. Check the units row on every tab. Document the model's currency and scale in Assumptions.
379
380## Verification
381
382### Formula Integrity Checks
383
384| Check Type | Method |
385|------------|--------|
386| Trace precedents | Select a formula cell → Formulas → Trace Precedents to verify it references correct inputs |
387| Trace dependents | Verify key inputs flow to expected output cells |
388| Evaluate formula | Use Formulas → Evaluate Formula to step through complex calculations |
389| Check for hardcodes | Projection formulas should reference assumptions, not contain hardcoded values |
390| Test with known values | Input simple test values to verify formulas produce expected results |
391| Cross-tab consistency | Ensure the same formula logic applies across all projection periods |
392| Find inconsistent formulas | Use Ctrl+\ to find differences across columns |
393
394### Cross-Statement Integrity Checks
395
396Run these checks after model completion. All must equal zero:
397
398| Check | Formula | Expected Result |
399|-------|---------|-----------------|
400| Balance Sheet Balance | `=Assets - Liabilities - Equity` | 0 |
401| Cash Tie-Out | `=CF_Ending_Cash - BS_Cash` | 0 |
402| Net Income Link | `=IS_Net_Income - CF_Starting_Net_Income` | 0 |
403| Retained Earnings | `=Prior_RE + NI + SBC - Dividends - BS_Ending_RE` | 0 |
404| Equity Financing | `=ΔCommon_Stock_APIC - Equity_Issuance_CFF` | 0 |
405
406### Recalculation Verification
407
408Open 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.
409
410### Quick Debug Workflow
411
412When Master Status shows errors:
4131. Scroll to find red-highlighted sections on the Checks tab
4142. Identify which check category has failures
4153. Navigate to source tab to investigate
4164. Fix the underlying issue
4175. Return to Checks tab to verify resolution
4186. Re-open in Excel and force a full calculate
419
420### Final Delivery Checklist
421
422- [ ] All historical data matches source documents
423- [ ] All projection cells are formulas (no hardcodes)
424- [ ] BS balances for every period (Assets = L + E)
425- [ ] CF ending cash = BS cash for every period
426- [ ] RE roll-forward ties for every period
427- [ ] All scenarios toggle correctly and pass checks
428- [ ] No #REF!, #DIV/0!, #VALUE!, #NAME? errors
429- [ ] Units consistent across all tabs
430- [ ] Workbook fully recalculates and opens in Excel
431- [ ] All `[UNSOURCED]` cells flagged to user
432
433## Related Skills
434
435- `excel-author` — Cell coloring conventions, formula patterns, recalc script, named ranges, sensitivity tables
436- `dcf-model` — DCF valuation built on top of 3-statement model outputs
437- `lbo-model` — LBO transaction model with debt waterfall
438- `pptx-author` — Presentation output of model results
439
440## Attribution
441
442Adapted 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