How to build a cash flow forecast from your accounting data

Profit is not the same as cash in the bank

Many profitable small businesses still run into trouble because cash arrives at the wrong moment. A large job is prefinanced, a customer pays after 70 days instead of 30, and at the same time the quarterly VAT payment is due. A cash flow forecast shows such dips weeks in advance. Want to start right away? Download the free 13-week cash flow forecast template and fill it in with the steps below.

The Dutch Chamber of Commerce (KVK) describes a cash budget as an overview of income and expenses per month or quarter, including VAT. A forecast is the rolling, updated version of it: refreshed every week using current figures from your records. Whether you make a profit shows in an operating budget, not in a cash flow forecast.

Choose the horizon: 13 weeks

For day-to-day control, a 13-week horizon by week works well. It is long enough to see a VAT payment, holiday pay or a large purchase coming, and short enough to stay accurate. For investment decisions, add a monthly forecast for the year.

Use the template

Download the free 13-week cash flow forecast template (Excel). Enter the opening balance, your minimum balance and the expected receipts and payments per week. The template calculates the closing balance, carries it forward as the next week's opening balance and turns weeks below your minimum red. The steps below explain where each amount comes from.

Step 1: start with the actual bank balance

Take the balance of all business accounts on the start date. Use the bank statement, not the ledger balance, if there are unprocessed transactions.

Step 2: derive expected receipts from open receivables

Open sales invoices with their due dates are the foundation. But customers rarely pay exactly on the due date. Adjust for actual payment behavior per customer or customer group: if a customer pays on average 18 days after the due date, shift that receipt by 18 days.

For large overdue items, make a deliberate estimate: will this money arrive this month, later, or perhaps not at all? A forecast in which every overdue invoice arrives "next week" is too optimistic.

Step 3: derive expected payments from open payables

Open purchase invoices with due dates cover the first weeks of payments. Add items that do not appear as invoices in your accounting system:

• Payroll and payroll taxes, including holiday pay
• Rent, leases, insurance and subscriptions
• VAT payments per month or quarter
• Loan repayments and interest
• Planned investments

You set up these fixed items once in a schedule. They change little.

Step 4: add expected revenue that is not yet invoiced

For weeks further out, there are no invoices yet. Use work in progress, planned project milestones or budgeted revenue, and add the usual payment term. Keep these lines separate so you can see which part of the forecast rests on invoices and which part on expectations.

A worked example

Fictional example. In week 3, payroll coincides with the quarterly VAT payment; in week 4, rent and two large purchase invoices follow.

Week 4 drops below zero. You now know that four weeks ahead. You can call the largest outstanding customer, discuss payment terms with a supplier or prepare your credit line. Without a forecast, you find out when a payment is declined.

Want to see whether your records hold enough data for a forecast? Request a 20-minute demo. We connect your accounting system and show open items and payment behavior on your own figures.

Step 5: update weekly and measure the variance

A forecast is only reliable if you check it. Every week, compare the forecast with the actual balance and note the largest variance. If receipts are consistently later than expected, adjust your payment-behavior correction.

This weekly update is where most spreadsheets fail: someone has to export and paste open items every week. By connecting your accounting system, for example Exact Online, Twinfield or Moneybird, invoices and due dates arrive automatically, as far as the package makes them available. Add fixed costs and budgeted revenue as your own CSV file.

Where AI helps, and where it does not

An AI agent is useful for quickly answering questions such as "Which customers pay latest after the due date on average?" or "Which purchase invoices above EUR 5,000 fall due in the next three weeks?". The forecast itself should run on fixed calculation rules, so the result is produced the same way every week and can be checked. Let AI help you investigate and explain, not invent the numbers.

Common mistakes

• Revenue instead of receipts. An invoice sent this month only becomes cash when the customer pays.
• Forgetting VAT. Amounts in a cash forecast include VAT, and the VAT payment is a large outflow.
• Making it once. A forecast from three months ago says nothing. Make it a weekly routine.
• No buffer. Set a minimum balance, for example one month of fixed costs, and flag weeks that fall below it.

A rising DSO is often the first cause of a cash squeeze. Read also improving receivables management with data. How much cash is tied up in receivables and stock you work out with the working capital calculator; what remains after investment with the cash flow calculator.

You can track the figures behind the forecast, such as open items and DSO, continuously in a financial dashboard.