A company cash flow template in Excel is a worksheet that tracks when money is expected to enter and leave the business, then calculates the cash balance at the end of each period. In Microsoft Excel for Microsoft 365 on Windows, set up one column per month, list receipts and payments in separate rows, and use formulas to calculate total cash in, total cash out, and ending cash.
What a company cash flow template shows
A cash flow template organizes cash movement by date. It helps a business estimate whether it will have enough available cash to meet upcoming obligations, and it gives owners a way to compare planned amounts with actual transactions. A basic version can fit on one worksheet; a more detailed workbook may use separate tabs for assumptions, monthly forecasts, and actual results.
Cash flow is not the same as profit. A sale recorded as revenue may not bring in cash until a customer pays, while a loan payment may use cash without being recorded as an operating expense in the same way as a supplier bill. The timing difference matters: a company can report a profit and still face a cash shortage if collections arrive after payroll or rent is due.
A useful template should make those timing differences visible. It should also distinguish among day-to-day operating activity, purchases or sales of long-term assets, and financing activity such as borrowing or owner contributions. For a small business, the categories can remain simple at first; add detail only when it helps explain or manage cash movement.
A practical monthly worksheet layout
Start with a blank worksheet. Put category names in column A, and use columns B through M for the twelve months of the forecast. Put each month’s date or name in row 3. The following row plan gives the template a consistent structure:
- Row 4: Opening cash balance.
- Rows 5–7: Customer receipts, other operating receipts, and cash from loans or owner contributions.
- Row 8: Total cash inflows.
- Rows 9–14: Supplier payments, payroll, rent and overhead, taxes and interest paid, capital purchases, and principal repayments or owner distributions.
- Row 15: Total cash outflows.
- Row 16: Net cash flow for the month.
- Row 17: Ending cash balance.
These labels are a starting point, not a required accounting format. Change them to fit the company’s transactions, but avoid combining categories that need different timing assumptions. For example, if customer receipts are often late, keep them separate from other receipts so you can revise their expected collection dates without changing unrelated estimates.
Opening cash should represent cash available at the start of the period, using the definition appropriate to the business. Decide whether restricted cash, undrawn credit, or other balances are excluded, and apply that choice consistently. A forecast is only as useful as its definition of available cash.
Build the formulas and check the arithmetic
Enter amounts as positive numbers in the receipts and payment rows. This makes the input rows easier to read; the net cash formula subtracts total payments from total receipts. The formulas below use the worksheet layout described above and comma argument separators.
With receipts entered in B5:B7 for the first month, enter this in B8. For the example, customer receipts are $42,000, other operating receipts are $2,500, and financing receipts are $0, so total cash inflows equal $44,500.
=SUM(B5:B7)
With cash payments entered in B9:B14, enter this in B15. If the six payment categories are $16,000, $12,000, $4,000, $2,000, $3,000, and $1,000, the result is $38,000.
=SUM(B9:B14)
With total inflows in B8 and total outflows in B15, enter this in B16. Using the example totals, net cash flow is $44,500 minus $38,000, or $6,500.
=B8-B15
With opening cash in B4 and net cash flow in B16, enter this in B17. If opening cash is $20,000, ending cash is $26,500.
=B4+B16
For the next month, put the prior month’s ending balance into the opening-balance cell. With the first month’s ending balance in B17, enter this in C4; the result is $26,500. Copy the monthly total and balance formulas across the other month columns, checking that the cell references move to the corresponding column.
=B17
Enter assumptions and make a useful forecast
Use source information rather than simply repeating last month’s figures. Useful inputs include open customer invoices, scheduled supplier payments, payroll dates, rent schedules, tax payment dates, planned equipment purchases, and loan payment schedules. For each estimate, record the expected amount and the month in which cash is expected to move. A short notes column can identify the source or explain an unusual assumption.
For customer collections, estimate the timing based on invoice due dates and the company’s observed payment patterns. For supplier costs, use the contractual or expected payment date rather than the date an expense is recorded. For seasonal businesses, forecast months individually instead of applying one average to every month. Large, irregular events—such as an annual insurance payment or a major equipment purchase—should appear in the period when payment is expected.
Keep assumptions visible and editable. If a forecast depends on a customer paying an invoice in a particular month, note that dependency rather than treating the amount as guaranteed. Where uncertainty is meaningful, prepare a base case and a more cautious scenario on separate sheets or in clearly labeled sections. Avoid changing formulas to force a desired ending balance.
Forecast periods, actuals, and review frequency
A monthly forecast is a convenient starting point for planning over the coming year, but it can hide a short-term gap inside a month. A business with tight cash, uneven receipts, or frequent payments may need a weekly forecast for the near term, then monthly periods farther out. The appropriate detail depends on how often cash moves and how quickly the company needs to respond.
To compare actual results with the forecast, preserve the original forecast values or save dated copies before updating assumptions. Add actual receipts and payments in separate columns or on a separate actuals sheet; do not overwrite the forecast without leaving a record. At the end of each period, compare what happened with what was expected. A difference may come from a changed payment date, a missed sale, an unexpected bill, or an estimate that was too optimistic.
Refresh the forecast on a regular schedule and whenever a material event changes expected cash. A weekly review may suit a business managing near-term payments; a less volatile company may use a monthly routine. The purpose is not to predict every transaction perfectly. It is to spot developing shortfalls early enough to investigate their cause and consider appropriate options.
How to read the ending cash balance
The ending cash row shows the result of the assumptions in that period and carries into the next period as opening cash. A declining balance is a signal to inspect the timing and assumptions, not proof by itself that the company is in financial trouble. A positive balance also does not guarantee that all obligations can be met: the forecast may omit commitments, overstate collections, or include cash that is not freely available.
Compare the projected balance with the company’s practical minimum cash requirement and the timing of upcoming obligations. If the balance approaches a threshold, examine the underlying receipts and payments. Ask whether customer collections are realistic, whether a planned purchase can be rescheduled, or whether a payment date or financing assumption needs confirmation. Do not count a possible loan, investment, or delayed payment as certain until its terms and timing are verified.
Loan proceeds, principal repayments, and interest payments have different roles in the worksheet. The example layout records loan proceeds among inflows, principal repayment among payments, and interest paid in the taxes-and-interest category. Actual accounting presentation can vary by reporting purpose and applicable rules. The template is a cash-planning tool, not a substitute for accounting records or professional advice. Confirm real loan terms with the lender and consult a qualified professional for financial, tax, or accounting questions.
Formatting and common template problems
Make inputs visually distinct from formula cells, use a consistent currency format, and label the time periods clearly. In Microsoft 365 for Windows, select the relevant cells and use Home > Number to choose a currency or accounting format. Use descriptive row names and keep units consistent; do not mix dollars with thousands of dollars unless the worksheet labels the scale prominently.
Common errors include these:
- Mixing profit and cash: Enter expected payments and receipts by cash date, not merely accounting income and expenses.
- Counting a transaction twice: If a receipt is already included in customer collections, do not also add it under other receipts.
- Omitting irregular payments: Include annual, quarterly, and one-time commitments in the month they are expected to be paid.
- Breaking the balance chain: Check that each month’s opening balance equals the previous month’s ending balance.
- Hard-coding totals: Keep calculated totals in formula cells so changes to input amounts update the result.
Before relying on a new worksheet, test it with a small set of known values and check the arithmetic manually. Review formulas after copying them across months, especially if rows or columns were inserted. Excel for the web supports ordinary worksheet formulas and editing, although some workbook features and interface details can differ from the Windows desktop app. On Mac, Excel’s formula logic is the same; ribbon labels and some keyboard shortcuts can differ.
Excel template versus alternatives
A custom Excel worksheet is flexible: you can tailor categories, timing, and scenarios to the company without adopting a particular accounting system. It is also easy to share as a workbook, but collaborators need clear input rules and version control to avoid conflicting copies. A spreadsheet may be a poor fit if many people need simultaneous controlled updates or if transaction-level integration and audit trails are important.
Accounting software can provide actual transaction data and reports, while a dedicated cash forecasting tool may support more complex scenarios. Those products differ in features and price, so compare them against the company’s needs rather than assuming one is automatically better. A simple spreadsheet can be enough for a small operation with limited transactions; growing complexity, frequent updates, or reconciliation demands may justify a more integrated process.
Frequently Asked Questions
Is a cash flow template the same as a cash flow statement?
A template is a worksheet structure that can be used for planning or tracking. A cash flow statement is a financial report prepared from actual accounting information under the relevant reporting basis. A forecast template should not be presented as an official statement of historical results.
Can I use this template for a cash flow forecast?
Yes. Enter expected receipts and payments in the periods when cash is likely to arrive or leave, and update assumptions as circumstances change. Keep forecast figures distinguishable from actual results so readers do not confuse estimates with completed transactions.
Should expenses be entered as negative numbers?
Not in the layout described here. Enter payment amounts as positive numbers in the outflow rows; the net cash formula subtracts the total outflows. If you choose a different convention, adjust every formula consistently and label the worksheet so users know how to enter data.
How many months should a company forecast?
Twelve monthly columns offer a useful annual view, but the right horizon depends on the planning purpose and available information. A business facing immediate liquidity pressure may need a more detailed weekly view for upcoming weeks, alongside a longer monthly outlook.
Can I use the worksheet for a loan or investment decision?
It can help organize assumptions about timing, but it cannot establish whether a loan or investment is suitable. Verify borrowing costs, repayment dates, covenants, and other terms with the lender, and consult a qualified professional for financial or tax advice.
Related
- Can You Type Subscript in Excel? Methods and Limits
- Can You Strikethrough Text in Excel? Shortcuts & Steps
- Can You Track Changes in Excel? How Each Option Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Power Query Excel: Split a Column into PartsIn Excel for Microsoft 365 on Windows, split a column in Power Query by opening the query, selecting…
- How to Duplicate a Page in Excel and Keep Its LayoutTo duplicate a page in Excel, right-click its worksheet tab, choose Move or Copy, check Create a copy,…
- Google Sheets SUMIF: How to Exclude Subtotal Rows SafelyTo keep subtotal rows out of a Google Sheets SUMIF result, add a row-type column such as “Detail”…
- Excel Freeze Panes Based on Selection: How It WorksYes. In Excel for Microsoft 365 on Windows, select the cell immediately below the rows and immediately to…
- How to Set an Out of Office in OutlookTo set an out-of-office email in Outlook, open Automatic replies, turn them on, write your message, and—if available—choose…
- Google Sheets Checkbox X Symbol: Show an X InsteadGoogle Sheets’ built-in checkbox cannot be changed to draw an X instead of its usual check mark. To…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.