Excel Budget Template for UK Small Businesses: Annual Planning and Monthly Tracking

Overview

A small-business budget spreadsheet is a planning model, not a prediction that will remain correct for twelve months. Its purpose is to create a consistent set of assumptions, translate them into monthly figures and provide a useful comparison with actual results.

A practical annual budget template in Excel can contain five connected areas:

• Assumptions: sales volumes, prices, payment timing, staffing plans and cost increases.
• Income: expected sales by month, product, service or customer group.
• Operating costs: recurring overheads and variable costs such as materials, software, premises and marketing.
• People and provisions: wages, employer costs, VAT, tax provisions and one-off payments.
• Review: actual results, variance calculations, revised forecasts and scenario comparisons.

Keep inputs separate from calculations wherever possible. For example, place the assumed monthly subscription cost on an assumptions sheet and reference it in the budget rather than typing the same value into twelve cells. This makes the model easier to audit and update.

It is also useful to distinguish profit from cash. A sale may be recorded in one month but paid in another, while an annual insurance bill may create a large cash movement even though the business treats the cost differently for reporting. If timing is important to your business, connect the budget to a cash flow forecast. The 13-week cash flow forecast template provides a more detailed short-term view.

How to estimate

Start by choosing the period covered by the model. A calendar-year budget is straightforward, but a business with a different accounting year may prefer twelve months from its financial year start. Label every month clearly and include a full-year total column.

1. Estimate income from operational drivers

Avoid entering one annual sales figure and dividing it evenly unless demand is genuinely stable. Instead, estimate the drivers that produce revenue:

Monthly revenue = Units sold × Average price

For a service business, the equivalent might be:

Monthly revenue = Billable hours × Charge-out rate

Use separate rows for different revenue streams when they have different prices, payment terms or margins. Include a timing assumption if invoices are not normally paid in the same month. This makes the budget more useful for cash planning.

2. Split fixed, variable and one-off costs

Fixed costs are broadly stable over the planning period, such as a regular premises cost or software subscription. Variable costs change with sales or activity, such as payment fees, materials or delivery charges. One-off costs include equipment, professional fees, recruitment and planned projects.

This classification helps you test decisions. If sales fall, variable costs may reduce automatically, while fixed costs remain. If you are considering a new hire or premises, a separate line makes the effect visible instead of hiding it in a general overhead total.

3. Calculate monthly variance

Once actual figures are available, add columns or a separate actuals sheet. A basic variance formula is:

Variance = Actual − Budget

For income, a positive variance is normally favourable. For costs, a positive variance may be unfavourable, so consider adding a labelled status column rather than relying only on colour. A percentage variance can be calculated as:

Variance % = (Actual − Budget) ÷ Budget

Protect against division by zero with an IF formula, for example =IF(Budget=0,"",(Actual-Budget)/Budget). Use conditional formatting to flag material differences, but define a threshold that suits the size of the business. A small percentage change on a major cost may matter more than a large percentage change on a minor one.

Inputs and assumptions

Create an assumptions table with columns for the item, value, unit, start month, source or rationale, and review date. This turns hidden judgements into visible model inputs.

Useful assumptions include:

• Expected units, clients, projects or billable hours by month.
• Average selling price and any planned price changes.
• Payment timing, deposits and expected late-payment patterns.
• Payroll, employer costs, pension provisions and planned recruitment.
• Rent, utilities, insurance, software, accounting and other overheads.
• Materials, subcontractor costs, delivery charges and transaction fees.
• VAT treatment and the dates on which VAT payments are expected.
• A tax provision based on an assumption that can be reviewed with appropriate professional advice.

Keep VAT visible rather than mixing it into every line. Depending on the transaction and the business's circumstances, show net sales, VAT and gross cash movement in separate rows. Do not hard-code a rate without recording the applicable period and checking the relevant official guidance. The VAT calculator in Excel guide explains how to structure VAT-inclusive and VAT-exclusive checks.

Scenario planning is easiest when the three cases change a small number of clearly identified drivers. For example, the base case might use expected sales and costs; the best case might increase sales volume and reduce avoidable costs; the worst case might reduce sales, delay receipts and include a contingency for selected expenses. Avoid changing every cell independently, because that makes the scenario difficult to understand or defend.

Worked examples

Imagine a consultancy that expects to complete four projects in January at an average fee of £2,000. Its planned January revenue is:

4 × £2,000 = £8,000

If the business expects six projects in February and the average fee remains unchanged, February revenue is £12,000. If the fee changes, store the new fee as a separate assumption from the month in which it applies.

Suppose January operating costs are budgeted as £1,200 for subcontractors, £450 for software and insurance, and £3,000 for payroll-related costs. Total operating costs are £4,650, producing a budgeted operating surplus before other provisions of:

£8,000 − £4,650 = £3,350

If actual January revenue is £7,500 and actual costs are £4,900, the revenue variance is £500 unfavourable and the cost variance is £250 unfavourable. The combined movement against the planned operating surplus is £750 lower than budget. The important next step is not simply to record the difference, but to explain it: fewer projects, lower fees, delayed billing, higher subcontractor usage or another cause.

For a business that pays suppliers before customers pay invoices, add a cash timing section. The budget may show a profitable month while the cash balance falls. Track opening cash, receipts, payments and closing cash separately so that a profitable forecast does not create false confidence about short-term liquidity.

You can connect budget outputs to a reporting workbook or sales dashboard. For wider reporting context, see the sales dashboard template for revenue, pipeline and conversion KPIs. If fixed costs and contribution assumptions are central to the plan, use the break-even analysis calculator as a separate decision check.

When to recalculate

Review the spreadsheet monthly after actual figures are available. Enter actuals consistently, investigate material variances and update the remaining months using the latest evidence. This rolling forecast is generally more useful than leaving the original annual budget unchanged.

Recalculate immediately when a major input changes, including:

• A significant price, contract, supplier or premises cost changes.
• Sales volumes or conversion assumptions move materially away from plan.
• A new employee, contractor or project is approved.
• Payment terms change or overdue invoices affect expected receipts.
• VAT, payroll or other applicable rates and thresholds need review.
• An unexpected one-off cost or investment is added.

At each review, save a dated version and record what changed. Use one sheet for the original approved budget, another for actuals and a forecast sheet for the current outlook. This preserves an audit trail and prevents the model from silently rewriting its own history.

Finally, check formulas before relying on the result: confirm that all twelve months are included, totals reconcile, VAT is not counted as income, payroll assumptions are complete, and scenario switches affect only the intended lines. A well-maintained Excel budget template for a UK small business should be simple enough to update each month and detailed enough to support a real decision.