How to Create a Cash Flow Forecast in Excel: 5 Easy Steps
A cash flow forecast estimates the money coming into and going out of your business over the coming weeks or months. To build one in Excel, set up one column per period, enter your opening balance, and forecast cash in and cash out line by line. Then calculate each period's closing balance and look for the lowest point.
This guide explains what the forecast contains, how to choose weekly or monthly periods, and the five Excel steps with formulas. It then walks through a worked example, how to read the result, and the mistakes that make forecasts unreliable.
What a cash flow forecast is
The Australian Government's business.gov.au site gives a clear definition. "A cash flow forecast is an estimate of your future sales and costs." It adds that the forecast helps you "understand if you will have enough income to cover your costs."
The purpose is practical. According to the same page, the forecast "will help you prevent cash shortages and avoid debt." Profit and cash are not the same thing. A business can be profitable on paper and still run short of cash if customers pay late or a large bill falls due first.
It is easy to confuse the forecast with a cash flow statement. The statement records what already happened. The forecast estimates what will happen, using the same structure. The business.gov.au site makes the link directly. You can create a forecast "by entering your estimated figures for each future period" in a cash flow statement template.
If you need the backward-looking version, our guide to the cash flow report covers it. This guide covers the forward-looking one.
What goes into the forecast
The business.gov.au site lists the lines a cash flow statement needs, and a forecast uses the same lines with estimated figures.
| Line | What it means | How to calculate it |
|---|---|---|
| Opening balance | Cash at the start of the period | First period: your bank balance. Later periods: the previous closing balance |
| Cash incoming | Money flowing into the business | Sales, debtor receipts, grants, tax rebates |
| Total incoming | All cash in for the period | Sum of the incoming lines |
| Cash outgoing | Payments the business makes | Fees, marketing, purchases, rent, utilities, and similar |
| Total outgoing | All cash out for the period | Sum of the outgoing lines |
| Monthly cash balance | Net movement for the period | Total incoming minus total outgoing |
| Closing balance | Cash at the end of the period | Opening balance plus total incoming minus total outgoing |
Two notes from the same page are easy to miss. If you use estimated costs, "label and explain them clearly." And state whether your figures include or exclude sales tax, since mixing the two distorts the totals.
Weekly or monthly periods
The period you choose changes how useful the forecast is.
Monthly periods suit planning for the year ahead. The business.gov.au template works by year and month. Monthly totals are also easy to compare with last year's figures. Twelve columns fit comfortably on one sheet.
Weekly periods suit tight cash situations. When payroll, rent, and supplier bills fall in different weeks of the same month, a monthly view can hide a short-term gap. A rolling 13-week view, with one column per week, covers a full quarter at that level of detail.
A practical approach is to keep both. Use weekly periods for the next quarter, where timing matters most, and monthly periods for the rest of the year.
How to create a cash flow forecast in Excel
The five steps below build a 12-month forecast on one sheet. The same layout works for weekly periods if you change the column headers.
Step 1: Set up the layout and opening balance
Put the line items down column A and the periods across row 1. Start with the period labels in B1 through M1, such as Jan through Dec.
In column A, enter the lines in this order: Opening balance, then a heading for Cash incoming with one row per source of income. Add Total incoming, then Cash outgoing with one row per cost, then Total outgoing. Finish with Net cash flow and Closing balance.
In B2, the first opening balance cell, enter your current bank balance. Leave the other opening balance cells for now, because they will link to the previous closing balance in Step 4.
Label the sheet clearly as a forecast and note the date you built it. A forecast without a date is easy to mistake for actual figures later.
Step 2: Forecast cash incoming
Enter one row for each source of cash, such as customer receipts, other income, loans received, grants, and tax refunds.
The business.gov.au site suggests a simple method. You can forecast cash incoming "by looking at previous years, identifying seasonal trends and accounting for regular sources of income." Start with the same month last year, then adjust for known changes.
Record cash when you expect to receive it, not when you send the invoice. If customers usually pay 30 days after invoicing, January's sales arrive in February. Getting this timing right is what turns a sales plan into a cash forecast.
Then add the Total incoming row. In the first period column, enter =SUM(B4:B8), adjusted to your income rows, and fill it right across all twelve columns.
Step 3: Forecast cash outgoing
Enter one row for each type of payment. Common lines include payroll, rent, suppliers, marketing, utilities, loan repayments, and tax payments.
Use the same method as for income. The business.gov.au page says to look at previous years, identify seasonal trends, and account for "your major costs." Fixed costs, such as rent, repeat every month. Variable costs, such as supplier purchases, should rise and fall with expected sales.
Enter payments in the period you expect to pay them. Quarterly bills, annual insurance, and tax payments often cause the lowest points in a year, so place them in the right month.
Add the Total outgoing row with a SUM formula across your cost rows, then fill it right.
Step 4: Calculate the net and closing balance
Now link the rows together. In the Net cash flow row, subtract total outgoing from total incoming. If Total incoming is in row 9 and Total outgoing is in row 17, enter =B9-B17 and fill right.
In the Closing balance row, add the net to the opening balance: =B2+B18, adjusted to your rows. This matches the business.gov.au method of adding the opening balance and total incoming, then subtracting the total outgoing.
Finally, link each opening balance to the previous closing balance. In C2, enter =B19, using your closing balance row, and fill right. The forecast now rolls forward automatically.
To make shortfalls obvious, select the closing balance row and add conditional formatting that turns negative values red. Use =MIN(B19:M19) in a spare cell to show the lowest closing balance of the year.
Step 5: Test scenarios and update each month
A single forecast is a guess. Three versions give you a range.
Copy the sheet twice and name the copies Low and High. In the Low version, delay customer receipts by one period and cut sales by a set percentage. In the High version, do the opposite. Compare the lowest closing balance across all three.
Then keep the forecast alive. At the end of each month, replace that month's estimates with actual figures and extend the forecast by one more month. The gap between forecast and actual tells you which assumptions to fix.
A worked example
Here is an illustrative three-month forecast for a small services business. All figures are made up.
| Line | Jan | Feb | Mar |
|---|---|---|---|
| Opening balance | $20,000 | $14,500 | $17,000 |
| Customer receipts | $28,000 | $36,000 | $34,000 |
| Total incoming | $28,000 | $36,000 | $34,000 |
| Payroll | $22,000 | $22,000 | $22,000 |
| Rent and utilities | $4,500 | $4,500 | $4,500 |
| Quarterly tax | $7,000 | $0 | $0 |
| Other costs | $0 | $7,000 | $8,000 |
| Total outgoing | $33,500 | $33,500 | $34,500 |
| Closing balance | $14,500 | $17,000 | $16,500 |
January is the tight month. Receipts are lower because December's invoices have not all been paid, and the quarterly tax bill lands at the same time. The closing balance drops by $5,500.
The forecast shows two options before January arrives. The business can chase December invoices earlier, or move a supplier payment into February. Either one keeps the balance comfortable.
Getting the timing right
Most forecast errors are timing errors, not amount errors. A few habits make the dates more reliable.
Use your real collection pattern. Look at last quarter's invoices and note how many days customers actually took to pay. If most pay in 30 days but a few large accounts take 60, split those accounts onto their own row.
Map every fixed payment to its date. Payroll dates, rent, loan repayments, and subscription renewals rarely move. Put each in the exact week or month it leaves the account.
Separate one-off items. Equipment purchases, deposits, and annual fees distort the pattern if they sit inside a general row. Give each a row of its own and a note explaining it.
Keep a buffer line. Some teams add a small contingency row for costs they cannot yet name. It keeps the cash flow forecast honest about uncertainty instead of pretending every cost is known.
Check against the bank. At the end of each period, compare the forecast closing balance with the actual bank balance. A gap of more than a few percent means an assumption needs fixing.
How to read the forecast
A finished forecast answers three questions.
When is cash lowest? Find the lowest closing balance and the month it falls in. That is the number to plan around, not the year-end total.
Why is it low? Look at the rows for that month. The cause is usually timing, such as a slow-paying customer, a quarterly bill, or a seasonal dip in sales.
What can move? Some rows can shift and some cannot. Payroll rarely moves. Supplier terms, collection efforts, and the timing of large purchases often can.
If the forecast needs to sit next to your budget, pair it with a budget vs. actual report. The budget shows what you planned to earn and spend, and the forecast shows when the cash moves.
Doing it faster with AI
The Excel method works well once the data is clean. It gets slow when the inputs arrive as bank exports, receivables ageing reports, and payables lists that need sorting first.
An AI workspace can do the sorting and the arithmetic in one request. Upload the exports to Powerdrill Bloom and ask for a weekly or monthly forecast. Ask for an opening balance, categorized inflows and outflows, and a closing balance for each period.
Its AI cash flow analysis tool page describes this job specifically. It covers "cash position, runway, working capital and 13-week cash flow" from accounting data, with scenario projections. The page lists bank statements, receivables ageing, payables ageing, and an existing forecast as inputs, and branded PDF, deck, or canvas link as outputs.
Check the result the way you would check a spreadsheet. Confirm the opening balance against the bank, and spot-check two months of receipts and payments. For comparisons of dedicated software, see this roundup of cash flow forecasting tools. For wider spreadsheet work, the Excel AI assistant page covers the same file-first approach.
Common mistakes
These errors make forecasts unreliable:
- Using invoice dates instead of payment dates. Cash arrives when customers pay, not when you bill them.
- Forgetting irregular payments. Quarterly tax, annual insurance, and loan balloon payments cause the deepest dips.
- Mixing figures with and without sales tax. The business.gov.au site advises stating clearly whether figures include or exclude it.
- Hard-coding the opening balances. Link each opening balance to the previous closing balance so changes flow through.
- Never updating it. A forecast built once and left alone drifts away from reality within a few months.
- Leaving estimates unlabeled. The business.gov.au site recommends labeling estimated costs and explaining them clearly.
When your inputs are bank exports and ageing reports, you can try Powerdrill Bloom. It turns them into a forecast you can check line by line.
Frequently asked questions
What is a cash flow forecast?
A cash flow forecast is an estimate of the cash coming into and going out of a business over future periods. The business.gov.au site describes it as "an estimate of your future sales and costs." It shows whether you will have enough income to cover your costs.
How do you make a cash flow forecast in Excel?
List the periods across the top and the line items down the side. Enter the opening balance, forecast cash incoming and outgoing for each period, and total each group. Then calculate net cash flow and the closing balance, and link each opening balance to the previous closing balance.
What is the formula for closing cash balance?
The closing balance equals the opening balance plus total cash incoming, minus total cash outgoing. In Excel, if the opening balance is in B2 and net cash flow is in B18, the formula is =B2+B18. Each next period's opening balance links to this cell.
How far ahead should a cash flow forecast go?
Most businesses forecast 12 months ahead in monthly periods for planning. When cash is tight, a rolling 13-week forecast in weekly periods shows timing gaps that a monthly view can hide. Some teams keep both.
What is the difference between a cash flow forecast and a cash flow statement?
A cash flow statement records the cash that actually moved in a past period. A forecast uses the same structure with estimated figures for future periods. The business.gov.au site notes that you can build one from its statement template by entering estimates for each future period.
Sources: business.gov.au, Set up a cash flow statement · business.gov.au, Cash flow. Guidance read on September 24, 2026.