Quick answer: A basic cash flow forecast is a table with one column per month and four key lines: opening cash, cash coming in, cash going out, and closing cash. Each month's closing balance becomes the next month's opening balance. You can build this in Excel in under an hour and refine it as you learn your patterns.
Why forecast cash, not just profit?
A profit and loss statement shows performance, but it does not show when money actually moves. A business can be profitable and still run short of cash if customers pay late or a large bill falls due. Forecasting cash helps you see shortfalls early. For background on the three core reports, see Understanding Financial Reports: P&L, Balance Sheet and Cash Flow.
Step 1: Set up the layout
Open a new sheet. Put the months (for example Jan to Dec) across row 1, starting in column B. Then add these labels down column A:
- Opening cash
- Cash in: customer payments, other income
- Total cash in
- Cash out: rent, payroll, suppliers, software, tax payments, loan repayments
- Total cash out
- Net cash flow
- Closing cash
Step 2: Enter your starting balance
In the first month's opening cash cell, enter your actual bank balance on the first day of the period. Use the real figure from your bank or your reconciled books.
Step 3: Forecast cash coming in
Base it on when customers pay, not when you invoice. If your customers typically pay about 30 days after an invoice, then January sales appear as cash in February. Start from your actual invoice history and unpaid invoices rather than hopeful targets.
Step 4: Forecast cash going out
List fixed costs first (rent, subscriptions, loan payments), then variable costs (supplies, shipping, contractor fees). Add irregular items in the month they fall due: annual insurance, tax payments, equipment purchases. Missing these is the most common cause of surprise shortfalls.
Step 5: Add the formulas
Use these formulas, assuming row 3 is Total cash in, row 5 is Total cash out, row 6 is Net cash flow, row 7 is Closing cash, and the first month is column B:
Total cash in (B3): =SUM(B2:B2) -> extend the range to cover every income line Total cash out (B5): =SUM(B4:B4) -> extend the range to cover every expense line Net cash flow (B6): =B3-B5 Closing cash (B7): =B1+B6 -> if opening cash is in B1 Next opening cash (C1): =B7
Copy the formulas across to column M so every month links to the previous month's closing balance. Adjust the cell references to match your own layout.
A hypothetical example
The numbers below are invented to show the mechanics, not benchmarks.
| Month 1 | Month 2 | Month 3 | |
|---|---|---|---|
| Opening cash | 5,000 | 5,800 | 4,300 |
| Total cash in | 6,000 | 4,500 | 7,000 |
| Total cash out | 5,200 | 6,000 | 5,500 |
| Net cash flow | 800 | -1,500 | 1,500 |
| Closing cash | 5,800 | 4,300 | 5,800 |
Even in this small example, Month 2 shows a negative net cash flow. Seeing that ahead of time lets you adjust: chase invoices, delay a purchase, or arrange a buffer.
Step 6: Add a minimum cash buffer
Decide the lowest balance you are comfortable holding. Add a row that checks it, for example =IF(B7<minimum,"Below buffer","OK"), and use conditional formatting to highlight months that fall below it. Your buffer is a personal decision and depends on your business.
Step 7: Compare forecast with actuals
Each month, add a second block with actual figures and note the gaps. Over a few months you will learn which assumptions were too optimistic, and your forecast will improve. Treat the forecast as an estimate, not a promise.
Common mistakes
- Forecasting when you invoice instead of when customers pay.
- Forgetting annual or quarterly payments.
- Mixing profit figures with cash figures.
- Never updating the sheet after the first month.
- Using an unreconciled opening balance. Reconcile your bank account first, as explained in the bookkeeping and accounting guide.
Frequently asked questions
Should I forecast weekly or monthly?
Monthly is a good starting point. If cash is tight, a weekly view for the next 8 to 13 weeks gives more precision.
Can I build this in Google Sheets?
Yes. The layout and formulas work the same way in Google Sheets.
How accurate will it be?
It depends on the quality of your assumptions. Forecasts are estimates and cannot guarantee outcomes.
Conclusion
A simple cash flow forecast turns bank balances into a plan. Build the layout, link each month to the last, update it regularly, and review the gaps.
Disclosure: the owner of this site offers cash flow forecasting and bookkeeping support on Fiverr. If you would rather have help, see the services profile. This article is general education, not financial advice.
comments