Excel Budget Template UK: Build a Flexible Small-Business Cash Flow Model

Overview

A business budget should do more than add up annual income and expenditure. It should help you answer operational questions such as: how much cash is expected next month, which costs are committed, what happens if sales fall, and whether actual performance is following the plan?

The most useful structure separates the model into a few clear worksheets:

• Assumptions: tax provisions, VAT treatment, payment delays, growth rates and other inputs.
• Income: expected sales by month, customer, service or product line.
• Costs: fixed overheads, variable costs, payroll and one-off spending.
• Cash flow: opening cash, receipts, payments and closing cash.
• Actual versus forecast: a comparison of the original budget with updated results.

Keeping these areas separate makes the workbook easier to audit than a single large table. It also allows you to change one assumption, such as expected sales growth or a supplier cost, without rewriting every monthly figure.

If your budget needs to connect to a wider planning process, link it to a sales forecast and scenario model rather than duplicating assumptions. The sales forecast template in Excel can help structure expected demand, while the Excel scenario planning guide explains how to compare base, stronger and weaker outcomes.

How to estimate

Start with one column for each month and use rows for income and expenditure categories. A simple twelve-month layout might use columns B:M for January to December, with a total column in N. If your financial year does not begin in January, label the columns with the actual reporting periods instead.

1. Estimate income

List each dependable income stream separately. For example, a consultancy might use project work, retainers and training. Enter expected sales based on known work, a cautious pipeline estimate or a unit-based calculation:

Expected income = Units sold × Price per unit

Do not assume that an invoice raised is the same as cash received. Add a separate cash-receipts schedule if customers normally pay after a delay. For a basic model, receipts in a month can be linked to the relevant sales month or moved forward by an agreed number of months.

2. Separate fixed and variable costs

Fixed costs usually remain broadly stable over a planning period. Examples include rent, software subscriptions, insurance and regular professional fees. Variable costs change with sales or activity, such as materials, transaction charges, delivery and subcontractor costs.

A useful formula for a variable cost is:

Variable cost = Relevant sales × Variable cost percentage

Keep payroll visible rather than hiding it in a general overhead line. Include wages, employer costs and pension-related assumptions as separate rows where that level of detail supports decision-making. For a more detailed employer-cost model, see the Excel payroll cost calculator guide.

3. Build the cash-flow calculation

A budgeted profit figure does not show when money enters or leaves the bank account. Add these rows to the cash-flow sheet:

• Opening cash balance
• Cash received from customers
• Other cash receipts
• Supplier and operating payments
• Payroll payments
• VAT, tax and other payment reserves or settlements
• Capital purchases and loan payments
• Closing cash balance

The core formula is:

Closing cash = Opening cash + Total receipts − Total payments

Then link the next month’s opening cash to the previous month’s closing cash:

Next opening cash = Previous closing cash

This linked balance is one of the most important checks in a cash flow forecast template Excel workbook. It prevents each month from being treated as an isolated calculation.

Inputs and assumptions

Put assumptions in a dedicated, clearly labelled area rather than embedding them in formulas. This makes the model easier to review when pricing inputs change or when tax and VAT arrangements need checking.

VAT treatment

Decide whether each income and cost figure is entered exclusive of VAT, inclusive of VAT, or as a non-VAT amount. Mixing treatments is a common cause of misleading totals. For a VAT-exclusive amount, the VAT component can be represented as:

VAT amount = Net amount × Applicable VAT rate

For a VAT-inclusive amount, the net amount is calculated by dividing the gross amount by one plus the applicable rate. Keep the rate as an input cell and do not hard-code it into multiple formulas. Confirm the treatment and applicable rate for your circumstances before relying on the workbook for returns or payments. The VAT calculator in Excel guide covers the related invoice checks.

Tax reserves

A cash-flow model can include a planning reserve for tax, but a reserve is not the same as a final liability calculation. Use an assumption such as a percentage of a defined profit measure, label it clearly as an estimate, and review it with the person responsible for tax compliance.

Payment timing

Record when a sale is expected to be paid, not only when it is expected to be invoiced. You can add a payment-delay assumption and use it consistently, or create a receipts schedule for larger customers. Apply the same discipline to suppliers: some expenses may be paid immediately, while others are settled later.

Formula and control checks

Add visible checks to the workbook. Useful examples include:

• Income check = Sum of category rows − Reported income total
• Cash check = Closing cash in summary − Closing cash in cash-flow sheet
• Variance = Actual − Forecast
• Variance percentage = IFERROR(Variance ÷ Forecast, 0)

Use conditional formatting to flag missing assumptions, negative cash balances and unusually large variances. Protect formula cells if several people will update the file, and use a consistent file naming convention when versions are shared.

Worked examples

Assume a small service business forecasts £12,000 of net sales in one month. Its direct delivery costs are estimated at 20% of sales, fixed operating costs are £4,000, and payroll payments are £3,000. The model would calculate:

• Variable costs: £12,000 × 20% = £2,400
• Total operating outgoings before tax or VAT settlements: £2,400 + £4,000 + £3,000 = £9,400
• Budgeted operating surplus before other items: £12,000 − £9,400 = £2,600

If the opening bank balance is £8,000 and customer receipts are only £9,000 because part of the sales will be collected later, the cash position is different from the budgeted surplus. Before any other payments, the closing cash calculation is:

£8,000 + £9,000 − £9,400 = £7,600

This example shows why a budget planner should display both an income-and-cost view and a cash view. The business may appear profitable while still needing to manage collection timing, tax reserves or a large upcoming payment.

For forecast-versus-actual reporting, add the actual result beside each forecast. If forecast sales were £12,000 and actual sales were £10,500:

Sales variance = £10,500 − £12,000 = −£1,500

Review the reason for the variance rather than simply changing next month’s forecast to match it. It may reflect timing, lost work, a pricing change or an assumption that was too optimistic. If pricing is being reviewed, keep the budget linked to a separate margin or markup calculation; the markup versus margin guide explains why the two measures should not be treated as interchangeable.

When to recalculate

Update the workbook on a regular rhythm rather than waiting for a cash shortfall. A monthly review is suitable for many small businesses, while businesses with tight margins, rapid changes or frequent transactions may benefit from a shorter review cycle.

Recalculate the model when:

• Actual sales or costs replace forecast figures.
• Prices, supplier terms, payroll or recurring subscriptions change.
• Customer payment timing changes.
• A major contract, purchase or loan payment is added.
• VAT treatment, rates or filing assumptions need confirmation.
• The business enters a stronger, base or weaker trading scenario.

At each review, copy the previous forecast to an archive sheet, enter actual figures, investigate material variances and update only the assumptions that have genuinely changed. Avoid overwriting the original budget: preserving it provides a useful record of what was expected and why.

Finally, test the workbook before using it for a decision. Change one input and confirm that the expected rows update; check that monthly opening and closing cash balances link correctly; reconcile totals to accounting records; and review formulas for accidental hard-coded numbers. A simple, transparent small-business budget spreadsheet is usually more useful than a complex model no one trusts or maintains.