three-statement-linter

Three-Statement Linter

This skill reviews an integrated three-statement financial model the way a static analyzer reviews source code. It checks the structural integrity that makes a model trustworthy: the balance sheet must balance in every period, the cash flow statement must reconcile to the period-over-period movements in the balance sheet and tie to the closing cash line, formulas must not hide hardcoded numbers, signs must be consistent, there must be no unintended circular references, and growth rates must be defensible. It is built for FP&A analysts, controllers, deal teams, and reviewers who need fast, repeatable QA before a model is relied upon. It works only on the files the user supplies in this session.

When to use this

• Before sending a budget, forecast, or operating model to leadership, a board, or a lender.
• During due diligence, when you receive a target's model and need to assess its mechanical reliability before trusting the numbers.
• When a model "won't balance" and you need to localize where the break is.
• When auditing a forecast supporting a going-concern, impairment, or covenant analysis.
• When inheriting someone else's model and you want a structural map before editing it.
• As a recurring QA gate after each material model change.

Inputs

Provide one of the following:

1. An exported tabular view of the model with columns Statement, Line Item, <period columns...>, where Statement is one of IS, BS, CF. See the bundled sample samples/three_statement_model.csv.
2. The Excel workbook itself (.xlsx). When given a workbook, also request the formula view (in Excel: Formulas > Show Formulas, or supply a CSV exported with formulas visible) so hardcode and broken-link checks can run on the formula text, not just values.

Useful context to ask for if not provided: the sign convention in use (are costs, liabilities, and equity entered as negatives or positives?), the first forecast period (to separate actuals from projections), and the units/currency.

How to do it

Run the checks in this order. Earlier failures often cause later ones, so report root causes first.

1. Establish conventions and parse

• Determine the sign convention from the data. Two common conventions: (a) everything positive with subtotals computed by formula, or (b) contra/credit items entered negative. In the sample, costs, liabilities, and equity are negative, so Net Income = sum(IS rows) and Assets + (negative) Liabilities + (negative) Equity = 0 at balance.
• Identify period columns and the first forecast period. Cash flow typically starts one period later than the balance sheet because it needs an opening balance.

2. Balance sheet integrity (the primary invariant)

For every period: Total Assets = Total Liabilities + Total Equity. Equivalently, under the all-signed convention used in the sample, sum(all BS rows) = 0.

• Compute the imbalance delta = Assets - (Liabilities + Equity) per period.
• Flag any period where abs(delta) > tolerance. Use a tolerance of the larger of 1 currency unit or 0.01% of total assets to absorb rounding; anything above that is a real break.
• A non-zero, slowly-growing delta usually means a flow is missing from retained earnings or a cash plug is mis-wired. A constant delta from period one often means an opening-balance error.

3. Net income to retained earnings articulation

• Verify Retained Earnings(t) = Retained Earnings(t-1) + Net Income(t) - Dividends(t).
• If RE does not roll forward by exactly net income (less dividends), the income statement is not feeding equity. This is the single most common cause of a balance-sheet break.

4. Cash flow ties to balance-sheet movements

The cash flow statement is, mechanically, the explanation of the change in every non-cash balance-sheet account. For each working-capital and long-term line, confirm the CF line equals the period-over-period BS movement with the correct sign:

• CF Change in AR = -(AR(t) - AR(t-1)) (a rise in a current asset uses cash).
• CF Change in Inventory = -(Inventory(t) - Inventory(t-1)).
• CF Change in AP = +(AP(t) - AP(t-1)) (a rise in a liability releases cash).
• CF Capex = -(PP&E gross addition); reconcile PP&E(t) = PP&E(t-1) + Capex - Depreciation (net basis).
• CF Change in Debt = Debt(t) - Debt(t-1).
• Confirm Depreciation is added back in operating cash flow and also reduces PP&E on the BS - it must appear in exactly two places with opposite effect.

5. Closing-cash tie-out

• Compute Closing Cash = Opening Cash + CFO + CFI + CFF.
• This must equal the Cash line on the balance sheet for the same period. A mismatch here, when section 4 passed, points to a missing CF line (e.g., dividends, equity issuance, or tax paid) rather than a wiring error.

6. Broken links and hardcodes (formula-text checks)

On the formula view:

• Flag any cell containing #REF!, #VALUE!, #DIV/0!, #NAME?, #N/A, or external-link paths ([Book.xlsx]) - these are broken references.
• Flag hardcodes inside formulas: a numeric literal embedded in a calculation (e.g. =B5*1.08 or =B5+250) where the constant is an unlabeled assumption. Growth and tax rates belong in clearly labeled input cells, not buried in formulas. Pure structural constants (a 12 to annualize, a sign flip *-1) are acceptable but should be noted.
• Flag inconsistent formulas across a row: if one period in a calculated row is typed as a value while its neighbors are formulas, that period was likely overwritten.

7. Sign-convention and circularity checks

• Sign errors: confirm costs reduce profit, that a current-asset increase is a cash outflow, and that financing inflows are positive. A common error is adding (rather than subtracting) the change in receivables.
• Circularity: interest expense driven off an average debt balance that itself depends on the cash sweep creates a deliberate circular reference, usually resolved with iterative calculation or a circularity switch. Flag any circular chain and check whether iterative calc is intentional and a breaker exists. Unintended circularity (a cell referencing itself through the cash plug) must be flagged as an error.

8. Growth-rate and plausibility sanity checks

• Compute period-over-period growth for revenue, gross margin %, opex as a % of revenue, and key ratios (DSO, DIO, DPO, current ratio).
• Flag margins that move more than a few points without explanation, revenue growth that is implausibly high or constant to many decimals (a sign of a single hardcoded rate), negative gross margin, or working-capital days that drift unreasonably.

Output

Produce a Markdown findings report (a "lint report") with these sections:

1. Header: model name, periods covered, units/currency, sign convention detected, date of review.
2. Grade: an overall letter grade with a one-line rationale.
   • A: balances every period, CF ties, no broken links or hidden hardcodes.
   • B: ties cleanly but minor hardcodes or style issues.
   • C: one reconciling difference or several hardcodes.
   • D: a recurring imbalance or broken link affecting reliability.
   • F: model does not balance or cash does not tie; numbers not usable.
3. Findings table with columns: ID | Severity | Check | Location (statement / line / period) | Observed | Expected | Recommended fix. Severity is Error, Warning, or Info.
4. Reconciliations: per-period balance-sheet imbalance, closing-cash tie-out, and RE roll-forward, shown as a small table so the reviewer can see the magnitude.
5. Next actions: a short ordered list, root causes first.

Optionally also emit a findings.csv with the same columns as the findings table for tracking.

Quality checks

• Confirm you compared the correct periods - cash flow movements use t minus t-1, so the first CF period has no prior balance.
• Re-derive at least one tie-out by hand to confirm your arithmetic before grading.
• Do not report a rounding difference as an error; apply the tolerance in section 2.
• Distinguish a true error (does not balance) from a style issue (hardcode that happens to be correct). Grade on reliability, not aesthetics.
• State assumptions explicitly when the sign convention or forecast start is inferred rather than given.
• Never silently "fix" the user's numbers - report findings and recommend fixes; the user owns the model.

Worked example

Using the bundled synthetic sample samples/three_statement_model.csv (fictional data; costs, liabilities, equity entered negative):

• Balance check, FY2024: Assets = 1980 + 2080 + 1820 + 5100 = 10980. Liabilities + Equity = 1240 + 3200 + 2000 + 4540 = 10980. Balances. FY2025: Assets = 2535 + 3100 + 2640 + 5980 = 14255; L+E = 1810 + 3500 + 2000 + 6945 = 14255. Balances.
• RE roll-forward: RE FY2024 = 2400 + 1357 = 3757, but the model shows 4540. Net income (1357) does not fully explain the 2140 increase, so either dividends are negative (an injection) or a flow is missing - flag as Error, RE articulation.
• Closing-cash tie-out, FY2024: Opening cash 1400 + (1357 + 720 - 580 - 620 + 340) + (-1620) + 200 = 1400 + 1217 - 1620 + 200 = 1197. The CF statement reports Closing Cash 1797 and the BS reports Cash 1980. Three different numbers - flag as Error, cash does not tie; the CFO subtotal omits a line and the BS cash is plugged independently.
• Growth sanity: revenue grows 30.0% then 37.5%; gross margin is flat at 40.0% - plausible, Info only.
• Grade: F - the model balances but cash does not tie and retained earnings does not articulate to net income, so the closing figures cannot be relied upon. Root cause to fix first: wire RE to net income and rebuild the cash flow as the explained change in balance-sheet accounts.

Disclaimer

Disclaimer: This skill is a drafting and analysis aid, not professional advice. It does not provide accounting, audit, tax, investment, or legal advice. All output must be reviewed and approved by a qualified professional before use or reliance.

---

Part of LedgerSkills - security-vetted Claude Code skills for finance teams. New skills scanned and tested weekly; subscribe on the site.