Financial ModelingIntermediate8 min read

How to Build a Linked 3-Statement Excel Template

Build a fully linked 3-statement Excel model from scratch: P&L, Balance Sheet, and Cash Flow wired together so one assumption change flows through all three.

This guide walks you through building a fully linked three-statement financial model in Excel from a blank workbook, so a single change to a revenue growth rate or DSO assumption automatically cascades through the P&L, Balance Sheet, and Cash Flow Statement. No more manually patching numbers across tabs after every board revision. ### Why a Linked Financial Statements Template Beats Manual Updates Most analysts start with three separate tabs and wire them together after the fact. That works until it doesn't, usually at 11pm before a board pack deadline when the CF statement doesn't tie. Building the linkage architecture upfront takes 30 extra minutes and saves hours of reconciliation downstream.

What You'll Need

Excel 2016 or later (XLOOKUP available in 2019+; INDEX/MATCH used here for broader compatibility)
Familiarity with absolute vs. relative references and named ranges
A basic understanding of how net income flows into retained earnings and how non-cash charges flow into operating cash flow
Source data: revenue base, cost structure, working capital days, CapEx rate, debt schedule (or placeholders)

Step-by-Step Guide

Design the Tab Architecture for a Linked 3-Statement Excel Model

Before writing a single formula, map your tab structure. Every reference direction matters: Assumptions feeds everything, the P&L feeds net income to the Balance Sheet, and the Balance Sheet feeds working capital movements to the Cash Flow Statement. Circular references (typically in the revolver or interest) get resolved last.

Create 6 tabs in this order: Assumptions, P&L, BalSheet, CashFlow, Debt, Checks
Color-code tabs: blue for inputs (Assumptions), white for statements, red for Checks
Set column A as row labels, column B as a units/note column, and columns C onward as fiscal years (FY2024, FY2025, FY2026, FY2027, FY2028)
Freeze row 1 and column A on every statement tab so headers stay visible during navigation
Add a version cell in Assumptions!B1 formatted as v1.0 | May 2026 - board packs get revised 4-5 times and version control prevents sending the wrong file

Pro Tip

Name your year columns with a header row formula like =DATE(Assumptions!$C$2,12,31) formatted as "YYYY" so the entire model shifts when you change the base year in one cell.

Build the Assumptions Tab

The Assumptions tab is the only place hardcoded numbers belong. Every driver lives here. Statements pull from it; nothing pushes back to it (except actuals, handled separately).

Driver	Label	FY2025A	FY2026E	FY2027E	FY2028E
Revenue growth	`rev_growth`	14.2%	12.5%	11.0%	9.5%
Gross margin	`gm_pct`	38.5%	38.5%	39.0%	39.5%
EBITDA margin	`ebitda_pct`	21.2%	21.5%	22.0%	22.5%
DSO (days)	`dso`	47	45	45	44
DIO (days)	`dio`	30	28	27	27
DPO (days)	`dpo`	34	32	33	33
CapEx % revenue	`capex_pct`	3.4%	3.2%	3.0%	2.8%
D&A % revenue	`da_pct`	2.1%	2.0%	1.9%	1.9%
Tax rate	`tax_rate`	26%	26%	26%	26%

Name every driver row using Excel's Name Manager (Formulas > Name Manager) scoped to workbook. For example, name the row range for DSO dso_row - this makes statement formulas readable at a glance
Keep actuals (FY2025A) in a visually distinct column (light gray fill) to prevent accidental edits
Add a Base Revenue cell: Assumptions!C5 = 18400000 ($18.4M) - all revenue formulas multiply off this, not each other in a chain

Pro Tip

Put a "Scenario" toggle in Assumptions!B2 (Base / Bull / Bear) and use IF or CHOOSE to swap entire assumption rows. Saves building three separate models for the same deal.

Build the P&L Tab

With assumptions in place, the P&L tab becomes mostly arithmetic. Keep the formula pattern consistent across every row so auditing is fast.

// FY2026E Revenue (Assumptions tab base × (1 + growth))
C5 = Assumptions!C5 * (1 + Assumptions!C8)    // $18.4M × 1.125 = $20.7M

// Gross Profit
C7 = C5 * Assumptions!C9                        // $20.7M × 38.5% = $7.97M

// COGS (derived, not a direct input)
C6 = C5 - C7                                    // $12.73M

// EBITDA
C10 = C5 * Assumptions!C10                      // $20.7M × 21.5% = $4.45M

// D&A
C11 = C5 * Assumptions!C15                      // $20.7M × 2.0% = $414K

// EBIT
C12 = C10 - C11                                 // $4.04M

// Interest Expense (pulls from Debt tab)
C13 = -Debt!C18                                 // negative convention

// EBT
C14 = C12 + C13

// Tax
C15 = -MAX(C14 * Assumptions!C16, 0)            // zero-floor to avoid negative tax

// Net Income
C16 = C14 + C15

Use consistent sign convention throughout: revenues positive, costs and expenses positive (shown as deductions in the formula, not as negative hardcodes)
Build SG&A and R&D as separate line items using ebitda_pct minus gm_pct math - lenders and IC members always ask for the split
Cross-check: =C10/C5 next to the EBITDA line should equal Assumptions!C10 exactly - if it doesn't, your rounding is off

Pro Tip

Format the P&L with alternating light gray rows for every subtotal line (Gross Profit, EBITDA, EBIT, EBT, Net Income). Reviewers scan for these anchors first.

Build the Balance Sheet Tab

The Balance Sheet is where most linked models fall apart. AR, Inventory, and AP are calculated from the working capital days on the Assumptions tab, not entered manually.

// Accounts Receivable (DSO-based)
C5 = ('P&L'!C5 / 365) * Assumptions!C12        // ($20.7M / 365) × 45 = $2.56M

// Inventory (DIO-based, uses COGS)
C6 = ('P&L'!C6 / 365) * Assumptions!C13        // ($12.73M / 365) × 28 = $977K

// Accounts Payable (DPO-based, uses COGS)
C20 = ('P&L'!C6 / 365) * Assumptions!C14       // ($12.73M / 365) × 32 = $1.12M

// Retained Earnings (prior year + net income - dividends)
C30 = D30 + 'P&L'!C16 - Assumptions!C22        // D30 = prior year RE

Build the full PP&E roll: Opening PP&E + CapEx - D&A = Closing PP&E. CapEx pulls from ='P&L'!C5 * Assumptions!C15 (revenue × CapEx %)
The revolver (short-term debt) is a plug - come back to it after Cash Flow is built
Add a check row at the bottom: =C_TotalAssets - C_TotalLiabEquity. It should return exactly zero. If it doesn't, the model doesn't tie and nothing downstream is trustworthy

Pro Tip

Lock the retained earnings opening balance cell (the FY2025A column) and link it to the audited financials cell. A future-year RE that chains off a wrong opening balance contaminates every subsequent year silently.

Build the Cash Flow Statement

The Cash Flow Statement derives entirely from the P&L and Balance Sheet changes. Nothing gets hardcoded here except items with no upstream driver (like one-time payments).

// Operating Cash Flow section
// Start with Net Income
C5 = 'P&L'!C16                                  // $2.38M

// Add back D&A (non-cash)
C6 = 'P&L'!C11                                  // $414K

// Change in AR (increase in AR = use of cash, so negative)
C7 = -(BalSheet!C5 - BalSheet!D5)               // -(2.56M - 2.37M) = -$190K

// Change in Inventory
C8 = -(BalSheet!C6 - BalSheet!D6)

// Change in AP (increase in AP = source of cash, so positive)
C9 = BalSheet!C20 - BalSheet!D20

// Total CFO
C11 = SUM(C5:C10)

// Investing Cash Flow section
C14 = -('P&L'!C5 * Assumptions!C15)            // CapEx outflow: -$662K

// Financing Cash Flow section
C17 = -(Debt!C12 - Debt!D12)                    // Net debt repayment

// Net Change in Cash
C20 = C11 + C14 + C17

// Closing Cash
C22 = BalSheet!D25 + C20                         // Prior year cash + change

Verify C22 equals BalSheet!C25 (cash on the Balance Sheet). This is the second tie-out check - if it fails, find the delta before moving forward
Interest paid lives in CFO under GAAP (US) but many FP&A teams show it in CFF for comparability with IFRS - pick one and note it in the tab header

Wire the Three Financial Statements Together in Excel

With all three tabs built, confirm the linkages are intact and directionally correct. The wiring order is: Assumptions → P&L → Balance Sheet → Cash Flow → back to Balance Sheet (cash plug).

Trace Assumptions!C8 (revenue growth) through: it should change P&L!C5, which changes BalSheet!C5 (AR), BalSheet!C6 (Inventory), BalSheet!C20 (AP), and CashFlow!C7/C8/C9 (working capital movements)
The revolver plug on the Balance Sheet closes the loop: Revolver = Prior Revolver + Revolver Drawdown where Revolver Drawdown = -MIN(CashFlow!C20 + BalSheet!D25 - Assumptions!C_MinCash, 0). This formula draws the revolver only when projected cash falls below the minimum cash floor
Check that changing Assumptions!C12 (DSO from 45 to 50 days) increases AR by ~$284K, reduces CFO by the same amount, reduces closing cash by ~$284K, and increases the revolver by ~$284K - if all four move together, the three-statement linkage is working

Pro Tip

Add a "Delta Test" row on the Checks tab. Change one assumption by a fixed amount (say, revenue growth from 12.5% to 13.5%), verify the cascade, then Ctrl+Z back. Do this before sending any model externally.

Build a Checks Tab for the Linked Financial Model

A model without checks is a liability. The Checks tab catches the two failure modes that actually happen: Balance Sheet out of balance, and Cash Flow Statement closing cash not matching Balance Sheet cash.

// Balance Sheet check (should = 0)
C5 = BalSheet!C_TotalAssets - BalSheet!C_TotalLiabEquity

// Cash tie-out (should = 0)
C6 = BalSheet!C25 - CashFlow!C22

// Retained Earnings roll check (should = 0)
C7 = BalSheet!C30 - (BalSheet!D30 + 'P&L'!C16 - Assumptions!C22)

// Revenue growth check (should = 0)
C8 = 'P&L'!C5 - ('P&L'!D5 * (1 + Assumptions!C8))

Format each check cell with conditional formatting: green fill if =0, red fill if <>0. The Checks tab should be all green before the model leaves your hands
ModelMonkey can scan all 4 checks and explain discrepancies in plain English - useful when a junior analyst has been editing the model and you need to diagnose which linkage broke
Add a SUMPRODUCT across all check cells: =SUMPRODUCT(ABS(C5:C8)) - if this returns anything other than 0, the model has at least one error and you'll see it the moment you land on the tab

Pro Tip

Protect the Checks tab (Review > Protect Sheet, no password needed) so it can't be accidentally edited. A check cell that someone has hardcoded to zero is worse than no check at all.

Your Linked 3-Statement Excel Template Is Ready

At this point you have a fully linked three-statement model: one change to revenue growth (say, dropping from 12.5% to 9.0%) cascades through $20.7M projected revenue, adjusts gross profit, EBITDA, net income, AR/Inventory/AP balances, operating cash flow, and closing cash - all without touching a single statement tab directly.

The architecture described here scales to any deal. Add a Returns tab for MOIC/IRR, a DCF tab for terminal value (your WACC, your exit multiple, your FCFF build), or a Sensitivity tab for a 5×5 revenue/margin matrix. The three-statement core doesn't change.

As of May 2026, this is the same tab structure used across bulge bracket IB and FP&A teams building board packs, bank syndicate models, and IC memos. The specifics vary; the wiring doesn't.

If you want to skip the build-from-scratch step, Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel and can scaffold the tab architecture, assumptions table, and linkage formulas from a plain-English description of your business.

Wrapping Up

Frequently Asked Questions

How do I handle circular references in a linked three-statement model?

The most common circular reference comes from interest expense: interest depends on debt balance, debt balance depends on the revolver, and the revolver depends on cash, which depends on interest. The clean solution is to model interest on the prior period's average debt balance (`= (OpeningDebt + ClosingDebt) / 2 * InterestRate`) with iterative calculation enabled in Excel (File > Options > Formulas > Enable iterative calculation, max iterations 100). Most investment banking models use prior-period debt to avoid the circularity entirely.

What's the correct sign convention for a linked model?

Pick one convention and apply it everywhere: either all income statement items are positive (revenues positive, costs positive as deductions) or use the accountant's convention (revenues positive, costs negative). The more common FP&A convention is all-positive with costs shown as line-item deductions. Cash outflows on the Cash Flow Statement are negative. The Balance Sheet is always positive. Whatever you choose, document it in a cell comment on the P&L tab header.

How many years should a linked three-statement template cover?

Standard is 5 years of projections plus 2-3 years of historical actuals. LBO models often use 5+1 (exit year). DCFs typically use 5 years of explicit forecast plus a terminal value. Build the template for 5 projection years by default - adding columns is trivial, but restructuring a 3-year model to 7 years after the fact breaks relative references everywhere.

Why doesn't my Cash Flow Statement tie to the Balance Sheet cash?

The most common cause is a missing or double-counted item in working capital changes. Check that every current asset and current liability that changed between periods has a corresponding line in CFO. PP&E changes should flow through investing activities, not CFO. The second most common cause is dividends or equity issuance in the Balance Sheet that isn't reflected in CFF. Run the `=BalSheet!C25 - CashFlow!C22` check cell and trace the delta line by line.

Can I use this template for both GAAP and IFRS reporting?

The core structure works for both, but 3 items differ materially: interest paid (CFO under GAAP, CFO or CFF under IFRS), lease obligations (off-balance-sheet under old GAAP, on-balance-sheet under IFRS 16/ASC 842), and R&D capitalization (expensed under US GAAP, can be capitalized under IAS 38). Add a "Reporting Standard" toggle in the Assumptions tab and use `IF` logic on the affected lines if you need both presentations from the same model.