Cash flow forecast: how to build one, with a template layout

A cash flow forecast projects the cash coming in and going out in each future month to show the closing cash balance: opening cash + receipts − payments = closing cash. Receipts come from invoiced sales and how long customers really take to pay; payments come from supplier terms, payroll, tax and loan dates. A company opening January with $250,000 that collects $1,228,000 and pays out $1,210,000 over the quarter closes March with $268,000.

What a cash flow forecast is

It is a month-by-month plan of the bank balance. Profit says whether the business earns money; the forecast says whether there will be cash on the day the payroll and the supplier invoices fall due. As the SEC's Beginners' Guide to Financial Statements puts it, an income statement tells you whether a company made a profit, while a cash flow statement tells you whether it generated cash.

There are two ways to build one:

• Direct method. List the expected receipts and payments by type and date. This page uses it, because every line can later be compared with the bank.
• Indirect method. Start from forecast net income, add back non-cash items such as depreciation, and adjust for changes in receivables, inventory and payables. It suits a long-range plan built from a forecast P&L and balance sheet.

The same two methods exist for the reported cash flow statement: IAS 7 describes the direct method as disclosing "major classes of gross cash receipts and gross cash payments", and US GAAP (ASC 230) allows both.

Horizon: this page builds the monthly forecast, typically twelve months rolling. Treasury teams also run a weekly version for the next quarter; that is the 13-week cash flow model, a separate build.

The template layout

Months across, lines down. In Excel, the months sit in columns B onward, with the two months before the forecast starts (November and December here) on the left, because their sales are still being collected.

Closing cash = Opening cash + Receipts − Payments

Headroom = Closing cash − Minimum cash buffer

Step 1: receipts from collection timing

Sales are not receipts. A sale invoiced in January on net 30 terms becomes cash in February at the earliest, and some customers pay later. So the receipts line applies a collection profile to invoiced sales: the share of each month's invoices collected one month later, two months later and so on.

Measure the profile from the ledger, not from the terms on the invoice. Match payments to the invoices they settle and count what share of each month's invoicing arrived in each later month. In this company, 70% of a month's invoices are paid the month after and 30% the month after that:

Receipts in month m = 70% × Sales in month m−1 + 30% × Sales in month m−2

With November in column B and December in column C, January's receipts in D5 are:

=0.7*C3+0.3*B3

filled right. Put the 70% and 30% in their own cells rather than inside the formula, so a re-fitted profile changes every month at once. Delays before an invoice is raised push receipts back too; the booking-to-invoice cycle measures them per customer.

Step 2: payments by date

Each payment goes in the month the cash leaves, not the month the cost is booked.

• Suppliers. Purchases paid on their terms: net 30 means this month's payments are last month's purchases. Use the days you actually take to pay, measured from the payables ledger.
• Payroll. Semimonthly payroll is two runs every month. Biweekly payroll is 26 runs a year, so two months each year carry three pay dates; mark them in the forecast.
• Rent, insurance and other fixed dates. Quarterly or annual premiums land in one month, not spread across twelve.
• Income tax. A calendar-year corporation pays estimated federal income tax in installments due by the 15th day of the 4th, 6th, 9th and 12th months, per IRS Publication 542: April 15, June 15, September 15 and December 15. Add state estimated tax on its own dates.
• Sales tax. Tax collected from customers is remitted to each state on that state's schedule, monthly or quarterly. It is a receipt and a payment, not income.
• Loans and capex. Repayments and interest on the loan schedule; equipment on the purchase order's payment date.

For a company reporting outside the US, the same row holds VAT quarters.

This company pays semimonthly and owns its warehouse, so there is no rent line. The quarter's payments:

No estimated tax falls in the quarter: the first installment is April 15, so it belongs in the April column of a twelve-month forecast.

Step 3: roll the balance forward

Opening cash on January 1 is $250,000 and the minimum buffer is $200,000.

In Excel, closing cash in D12 and the next month's opening in E4 are:

=D4+D6-D10
=D12

February is the tight month: the bonus payout takes headroom down to $46,000. That is the month to test. If one large customer pays $50,000 due in February a month late, February closes at $196,000, below the buffer. A forecast with only the base case would not show that.

The check

Two identities prove the sheet.

Across the horizon. Closing cash at the end equals opening cash plus all receipts minus all payments: 250,000 + 1,228,000 − 1,210,000 = 268,000. In Excel, =D4+SUM(D6:F6)-SUM(D10:F10) must equal F12. If it does not, a month's opening is not linked to the previous month's closing.

Receivables. Opening receivables, plus sales, minus receipts, must equal what the profile leaves uncollected. At January 1 that is 30% of November plus all of December: 120,000 + 380,000 = 500,000. Rolled forward, 500,000 + 1,320,000 − 1,228,000 = 592,000, which is exactly 30% of February plus all of March: 132,000 + 460,000. A receipts formula pointing at the wrong column breaks this tie first.

Update it against actuals

Each month, put actual receipts beside the forecast and keep the history.

Error is actual minus forecast, as a share of the forecast. Two numbers come out of it. Accuracy: the absolute errors sum to 36,000, 3.0% of actual receipts. Bias: all three months are below forecast, so the forecast is consistently too high, not just noisy. That is the signal to re-fit the collection profile, because customers are paying slower than the 70/30 split assumes. The method for both measures is in how to measure forecast accuracy and bias in Excel, and forecast accuracy has the short definition.

Seasonal businesses should fit the profile by season as well as by customer: a December peak in sales is a January and February peak in receipts, as seasonality in sales measures shows.

Where it goes wrong

• Profit instead of cash. Sales are not receipts until customers pay, and costs are not payments until they are paid.
• A profile taken from the terms. Net 30 on the invoice does not mean cash in 30 days. Measure the profile from the ledger.
• Missing lumpy payments. Quarterly estimated income tax, sales tax remittances, insurance premiums, annual bonuses and the third biweekly payroll in a month.
• Never compared with actuals. Without a forecast-against-actual record, the same bias repeats every month.
• One scenario. No downside case for a large customer paying late, which is the case that breaks the buffer.

Receipts from the ledger, not from assumptions

The receipts line is only as good as the collection profile behind it. Covirage measures each customer's actual days to pay from the uploaded AR ledger and builds the collection profile from it with statistical models fitted on your data. The deterministic tools compute every figure; the external AI model explains what changed and never does the arithmetic. See FP&A reporting, and days sales outstanding for the receivables measure behind the profile. For the balances that drive receipts and payments, see working capital.