A practical cash flow forecast shows when money is expected to arrive, when it must leave, and how much cash remains after each movement. This guide explains how to build a monthly and 13-week cash flow spreadsheet for a small business, including opening balance, customer receipts, supplier payments, payroll, taxes, financing, scenarios, and runway.
Overview
A cash flow forecast is a time-based estimate of cash entering and leaving the business. It is not the same as a profit forecast. A profitable business can still face a cash shortfall if customers pay slowly, stock is purchased in advance, tax is due in a large instalment, or a loan repayment falls before expected receipts.
The basic cash flow projection formula is:
Closing cash balance = Opening cash balance + Total cash inflows − Total cash outflows
The closing balance for one period becomes the opening balance for the next. This makes the spreadsheet useful for answering practical questions: Can the business pay its obligations next month? When might funding be needed? What happens if sales receipts arrive two weeks late? How much cash is available for a planned purchase?
A monthly forecast is useful for budgeting and longer-range planning. A 13-week cash flow forecast gives a more detailed view of near-term liquidity and is particularly helpful when payment timing is uncertain. Keep both views connected to the same assumptions where possible, but do not force them into one overly complicated worksheet.
| Cash flow forecast | Profit forecast |
|---|---|
| Records expected cash receipts and payments by date or period | Records revenue and expenses when they are recognised under the chosen accounting approach |
| Highlights liquidity and possible cash shortfalls | Highlights operating performance and profitability |
| Includes loan proceeds, loan principal, and owner funding | Usually treats financing movements separately from operating profit |
| Changes when payment timing changes | May not change when an invoice is paid earlier or later |
How to estimate
Start with a simple spreadsheet layout. Put weeks or months across the columns and cash categories down the rows. Use separate sections for opening cash, inflows, outflows, financing, and closing cash.
1. Enter the opening balance
Use the actual bank and cash balance at the start of the first forecast period. If the forecast begins on the first day of a month, reconcile this figure to the relevant bank accounts. Do not include expected receipts in the opening balance.
2. Estimate cash receipts
List receipts by source rather than placing all expected sales in one row. Common categories include customer invoice collections, card or online payments, deposits, refunds received, grants or other funding, and asset sales. Base receipts on when cash is expected to arrive, not only when an invoice is issued.
For example, if customers generally pay 30 days after invoicing, sales recorded in April may create cash receipts in May. If customers pay in several instalments, split the expected receipt across the periods in which the instalments are due.
3. Estimate cash payments
Separate predictable payments from variable ones. Useful rows include supplier invoices, inventory purchases, rent, utilities, software, insurance, payroll, employer costs, tax payments, loan repayments, interest, equipment purchases, and owner drawings. A payroll cost estimator can help you avoid forecasting salary alone when the business also pays employment-related costs or benefits.
4. Calculate net cash movement
For each period, calculate:
Net cash movement = Total inflows − Total outflows
Then calculate the closing balance:
Closing balance = Opening balance + Net cash movement
Link the next period's opening balance to the previous period's closing balance. This reduces manual errors and makes the forecast roll forward automatically.
5. Add a cash runway measure
Runway is an estimate of how long available cash could support the business under a stated cash-burn assumption. A simple version is:
Runway in periods = Available cash ÷ Average net cash outflow per period
Use this cautiously. A business with irregular receipts or large annual payments should rely on the period-by-period closing balance rather than a single average. The first projected negative closing balance is usually the more important warning.
Inputs and assumptions
A forecast is only as useful as its assumptions. Create an assumptions area at the top or on a separate worksheet so that important drivers can be reviewed without searching through formulas.
- Opening cash: Confirm the starting balance and the date it applies to.
- Sales receipts: Record expected sales, customer payment terms, deposits, instalments, and any known delays.
- Supplier payments: Use expected payment dates rather than purchase dates. Include recurring suppliers and large one-off orders.
- Payroll: Enter the actual pay dates and include relevant employer costs. Use the payroll schedule rather than assuming payroll always falls at month-end.
- Taxes: Model tax payments in the periods when they are expected to leave the bank. Timing depends on the business and jurisdiction, so verify the dates used in your own records.
- Financing: Include expected loan proceeds, owner funding, repayments, and interest as separate rows.
- Capital expenditure: Place equipment, fit-out, vehicles, and other major purchases in the period of payment.
- Contingency: Add a clearly labelled reserve or downside adjustment rather than hiding a buffer inside unrelated expense estimates.
Use a base case, a best case, and a worst case. The scenarios should change meaningful drivers, such as customer payment timing, sales receipts, supplier costs, hiring dates, or the timing of a planned purchase. Avoid making the best case optimistic in every row and the worst case negative in every row without a reason; scenario assumptions should describe plausible operating conditions.
A useful spreadsheet structure is:
- Assumptions: payment terms, sales growth, payroll dates, tax dates, financing, and scenario selections.
- Monthly forecast: 12 months or more, with linked opening and closing balances.
- 13-week forecast: weekly receipts and payments for near-term cash management.
- Actuals and variance: actual cash movements compared with the forecast.
- Dashboard: closing cash, minimum cash balance, next shortfall, and key changes.
Worked examples
Monthly example
Assume a business begins April with an opening cash balance of 18,000 in its chosen currency. It expects customer receipts of 42,000 during the month. Planned payments are 16,000 to suppliers, 12,000 for payroll, 4,000 for operating costs, and 3,000 for tax.
Total outflows = 16,000 + 12,000 + 4,000 + 3,000 = 35,000
Net cash movement = 42,000 − 35,000 = 7,000
Closing cash balance = 18,000 + 7,000 = 25,000
If a planned equipment purchase of 10,000 is added to April, the closing balance falls to 15,000. The business may still be able to proceed, but the forecast now provides a clear basis for checking the following month's payroll, supplier payments, and minimum cash requirement.
13-week example
Suppose the same business expects a 20,000 customer receipt in week 3, but a major customer has not confirmed its payment date. In the base case, the receipt is included in week 3. In the downside case, it moves to week 6. The weekly forecast may show that cash becomes negative in week 4 under the downside case, even though the monthly forecast still shows a positive month-end balance.
That result identifies an action: contact the customer, request a deposit, delay a non-essential purchase, negotiate supplier timing, or arrange funding before the shortfall occurs. The forecast is not just a report; it is a list of dates at which decisions may be required.
For related planning, compare the forecast with a rolling sales forecast template, review payment terms using a professional invoice template, and use the contribution margin calculator when assessing whether additional sales will generate useful cash after variable costs.
When to recalculate
Review the 13-week forecast at least weekly when cash is tight, receipts are unpredictable, or the business is making significant purchases. Update the monthly forecast at each month-end and whenever the operating plan changes.
Recalculate after a major customer changes payment terms, a supplier changes prices, payroll changes, a tax or financing payment is scheduled, a new hire is approved, or a large purchase is brought forward. Refresh pricing inputs, rates, and payment assumptions whenever they change. If the forecast is used for funding discussions, label the preparation date and show which figures are actual, committed, or estimated.
Cash shortfall review checklist
- Confirm the opening bank balance against actual records.
- Identify the first period with a negative or uncomfortably low closing balance.
- List receipts that are uncertain, overdue, or dependent on a customer action.
- Check whether supplier, payroll, tax, and loan payments use the correct dates.
- Separate essential payments from discretionary purchases.
- Compare the base case with the downside case and record the trigger for each scenario.
- Assign an owner and deadline to every action intended to protect cash.
Keep the forecast simple enough to update consistently. A smaller model with accurate timing is more useful than a detailed workbook that nobody maintains. Reconcile actual results to the forecast, explain the largest variances, and carry those lessons into the next version. That routine turns a cash flow spreadsheet into a practical decision tool for budgeting, working capital, and runway planning.