A practical cash flow model Excel template lays out monthly opening cash, cash received, cash paid, net cash flow, and ending cash, with formulas that roll each month into the next. In Excel for Microsoft 365 on Windows, create a worksheet with months across columns and cash flow categories down rows; the example below gives you a reusable structure and working formulas.
What a cash flow model shows
A cash flow model estimates when money is expected to enter and leave a business, then shows the resulting cash balance over time. It is useful for planning payment timing, spotting periods when cash may run short, and comparing an expected outcome with actual results. A forecast is only as useful as its assumptions: an accurate sales estimate does not guarantee enough cash if customers pay late or bills come due earlier.
Cash flow is not the same as profit. A business can record a sale as revenue before collecting the customer’s payment, and it can incur an expense before paying the bill. Depreciation, for example, may reduce accounting profit without being a cash payment in that month. A cash model focuses on the timing of actual receipts and payments, while accounting reports may recognize revenue and expenses on a different schedule.
The template described here uses a direct cash view: it lists receipts and payments by month. It is intended as a planning workbook, not a replacement for a formal set of financial statements or professional advice. For a personal budget, the same structure works if you replace business categories with pay, benefits, housing, food, debt payments, savings, and other personal cash items.
Recommended worksheet layout
Use one worksheet called Cash Forecast. Put category names in column A and twelve months in columns B through M, with a separate assumptions or notes column to the right if needed. For a rolling forecast, keep the next twelve months visible and move the month headers forward as time passes. A simple layout can use these rows:
- Row 1: monthly headings.
- Row 2: opening cash balance.
- Rows 3–4: customer receipts and other cash receipts.
- Row 5: total cash inflows.
- Rows 6–9: supplier payments, payroll, rent and other operating payments, plus taxes, debt payments, or other relevant cash outflows.
- Row 10: total cash outflows.
- Row 11: net cash flow.
- Row 12: ending cash balance.
Keep categories specific enough to explain large movements, but not so detailed that maintaining the forecast becomes burdensome. A retailer might split supplier payments, payroll, rent, card fees, and sales tax. A consultant might track client receipts, subcontractors, payroll, software, rent, and tax payments. If a category is immaterial or difficult to estimate, it can be grouped under a clearly named other category and reviewed periodically.
Build the monthly template
- Enter the first month in B1 and continue one month per column through M1. Apply a date format such as mmm-yy so the headings stay readable.
- Enter the opening cash balance in B2. Use the balance expected at the start of the forecast, and note the source, such as a bank reconciliation date.
- Enter forecast receipts and payments in the rows below. Base them on expected payment dates rather than simply copying sales and expense totals from an income statement.
- Add formulas for totals, net cash flow, and ending balance. Copy formulas across the forecast months where appropriate.
- Compare actual results with the forecast each month, investigate material differences, and update future assumptions rather than overwriting prior forecast values without a record.
For the first month heading, with cell B1 designated as January 1, 2026, the layout assumes B1 contains the first forecast date. Enter =DATE(2026,1,1); the displayed date is January 1, 2026. This is a real date value, so Excel can format it and use it in date calculations. For the next heading, with January in B1 and February in C1, enter =EDATE(B1,1) in C1; the result is February 1, 2026. Fill that formula right to create subsequent monthly headings.
Core formulas and a worked example
Assume the row layout above and that January values are in column B. In the example, opening cash is $12,000; customer receipts are $18,000; other receipts are $1,000; supplier payments are $7,000; payroll is $6,000; rent and other operating payments are $2,000; and taxes, debt payments, or other cash outflows total $1,000. These are illustrative figures, not estimates for any particular business.
With customer receipts in B3 and other receipts in B4, and the total inflows in B5, enter =SUM(B3:B4) in B5. It returns $19,000 from the example values. The SUM formula adds the listed receipt rows, making it easy to add another receipt category within the range later.
With payment categories in B6:B9 and total outflows in B10, enter =SUM(B6:B9) in B10. It returns $16,000: $7,000 plus $6,000 plus $2,000 plus $1,000.
With total inflows in B5 and total outflows in B10, enter =B5-B10 in B11. January net cash flow is positive $3,000, because $19,000 of receipts less $16,000 of payments equals $3,000.
With opening cash in B2 and net cash flow in B11, enter =B2+B11 in B12. January ending cash is $15,000: the $12,000 opening balance plus $3,000 net cash flow. For February, with the February opening balance in C2 and January ending cash in B12, enter =B12 in C2. The result is $15,000, which links the months and prevents the forecast from treating each month as if it began with an unrelated balance. Repeat the formula pattern across the other months.
Forecast assumptions that matter
For each material line, identify the driver behind the number and when the cash is expected to move. A sales forecast might use expected customer invoices and collection timing; a payroll forecast might use pay dates and planned staffing; rent may follow the lease schedule. When customer payment timing varies, a separate collections schedule can make the forecast more realistic than entering all sales as same-month cash.
Record assumptions beside the model or on a separate Assumptions worksheet. Useful notes include the source, date updated, responsible person, and whether a figure is contracted, estimated, or uncertain. Avoid hiding important assumptions inside unexplained hard-coded totals. For instance, an expected receipt of $18,000 is more informative when the model indicates which customers or invoices support it and whether collection is expected in full.
Build more than one scenario when uncertainty is significant. A base case can use the most supportable assumptions; a downside case can test slower collections or higher costs; an upside case can reflect stronger receipts. Change the assumptions, not the formulas, and label the scenarios clearly. Scenario outputs are planning illustrations, not guarantees, and should not be confused with actual cash on hand.
Cash flow forecasts versus cash flow statements
A forecast looks forward and depends on estimates. A statement of cash flows reports historical activity for a period and is prepared under applicable accounting rules. The rows in this planning template are organized to help manage timing, so they may not map exactly to the operating, investing, and financing sections of a formal statement.
For a more complete business model, separate cash items into operating activities, investing activities, and financing activities. Examples include customer collections and supplier payments for operations, equipment purchases for investing, and loan proceeds or principal repayments for financing. Keep loan principal distinct from interest where the distinction matters to your reporting and analysis. Confirm how items should be classified for formal reporting with an accountant or other qualified professional.
A common misconception is that a positive net cash flow means the business is profitable. It only means receipts exceeded payments in that period under the model’s definitions. A business may receive a loan or sell equipment and show cash coming in without earning operating profit; conversely, it may report profit while waiting for customers to pay. Read the cash balance together with the assumptions and other relevant records.
Checks, formatting, and common problems
Use a few simple controls to make errors easier to notice. Check that the first opening balance agrees with a dated source, that each later opening balance links to the prior month’s ending balance, and that the formula rows extend through every forecast month. Check that outflows are entered as positive amounts if the model subtracts total outflows from inflows, and use the same convention consistently. Mixing negative payment inputs with a formula that already subtracts outflows can reverse the result.
To flag a negative ending balance, select the forecast range B12:M12 and use Home > Conditional Formatting > Highlight Cells Rules > Less Than. Enter 0 and select a noticeable format. This highlights a projected shortfall; it does not prove that the model is wrong or establish what action to take. Review the timing and assumptions behind the affected month. A projected negative balance can also reveal a need to revise the plan or discuss options with a lender or qualified professional.
Other common problems include treating invoice dates as collection dates, omitting irregular annual costs, forgetting taxes or debt payments, and assuming that every month has the same sales pattern. A model can also become difficult to audit if formulas are replaced with typed totals or if actuals and forecasts are mixed without labels. Keep actual and forecast figures distinguishable, retain a dated copy when making material forecast revisions, and reconcile actual cash movements to bank records.
Choosing or adapting a template
A useful template should be transparent, not merely attractive. Before using a downloaded workbook, inspect its formulas, date ranges, categories, and any hidden worksheets. Confirm that the closing balance rolls forward correctly, that input cells are distinguishable from calculated cells, and that the workbook does not depend on unfamiliar add-ins or macros. A workbook with macros may require additional security review; the simple model described here needs no VBA.
Adapt the model to the decisions it needs to support. A small operation with a tight cash cycle may need weekly detail for near-term payments and monthly totals further out. A stable business may find a monthly forecast sufficient. If customers pay on different terms, a receivables schedule can support collections estimates. If expenses vary with sales or production, document the driver and timing rather than forecasting the cost as an unexplained flat number.
Budget and financing decisions depend on real contracts and circumstances. The figures here are only a spreadsheet illustration, not financial, tax, investment, or legal advice. Check actual payment terms with the relevant lender or supplier and consult a qualified professional for advice specific to your situation.
Frequently Asked Questions
Is there a free cash flow model Excel template?
Excel itself can be used to build a template from the layout and formulas above, and organizations may offer downloadable workbooks. Review a download before entering sensitive information: verify its formulas, source, external links, and any macros. A template is useful only if its categories and timing fit your needs.
What is the difference between a cash flow model and a cash flow statement?
A cash flow model forecasts expected receipts, payments, and balances. A cash flow statement summarizes historical cash activity using applicable reporting rules. The forecast can help plan ahead, but it is not automatically a formal statement and should not be presented as one.
Should cash outflows be entered as negative numbers?
Either sign convention can work if formulas match it. In the example, enter outflows as positive amounts, sum them, and subtract total outflows from inflows. If you instead enter payments as negative values and add them to receipts, do not also subtract the total a second time.
How many months should a cash flow forecast cover?
There is no single correct horizon. A twelve-month monthly forecast is a practical starting layout because it shows seasonality and a full year of planned activity. A business facing immediate payment pressure may need more frequent near-term detail, while longer-term estimates usually become less certain and should be treated as scenarios.
Can the same template be used for a personal budget?
Yes. Replace business receipts and payments with household income and spending categories, and use the opening and ending balances for the cash account being tracked. Keep transfers between accounts separate from income and spending if counting them as both would double-count the money.
Related
- Bluebeam Quantity Link to Excel: Setup and Limits
- Bluebeam Export to Excel: Steps, Fields, and Fixes
- Blank Checklist Template Excel: Create One That Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Shortcut to Highlight All Data in a Column QuicklyIn Excel for Microsoft 365 on Windows, click the first cell in the column’s data and press Ctrl+Shift+Down…
- Excel Print Area Greyed Out: Why It Happens and FixesIn Excel for Microsoft 365 on Windows, the Print Area commands are most often greyed out because multiple…
- Conditional Formatting Alternate Row Color in ExcelTo alternate row colors in Excel, select the cells to format, choose Home > Conditional Formatting > New…
- Excel Web Keyboard Shortcuts: Essential Keys and TipsExcel for the web supports many of the familiar Excel keyboard shortcuts: use Ctrl+C, Ctrl+V, and Ctrl+Z on…
- How to Group Rows in Excel Under a Heading: Step-by-StepTo group rows under a heading in Excel, leave the heading row outside the selection, select the detail…
- Wrap Text in Excel: Keep Long Cell Entries VisibleTo keep text wrapped in Excel, select the cell or range, then choose Home > Alignment > Wrap…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.