A practical Excel workbook structure can turn uncertain sales, costs and payment timing into a rolling view of available cash. This guide explains how to build a small business budget and 12-month cash flow forecast, including assumptions, tax provisions, debtor and supplier timing, variance tracking and best-, base- and worst-case scenarios.
Overview
A budget and a cash flow forecast answer related but different questions. A budget sets out expected income and costs for a period, usually to support planning and performance review. A cash flow forecast estimates when money will actually enter and leave the bank account. A profitable month can still create a cash shortage if customers pay late, stock is purchased in advance or a large tax bill falls due.
| Planning view | Main question | Typical use |
|---|---|---|
| Budget | What income and costs do we expect? | Set targets, control spending and compare actual performance. |
| Cash flow forecast | When will cash be received and paid? | Identify funding gaps, payment risks and the timing of commitments. |
A useful cash flow forecast template in Excel should therefore keep these views connected without treating them as interchangeable. Use the budget for planned revenue and expenditure, then apply realistic payment assumptions to produce the cash view.
A simple workbook can contain six sheets:
- Instructions: explain the purpose of each input and the update routine.
- Assumptions: record opening cash, tax provisions, payment delays, growth and scenario settings.
- Budget: show monthly expected income and costs, normally excluding timing differences.
- Cash forecast: convert expected activity into receipts, payments and closing cash.
- Actuals and variance: compare budget, forecast and actual results.
- Dashboard: summarise cash balance, inflows, outflows, variance and upcoming pressure points.
If you need a broader annual planning structure, the Excel Budget Template for UK Small Businesses can complement this model. For a shorter-term liquidity view, see the 13-week cash flow forecast guide.
How to estimate
Start with a monthly timeline covering the next 12 months. Label the periods consistently, such as January to December or the relevant financial year. If the business operates across a different reporting cycle, use that cycle rather than forcing the model into calendar months.
1. Set the opening cash position
Enter the bank balance and any cash held elsewhere at the start of the first forecast month. Keep restricted funds, deposits or money reserved for a specific obligation separate where appropriate. The opening balance for each later month should be the previous month’s closing balance:
Closing cash = Opening cash + Total cash received - Total cash paid
In Excel, a simple monthly closing balance might use a formula such as =B5+B20-B35, where the cells represent opening cash, total receipts and total payments. Reference cells rather than typing values into multiple parts of the workbook.
2. Forecast income before applying payment timing
List expected sales by product, service, customer group or revenue stream. A practical calculation is:
Forecast revenue = Expected units or hours × Price per unit or hour
Keep the assumptions visible. If sales are uncertain, use separate best, base and worst cases instead of hiding a single optimistic estimate inside the formula.
3. Convert revenue into cash receipts
Revenue is not necessarily received in the month it is recorded. Add a debtor timing assumption, such as a percentage received in the month of sale and the remainder received in later months. The exact pattern should reflect your customer agreements and observed payment behaviour.
For example, if a forecast assumes that 60% of sales are received in the same month and 40% in the following month, the receipts row can combine the current month’s sales with the prior month’s unpaid portion. The model should make this timing visible so that a change in payment behaviour can be tested quickly.
4. Schedule operating costs and supplier payments
Separate fixed, variable and one-off costs. Fixed costs may include rent, software, insurance or regular subscriptions. Variable costs may move with sales, hours, units or projects. One-off costs should be entered in the month in which payment is expected, rather than spread automatically unless instalments are genuinely planned.
Supplier timing matters in the same way as debtor timing. Record whether costs are paid immediately, after an agreed period or in instalments. A cash forecast that assumes every supplier invoice is paid in the month incurred may overstate short-term cash pressure; one that assumes all suppliers allow delay may understate it.
5. Add tax, payroll and financing movements
Include cash items that are easy to overlook: payroll, employer costs, tax provisions, VAT movements where relevant, loan repayments, interest, equipment purchases and owner drawings. Treat a tax provision as a planning reserve unless the amount and payment date are known. The workbook should not be treated as a substitute for accounting or tax advice.
For payroll assumptions, include the full employer cost rather than wages alone. The Payroll Cost Calculator UK provides a related structure for considering the cost of a new hire.
Inputs and assumptions
Put assumptions in one clearly marked area. This makes the workbook easier to audit and reduces the risk of changing one figure while leaving related formulas unchanged. Useful inputs include:
- Opening cash balance.
- Monthly sales by stream, units, hours or projects.
- Prices, discounts, refunds and expected cancellations.
- Customer payment timing and expected late-payment rate.
- Supplier payment timing and recurring payment dates.
- Staff numbers, pay assumptions and other payroll costs.
- Rent, utilities, subscriptions, insurance and professional fees.
- Stock purchases, equipment and other capital expenditure.
- Tax or VAT provisions and planned payment months.
- Loan receipts, repayments, interest and owner drawings.
- Growth, inflation and cost-change assumptions.
Use separate columns for budget, actual and latest forecast. A variance formula can be expressed as:
Variance = Actual - Budget
For costs, an unfavourable variance may be a positive number, while for income it may be negative. Label the interpretation clearly rather than relying on colour alone. A short explanation column is valuable: “two invoices paid late”, “software renewal moved forward” or “project start delayed” is more useful than an unexplained difference.
Use data validation for scenario names and consistent categories. Avoid merged cells in calculation areas, hard-coded totals and formulas that refer to entire unrelated columns. Protect formula cells if the workbook will be edited by several people, while leaving input cells clearly identifiable.
Worked examples
Example 1: revenue received after sale
Suppose a business budgets £10,000 of sales in April and assumes 70% is collected in April, with 30% collected in May. April’s cash receipt from those sales is £7,000. The remaining £3,000 appears in May, subject to any other timing assumptions. The budget still shows £10,000 of April revenue, but the cash forecast shows the delayed receipt.
This distinction helps explain why the business may need sufficient opening cash even when the monthly budget appears profitable.
Example 2: supplier payment timing
Assume April purchases are forecast at £4,000, with payment made 30 days later. The budget records the cost against April activity, while the cash forecast places the payment in May. If the supplier changes the agreement to payment on order, update the timing assumption and review the following month’s closing cash.
Example 3: scenario planning
Create three scenario columns or selectable cases:
- Best case: stronger sales, faster collection and controlled discretionary spending.
- Base case: the most supportable plan using current information.
- Worst case: weaker sales, slower collection and unavoidable costs still paid on schedule.
The purpose is not to predict the future precisely. It is to show which assumptions have the greatest effect on cash. A low point in the worst case can prompt practical actions such as delaying non-essential expenditure, following up overdue invoices or arranging funding before the pressure arrives.
For margin-sensitive businesses, connect the forecast to a pricing model rather than entering arbitrary sales totals. The Product Pricing and Profitability Workbook covers markup, margin, VAT, break-even and scenario analysis. You can also use the Break-Even Analysis Calculator to test the sales volume needed to cover planned costs.
When to recalculate
Update the workbook at least monthly, before management or owner review. Replace the completed month’s forecast figures with actual bank receipts and payments, then extend the model by adding a new month at the end. This creates a rolling 12-month forecast rather than allowing the planning horizon to shrink.
Recalculate sooner when:
- A major customer changes its payment timing or places a large order.
- Sales, prices or project delivery dates move materially from plan.
- A supplier changes terms or a significant bill is brought forward.
- Payroll, rent, software, financing or other recurring costs change.
- Tax, VAT or other cash provisions need to be revised.
- The closing cash balance approaches a level that requires action.
- A new loan, asset purchase, hire or owner withdrawal is considered.
At each review, check four things: the next three months of closing cash, overdue receipts, committed payments and the difference between the base and downside cases. Record the reason for material changes so the forecast becomes a management record rather than just a changing set of numbers.
Finally, refresh labels, example categories, inflation assumptions and any tax-year terminology when the business’s reporting year or official guidance changes. For related performance reporting, the Monthly Variance Analysis Template can help structure the review. A well-maintained spreadsheet template is most useful when it is updated routinely, challenged against actual cash and used to make decisions before a problem becomes urgent.
