Cash Flow Excel Sheet: Build a Reliable Monthly Forecast

How-to basics · Updated November 18, 2026

A cash flow Excel sheet tracks when money comes in and goes out, then calculates the cash left at the end of each period. For a simple monthly forecast, list your opening cash, expected receipts and expected payments; calculate net cash flow as receipts minus payments, then add it to opening cash.

What a cash flow sheet shows

A cash flow sheet is a practical view of timing: it helps you estimate whether cash will be available when bills are due. A profitable business can still face a cash shortage if customers pay late, inventory must be purchased early, or several large bills fall due in the same week. A household can use the same structure to compare paydays with rent, loan payments and other expenses.

It is useful to distinguish a cash flow forecast from an income statement. An income statement generally records revenue and expenses when they are earned or incurred under the accounting method being used. A cash forecast records expected cash receipts and payments when they are expected to happen. For example, a sale made on credit may count as revenue before the customer pays; the forecast should show the receipt in the month payment is expected.

Set up a simple monthly workbook

In Excel for Microsoft 365 on Windows, start with a blank worksheet. Put the month names or month-start dates across row 1, beginning in B1, and place the cash categories in column A. One straightforward layout is:

  • Row 2: Beginning cash
  • Rows 3–4: Customer or paycheck receipts, and other cash received
  • Row 5: Total cash received
  • Rows 7–10: Payroll, housing or operating costs, suppliers or groceries, and other payments
  • Row 11: Total cash paid
  • Row 12: Net cash flow
  • Row 13: Ending cash

Leave row 6 blank to separate receipts from payments visually. Add, remove or rename categories to fit the purpose. A small shop might separate rent, payroll, inventory, utilities and loan payments; a household might use rent, food, transport, insurance and savings. Keep categories specific enough to explain a shortfall, but not so detailed that updating the sheet becomes a chore.

Enter formulas and connect each month

With total receipts in row 5 and individual receipt amounts in B3:B4, enter this in B5 and copy it across the forecast months:

=SUM(B3:B4)

With payments listed in B7:B10 and the total cash paid in row 11, enter this in B11 and fill it across:

=SUM(B7:B10)

With total cash received in B5 and total cash paid in B11, enter this in B12 to calculate the month’s net cash flow:

=B5-B11

With opening cash in B2 and net cash flow in B12, enter this in B13 to calculate ending cash:

=B2+B12

For the next month, link its opening balance to the previous month’s ending balance. With January ending cash in B13 and February beginning cash in C2, enter this in C2:

=B13

Copy that link across the remaining months. Enter the real opening cash amount in B2, then let the links carry the balance forward. This connection is important: treating each month as an unrelated calculation can hide a shortage that carries over from one month to the next.

Work through a sample month

Suppose a business begins January with $12,000. It expects $18,000 from customers and $2,000 from other receipts. It forecasts $11,000 for payroll, $3,000 for rent and utilities, and $2,000 for suppliers. Total receipts are $20,000, total payments are $16,000, and net cash flow is positive $4,000. Ending cash is therefore $16,000.

In the next month, the opening cash should be that $16,000, not the original $12,000. If February receipts are $14,000 and payments are $19,000, February net cash flow is negative $5,000 and ending cash is $11,000. A negative monthly net flow is not automatically a crisis: the opening balance may cover it. The ending balance and the timing of payments are what reveal whether cash is likely to run short.

These figures are illustrative arithmetic, not financial, tax, investment or legal advice. For a loan forecast, check actual payment dates, interest, fees and other terms with the lender or a qualified professional.

Choose a useful forecast period

A monthly forecast is a good starting point when income and bills are fairly regular, or when you are planning several months ahead. Use one column per month and look far enough forward to include seasonal changes, annual insurance payments, tax dates, renewals or planned equipment purchases. A 12-month view is common for planning, but the right horizon depends on what decisions the sheet needs to support.

If cash is tight or payment dates matter, a weekly forecast can expose gaps that monthly totals conceal. A month may show enough income to cover its expenses while a large bill is due before the money arrives. In that case, create one column per week, or use a separate short-term schedule with expected dates. Avoid mixing daily, weekly and monthly periods in the same calculation unless the periods are clearly defined.

For a simple monthly workbook, type the actual month-start dates into row 1 and apply a date format such as mmm-yy. In Excel for Windows, select the cells and use Home > Number Format to choose a date format. On a Mac, the Home tab also contains number-format controls, though the ribbon arrangement can differ slightly.

Make estimates more dependable

A forecast is only as useful as its assumptions. Separate confirmed amounts from estimates, and record the expected date rather than assuming every sale or invoice will be paid immediately. Review past bank activity, billing schedules and known commitments when estimating recurring amounts. For variable receipts, use a cautious estimate instead of relying on the most optimistic month.

  • Record customer receipts in the period you realistically expect to collect them, not simply when you issue an invoice.
  • Include irregular payments such as insurance, annual subscriptions, repairs, taxes or seasonal stock orders.
  • Show loan proceeds as cash received and scheduled repayments as cash paid when they occur. Keep the components clear enough to reconcile with actual statements.
  • Use a notes column or a separate assumptions area for the source of an estimate, such as a contract date or a planned payment schedule.

It can also help to make a base forecast and a cautious scenario on separate sheets. Change uncertain assumptions, such as collection dates or sales volume, and compare ending cash. Label each scenario clearly so an estimate is not mistaken for a guaranteed result.

Format the sheet for checking

Format the input cells and calculation cells differently—for example, use one fill color for amounts you enter and another for formulas. Apply a consistent currency format, use bold text for total rows, and freeze the top row if the workbook has many months or categories. These choices do not change the arithmetic, but they make it easier to spot an overwritten formula or a missing estimate.

To flag a forecast balance below a chosen threshold, select the ending-cash cells and use Home > Conditional Formatting > Highlight Cells Rules > Less Than. Enter the threshold and choose a format. A warning is a prompt to investigate, not proof that the forecast is wrong: confirm the date, assumptions and available cash before acting. You can also compare forecast figures with actual bank activity in a separate actuals column or worksheet, keeping the original forecast visible for reference.

Common mistakes to avoid

  • Counting sales as cash too early: An invoice is not cash in the bank. Estimate the collection period and enter the receipt when payment is expected.
  • Leaving out one-off costs: Annual bills and occasional repairs can create a shortfall even when ordinary months look healthy.
  • Breaking the balance chain: Each month’s opening cash should match the previous month’s ending cash. Check links after inserting columns or copying worksheets.
  • Summing balance rows as if they were flows: A year-end cash balance is a point-in-time amount, not a monthly receipt. For a yearly net-flow figure, add the monthly net cash flows; do not add the monthly ending balances.
  • Confusing a forecast with a bank reconciliation: Forecast estimates explain what may happen. Reconciliation checks recorded transactions against a bank statement. They serve different purposes.

When a template or another tool makes sense

Building the sheet yourself gives you control over categories and assumptions, and it can be enough for a simple household budget or a small operation with a few cash streams. A reputable Excel template can save setup time, but inspect its formulas, date ranges and included categories before relying on it. Check that it distinguishes cash received from sales recorded, and that opening and ending balances roll forward correctly.

Accounting or cash-management software may be more appropriate when several people need access, transactions must be imported, or reporting and audit trails matter. A spreadsheet can still be useful for planning scenarios, but it is easy to overwrite a formula or let estimates drift out of date. Whichever method you use, keep source records and review the forecast regularly against actual cash activity.

Frequently Asked Questions

Is there a free cash flow Excel sheet?
A basic one can be made in a blank Excel workbook using the layout and formulas above. Microsoft also provides templates through Excel’s template gallery, but availability can vary. Review any downloaded template’s formulas and assumptions before entering important figures.

What is the formula for cash flow in Excel?
For a simple period forecast, net cash flow is total cash received minus total cash paid. Ending cash is beginning cash plus net cash flow. Those calculations show cash movement; they are not a substitute for a formal statement prepared under applicable accounting rules.

Should a cash flow sheet be weekly or monthly?
Choose the period that matches the decisions you need to make. Monthly columns work for broad planning; weekly periods can reveal timing problems when bills and receipts arrive on different dates. A short-term weekly forecast can complement a longer monthly plan.

Is cash flow the same as profit?
No. Profit and cash flow can differ because sales on credit, unpaid bills, loan principal, equipment purchases and other timing or accounting items affect them differently. A cash forecast focuses on when cash is expected to enter or leave an account.

How often should I update the forecast?
Update it whenever a meaningful assumption changes and on a regular schedule—often weekly for a near-term cash plan and monthly for a longer-range forecast. Compare estimates with actual receipts and payments so future forecasts reflect what really happened.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.