How to Build a Financial Model, Step by Step

How to build a financial model comes down to a sequence you can follow every time: decide what the model must answer, set up a structure that separates inputs from calculations, forecast revenue and costs from drivers, link them into the statements, add checks and test scenarios. A model that is clearly structured and tested is more valuable than a sophisticated one nobody trusts. This guide walks through the steps, then builds a worked startup cash runway model so you can see what the model tells you. It is part of budgeting and forecasting and connects to the three statement financial model, income statement forecasting and balance sheet forecasting.

Step 1: Define the purpose and audience

The purpose decides the design.

Write the question in one sentence. If you cannot, the model will grow without direction.

Step 2: Choose the time frame and level of detail

Monthly for the first year or two, then quarterly or annual is typical. Detail should match the decision: do not model 500 products when a product family forecast serves the purpose.

Step 3: Set up the structure

Follow a standard layout. See financial model structure and the template.

• One sheet for inputs, clearly labeled and color-coded.
• Calculation sheets that contain formulas only.
• Outputs and checks on separate sheets.
• A consistent timeline across every sheet.
• One formula per row, copied across.

Step 4: Gather historical data

Use the last two or three years of actuals to ground the assumptions. Reconcile them to the accounts before you rely on them, and note any one-offs. For a startup with no history, document the sources of each assumption: pilot data, quotes, comparable companies.

Step 5: Build the revenue forecast from drivers

Break revenue into drivers (see how to forecast revenue). Keep each driver in the inputs sheet.

Step 6: Forecast costs by behavior

Step 7: Build schedules and statements

Create working capital, capex and depreciation, and debt schedules, then the three statements, as in the three statement financial model.

Step 8: Add checks

Balance sheet balance, cash reconciliation, roll-forward checks and a master flag.

Step 9: Add scenarios and sensitivities

A scenario switch for base, low and high, and sensitivities on the top drivers. See scenario planning for finance teams and sensitivity analysis in financial models.

A realistic timeline

Most of the time goes to the data and the assumptions, not the formulas. If the data are not ready, spend the time there first.

Step 10: Review, document and hand over

Have someone else review the logic, and write a one-page note on purpose, structure, key assumptions, limitations and how to update. See the model audit checklist.

A worked example: startup cash runway

A hypothetical startup has $300,000 of cash. It expects revenue of $20,000 in month 1, growing 15 percent a month. Gross margin is 70 percent and operating expenses are a fixed $45,000 a month. For simplicity, ignore working capital, tax and capital spending.

Formulas:

• Revenue (month n) = 20,000 × 1.15^(n-1)
• Gross profit = Revenue × 70%
• Net cash flow = Gross profit - $45,000
• Closing cash = Opening cash + Net cash flow

Check month 1: 20,000 × 0.70 = 14,000; 14,000 - 45,000 = -31,000; 300,000 - 31,000 = 269,000. Check month 2: 20,000 × 1.15 = 23,000; × 0.70 = 16,100; - 45,000 = -28,900; 269,000 - 28,900 = 240,100.

What the model says

• Cash never runs out in this base case. The lowest balance is about $130,002 at the end of month 9, after which cash flow turns positive.
• Cumulative cash used is $300,000 - $130,002 = about $170,000.
• Break-even revenue is $45,000 / 0.70 = $64,286 a month, reached in month 10 (revenue $70,358).

Why the answer needs a downside

Now test a slower growth assumption: 10 percent a month instead of 15, with everything else unchanged.

In the slower case the company reaches break-even revenue of $64,286 in month 14 instead of month 10, and the lowest cash balance is about $58,318 at the end of month 13, compared with $130,002 in the base case. Cash does not run out, but headroom shrinks from about 43 percent of the opening balance to about 19 percent (58,318 / 300,000), and the plan has no room for a delayed customer payment or an unplanned hire. Add a month of slippage in receivables, or a $10,000 monthly cost overrun, and the answer changes again. This is the benefit of a model: a five-point change in a growth assumption moves the safety margin by more than half, and you see it before you commit to costs. Run a downside before you raise money or hire ahead of revenue. A sensible rule is to ask what growth rate makes the plan fail, and then judge how likely that rate is.

Turning the result into decisions

Documenting assumptions

For each driver, record the value, the source (history, quote, pilot, judgment), the owner and the date it was last reviewed. A table like the one below can sit on the inputs sheet:

Assumptions labeled "judgment" are the first to test and the first to replace with actuals. See how to document budget assumptions.

What to add as the model matures

Cash and working capital

Profit and cash differ. A growing business absorbs cash in receivables and inventory. Add working capital as soon as the model matters for funding. See cash flow management.

Common mistakes

• Starting to build before defining the purpose.
• Mixing inputs and formulas.
• Hard-coding numbers in formulas.
• Forecasting everything at the same level of detail.
• No checks.
• No downside case.
• Not documenting.
• Over-engineering.

FAQs

How do I build a financial model?

Define its purpose, set up inputs and calculations separately, forecast revenue and costs from drivers, link the three statements, add checks, and test scenarios.

How long does it take to build a financial model?

A simple model can take a day; a detailed one can take weeks, mostly spent on gathering and validating assumptions.

What software should I use?

Excel or Google Sheets are enough for most companies. The structure and discipline matter more than the tool.

How do I know if my model is right?

Checks must pass, results must reconcile to history, and a reviewer who did not build it must be able to follow it.

What is the most common mistake?

Typing numbers into formulas, which hides assumptions and makes errors hard to find.