Cash Flow Projection Template Excel: Build a Forecast

How-to basics · Updated November 21, 2026

A cash flow projection template in Excel is a worksheet that estimates when money will enter and leave a business, then shows the expected cash balance for each period. Set up columns for months and rows for opening cash, receipts, payments, net cash flow, and closing cash; use simple formulas to carry each month’s closing balance into the next month.

What a cash flow projection shows

A projection is a forward-looking estimate, not a record of what has already happened. It helps answer practical questions such as whether cash on hand is likely to cover payroll next month, when a large customer payment may arrive, or how a planned purchase could affect the balance. A business can be profitable on paper and still face a cash shortfall if it pays suppliers before customers pay invoices.

That timing is why a cash projection is different from a profit-and-loss statement. A profit-and-loss statement generally records revenue and expenses according to accounting rules, while a simple cash forecast focuses on expected cash receipts and payments and their dates. A forecast is also not the same as a historical cash flow statement, which reports cash movements that have already occurred.

Excel does not supply one universal cash flow projection template that fits every business. A useful workbook reflects the organization’s actual cash sources, payment schedule, and level of detail. The structure below is a practical starting point for a small business, project, or household forecast; adapt the categories and period to suit the purpose.

Choose a time period and forecast horizon

Use a period that matches the timing of important decisions. Monthly columns are often convenient for planning a year ahead because rent, payroll, and many bills recur monthly. A business with tight cash margins, fast-changing sales, or frequent settlements may need a weekly forecast for the near term, with monthly estimates farther out. A household tracking regular paydays and bills may prefer weekly or monthly periods.

  • Weekly: Makes near-term payment timing more visible, but requires more frequent updates and more detailed assumptions.
  • Monthly: Gives a readable overview for recurring bills, seasonal sales, and annual planning.
  • Quarterly: Can work for a high-level long-range view, but may hide a cash gap that occurs inside a quarter.

There is no single correct forecast horizon. Start with the time span needed for the decision at hand, and make the first periods more detailed if timing matters. If a monthly forecast suggests cash could run low in June, a weekly schedule for that month can reveal whether the issue is a particular payroll date, a delayed receipt, or a broader shortfall.

Set up the Excel template layout

In a blank workbook, put a period label in cell A1 and the first month in B1, then add the following months across row 1. For a one-year monthly template, use columns B through M. Put line-item names in column A and leave the amount cells in each period column. The example below uses positive numbers for both receipts and payments; the formulas subtract total payments from total receipts.

  • Row 4: Opening cash balance
  • Rows 6–7: Customer receipts and other receipts
  • Row 8: Total cash inflows
  • Rows 10–13: Supplier payments, payroll, rent and utilities, and taxes, debt payments, or other outflows
  • Row 14: Total cash outflows
  • Row 16: Net cash flow
  • Row 18: Closing cash balance

Leave blank rows between groups to make the worksheet easier to scan. Add more receipt and payment categories when they matter, such as card settlements, insurance, equipment purchases, owner contributions, or loan proceeds. Keep categories distinct enough to explain a change, but avoid a long list of immaterial items that makes updates difficult.

Enter the core formulas

Enter the opening balance for the first month from a known starting point, such as the cash balance at the beginning of the forecast. With receipts in rows 6 and 7 and the first month in column B, enter this total-inflows formula in B8, then copy it across the forecast months:

=SUM(B6:B7)

With payment categories in rows 10 through 13 and the first month in column B, enter this total-outflows formula in B14 and copy it across:

=SUM(B10:B13)

With total inflows in B8 and total outflows in B14 for the first month, enter this net-cash-flow formula in B16 and copy it across:

=B8-B14

With opening cash in B4 and net cash flow in B16 for the first month, calculate the closing balance in B18:

=B4+B16

For the following month, link its opening balance to the previous month’s closing balance. With the second month in column C and the previous closing balance in B18, enter this formula in C4, then copy the pattern to the right:

=B18

For a longer forecast, continue the same pattern across the columns. If the worksheet uses different row numbers, change the references to match its layout. Avoid typing a separate opening balance in every month: linking balances helps the forecast roll forward consistently and makes it easier to spot a broken formula.

Worked example: follow cash through one month

Suppose the first month begins with $12,000. The forecast estimates $18,000 in customer receipts and $2,000 in other receipts. Payments are $9,000 to suppliers, $6,000 for payroll, $1,500 for rent and utilities, and $2,500 for other scheduled payments. Enter those amounts as positive values in the appropriate rows.

With the two receipt values in B6:B7, the total-inflows formula in B8 returns $20,000. With the four payment values in B10:B13, the total-outflows formula in B14 returns $19,000. The net-cash-flow formula in B16 returns $1,000 because $20,000 minus $19,000 equals $1,000. The closing-cash formula in B18 returns $13,000 because the $12,000 opening balance plus $1,000 net cash flow equals $13,000. The next month’s opening balance should therefore link to that $13,000 closing balance.

These figures are illustrative only. The example assumes the listed amounts arrive or are paid in the month shown, and it does not determine whether any particular business has enough cash for its obligations. Timing assumptions matter: if a customer’s $18,000 payment is expected in one month but actually arrives in the next, the two months’ projected balances change even if the total receipts over both months do not.

Build realistic assumptions and scenarios

Base estimates on information that can be checked: invoice due dates, payroll schedules, signed contracts, recurring bills, and recent collection or payment patterns. For uncertain sales, do not treat a quotation or an unsigned order as guaranteed cash. Record the expected receipt in the period it is likely to become available, not automatically in the period the sale is made.

It can help to keep a central forecast and compare it with a cautious alternative. For example, a cautious version might move a large customer receipt one month later, reduce variable receipts, or include a planned one-time equipment payment. Label each scenario clearly and change the relevant assumptions rather than editing formulas. This makes the effect of an assumption visible without implying that any scenario is certain.

Keep assumptions beside the figures or on a separate worksheet. Useful notes include the expected payment date, source of an estimate, whether an amount includes tax, and who is responsible for updating it. When an estimate changes, record the reason and date. A forecast is a planning tool, so clear assumptions and regular updates are more valuable than a complicated workbook that no one maintains.

Make the workbook easier to review

Use consistent number formats, such as currency with two decimal places or no decimals where cents are not useful. Apply the same convention throughout the workbook. In Excel for Microsoft 365 on Windows, select the relevant cells and use Home > Number to choose a number format. You can also use Home > Conditional Formatting to highlight low or negative closing balances; set the rule according to a threshold that is meaningful for the forecast, rather than relying on color alone.

Make inputs visually distinct from calculated cells, and label the currency and the period covered near the top of the sheet. Do not use color as the only indicator, since a reader may print the workbook in grayscale or have difficulty distinguishing colors. Add a note explaining whether the starting cash figure includes restricted funds, an overdraft, or balances in multiple accounts. Consistent scope prevents comparisons between unlike balances.

For a workbook used by several people, protect formula cells from accidental edits while leaving input cells available. In Windows Excel, review the settings under Review > Protect Sheet. Protection is intended to prevent routine editing mistakes, not to provide strong security for sensitive information. Excel for Mac has similar worksheet-protection capabilities, though ribbon layout and some dialog details can differ by version.

Common forecasting problems to check

  • Mixing cash and accounting revenue: A recorded sale is not necessarily cash received. Forecast the receipt using the expected collection timing.
  • Counting a transfer twice: Moving money between two accounts owned by the same organization usually changes the account balances, not the organization’s total cash. Decide whether the forecast covers one account or all relevant accounts.
  • Omitting irregular payments: Annual insurance, maintenance, tax payments, loan installments, and equipment purchases can create a shortfall even when routine months look healthy.
  • Using inconsistent signs: In this template, receipts and payments are entered as positive numbers and total outflows are subtracted. Do not also enter expenses as negative amounts, or the result may be overstated.
  • Overwriting formulas: Check that totals and closing balances still contain formulas after copying or pasting. A useful review is to compare a few totals with a hand calculation.
  • Ignoring uncertainty: A single forecast can look precise even when its inputs are estimates. Document assumptions and compare a cautious case when a delayed receipt or unexpected payment would matter.

A negative projected closing balance is a warning that the assumptions imply more payments than available cash in that period; it is not proof that the organization will actually run out of money. Review the dates, balances, and assumptions, then discuss real financing terms or payment obligations with the relevant lender or a qualified professional. This illustrative spreadsheet guidance is not financial, tax, investment, or legal advice.

Excel template versus other ways to forecast

A custom Excel workbook is flexible: categories, timing, and scenarios can be adapted, and the formulas are visible for review. The trade-off is that someone must maintain the inputs, formulas, and controls. A prebuilt Excel template can save setup time, but check its row definitions, sign convention, treatment of transfers, and formulas before entering real data. Do not assume a template is accurate simply because it looks polished.

Accounting or cash-management software may be more suitable when a business needs frequent bank updates, multiple users, or reporting tied to its accounting records. A paper schedule or a simpler spreadsheet can be enough for a small, short-term plan. Whichever method is used, the forecast should state its scope, period, assumptions, and last-updated date so a reader can interpret the figures correctly.

Frequently Asked Questions

Is there a cash flow projection template in Excel?
Excel does not have one standard template that fits every situation. You can create a workbook with monthly columns for opening cash, receipts, payments, net cash flow, and closing cash, or adapt a prebuilt template after checking its formulas and assumptions.

What formula calculates closing cash in Excel?
For a period, closing cash is opening cash plus net cash flow. In the layout above, the first month’s closing balance is calculated from B4 and B16 with =B4+B16. The formula assumes receipts and payments are recorded consistently and net cash flow is already calculated correctly.

How many months should a cash flow forecast cover?
Choose a horizon that supports the decision you are making. A monthly year-ahead view is a useful planning format for many users, while a near-term weekly view can expose payment timing more clearly. A longer horizon may be helpful for planning, but estimates generally become less certain farther out.

Should expenses be positive or negative in a cash forecast?
Either convention can work if used consistently. The template here enters receipts and payments as positive values, totals each group separately, and subtracts outflows from inflows. If you use negative expense values instead, adjust the formulas so they do not subtract those amounts a second time.

How often should I update a cash flow projection?
Update it whenever a significant assumption changes and on a regular schedule suited to the decisions it supports. Compare actual receipts and payments with the prior forecast, investigate meaningful differences, and revise future periods. A frequent update is not useful if the underlying dates and assumptions are not checked.

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.