How to Build a Financial Model in Excel: A Step-by-Step 3-Statement Model

Guide · Updated July 2026 · ~9 min read

If you have ever inherited a "model" that was really a wall of hardcoded numbers, you know the pain. This guide walks through how to build a financial model in Excel the right way: a linked 3-statement model driven by a single assumptions tab, formatted so anyone can audit it, with sanity checks that catch mistakes before your CFO does.

A proper financial model in Excel does three things at once. It forecasts your income statement, projects your balance sheet, and derives cash flow — and all three stay connected, so changing one assumption ripples through the whole thing. That linkage is the entire point. Let's build it.

What a financial model in Excel actually is

A 3 statement model connects the income statement (P&L), the balance sheet, and the cash flow statement into one living system. The connections are not decoration — they are accounting identities:

• Net income from the income statement flows into retained earnings on the balance sheet and sits at the top of the cash flow statement.
• Depreciation is a non-cash expense: it reduces net income but gets added back in cash flow, and it accumulates against your fixed assets.
• Ending cash from the cash flow statement becomes the cash line on the balance sheet.
• Because Assets must equal Liabilities plus Equity, that ending cash figure is what makes the whole thing balance. If it doesn't balance, you have a bug.

Get the linkages right and the model becomes self-checking. Get them wrong and no amount of formatting will save it.

Step 1: Set up the workbook structure

Before typing a single formula, lay out your tabs. Separation of inputs, calculations, and outputs is what keeps a model maintainable.

1. Create these worksheets, left to right: Assumptions, Income Statement, Balance Sheet, Cash Flow, and Checks.
2. In each statement tab, reserve column A for spacing, column B for line-item labels, and start your periods in column D onward — one column per period (month, quarter, or year). Keep the period headers identical across all tabs so a formula in column F always means the same period everywhere.
3. Freeze the header and label area: click the first cell of your data region (say D4), then go to View > Freeze Panes > Freeze Panes. Now labels stay visible as you scroll.
4. Use one column per period and never merge cells in the data grid — merged cells break fills, sorting, and references.

The golden rule from here on: inputs live only on the Assumptions tab. Every forecast column on the other tabs contains formulas, never typed numbers. (Historical actuals are the one honest exception — they're real, so they're hardcoded, and by convention they go in blue.)

Start from real numbers: let HISAB 360 pull your actuals

The slowest, least rewarding part of the build you just started is the plumbing: creating the tabs, laying out identical period columns, and then re-keying last year's numbers cell by cell so the forecast has somewhere to grow from. If you build inside Excel on Windows and your books live in an accounting system, HISAB 360 — an AI assistant that runs inside Excel — can do that groundwork and seed the model with live data instead of a blank grid.

HISAB connects two-way to six ERPs — QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage — so it can read your actuals directly. You ask it in plain English. For example:

"Create a 3-statement model with Assumptions, Income Statement, Balance Sheet, Cash Flow and Checks tabs, monthly columns starting in column D. Pull the last 12 months of actuals from QuickBooks into the historical columns of the Income Statement and Balance Sheet."

HISAB scaffolds the five tabs with matching period headers, then pulls your P&L and balance-sheet actuals from the connected ERP and writes them into the historical columns as hardcoded inputs (blue font, per the convention below). Your first forecast period now grows off real figures — actual revenue run-rate, real accounts receivable and payable, the closing cash and net PP&E from your last close — rather than a guess. Worth knowing if you plan to key the historicals in by hand instead: QuickBooks Online won't export formulas to Excel, so every subtotal in that export is a hardcoded number you will have to rebuild as a formula anyway.

Because those actuals are sitting in the sheet, you can also let HISAB derive your first-cut drivers from them. Ask "what was my average DSO and DPO over the last 12 months?" and drop the answers onto the Assumptions tab. From there you follow exactly the steps below: the structure and the starting balances are done, and the modelling judgement — growth rates, margins, working-capital days — is still entirely yours to apply, whether or not you use the tool. Once the model is live, each month's actuals sitting beside the forecast is the raw material for budget vs actual variance analysis.

Step 2: Build the assumptions tab

The assumptions tab is the steering wheel. Group your drivers so a reviewer can find them, and give each a clean, editable cell.

Typical drivers for a small-business or startup forecast:

• Revenue drivers: starting revenue, a monthly or annual growth rate, or units × price if you sell discrete things.
• Margin drivers: COGS as a percent of revenue, or a gross margin percent.
• Operating expenses: salaries, rent, marketing — either fixed amounts or as a percent of revenue.
• Working capital: days sales outstanding (DSO) for receivables, days payable outstanding (DPO), and days inventory on hand.
• Capital and financing: planned capex, useful life for depreciation, opening debt balance, interest rate, and tax rate.

Planned capex is the one driver that usually needs justifying before it earns a cell here. Appraise the spend on its own incremental cash flows first: calculate the NPV in Excel at your cost of capital, and calculate the IRR to see what annual return the project implies. Whatever survives that test is the number you type into the capex row.

Color-code every input cell so it's obvious what's editable. The industry convention is blue font for hardcoded inputs, black for formulas, and green for links that pull from another sheet. Select your input cells, press Ctrl+1 to open Format Cells, and set the font colour. This single habit makes your model readable to anyone who has ever opened a real one.

Step 3: Build the income statement

With drivers in place, the income statement is mostly multiplication. Reference the Assumptions tab — don't retype rates.

Work top to bottom:

1. Revenue — prior period revenue × (1 + growth rate), or units × price.
2. COGS — revenue × COGS percent.
3. Gross profit — revenue − COGS.
4. Operating expenses — pull each line from Assumptions.
5. EBITDA — gross profit − operating expenses.
6. Depreciation — from your capex/depreciation schedule (below).
7. EBIT — EBITDA − depreciation.
8. Interest expense — opening debt × interest rate, from the debt schedule.
9. Pre-tax income — EBIT − interest.
10. Taxes — pre-tax income × tax rate (floor at zero if pre-tax income is negative).
11. Net income — pre-tax income − taxes.

Keep a small supporting schedule below or on the side for depreciation (opening PP&E, add capex, subtract depreciation, get closing PP&E) and debt (opening balance, draws, repayments, closing balance). These feed both the income statement and the balance sheet. If your fixed assets need more than a single line, building a depreciation schedule in Excel sets out the asset register, the SLN and DDB calculations per period, and the disposals.

Step 4: Build the balance sheet

The balance sheet is a snapshot at each period end. Build it in three blocks. If you want the presentation layout and the balance check in isolation first, this balance sheet template for Excel sets out the structure line by line.

• Assets: cash (this links from the cash flow statement — leave it as a link, we'll close the loop), accounts receivable (revenue × DSO ÷ 365), inventory (COGS × days inventory ÷ 365), and net PP&E (closing balance from the depreciation schedule).
• Liabilities: accounts payable (COGS × DPO ÷ 365) and debt (closing balance from the debt schedule).
• Equity: common stock or paid-in capital (usually an opening input that stays flat unless you raise money), plus retained earnings.

Retained earnings roll forward: prior retained earnings + current net income − dividends. That one formula is the bridge from the income statement into the balance sheet — the retained earnings formula in Excel works it through end to end, along with the statement of retained earnings.

Step 5: Build the cash flow statement

The cash flow statement reconciles net income to the actual movement in cash, in three sections.

• Cash from operations: start with net income, add back depreciation (non-cash), then adjust for working capital. An increase in receivables or inventory uses cash (subtract it); an increase in payables provides cash (add it). Compute each as the change from the prior period balance.
• Cash from investing: subtract capex.
• Cash from financing: add debt draws, subtract debt repayments, add any equity raised, subtract dividends.

Sum the three sections to get the net change in cash. Then: opening cash + net change = closing cash. Now go back to the balance sheet and point the cash line at this closing cash figure. That link is what forces the balance sheet to balance.

Step 6: Formatting best practices

Formatting is not vanity — it is how errors become visible.

• Number format: select your data grid, press Ctrl+1, and apply a custom format like #,##0;(#,##0) so negatives show in parentheses. Use 0.0% for rates and 0.0x for multiples.
• No decimals on large figures — thousands or millions with a clearly stated unit in the header (e.g., "USD '000").
• Consistent column widths and a single font throughout. Bold your subtotals (Gross Profit, EBITDA, Net Income) and leave line items regular.
• Group supporting schedules so reviewers can collapse them: select the rows and press Shift+Alt+Right Arrow, or use Data > Group.
• Keep the blue/black/green colour discipline from Step 2. A reviewer should be able to tell input from formula at a glance.

Step 7: Sanity checks that catch mistakes

Build a dedicated Checks tab and reference it constantly. Good checks fail loudly.

1. Balance sheet balances: Total Assets − (Total Liabilities + Total Equity) should equal zero in every period. Wrap it in an IF that prints "OK" or "ERROR".
2. Cash is never negative without a financing plan. If it goes negative, you need a revolver or more funding — flag it.
3. Retained earnings tie out to the roll-forward.
4. No stray hardcodes: scan formula cells for typed numbers. On the Home tab, Find & Select > Go To Special > Constants highlights every hardcoded value at once — anything blue on a forecast column of a statement tab is a red flag.

One note on circular references: if you model interest on an average cash or debt balance, the model can become circular (interest affects cash, cash affects interest). For a first build, calculate interest on the opening balance to avoid it. If you do need circularity later, enable it via File > Options > Formulas > Enable iterative calculation and set a maximum change of 0.001.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.