How to use Excel for financial modeling: a beginner's guide

Financial modeling is how you turn business assumptions into numbers. A good financial model lets you test scenarios, predict outcomes, and make better decisions. And you do not need expensive software to build one. Excel is all you need.

What is a financial model

A financial model is a spreadsheet that projects your business’s financial performance. It takes your assumptions about revenue, costs, and growth and turns them into projected income statements, balance sheets, and cash flow statements.

A well-built model answers questions like: What happens if sales grow 20% next year? What if our biggest customer leaves? Can we afford to hire two more people?

The three core statements

Every financial model starts with the three financial statements:

Income statement — Shows your revenue, expenses, and profit over time. Start with revenue at the top, subtract costs to get gross profit, subtract operating expenses to get operating profit, and subtract interest and taxes to get net income.

Balance sheet — Shows what you own (assets), what you owe (liabilities), and what is left for owners (equity). The fundamental equation is Assets = Liabilities + Equity.

Cash flow statement — Shows how cash moves in and out of your business. It reconciles net income with actual cash flow by accounting for changes in working capital, investments, and financing.

Building your first model

Start simple. Open Excel and create three sections: assumptions, income statement, and cash flow.

Assumptions are your inputs. List revenue growth rate, gross margin, operating expenses, and anything else that drives your business. Keep assumptions in one place so you can change them easily.

Revenue is your top line. If you sell a product, project units sold times price per unit. If you sell services, project billable hours times hourly rate. Build month-by-month for the first year and annual projections for years 2-5.

Expenses fall into two categories: fixed and variable. Fixed costs stay the same regardless of sales (rent, salaries). Variable costs change with sales (materials, commissions, shipping).

Linking everything together

The power of a financial model is in the links. Your revenue assumption feeds into your income statement. Your income statement feeds into your cash flow. When you change an assumption, everything updates automatically.

Use cell references, not hard-coded numbers. If your revenue growth rate is in cell B2, reference it as $B$2 everywhere else. This is what makes a model dynamic rather than static.

Common formulas you will use

SUM — Adds up a range of cells. Use it for total revenue and total expenses.

SUMIF — Adds up cells that meet a condition. Useful for categorizing expenses by department.

VLOOKUP or XLOOKUP — Finds a value in a table. Use it to pull data from one sheet to another.

NPV and IRR — Net present value and internal rate of return. These measure whether an investment is worth making.

IF statements — Create conditional logic. For example, if revenue exceeds a threshold, bonus expenses kick in.

Best practices for clean models

Keep your model easy to read. Use consistent formatting: blue font for inputs, black font for calculations. Separate input sheets from calculation sheets. Add comments to explain complex formulas.

Never hide rows or columns. If you need to hide something, move it to another sheet. Hidden data is forgotten data, and forgotten data leads to mistakes.

Build in error checks. Add a row that checks whether your balance sheet balances (Assets = Liabilities + Equity). If it does not, you know something is wrong.

Scenario analysis

Once your base model works, build scenarios. Create a base case (most likely), an upside case (optimistic), and a downside case (pessimistic). Link all three to the same model structure but with different assumptions.

This is where financial modeling becomes powerful. Instead of guessing the future, you prepare for multiple futures. If the downside case shows you running out of cash in 12 months, you know you need to raise money or cut costs now.

Use the Excel Financial Tools to access pre-built templates and calculators that can jumpstart your financial modeling process.