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
- Calendar Excel Sheet Template: Build or Choose One
- How to Calculate FV in Excel
- Weighted Average in Excel PivotTables: How to Calculate
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- What Number Is Column B in Excel? Meaning and UsesColumn B is column number 2 in Excel: column A is 1, and the count starts at 1…
- How to Lock Cells in Excel for the Web and Protect DataTo lock cells in Excel for the web, unlock any cells people should still be able to edit,…
- Delete Emails From Archive in Outlook: Step-by-StepTo delete archived emails in Outlook, open the Archive folder, select the messages, and choose Delete. Outlook moves…
- How to Highlight Half a Cell in Excel with a ShapeExcel does not have a built-in command to fill only half of a cell. To create that effect,…
- How to Insert a Line in Word: Borders and Shapes GuideTo insert a line in a Word document, type three hyphens (—) on a blank line and press…
- Excel Solver Limitations: What It Can and Cannot DoExcel Solver is best suited to small, well-structured optimization models: the standard Solver add-in can adjust at most…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.