A cash flow forecast Excel template lists expected cash receipts and payments by week or month, then calculates the cash balance left over. In Excel for Microsoft 365 on Windows, set up date columns, enter your opening balance and assumptions, and use formulas to total inflows, outflows, and closing cash; the layout and example below provide a practical template you can build in a blank workbook.
What a cash flow forecast shows
A cash flow forecast estimates when money is expected to enter and leave a business. It focuses on timing and available cash, rather than simply whether the business expects to make a profit. A sale recorded as revenue may not produce cash until a customer pays, while a supplier bill may need to be paid before the related sale brings in money.
That difference makes a forecast useful for spotting possible shortfalls early. If a forecast shows a low balance in three weeks, the business can investigate the assumptions and timing before that date arrives. A forecast is not a guarantee: it is a working projection based on current information and estimates.
Choose a weekly or monthly layout
Choose a period that makes the timing of important payments visible. A weekly forecast can help when cash is tight, receipts are irregular, or payroll and supplier payments fall on specific dates. A monthly forecast is often easier to maintain for longer-range planning, but it can conceal a cash squeeze that occurs between month-end dates.
You can start with 13 weekly columns to see roughly a quarter ahead, or use monthly columns for a longer planning horizon. These are practical choices, not mandatory standards. Some businesses use both: a short-term weekly view for near-term cash management and a monthly view for broader planning. Keep the time period consistent across receipts and payments, and label each column clearly with an actual Excel date.
Set up the template in Excel
Create a sheet named Forecast. Use column A for labels and columns B onward for consecutive weeks or months. The following row plan gives you a simple direct cash forecast that you can adapt to your business:
- Row 1: period-ending dates.
- Row 2: opening cash balance.
- Rows 5–7: customer sales receipts, collection of outstanding invoices, and other receipts.
- Row 8: total inflows.
- Rows 11–16: payroll, supplier payments, rent, debt payments, taxes, and other payments.
- Row 17: total outflows.
- Row 19: net cash flow.
- Row 20: closing cash balance.
Leave blank rows between sections if they make the sheet easier to read. Add categories that reflect real cash movements, but avoid so many small lines that updating the forecast becomes burdensome. If a payment is important enough to affect a decision, give it its own row; otherwise, a sensible grouped category may be sufficient.
Add formulas for totals and balances
Enter the expected amounts in the relevant period columns. For an example, assume column B is the first forecast period, row 2 is opening cash, rows 5–7 are inflows, rows 11–16 are outflows, and rows 8, 17, 19, and 20 hold the calculated totals. Assume the figures in B5:B7 are 18,000, 6,000, and 500. With inflows in B5:B7, enter this in B8:
=SUM(B5:B7)
That example returns 24,500. With outflow amounts in B11:B16 of 8,000, 5,000, 2,000, 900, 0, and 600, place the following formula in B17:
=SUM(B11:B16)
It returns 16,500. With total inflows in B8 and total outflows in B17, enter this formula in B19:
=B8-B17
It returns net cash flow of 8,000. With opening cash of 12,000 in B2 and net cash flow in B19, calculate the closing balance in B20 with:
=B2+B19
The result is 20,000. For each later period, link its opening cash to the previous period’s closing cash. With the prior period’s closing balance in B20, the next period’s opening balance in C2 is:
=B20
That returns 20,000 in this example. Copy the total and balance formulas across the forecast columns, checking that each formula points to the same rows in its own column. In Excel for Microsoft 365 on Windows, use the fill handle at the lower-right corner of a selected cell to copy a formula across adjacent cells. On Mac, the fill handle works similarly; keyboard shortcuts can vary with keyboard settings.
Enter realistic cash timing
Base receipts on expected payment dates, not just invoice dates or sales targets. If customers typically pay 30 days after invoicing, placing the entire sale in the current week can make the forecast look healthier than the bank balance is likely to be. Use the best available evidence, such as invoice due dates, customer payment history, signed contracts, and known collection delays.
For payments, include the date cash is expected to leave the account. Consider payroll dates, rent, supplier terms, tax deadlines, loan payments, insurance, subscriptions, and one-off purchases. Separate recurring payments from irregular items so that a missed unusual expense does not make the forecast misleading. When debt payments are included, use the actual schedule and check terms with the lender or a qualified professional; this spreadsheet example is not financial, tax, investment, or legal advice.
Make assumptions visible and test scenarios
A useful forecast makes estimates easy to identify and revise. Add a short note beside uncertain items or create a separate Assumptions sheet showing the source, expected date, amount, and confidence level for major receipts and payments. Distinguish confirmed items from estimates rather than presenting every figure as equally certain.
Build a base case from your most supportable expectations, then consider a downside case. For example, move a large customer receipt one week later, reduce uncertain sales receipts, or add an estimated repair payment. Compare the resulting closing balances with the base case. If the business has a minimum cash level it wants to maintain, display that threshold clearly and investigate periods when the projected balance falls below it. Treat scenario results as planning signals, not promises about what will happen.
Choose a forecast method that fits the question
The row-based layout above is a direct cash forecast: it records expected cash receipts and payments in the periods they are expected to occur. This makes it relatively intuitive for short-term cash planning. It also depends on good information about payment dates, so estimates may need frequent updates.
An indirect cash flow forecast starts from projected profit or another accounting measure and adjusts for non-cash items, working capital changes, and other cash movements. It can connect more naturally to financial statements and longer-range business plans, but takes more accounting inputs to build and explain. Neither method is automatically more accurate. Choose the approach that answers the decision at hand and that the people maintaining it can update reliably.
Common spreadsheet problems to prevent
- Mixing cash and revenue: A booked sale is not necessarily cash received. Forecast the collection period, not just the sale.
- Using inconsistent periods: Do not put weekly receipts beside monthly payments without making the timing clear.
- Forgetting the opening balance: A forecast without a verified starting cash figure cannot produce a meaningful closing balance.
- Double-counting: If an invoice collection is already included in customer receipts, do not add it again under another label.
- Leaving out one-off items: Include known equipment purchases, annual bills, deposits, and other less frequent payments.
- Overwriting formulas: Keep input cells visually distinct from calculated cells and review formulas after pasting data.
Use a consistent number format, such as currency with two decimal places when cents matter, and apply the same format to all periods. In Excel for Microsoft 365 on Windows, select the cells and use Home > Number to choose a number format. Freeze the label column or top date row if the forecast is wide: select the cell below and to the right of the rows and columns to keep visible, then choose View > Freeze Panes > Freeze Panes. On Mac, the Freeze Panes command is also on the View tab, though the ribbon arrangement may differ.
Keep the forecast useful over time
A forecast needs regular comparison with actual cash activity. At the end of each period, replace the elapsed estimate with actual receipts and payments, then revise future periods using current information. Keep prior versions or record significant changes if you need to understand why a projection moved. A forecast that is never updated can become less useful than a simple, current one.
Check that the opening balance for the first period agrees with a reliable cash figure, and reconcile later opening balances to the previous closing balances. Review unusually large changes, blank periods, and negative balances. A negative projected balance is not automatically an error: it may be a genuine warning, or it may point to a missing receipt, a duplicated payment, or a formula that was not copied correctly. Verify the underlying entries before drawing conclusions.
Frequently Asked Questions
Can I get a cash flow forecast template in Excel?
You can build one in a blank Excel workbook using the row layout and formulas above, or adapt a template available from a trusted source. Before relying on any downloaded workbook, inspect its formulas, assumptions, date periods, and any hidden sheets so you understand how it calculates results.
How far ahead should a cash flow forecast go?
Use a horizon long enough to cover the decision you need to make. Weekly columns can clarify near-term timing, while monthly columns can help with longer-range planning. The right horizon depends on how predictable your receipts and payments are and how often you can update the forecast.
Is a cash flow forecast the same as a profit forecast?
No. A profit forecast estimates income and expenses under accounting rules, while a cash forecast tracks when money is expected to be received or paid. Payment timing, unpaid invoices, loans, and non-cash expenses can make the two figures differ.
Why does my forecast show cash I do not have yet?
It may include expected receipts that have not arrived. Check that each receipt is entered in its expected collection period, rather than the date a sale was recorded or an invoice was issued. Update estimates when customers pay late or circumstances change.
Can Excel automatically predict all future cash movements?
Excel can calculate totals and balances from the assumptions you enter, but it cannot know whether a customer will pay on time or an unexpected bill will occur. A forecast is only as useful as its inputs, review process, and transparent assumptions.
Related
- Align Images in Excel: Steps, Options, and Common Fixes
- Can Excel Compare Means With a T-Test? Steps and Choices
- Can Excel Interpolate Data? Methods and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Time Format: Show Hours and Minutes Only CorrectlyTo show hours and minutes in Excel, select the cells, press Ctrl+1, choose Custom, and enter h:mm (or…
- How to Add a New Section to a Word Table of ContentsTo add a new section to a table of contents in Microsoft Word, format the section title with…
- How to Schedule an Email in Outlook to Send at a Later TimeTo schedule an email in Outlook, write your message, open the menu beside Send, choose Schedule send, and…
- Track Changes in Word: Turn It On, Review, and Accept EditsIn Microsoft Word for Microsoft 365 on Windows, open the Review tab and click Track Changes to record…
- Cover Page in Word: How to Add and Customize ItTo add a cover page in Microsoft Word for Microsoft 365 on Windows, place the cursor anywhere in…
- How to Make Columns in Word Without Vertical LinesTo make columns in Word without a divider, place the cursor in the text or select the text,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.