How to Build a Financial Model From Scratch [Step-by-Step]

What Is a Financial Model?

A financial model is a quantitative representation of a company's past, present, and projected financial performance. Built primarily in Microsoft Excel or Google Sheets, it translates business assumptions into numbers — revenue growth rates, cost structures, capital expenditure plans, and working capital requirements — and outputs a set of financial statements that analysts and decision-makers rely on.

According to Investopedia, financial models are used for a wide range of business purposes, from valuing a company during an acquisition to stress-testing a startup's cash runway. Whether you work in investment banking, private equity, corporate finance, or financial planning and analysis (FP&A), knowing how to make a financial model is one of the most valuable technical skills you can build.

At its core, every financial model rests on three pillars: inputs (assumptions), calculations (formulas and logic), and outputs (statements and charts). Mastering the interplay between these three elements is what separates a junior analyst from a skilled financial modeler.

Types of Financial Models

Before you start building, it helps to understand which type of model you need. Each model serves a different analytical purpose, and the structure changes accordingly.

The 3-statement model is the starting point for virtually every other model type. Once you can link the income statement, balance sheet, and cash flow statement correctly, you can extend that foundation into a DCF, an LBO, or an M&A model. That is why this guide focuses on building a 3-statement model step by step.

Step-by-Step: Building a 3-Statement Model From Scratch

The 3-statement model integrates the income statement (IS), balance sheet (BS), and cash flow statement (CFS) into a single, dynamically linked model. When you change a revenue assumption, it flows automatically into net income, retained earnings, and ultimately cash — without any manual overrides. Here is the full process.

Step 1: Build Your Assumptions Tab

Every model starts with a dedicated assumptions or drivers tab. This is the control panel of your model. All hard-coded inputs — revenue growth rates, gross margin percentages, tax rates, capex as a percentage of revenue, days sales outstanding (DSO), days inventory outstanding (DIO), and days payable outstanding (DPO) — should live here and nowhere else.

Why centralize assumptions? Because when a client asks "What if revenue grows at 8% instead of 12%?", you should only need to change one cell. If growth assumptions are scattered across multiple sheets, sensitivity analysis becomes a debugging nightmare.

Common assumptions to include:

• Revenue growth rate (year-over-year, %)
• Gross margin (% of revenue)
• EBITDA margin (% of revenue)
• Depreciation (% of PP&E or straight-line)
• Tax rate (%)
• Capex (% of revenue or fixed amount)
• Working capital days (DSO, DIO, DPO)
• Debt interest rate and repayment schedule

Step 2: Build the Income Statement

With your assumptions in place, build the income statement (also called the profit and loss statement or P&L). Start from revenue and work down to net income.

The typical structure:

1. Revenue — driven by your growth assumption applied to the prior year base
2. Cost of Goods Sold (COGS) — derived from gross margin assumption
3. Gross Profit = Revenue − COGS
4. Operating Expenses (SG&A, R&D) — often modeled as % of revenue
5. EBITDA = Gross Profit − Operating Expenses
6. Depreciation & Amortization — pulled from balance sheet / capex schedule
7. EBIT (Operating Income) = EBITDA − D&A
8. Interest Expense — linked to debt balances on the balance sheet
9. Pre-tax Income (EBT) = EBIT − Interest Expense
10. Income Tax = EBT × Tax Rate
11. Net Income = EBT − Income Tax

At this point, do not worry about making the balance sheet balance. Just ensure your income statement logic is clean and all formulas reference the assumptions tab rather than containing hard-coded numbers.

Step 3: Build the Balance Sheet

The balance sheet is the most technically demanding part of a 3-statement model because it must balance at all times: Assets = Liabilities + Equity. Build it section by section.

Current Assets:

• Cash — this will ultimately be the plug from the cash flow statement
• Accounts Receivable = Revenue × (DSO / 365)
• Inventory = COGS × (DIO / 365)
• Other Current Assets — often held flat or grown with revenue

Non-Current Assets:

• PP&E (net) = Prior PP&E + Capex − Depreciation
• Intangibles and goodwill — held flat unless there is an acquisition

Liabilities:

• Accounts Payable = COGS × (DPO / 365)
• Short-term and long-term debt — per the debt schedule
• Deferred tax liabilities — simplified as a plug in basic models

Equity:

• Share capital — held flat unless equity is issued
• Retained Earnings = Prior Retained Earnings + Net Income − Dividends

The net income from the income statement feeds directly into retained earnings on the balance sheet. This is one of the critical links you must get right.

Step 4: Build the Cash Flow Statement

The cash flow statement (CFS) reconciles net income to actual cash movement. Use the indirect method, which is standard in financial modeling:

Operating Activities:

• Start with Net Income
• Add back: Depreciation & Amortization (non-cash)
• Adjust for changes in working capital (increase in AR = cash outflow; increase in AP = cash inflow)

Investing Activities:

• Capital Expenditures (negative — cash outflow)
• Acquisitions or asset disposals if applicable

Financing Activities:

• Debt raised / repaid
• Dividends paid
• Equity issued / repurchased

The ending cash balance = beginning cash + net change in cash (sum of all three sections). This ending cash balance then feeds back into the cash line on the balance sheet — completing the circular link.

Step 5: Link the Three Statements and Audit

Once all three statements are built, you must verify that the model links correctly. The key checks are:

1. Balance sheet balances: Assets = Liabilities + Equity in every period
2. Net income ties: Net income on the IS equals the addition to retained earnings on the BS
3. Cash ties: Ending cash on the CFS equals cash on the BS
4. D&A ties: D&A on the IS matches D&A on the CFS and feeds the PP&E roll on the BS

Build a balance check row at the top of the balance sheet tab: =IF(Total Assets = Total Liabilities + Equity, "BALANCED", "ERROR"). This should show "BALANCED" at all times. If it shows "ERROR", trace your formulas before moving forward.

Best Practices and Formatting Conventions

A well-structured model is as important as a correct model. Professionals in investment banking and private equity follow specific conventions that make models easy to audit, share, and update.

Common Mistakes When Building Financial Models

Even experienced analysts make errors when building complex spreadsheets. Knowing the most common mistakes helps you avoid them from the start.

• Hardcoding numbers inside formulas. Writing =B12*0.30 instead of =B12*AssumptionsTab!$B$5 is one of the most dangerous habits. It makes sensitivity analysis impossible and errors hard to trace.
• Not checking whether the balance sheet balances. Many beginners forget to build a balance check. If assets do not equal liabilities plus equity, the model has a linking error somewhere.
• Circular references without an iterative calculation setting. When interest expense depends on the debt balance, which depends on cash, which depends on cash from operations, which includes interest — you have a circular reference. This is common in professional models but must be handled correctly in Excel's settings.
• Mixing historical actuals with forecast formulas. If you accidentally apply a growth formula to an actuals column, you corrupt your historical base. Always protect historical periods.
• Overly optimistic assumptions. Revenue growing at 40% for five years is rarely realistic. Ground every assumption in comparable company data, industry benchmarks, or management guidance.
• Not documenting assumptions. A model without source documentation is a liability. Add comments or a separate "sources" tab explaining why each assumption was chosen.
• Using volatile functions excessively. Functions like OFFSET, INDIRECT, and NOW recalculate every time Excel updates, which can make large models extremely slow.

Tools and Resources for Financial Modelers

The choice of tools can meaningfully affect both the quality and speed of your modeling work.

Microsoft Excel remains the industry standard for financial modeling, particularly in investment banking, private equity, and corporate finance. Its power comes from pivot tables, advanced formulas (INDEX/MATCH, XLOOKUP, SUMIFS), data tables for sensitivity analysis, and Solver for optimization.

Google Sheets is preferred in startups and remote-first environments due to its real-time collaboration. It lacks some of Excel's more advanced features but is sufficient for most FP&A and early-stage modeling work.

Python (pandas, NumPy) is increasingly used for large-dataset financial analysis and automated model updates. If you work with financial data at scale, learning Python alongside Excel gives you a significant advantage.

Bloomberg Terminal is the go-to data source for market data, comparable company analysis, and historical financials in institutional finance roles.

Capital IQ and FactSet are widely used in investment banking and asset management for pulling financial statements, running comps screens, and exporting data directly into Excel.

Frequently Asked Questions