How to Build a Simple Financial Model for a Small Business

A financial model is a spreadsheet that turns your assumptions about customers, prices and costs into a monthly picture of revenue, profit and cash. You do not need an MBA or a template with forty tabs. This playbook builds a working model in five sheets that you can finish in an afternoon and update every month.

Laptop on a desk displaying a spreadsheet model with columns of monthly figures — A good model is small enough to understand and honest enough to be wrong in useful ways.

Key takeaways

Keep every assumption on one sheet, in its own cell, and never type a number into a formula.
Build revenue from drivers (customers × price, orders × order value) rather than guessing a total.
Model cash separately from profit. Timing of payments is where small businesses get surprised.
Three scenarios (base, downside, upside) are worth more than one precise forecast.

Most small business owners have a budget, which is a list of expected income and expenses for the year. A financial model goes a step further: it links the numbers to the things that cause them. Change the price, the number of customers or the hiring date, and the whole picture updates. That is what lets you ask "what if" before spending real money.

The structure below works for a café, a consultancy, an online store or a software startup. The drivers change, the architecture does not.

The five-sheet structure

Sheet	Purpose	Rule
1. Assumptions	Every input in one place: prices, volumes, costs, dates, payment terms	Only sheet where you type numbers
2. Revenue	Monthly revenue built from drivers	Formulas only, referencing Assumptions
3. Costs	Cost of sales, payroll, overheads, by month	Formulas only
4. Profit and cash	Monthly P&L, then cash flow and closing bank balance	Formulas only
5. Scenarios and summary	Base, downside and upside, plus a one-page dashboard	Switch scenarios with a single cell

Step 1: set up the assumptions sheet

Create a sheet called Assumptions. Give every input a label, a value, a unit and a note on where the number came from. Group them:

Timing: start month, number of months to model (24 to 36 is typical).
Revenue drivers: starting customers or orders, monthly growth rate, average price or order value, churn or repeat rate.
Direct costs: cost per unit or cost of sales as a percentage of revenue, payment processing rate.
People: each role, monthly salary, start month, employer taxes and benefits as a percentage.
Overheads: rent, software, insurance, marketing budget, professional fees, each as a monthly amount.
Cash timing: days customers take to pay, days you take to pay suppliers, opening cash balance, any loan or investment and when it lands.
Tax: an estimated rate on profit and when it is paid. Rates and rules differ by country, so check with your accountant.

Common mistake

Hard-coding numbers inside formulas, such as =B5*1.05 for 5% growth. Three months later nobody remembers what 1.05 meant or where it lives. Put growth in its own labeled cell on the Assumptions sheet and reference it. Every number in the model should be traceable to one input.

Step 2: build revenue from drivers

On the Revenue sheet, put months across the top. Each row is a step in the chain from driver to revenue. For a subscription or membership business:

Opening customers = previous month's closing customers.
New customers = opening customers × growth rate, or a fixed monthly number from marketing plans.
Lost customers = opening customers × churn rate.
Closing customers = opening + new − lost.
Revenue = average of opening and closing customers × monthly price.

For a retail or service business, replace customers with orders or projects: orders per month × average order value, or billable hours × hourly rate. If you have several products or revenue lines, give each its own block and sum them.

Worked example

Imagine a small fitness studio with 120 members paying $89 a month. It expects to add 12 members a month and lose 4% of members each month. Month 1: opening 120, new 12, lost about 5 (4% of 120), closing 127. Revenue is roughly 123.5 members × $89, about $11,000. By month 12 the studio reaches around 190 members and about $16,700 in monthly revenue. If the owner changes churn from 4% to 3% on the Assumptions sheet, month 12 revenue rises to roughly $18,000 with no other change, which shows exactly what a retention program is worth.

Step 3: model costs by behavior

Split costs into three groups on the Costs sheet, because each behaves differently.

Variable costs

These move with revenue. Model them as a percentage of revenue or a cost per unit: cost of goods, packaging, payment processing fees, sales commissions. Reference the rate on Assumptions and multiply by that month's revenue or units.

Payroll

One row per role. Use a formula that returns the salary only from the start month onward, for example =IF(month>=start_month, salary, 0). Add a row for employer taxes and benefits as a percentage of total salaries. Payroll is usually the largest cost and the one with the most timing risk, so keep it explicit.

Fixed overheads

Rent, insurance, software, utilities, accounting fees and the marketing budget. These are typically flat monthly amounts, with step changes when you move premises or add a tool. Model the step change with a start month, as with payroll.

Close-up of printed sheets filled with columns of financial figures — Costs that move with sales and costs that do not need separate rows, because they answer different questions.

Step 4: profit, then cash

On the fourth sheet, build a simple monthly profit and loss: revenue, minus variable costs, equals gross profit; minus payroll and overheads, equals operating profit; minus interest and estimated tax, equals net profit. Every line references the sheets above.

Then build cash flow underneath it. This is the part most simple budgets skip and the part that matters most.

Cash in from customers: revenue shifted by your payment terms. If customers pay in 30 days on average, cash in this month equals last month's revenue. If you take payment upfront, it equals this month's revenue.
Cash out to suppliers: variable costs shifted by your payable terms.
Payroll and overheads: usually paid in the month they occur.
Capital spending: equipment, fit-out and other one-off purchases, from Assumptions.
Financing: loans received, loan repayments, owner contributions or investment.
Tax payments: in the months they fall due, not spread evenly.

Net cash flow is cash in minus all cash out. Closing cash equals opening cash plus net cash flow. The closing cash row is the most important line in the model. If it ever turns negative, you have found the month you need a loan, an investment or a change of plan.

38% The share of failed startups whose founders cited running out of cash or failing to raise new capital as a reason for shutting down, in CB Insights' 2021 review of startup post-mortems Self-reported reasons from a sample of failed companies. The figure illustrates why the cash row matters more than the profit row

Step 5: scenarios and a summary

Add a cell on the Assumptions sheet labeled Scenario, with values 1, 2 or 3. For each key driver (growth, churn, price, a hiring date), store three values and pick the active one with a formula such as =CHOOSE(scenario, base, downside, upside). Now the whole model switches with one cell.

A downside case usually cuts growth by a third to a half, raises churn, and delays revenue by a month or two. An upside case does the reverse. The point is not to predict which will happen. It is to see whether you survive the downside and whether you could handle the upside.

Finish with a one-page summary: monthly revenue, gross margin, operating profit, closing cash and the lowest cash balance in the period, for each scenario. This is the page you show a lender, a partner or yourself.

Pro tip

Add a row of checks. Closing customers should never be negative. Cash in over the whole model should roughly equal revenue minus the change in receivables. A single cell that shows "OK" when all checks pass will catch most formula errors before they reach a decision.

Keeping the model alive

A model that is built once and abandoned is a wasted afternoon. Each month, after you close the books, add a column of actual figures next to the forecast and look at the gaps. Then update the assumptions, not the formulas. If customers took 45 days to pay instead of 30, change the payment-terms cell. If growth was 8% instead of 12%, change the growth cell. Over a year, the assumptions sheet becomes a record of what you have learned about your own business.

Keep it simple when

You have one or two revenue lines
You are the only person using it
Decisions are about hiring, pricing and cash timing

Add detail when

You carry inventory with long lead times
You raise outside money and need a full balance sheet
Several products have very different margins

Your model-building checklist

Create five sheets: Assumptions, Revenue, Costs, Profit and cash, Scenarios
Enter every input on Assumptions with a label, unit and source note
Build revenue from customers, orders or hours times price
Split costs into variable, payroll and overheads with start months
Build cash flow with payment timing and find the lowest cash month
Add three scenarios and a one-page summary
Book a monthly slot to add actuals and update assumptions

Frequently asked questions

How far ahead should the model go?

Twenty-four months in monthly detail is enough for most small businesses. Go to 36 months if you are raising money or planning a large investment. Beyond that, monthly precision is false precision.

Do I need a balance sheet in the model?

Not at first. Revenue, costs and a cash flow with timing cover most decisions. Add a balance sheet when a lender or investor asks for one, or when inventory and fixed assets become a large part of the business.

Should I use a template?

Templates can help you see a structure, but a model you build yourself is one you understand. Start with the five sheets here, then borrow ideas from templates as you need them.

How accurate does a model need to be?

Less accurate than you think. The goal is to see the shape of the future, spot the month cash gets tight and compare choices. Being 10% off on revenue is normal. Missing a tax payment because it was not in the model is the error to avoid.

The bottom line

A simple financial model is five sheets, one place for assumptions, revenue built from drivers, costs split by behavior and a cash row you check every month. Build it in an afternoon, keep it alive with actuals and it will answer the questions that keep small business owners awake: can we afford this hire, what happens if sales slip, and when does cash run short.