A cash flow forecast spreadsheet helps you see when money is expected to arrive, when obligations are due, how much runway remains, and when a funding or spending decision needs attention. This guide explains how to build a maintainable cash flow template in Excel or Google Sheets, choose useful assumptions, test timing risks, and update the forecast as actual results replace estimates.
Overview
A cash flow forecast is a forward-looking schedule of cash receipts and cash payments. It is different from a profit report: a profitable sale may not produce cash until a customer pays, while a loan repayment or equipment purchase may reduce cash without appearing as an ordinary operating expense in the same period.
A practical cash flow forecast spreadsheet should answer four questions:
- What cash balance is available at the start of each period?
- How much cash is expected to come in, and when?
- Which payments are due, and when will they leave the account?
- What will the ending balance and remaining runway be if the assumptions hold?
For most small businesses, a monthly model is a useful starting point. A weekly view may be more appropriate when balances are tight or receipts and payments move frequently. The best format is the one you can maintain consistently. A detailed model that is not updated is less useful than a simple forecast with current information.
Consider organizing the workbook into separate tabs for Inputs, Forecast, Actuals, and Summary. Keep assumptions in one place rather than embedding numbers throughout formulas. This makes the workbook easier to review when prices, payment terms, staffing plans, or financing needs change.
How to estimate cash flow
Begin with a period-by-period cash roll-forward. The core calculation is:
Ending cash = Opening cash + Total receipts − Total payments
The opening cash balance for the next period should equal the previous period’s ending balance. In a spreadsheet, this creates a visible chain from today’s cash position to the forecast horizon.
1. Set the forecast periods
Choose weekly or monthly columns and define a forecast horizon that is long enough to cover the decisions you need to make. A short-term cash view can highlight upcoming payment pressure, while a longer view can show seasonal gaps, planned investments, or a likely need for financing.
2. List expected receipts by timing
Separate receipts into useful categories, such as customer payments, subscriptions, deposits, loans, owner contributions, grants, asset sales, or other income. Record the period in which cash is expected to arrive—not simply the period in which an invoice is issued.
If customers pay on different terms, create separate lines for immediate payments and delayed collections. You can also add a collection-rate assumption to distinguish billed sales from cash actually received. For example, a forecast might assume that a portion of current-period sales is collected immediately and the remainder arrives in later periods. Label this clearly so the assumption can be challenged and updated.
3. List payments by due date
Group payments into recurring operating costs, payroll or contractor costs, inventory and materials, taxes or statutory obligations, debt service, capital purchases, owner distributions, and one-time items. Payment timing matters as much as the amount. A monthly software subscription, an annual insurance payment, and a supplier invoice with delayed terms should not be spread across the forecast unless that reflects the actual cash schedule.
4. Calculate the balance and runway
Calculate net cash flow for each period:
Net cash flow = Total receipts − Total payments
Then calculate the ending balance using the roll-forward formula. To estimate runway, divide the available cash by an appropriate average cash burn. A simple version is:
Runway in periods = Available cash ÷ Average net cash outflow per period
Use this as an indicator rather than a promise. Runway changes when receipts arrive, expenses shift, or a large one-time payment is introduced. If the business has positive and negative periods, a period-by-period balance chart is usually more informative than one average.
Inputs and assumptions
A forecast is only as useful as the assumptions behind it. Add an assumptions table with the input name, value, source or rationale, date reviewed, and notes. This creates an audit trail without requiring a complicated financial model spreadsheet.
Opening balance
Enter the cash that is actually available for the forecast. If multiple accounts are included, decide whether to show them separately or combine them. Restricted funds, reserved tax cash, and credit facilities should be identified rather than quietly treated as unrestricted cash.
Sales and collection timing
Use a sales or invoice schedule when possible, then map expected collection dates into the forecast. Avoid treating every sale as an immediate receipt. Include a separate assumption for late or uncertain collections if that risk is material.
Recurring and variable expenses
Recurring costs can be entered as fixed monthly amounts, while variable costs can be tied to a driver such as units sold, project volume, or payroll. Keep one-time expenses on separate lines so they are easy to review and remove after the payment occurs.
Scenario controls
Add a scenario selector or separate columns for base, cautious, and stronger cases. Change only a few clearly defined variables, such as sales volume, collection timing, supplier costs, hiring dates, or payment delays. A scenario analysis spreadsheet can help structure these comparisons. The purpose is not to predict one exact outcome; it is to show which assumptions have the greatest effect on cash.
Controls and checks
Include checks for missing opening balances, negative cash, duplicate payment lines, and formulas that do not reconcile. Use conditional formatting to highlight an ending balance below a chosen internal threshold. Do not hide negative values: they identify a funding gap, timing problem, or assumption that needs a decision.
Worked examples
Assume a small service business begins a month with $18,000 in available cash. It expects $12,000 in customer receipts, including $7,000 from existing invoices and $5,000 from new work. Planned payments are $6,000 for payroll and contractors, $2,500 for operating expenses, $3,000 for taxes or other obligations, and $1,500 for equipment.
The calculation is:
- Opening cash: $18,000
- Total receipts: $12,000
- Total payments: $13,000
- Net cash flow: −$1,000
- Ending cash: $17,000
The result appears comfortable in isolation, but timing may change the decision. If the $7,000 customer receipt is delayed to the following month, the business would have $10,000 of cash before considering any additional receipts. The forecast should therefore show receipt dates, not only monthly totals.
Now add a cautious scenario in which new-work receipts fall from $5,000 to $2,000 and equipment spending is postponed. Receipts become $9,000, payments become $11,500, and net cash flow becomes −$2,500. Comparing the scenarios shows that collection performance and discretionary spending are important cash levers. The owner can then decide whether to accelerate collections, defer the equipment purchase, reduce another payment, or arrange funding before the balance becomes urgent.
For a more complete planning system, connect the forecast to a KPI dashboard spreadsheet that tracks sales, receivables, gross margin, and cash balance. Keep the cash forecast as the financial model, and use the dashboard to monitor the operating indicators that explain changes in the model.
When to recalculate
Update the forecast on a defined cadence and whenever a material assumption changes. A weekly review is useful when cash is tight, receipts are unpredictable, or payments are frequent. A monthly review may be sufficient when the business has stable billing and predictable obligations.
At each review:
- Replace completed forecast periods with actual receipts and payments.
- Compare actual cash flow with the forecast and record the main variance.
- Move unpaid receipts and payments to their most likely new dates.
- Refresh sales expectations, collection timing, payroll, supplier costs, and planned purchases.
- Review the lowest projected cash balance, not only the final balance.
- Run the cautious scenario before approving large discretionary spending.
Recalculate immediately when pricing inputs change, a major customer delays payment, a supplier changes terms, staffing plans move, financing is added or repaid, or a large purchase is considered. Link the forecast to the wider planning process with a strategic planning spreadsheet so cash consequences are visible alongside goals and initiatives.
Finally, keep a dated copy or change log for important revisions. The objective of a cash flow template is not to produce a perfect prediction. It is to create an honest, repeatable view of what may happen next and give you enough warning to make a measured decision.
