Three-Statement Modeling That Survives Scrutiny

You inherit the file on day three. It's 80MB. Sixteen tabs. Three different shades of yellow that nobody on the team can explain. There's a hardcoded 1.07 buried in the middle of a SUMIFS on the revenue tab, and when you ask the senior analyst what it is, she says, "I think Mark put that in for the China deal in 2023." Mark left in 2024.

You change revenue growth from 18% to 19% to test a scenario. Net income goes negative in year 2. The cash flow statement shows $4M of negative cash and the model errors out. The balance sheet is off by $873,412.

Most "3-statement models" inside operating companies are this. And the worst part isn't that they're ugly. It's that the CFO is making decisions with them. A model that breaks under one VP's question is worse than no model, because it gives leadership false confidence and you take the blame when reality diverges from the deck.

This is the playbook for building, or rebuilding, a 3-statement model that holds up. Structure beats sophistication. A simple model that always balances beats a clever model that breaks under one assumption flip.

Structural rules that make the model auditable

Before you write a single formula, set the rules of the file. These aren't aesthetic preferences. They're the difference between a model that scales and one that becomes radioactive in nine months.

One tab per driver. Revenue on its own tab. Headcount on its own tab. Opex, capex, debt, working capital, taxes — each gets its own sheet. The three statements (IS, BS, CF) are output tabs. They link in. They never compute.

If you find yourself building a revenue assumption inside the income statement tab, stop. You're creating a tab where future-you has to scroll through a P&L to find a 3% growth rate. That's how 80MB inheritances get born.

One direction of flow. Left to right, top to bottom, always. Driver tabs feed statement tabs. Statement tabs feed the dashboard. Nothing references upstream. If your CF tab pulls from your dashboard, you've created a circular dependency that will haunt you.

A clean rule: open the file, look at the tab order. If you read tabs left to right, you should see Inputs → Drivers → Statements → Dashboard → Checks. Never reverse. Never skip.

Color-code religiously.

• Blue for hardcoded inputs (the only cells anyone is allowed to type a number into)
• Black for formulas inside the same tab
• Green for cross-tab links

No exceptions, no "I'll fix it later." The day you let a black formula reference another tab is the day you lose the ability to audit. Six months later, you won't remember which black numbers were really links, and you'll spend a Saturday tracing precedents.

One row, one purpose. A revenue row computes revenue. A growth-rate row computes a growth rate. Don't mix. The temptation to write =B14*(1+0.18) inside a revenue row is real because it saves a row. It also hides the assumption from the next analyst, who will spend an hour finding it.

These four rules (one driver per tab, one-way flow, three colors, one row per purpose) solve roughly 70% of the problems in inherited models. They're boring. They work.

The integrity check: three formulas, one tab, no negotiation

A 3-statement model is integrated when net income on the IS reconciles to the change in retained earnings on the BS, when the ending cash on the CF equals cash on the BS, and when assets equal liabilities plus equity to the penny. If any of those break, the model is wrong, and any decision made on it is suspect.

Build a tab called _Checks (the underscore floats it to the front in alphabetical sort). Put three formulas at the top, with conditional formatting that turns the cell bright red when the value isn't zero or TRUE.

Check 1: Balance sheet balances.

=ROUND(SUM(bs_total_assets) - SUM(bs_total_liab) - SUM(bs_total_equity), 2)

This must equal zero, every period, every scenario. If it doesn't, you have a plug somewhere you didn't intend, or a sign error on a working-capital item, or you're double-counting a debt issuance. Don't move on until this is zero.

Check 2: Cash flow ties to the balance sheet.

=ROUND(cf_ending_cash - bs_cash, 2)

The ending cash from your cash flow statement must equal the cash line on your balance sheet for the same period. If it doesn't, your CF is missing something, usually a non-cash adjustment, a deferred tax movement, or a sign on a financing activity.

Check 3: Income statement reconciles to retained earnings.

=ROUND(bs_retained_earnings_end - bs_retained_earnings_beg - is_net_income + bs_dividends, 2)

Net income for the period plus beginning retained earnings minus dividends should equal ending retained earnings. If it doesn't, you've either got a stranded equity adjustment (a stock buyback that's hitting the wrong line) or your IS isn't fully flowing into equity.

Run all three on every column, every scenario. The _Checks tab should look like a stoplight: all green TRUE, no red. If a CFO opens your model and sees green TRUE, FALSE, FALSE flashing red, that's the entire conversation now. You won't get to walk through the EBITDA bridge. Save yourself the meeting.

Naming conventions: future you is the user

The single highest-leverage habit in modeling is named ranges. Instead of 'Assumptions'!B14, you reference rev_growth_y1. Instead of 'Working Capital'!F22, you write wc_dso_target.

The payoff: when you read a formula six months later, you can understand it without bouncing between tabs. =is_revenue_y2 * gm_target_y2 - opex_total_y2 reads like a sentence. ='IS'!C8*'Assumptions'!B16-'Opex'!D14 reads like noise.

Use consistent prefixes:

• is_ for income statement lines
• bs_ for balance sheet
• cf_ for cash flow
• dr_ for drivers
• wc_ for working capital
• kpi_ for output metrics

Then suffix with the metric and period: is_revenue_y1, bs_inventory_q4, dr_headcount_eng_y2. The naming reads like an API. Anyone who picks up the model can guess the name of the cell they want.

One more rule: never name a range with a hardcoded number in it. tax_rate_25 is a trap because the day the rate moves to 21%, the name lies. Use tax_rate_federal and let the value live in a blue input cell.

Sensitivity tables that earn their place

Most sensitivity tables in inherited models are vanity. A two-variable data table on revenue growth versus pricing, output: revenue. Cool. The CFO already knows higher prices and higher growth produce more revenue. You've added a tab and 200KB to the file.

Useful sensitivities run on the metrics the CFO will be asked about in the board meeting. EBITDA margin under three pricing scenarios and three CAC scenarios. Free cash flow under different DSO assumptions. Cash runway under headcount-growth scenarios crossed with collections speed.

A practical setup: pick the three KPIs that go on the board deck (EBITDA margin, FCF, cash runway). For each, build a two-variable sensitivity. Put the variables on the side of each table that match the levers the CEO actually pulls. CAC and conversion rate, not revenue and revenue. Headcount-growth and average salary, not opex and opex.

If the CFO can look at one table and say, "If pricing slips 5% and CAC goes up 20%, are we still hitting the FCF guide?" that's a useful sensitivity. If she has to compute the answer in her head from the table, you built it wrong.

Place sensitivity tables on a Sensitivity tab, one table per KPI, clearly labeled. Don't bury them inside statement tabs.

The circular ref trap

Here's the loop every analyst hits eventually: interest expense depends on average debt balance. Average debt balance depends on the cash balance (because if you have cash, you'd pay down debt). Cash balance depends on net income. Net income depends on interest expense.

Excel sees this and either turns on iterative calculation, which sometimes converges and sometimes doesn't, or throws a #REF! and the model breaks.

You have two clean options. Pick one, document the choice in the model README, and stop relitigating it.

Option A: Turn on iterative calculation. File → Options → Formulas → Enable iterative calculation, max iterations 100, max change 0.001. This works, but it's fragile. The model will sometimes show different numbers when the file opens versus when you press F9. Auditors will ask. You'll spend twenty minutes explaining circular references in a diligence call.

Option B: Break the circular with a one-period lag. Compute interest expense based on prior-period debt balance, not current. The math is slightly less precise (you're losing one period of interest accuracy), but the model is deterministic. Open it, recalculate, get the same answer every time.

For operating models inside companies under $500M revenue, Option B wins. The accuracy loss is rounding error against the assumption-uncertainty in the rest of the model. For LBO models or valuation work where the interest schedule matters, Option A may be necessary.

Whichever you pick, write it in a README tab, in plain English: "Interest expense uses prior-period debt balance to avoid circular reference. See cell cf_interest_y1 formula."

Version control and the change log that saves your job

Filename convention: Model_v3.2_2026-04-26_VH.xlsx. Major version bumps for structural changes. Minor bumps for assumption updates. Date in ISO format so files sort chronologically. Initials of the last editor.

Inside the file, a _ChangeLog tab with five columns: Date, Editor, Version, What Changed, Why.

A sample row:

When you're sitting in a diligence room and the buyer's analyst asks why Q3 forecast revenue moved $1.2M between version 2.8 and 3.0, you open the change log and read the row. You don't search your inbox for the email Mark sent you in February. You don't say "I think." You read the row.

This single tab will save your job at least once in your career. Build it now, before you need it.

When to graduate to a planning tool

Excel is the right tool for a 3-statement model — until it isn't. The signals you've outgrown it:

• You're rebuilding the model substantially every quarter because the prior version got too crufty to extend
• More than two people regularly touch the file and you can't trust merged versions
• A scenario change takes more than an hour to run end-to-end, including breaking and refixing checks
• Your file is over 50MB or has more than 25 tabs
• You can't link actuals from the GL automatically, and every month-end is a copy-paste exercise that takes a day

When two or more of those are true, it's time to look at a real planning tool. The viable options for FP&A teams in mid-size companies:

Adaptive Planning (Workday). Best fit if you're already in the Workday ecosystem (HCM, Financials). Strong on driver-based modeling, weak on flexibility for highly custom calc logic.

Cube. Lighter weight, sits on top of Excel rather than replacing it. Best for teams that don't want to retrain on a new modeling paradigm but need version control, multi-user editing, and actuals integration.

Pigment. Newer, more flexible. Strong on multi-dimensional modeling and scenario branching. Better for teams with complex business logic (multiple SKUs, geographies, channels) where Excel's row-and-column structure breaks down.

The decision criteria, in order of weight:

1. How custom is your modeling logic? If it's standard FP&A (driver-based revenue, headcount-driven opex), Adaptive or Cube is fine. If you have unusual logic (project-based revenue, complex consolidations, deep multi-currency), look at Pigment.
2. Where does your data live? If actuals are in NetSuite, Sage Intacct, or QuickBooks, all three integrate. If you're on a homegrown ERP, integration cost will dominate the decision.
3. How many seats and what budget? Cube starts around $2,500/month. Adaptive and Pigment usually land in the $30-80K/year range for a mid-size FP&A team.
4. How willing is your team to retrain? Cube has the smallest learning curve. Adaptive is moderate. Pigment requires real investment in retraining and rebuilding the model in a new paradigm.

Don't graduate prematurely. A clean Excel model with the discipline above will serve a 50-person FP&A function. The trigger isn't ambition; it's pain. When the pain of staying in Excel exceeds the pain of the migration, move.

The bar

A 3-statement model that survives scrutiny is one any analyst on the team can pick up, change one input, see the checks stay green, and trust the output. That's the bar. Not elegance. Not cleverness. Trust.

You build it by making structure boring and explicit. One tab per driver. One direction of flow. Three colors. Three integrity checks on a _Checks tab. Named ranges over cell references. Documented circular-reference handling. A change log that lets diligence questions get one-line answers.

Do that, and the next analyst who inherits your file won't curse your name. They'll change one input, watch the checks stay green, and get on with their job.

That's the model that survives scrutiny.

Learn More

• The Financial Analyst's First 90 Days
• Variance Analysis That Tells a Story
• Building the Monthly Close Pack
• Financial Analyst Job Description Template