0 ITEMS
How to Build a Monthly Cash Flow Forecast: A Step-by-Step Method
A monthly cash flow forecast is built by starting with opening cash, adding the cash expected from customers, subtracting the cash due to suppliers, staff and lenders, and checking each closing balance against a minimum buffer. The six steps below show a growth-stage business owner how to do this, with a six-month worked example that recomputes from its own inputs.
Key Takeaways
- A cash flow forecast records when money actually moves, not when sales are booked, so profit and cash can diverge sharply.
- Every month follows one chain: opening cash, plus expected receipts, minus expected payments, equals closing cash.
- Collection timing is a common source of forecast error, so spread receipts across the months in which invoices are actually paid.
- A minimum cash buffer turns the forecast into an early-warning tool by flagging every month that closes below it.
- Comparing actual cash with the forecast each month, then revising the months ahead, keeps the forecast reliable.
Four Inputs Are Needed Before Building the Forecast
The forecast needs the current bank balance, a sales forecast, the payment terms that customers and suppliers actually follow, and a list of fixed commitments such as payroll, rent and loan repayments. A small and medium-sized enterprise (SME) usually already holds these in bank statements, invoicing records and contracts.
The method rests on the direct approach, which projects gross cash receipts and gross cash payments instead of adjusting profit. The International Financial Reporting Standards (IFRS) Foundation page on its cash flow standard describes both the direct and the indirect way of reporting operating cash flows. For a forecast the direct form is the practical choice, because the owner already knows which invoices and bills are coming.
Profit and cash differ because a sale is recorded when it is invoiced, while the money arrives later. Business Wales, the Welsh Government-branded business support site, notes that a business can show a profit on paper yet have negative cash flow and be unable to pay its bills.
Six Steps to Build a Monthly Cash Flow Forecast
The six steps below turn those four inputs into a working forecast. Each step answers one question in order: where cash starts, when it arrives, when it leaves, what remains at month end, whether that is enough, and how far reality has moved from the plan. The worked example after Step 6 applies all six steps to one company.
Step 1: Start From the Actual Bank Balance
The forecast opens with the reconciled cash held in the bank accounts on the first day of the period, not an accounting estimate. Money owed by customers and stock on the shelves is not cash on hand, and it enters the forecast only when it is collected or sold.
Step 2: Place Cash Inflows in the Month They Arrive
Each receipt belongs in the month the money is expected to reach the bank, which is usually later than the month of the sale. Begin with the sales forecast, then apply a collection pattern: the share of each month’s invoices paid in the same month, the next month and two months later. Past bank statements and accounts receivable records reveal that pattern, and days sales outstanding (DSO), the average number of days a business takes to collect payment after a credit sale, provides a quick cross-check.
Late payment makes this step the most sensitive one. Intrum’s European Payment Report 2026 found that about 12% of revenue arrives late on average among the executives surveyed.
Step 3: Place Cash Outflows in the Month They Are Paid
Each payment belongs in the month it leaves the bank. Group payments into three types: supplier payments, which follow agreed terms such as 30 days; fixed commitments such as payroll, rent and loan repayments; and one-off items such as equipment purchases or tax bills. Supplier terms matter because a purchase made this month may not be paid until next month.
Step 4: Calculate Net Cash Flow and Closing Cash
Net cash flow for a month equals total cash inflows minus total cash outflows, and closing cash equals opening cash plus net cash flow. The closing balance of one month becomes the opening balance of the next. In a spreadsheet, each opening balance should be a formula pointing at the previous closing balance, never a typed number, and a check cell should confirm that total net cash flow equals the change in cash.
Step 5: Set a Minimum Cash Buffer and Test Every Month Against It
A minimum cash buffer is a balance the business agrees never to fall below, and any forecast month that closes beneath it is a warning to act. This guide sizes the buffer at one month of fixed operating costs, a starting point that each business should adjust to how volatile its own receipts are.
Cash pressure is common among small firms. In the Federal Reserve Banks’ 2025 Small Business Credit Survey report on employer firms, based on a survey fielded in late 2024, 51% of small employer firms surveyed cited uneven cash flows as a challenge.
Step 6: Compare Actual Cash With the Forecast Every Month
After each month closes, the actual cash result goes beside the forecast, the largest differences are identified, and the remaining months are revised. The Australian Taxation Office advises monitoring a cash flow budget as circumstances change and watching for significant differences between budgeted amounts and actual results, and Business Wales suggests an actual column beside each forecast month. Recording why each gap occurred improves the collection pattern used in Step 2. A monthly budgeting routine makes this review a habit.
Worked Example: Six Months of Cash Flow Show Month 4 Dipping Below the Buffer
This worked example combines the six steps using illustrative figures. Assume a growth-stage company with $80,000 in opening cash. Assume credit sales of $120,000 in Month 1, rising by $10,000 each month, after sales of $100,000 two months before Month 1 and $110,000 one month before it. Assume customers pay 30% of each month’s invoices in the same month, 50% in the next month and 20% two months later. Assume supplier payments equal 45% of the previous month’s sales, fixed operating costs are $56,000 a month (payroll $40,000, rent $6,000, other operating costs $10,000), a separate loan repayment is $5,000 a month, and one equipment purchase of $80,000 falls in Month 4. Assume the minimum buffer is one month of fixed operating costs, or $56,000.
| Month | Sales | Cash collected | Cash paid out | Net cash flow | Closing cash |
|---|---|---|---|---|---|
| 1 | 120,000 | 111,000 | 110,500 | 500 | 80,500 |
| 2 | 130,000 | 121,000 | 115,000 | 6,000 | 86,500 |
| 3 | 140,000 | 131,000 | 119,500 | 11,500 | 98,000 |
| 4 | 150,000 | 141,000 | 204,000 | -63,000 | 35,000 |
| 5 | 160,000 | 151,000 | 128,500 | 22,500 | 57,500 |
| 6 | 170,000 | 161,000 | 133,000 | 28,000 | 85,500 |
Month 1 shows the gap between sales and cash. Sales are $120,000, but cash collected is $111,000: $36,000 from Month 1 invoices, $55,000 from invoices issued one month earlier and $20,000 from invoices issued two months earlier. Cash paid out is $110,500, made up of $49,500 to suppliers, $56,000 of fixed operating costs and the $5,000 loan repayment.
Month 4 is the lesson. Sales reach $150,000, yet cash paid out climbs to $204,000 because of the $80,000 equipment purchase, so closing cash falls to $35,000, which is $21,000 below the buffer. The forecast exposes this months ahead, leaving time to collect earlier, negotiate supplier terms or finance the purchase. The check cell confirms the result: $80,000 opening cash plus $5,500 of total net cash flow equals $85,500 closing cash in Month 6.
Common Mistakes to Avoid
Four mistakes commonly weaken a cash flow forecast: recording receipts when invoiced instead of when paid, leaving out one-off outflows such as taxes and equipment, never comparing the forecast with actual results, and typing closing balances instead of linking them. Each one makes the forecast look healthier than the bank account will.
Frequently Asked Questions
How often should a monthly cash flow forecast be updated?
The forecast should be refreshed once per month, right after the bank reconciliation, so that real balances replace estimates. In a tight period, such as before a large purchase or a loan payment, a weekly refresh of the next two months gives earlier warning. Each update should also roll the horizon forward by one month, which keeps a full year of projections available at all times.
How far ahead should a cash flow forecast look?
Most growth-stage businesses benefit from a twelve-month horizon, because it covers seasonal swings, annual bills and the timing of planned investments. Where cash is already tight, a short-term view of about thirteen weeks adds detail for the near term, while the twelve-month view shows whether today’s decisions create problems later in the year. The detail should be highest in the first months and coarser after that.
What is the difference between a cash flow forecast and a profit and loss forecast?
A profit and loss forecast records revenue when it is earned and costs when they are incurred, so it includes non-cash items such as depreciation. A cash flow forecast records only money entering and leaving the bank. Equipment purchases and loan principal repayments therefore appear in the cash forecast even though neither is a day-to-day expense, while depreciation does not appear in it at all.
How do I build a cash flow forecast in Excel?
Put months across the columns and place opening cash, receipts, payments, net cash flow and closing cash down the rows. Keep every assumption, such as collection percentages and supplier terms, on a separate inputs sheet so that formulas never contain typed numbers, and add a check cell that proves the closing balance reconciles. Ready-made structures are listed in the Cash Flow Projections collection at eFinancialModels.
What should a business do if the forecast shows a cash shortfall?
The first move is to test which lever closes the gap at the lowest cost. Sending invoices sooner, asking for deposits or tightening credit terms raises receipts; agreeing longer supplier terms or spreading a large purchase lowers early payments. If those fall short, an overdraft or credit line arranged before the shortfall appears is usually cheaper and easier to obtain than emergency funding.
Conclusion: Build the Forecast Monthly and Keep It Current
A monthly cash flow forecast works when receipts and payments sit in the months the money moves, each closing balance feeds the next opening balance, and a buffer flags thin months early. The worked example shows sales growing every month while cash still dips below the buffer in Month 4, which is exactly the gap a forecast exists to reveal. Owners who want a ready-made structure can start from the Monthly Budget Template (with Financial Statements) from eFinancialModels, a one-year monthly Excel budget that includes a cash flows statement and actual-versus-budget charts, and then browse further options at efinancialmodels.com.
