Most 3FS models in Google Sheets break in one of 3 places: retained earnings doesn't tie, the cash flow statement is partially hardcoded, or the circular reference from the debt schedule quietly produces wrong numbers. This article covers how to build it right.
The 4 Linkages That Actually Matter in a 3FS Model
The income statement, balance sheet, and cash flow statement share exactly 4 structural linkages. Every other number in the model is either a standalone input or a formula within one statement. Get these 4 right and the model closes; miss one and you're back-solving by hand.
| Linkage | Source tab | Destination tab | What flows |
|---|---|---|---|
| Net income → retained earnings | P&L | Balance Sheet | =P&L!C45 into beginning RE + current period NI |
| D&A → operating cash flow | P&L | Cash Flow | =P&L!C28 as add-back in CFO section |
| CapEx → PP&E | Cash Flow | Balance Sheet | =Cash_Flow!C12 reduces ending PP&E net of D&A |
| Ending cash → cash & equivalents | Cash Flow | Balance Sheet | =Cash_Flow!C55 into current assets |
The retained earnings linkage is where most people introduce a hardcode. They'll manually key in beginning RE rather than pulling it from the prior period's balance sheet. That breaks the model the moment you add a column.
The correct formula in a multi-year model:
// Balance Sheet, cell E14 (Year 2 retained earnings)
// Prior period closing RE + current year net income - dividends paid
='Balance Sheet'!D14 + 'P&L'!E45 - Assumptions!$C$18
And the cash balance tie-out — the one that tells you immediately whether the model balances:
// Balance Sheet check cell (should equal zero)
='Balance Sheet'!E8 - 'Cash Flow'!E55
If that cell is nonzero, something in linkages 3 or 4 is broken.
The 3FS Model Circular Reference Problem
The debt schedule creates a genuine circular reference. Interest expense depends on the average debt balance. The average debt balance depends on debt paydown in the period. Debt paydown depends on free cash flow. Free cash flow depends on net income. Net income depends on interest expense.
This isn't a formula error — it's structural. Google Sheets handles it with iterative calculation (File → Settings → Calculation → Iterative calculation, max iterations 100, threshold 0.001). As of April 2026, Google Sheets iterative calculation is stable for this use case; it converges in 3-5 passes for a standard term loan schedule.
The interest expense formula with a revolver or term loan:
// P&L interest expense for a $18.4M term loan at 6.5%
// Uses average of beginning and ending balance to handle partial paydown
=AVERAGE('Debt Schedule'!D8, 'Debt Schedule'!E8) * Assumptions!$B$12
Where Assumptions!$B$12 holds your 6.5% interest rate. The debt schedule itself pulls ending balance from the cash flow statement's mandatory debt repayment line. That's the circle.
Without iterative calculation enabled, Sheets returns a circular reference error and the interest line defaults to zero — which means your model shows higher NI than reality. A $1.2M misallocated interest charge on a typical mid-market deal can move EBITDA margins by 80-120bps. That matters in a board pack.
Tab Architecture for a 3FS Model in Google Sheets
A functional 3FS model needs at minimum 8 tabs:
- Assumptions — all inputs live here, nothing hardcoded downstream
- P&L — income statement with 5+ years of quarterly or annual columns
- Balance Sheet — linked to P&L and Cash Flow
- Cash Flow — indirect method, built from P&L + balance sheet changes
- Debt Schedule — amortization table feeding interest expense and ending balances
- Working Capital — AR, AP, inventory calculations feeding CFS
- Returns — IRR/MOIC analysis pulling from all three statements
- Checks — balance sheet check, cash flow check, NI tie-out in one place
The Working Capital tab is the one people skip when they're in a hurry. Skipping it means your cash flow from operations is wrong every time DSO or DPO moves. For a business with $47.3M revenue and DSO of 46 days, AR is roughly $6.0M. A 5-day DSO improvement releases ~$650K of cash — that's a meaningful line in any runway analysis and it needs to flow through automatically.
// Working Capital tab: accounts receivable
// Revenue from P&L × (DSO / 365)
='P&L'!C8 * (Assumptions!$B$6 / 365)
// Cash Flow tab: change in AR (source of / use of cash)
// Increase in AR is a use of cash (negative)
=-('Working Capital'!C5 - 'Working Capital'!B5)
The sign convention on working capital changes trips people up constantly. An increase in AR is a use of cash — it should reduce operating cash flow. An increase in AP is a source of cash — it should increase operating cash flow. Get the signs backward and your CFS shows higher free cash flow than the business actually generates.
Where AI Speeds This Up
The tedious part of a 3FS model isn't the architecture — it's reconciling why the balance sheet doesn't tie after you add a new scenario column or change the depreciation schedule. You end up tracing back through 6 tabs hunting for one formula that lost its absolute reference.
ModelMonkey handles this kind of cross-tab audit well. You can ask it to check all formulas referencing 'P&L'!C:C across the workbook, surface any that look hardcoded, and flag the balance sheet check cell. It's not writing the model for you, but it cuts the reconciliation time on a complex 3FS from 45 minutes to about 5.
The other use case is scenario columns. Adding a bear case to a 3FS model typically means duplicating 3 tabs, updating column references, and confirming the new columns still pull from the right Assumptions cells. Mechanical, repetitive, and easy to miss one.