Small Business Cash Flow Forecast Template for Excel: 13-Week Planning Guide

Overview

A cash flow forecast is a timing tool, not a profit report. It focuses on when cash is expected to enter or leave the bank account. A business can show a profit on its income statement and still face a short-term cash shortage if customers pay late, stock must be purchased in advance or several large bills fall due in the same week.

A 13-week forecast is useful because it is detailed enough to support near-term decisions while remaining manageable to update. It can help you decide whether to delay discretionary spending, follow up overdue invoices, schedule a supplier payment or review a planned purchase. It should not replace a longer-term budget or business plan. Instead, it provides a regularly refreshed operational view.

For a small business cash flow forecast in Excel, use one column for each week and rows for the main categories of receipts and payments. Keep the model deliberately transparent. Someone reviewing it should be able to see which numbers are based on bank information, which are taken from invoices or contracts, and which are estimates.

A practical workbook can contain four tabs:

• Instructions: purpose, update routine, assumptions and colour coding.
• Inputs: expected receipts, bills, payroll dates, tax provisions and other known items.
• 13-week forecast: the main weekly cash flow calculation.
• Checks and scenarios: reconciliation checks, sensitivities and alternative cases.

If you already use an Excel budget template for broader planning, the 13-week model can sit alongside it. The budget may use monthly revenue and cost assumptions, while the cash flow forecast uses expected payment dates and opening bank balances.

Template structure

Start the forecast with a clear date row. Use week commencing dates or week ending dates consistently, and label the period covered. For example, columns C to O could represent 13 consecutive weeks, with column B containing row labels and column P showing the 13-week total where a total is meaningful.

Opening and closing cash

The core calculation is:

Closing cash = Opening cash + Total receipts - Total payments

The opening balance for the first week should match the relevant bank balance or agreed starting point. Each following week should carry forward the previous week’s closing balance:

Next week opening cash = Previous week closing cash

If the business uses several accounts, decide whether the forecast covers one operating account or a combined cash position. If accounts are combined, document which balances are included and exclude restricted or ring-fenced funds unless they are genuinely available for the payments being modelled.

Receipts

Break expected cash receipts into categories that help you make decisions. Typical rows include:

• Customer invoices already issued
• Card, online or direct sales
• Deposits or advance payments
• Other operating receipts
• Owner funding, loans or other financing

For invoiced sales, forecast the expected payment week rather than automatically using the invoice date. A simple input table can include customer or invoice reference, gross amount expected, due date, expected receipt date and confidence status. Use statuses such as confirmed, likely and uncertain, then include or exclude items from scenarios as appropriate.

Payments

Separate regular operating costs from occasional or exceptional payments. Useful rows include:

• Suppliers and materials
• Rent, utilities and premises costs
• Software, subscriptions and insurance
• Payroll and employer costs
• VAT or sales tax payments
• Corporation tax or other tax provisions
• Loan repayments and interest
• Equipment, stock or capital purchases
• Owner drawings or dividends, where relevant to the cash plan

Do not combine every payment into one line. A forecast is most useful when it shows why the cash balance changes. Payroll deserves its own row or section because it is usually date-sensitive and may include more than employee net pay. Employer costs, pension contributions and other payroll-related payments should be timed according to the business’s actual payment schedule. See the Excel payroll cost calculator guide for a more detailed way to model these inputs.

Useful formulas and controls

Use formulas rather than manually typing totals into the forecast. If receipts are in rows 8 to 14 and payments are in rows 20 to 32, a weekly net movement formula might be:

=SUM(C8:C14)-SUM(C20:C32)

The closing balance could then be:

=C5+C35

where C5 is opening cash and C35 is net movement. The next week’s opening balance might be:

=C36

Use a separate minimum cash target input rather than hiding a threshold inside a formula. A warning row can then show:

=IF(C36<MinimumCashTarget,"Review","OK")

Apply conditional formatting to negative balances and balances below the chosen target. Add a check that the first opening balance agrees with the selected bank balance, and another that the total of detailed rows agrees with the summary section.

How to customize

Begin with the business’s real cash cycle. A consultancy may have a small number of invoices and substantial payroll, while a retailer may need detailed stock purchasing and daily sales receipts. The template should reflect the decisions you need to make, not every possible accounting category.

Set up an inputs table with one row per expected transaction or payment. Suggested fields are:

• Date expected in the bank
• Type: receipt or payment
• Category
• Description or reference
• Amount
• Status or confidence
• Notes about the assumption

Then use SUMIFS to bring amounts into the appropriate week. For example, if the input date is in column A, category in column C, amount in column D, and the week start date is in C3, a date-based formula can sum transactions falling within that week:

=SUMIFS(Inputs!$D:$D,Inputs!$A:$A,">="&C$3,Inputs!$A:$A,"<"&C$3+7)

Adapt the ranges and worksheet name to your own file. If you are less comfortable with formulas, a manually maintained weekly input area can still work, provided each figure has a source or note.

Keep VAT and tax assumptions explicit. Record the expected payment date and the amount you intend to pay, rather than relying only on the month in which a sale or expense was recorded. Use the business’s applicable treatment and current records; do not hard-code a rate that may later become inappropriate. A separate VAT calculator in Excel can help check whether invoice totals and VAT amounts have been entered consistently.

For scenario planning, duplicate the forecast or add columns for base, cautious and stronger cases. A cautious case might delay some receipts, reduce projected sales and bring selected payments forward. Avoid changing every input by the same percentage unless that is a deliberate assumption. Cash timing is often more important than an across-the-board sales adjustment.

Protect formula cells, use a consistent input colour and add a version date to the workbook. If several people update it, use a clear file naming convention and store the latest approved version in one shared location.

Examples

Imagine a service business with an opening cash balance of £18,000 at the start of week one. It expects a customer payment in week two, monthly payroll in week four, a supplier payment in week five and a tax-related payment in week six. The forecast should place each item in the week when the money is expected to move, not simply in the week when the invoice was raised.

If the week-four closing balance falls below the business’s minimum cash target, the forecast has identified a decision point. The owner might check whether the customer payment can be confirmed, follow up an overdue invoice, review the timing of a non-essential purchase or discuss payment timing with a supplier. The forecast does not prove that a problem will occur; it shows where an assumption needs attention.

For a stock-based business, add purchase orders and expected delivery payments separately from sales receipts. A sales forecast may indicate strong demand, but the cash flow model should also show whether stock must be paid for before customers pay. Linking the cash forecast to a sales dashboard can help compare expected sales with actual receipts, but do not treat a sales target as cash until its payment timing is reasonably supported.

You can also connect the model to break-even planning. A break-even analysis explains the sales level needed to cover costs, while a cash flow forecast explains when those sales are likely to turn into available cash. These are related but different questions, so retain both views rather than using one as a substitute for the other.

When to update

Update the forecast at least weekly during normal operations, and more often when cash is tight or transaction timing is changing quickly. A useful weekly routine is:

1. Enter the latest bank balances and compare them with the prior forecast.
2. Mark receipts that arrived, slipped or became less certain.
3. Record newly received bills, purchase orders and payment commitments.
4. Update payroll, VAT, tax and financing dates using the latest available information.
5. Replace assumptions with actual figures where possible.
6. Review the lowest projected balance and the assumptions behind it.
7. Save a dated version so changes can be traced.

Revisit the structure when the business changes its payment terms, adds a sales channel, takes on employees, changes accounting arrangements or begins making larger stock or equipment purchases. Also review it after a forecast misses materially: the cause may be late customer payments, omitted liabilities, optimistic sales assumptions or incorrect payment dates.

To put the template into use, create the four tabs, enter the opening bank position, list every known receipt and payment for the next 13 weeks, and add notes beside uncertain figures. Test the formulas with a small sample, check that the closing balance rolls forward correctly, then set a recurring weekly review. The value of a cash flow spreadsheet template comes from disciplined maintenance: a simpler forecast updated consistently is usually more useful than a detailed model that quickly becomes outdated.