Cash Flow Report Template in Excel: Build and Use One

How-to basics · Updated November 21, 2026

A practical cash flow report template in Excel has one sheet for dated transactions and another that totals cash received, cash paid, net cash flow, and closing cash for a selected month. In Microsoft Excel for Microsoft 365 on Windows, you can build it with ordinary worksheet labels and SUMIFS formulas, then reuse it by changing the reporting month.

What a cash flow report template shows

A cash flow report summarizes when money actually enters and leaves a business or household. It helps answer questions such as whether available cash is likely to cover upcoming bills, which kinds of payments are largest, and how the cash balance changed during a period. A spreadsheet is useful because its categories, reporting dates, and level of detail can be adapted to the reader.

A simple monthly report usually separates cash inflows from outflows, calculates the difference, and adds that difference to the opening cash balance. The closing balance should reconcile to the cash balance at the end of the period. This is a management tool, not automatically a formal financial statement: a company may need a prescribed reporting format for external financial reporting.

Cash flow is not the same as profit. For example, a business might record a sale in one month but receive the customer’s payment the next month. The sale can affect profit before the cash arrives; a cash flow report records the receipt when it is paid. Similarly, buying equipment uses cash even though the full cost may not be treated as an expense in the same way or at the same time.

Choose a report style before building the workbook

For an internal monthly cash summary, a direct layout is often easiest to maintain: list cash receipts and payments by date and category, then total them for the reporting period. It shows the cash movements plainly and works well for a small organization, project, or personal budget.

A formal cash flow statement may instead use the indirect method. That method typically begins with net income and adjusts for noncash items and changes in operating assets and liabilities, while also presenting investing and financing activity. A transaction-only template cannot produce that statement correctly just by relabeling categories. If you need formal reporting, use records and guidance appropriate to your accounting basis and reporting requirements, and ask a qualified accounting professional when necessary.

Also decide whether the file will report actual cash movements, forecast future movements, or both. Actuals use completed transactions and payment dates. A forecast uses expected dates and amounts, which should be marked as estimates and updated as plans change. Keeping actual and forecast figures in separate columns or clearly labeled sheets avoids presenting an expectation as a completed transaction.

Set up the Excel workbook

Create two worksheets named Transactions and Summary. On Transactions, enter these headers in row 1: A1 Date, B1 Description, C1 Category, D1 Cash In, and E1 Cash Out. Add one transaction per row starting in row 2. Enter a payment in either Cash In or Cash Out, not both, and use a real Excel date in column A. For example, a customer payment received on January 12 belongs in Cash In on that date; a supplier payment made on January 15 belongs in Cash Out.

On Summary, set up the month selector and balances as follows:

  • Put Month in A1 and the first day of the reporting month in B1. For January 2026, enter a real date such as 1/1/2026, then format B1 as a month and year if desired.
  • Put Opening cash in A3 and enter the verified cash balance at the beginning of the month in B3.
  • Put Receipts in A5. List receipt categories in A6:A9, such as Customer receipts, Interest received, Owner contributions, and Other receipts.
  • Put Total receipts in A10. Put Payments in A12, then list payment categories in A13:A17, such as Payroll, Rent, Supplier payments, Taxes paid, and Other payments.
  • Put Total payments in A18, Net cash flow in A20, and Closing cash in A21.

Use categories consistently. If the summary says “Supplier payments,” the transaction entries must use that exact category spelling for the totals to include them. Add or remove category rows to suit the organization, adjusting formula ranges and totals to include the changed rows.

Add monthly totals and balance formulas

With transaction dates in Transactions!A2:A1000, categories in Transactions!C2:C1000, cash received in Transactions!D2:D1000, and the reporting month’s first date in Summary!B1, enter this in Summary!B6. The formula totals receipts whose category matches A6 and whose dates fall in the selected month:

=SUMIFS(Transactions!$D$2:$D$1000,Transactions!$C$2:$C$1000,$A6,Transactions!$A$2:$A$1000,">="&$B$1,Transactions!$A$2:$A$1000,"<"&EDATE($B$1,1))

Copy B6 down through B9. Each row will use the category in its own column A cell. The date test includes the first day of the selected month and excludes the first day of the next month, so transactions with times as well as dates are less likely to be missed. The ending row number 1000 is a practical example, not a limit; extend the ranges if your transaction list grows beyond it.

With cash paid in Transactions!E2:E1000, the same categories in column C, and payment categories in Summary!A13:A17, enter this in Summary!B13 and copy it down through B17:

=SUMIFS(Transactions!$E$2:$E$1000,Transactions!$C$2:$C$1000,$A13,Transactions!$A$2:$A$1000,">="&$B$1,Transactions!$A$2:$A$1000,"<"&EDATE($B$1,1))

With receipt totals in B6:B9, enter this in B10 to total them:

=SUM(B6:B9)

With payment totals in B13:B17, enter this in B18:

=SUM(B13:B17)

With total receipts in B10 and total payments in B18, enter this in B20:

=B10-B18

With opening cash in B3 and net cash flow in B20, enter this in B21:

=B3+B20

For an illustrative check, suppose opening cash is $5,000, receipts total $12,000, and payments total $9,500. Net cash flow is $2,500, and the calculated closing balance is $7,500. This arithmetic is a template example, not a claim about a particular business. For the next month, use the prior month’s verified closing balance as the new opening balance.

Enter and organize transactions reliably

Record the date cash moved, not simply the date an invoice was created. If an invoice is issued on February 28 but paid on March 10, a cash-basis report places the receipt in March. For a payment made by card or through a payment processor, decide whether the report tracks the gross customer payment and fee separately or the net deposit; apply the same approach consistently and reconcile it to the relevant bank or cash records.

Keep descriptions specific enough to investigate later, such as “March office rent” rather than “payment.” Avoid combining several unrelated events into one row when different categories or dates matter. To make the transaction list easier to filter and extend, select the header and data range and choose Home > Format as Table. Confirm that the table has headers. This is an organizational aid; the formulas above use fixed ranges, so extend their endpoints when adding rows beyond row 1000, or deliberately revise the formulas to use table references.

For consistent labels, you can maintain a short category list on a separate sheet and use Data > Data Validation to create a list for the Category cells. This reduces errors such as “Rent,” “rent,” and “Office rent” being treated as three different categories. Keep the categories useful rather than excessively detailed: a report with too many near-duplicates is harder to read and reconcile.

Check the report and make it readable

A formula can calculate correctly while the underlying records are incomplete. Reconcile the opening and closing cash figures to dependable records for the same dates, such as bank statements and cash accounts. If the calculated closing balance differs from the verified balance, check for omitted transactions, duplicate entries, incorrect dates, reversed inflow and outflow entries, and bank fees or transfers that were not recorded.

  • Confirm every transaction has a valid date within the intended period and a category that matches the summary exactly.
  • Check that a row has an amount in Cash In or Cash Out, not both, and that amounts use the correct sign convention. With this layout, enter positive amounts in the appropriate column.
  • Review transfers between accounts. If the report covers both accounts together, counting a transfer as both an outflow and an inflow can distort the total; decide whether transfers should be excluded or separately identified.
  • Format amounts as currency and dates consistently. Use bold labels for total receipts, total payments, net cash flow, and closing cash so readers can distinguish subtotals from transaction detail.

One common misconception is that a positive net cash flow proves a business is profitable or financially secure. It only says that recorded inflows exceeded recorded outflows for the selected period under the chosen definitions. Loan proceeds, owner contributions, delayed supplier payments, or a large one-off receipt can raise cash without showing recurring operating strength. Interpret the result alongside its categories, timing, and accounting records.

Template choices, limitations, and alternatives

A self-built workbook is flexible and transparent, but someone must enter transactions, maintain categories, check formulas, and preserve the file. A downloaded Excel template may save setup time, but inspect its formulas, date assumptions, and categories before relying on its totals. Do not assume a polished layout is proof that the workbook follows your accounting policy or reporting needs.

Accounting software or a bookkeeping system may be a better source when transactions are numerous, several people need access, or bank feeds and audit trails are important. Excel can still be useful for analysis, but exported data should be checked against the source system. For a simple forward-looking cash plan, add forecast dates and expected amounts in a clearly separated forecast area rather than mixing estimates into the actual transaction list.

There is no single universal cash flow report template that fits every purpose. A personal monthly cash summary, a small-business management report, a loan application schedule, and a formal statement can have different definitions and required detail. Before adopting a template, establish who will read it, what period it covers, which accounts it includes, and whether it is meant for internal planning or external reporting.

Frequently Asked Questions

Where can I get a cash flow report template in Excel? You can create the two-sheet workbook described above or download a template from a source you trust. Before entering important figures, check its formulas, category definitions, reporting period, and whether it tracks actuals or forecasts.

How do I calculate net cash flow in Excel? Subtract total cash paid from total cash received for the same period. In the example layout, the formula in B20 is =B10-B18. A positive result means recorded receipts were greater than recorded payments for that period.

How do I calculate the ending cash balance? Add net cash flow to the opening cash balance. In the example layout, B21 uses =B3+B20. Compare the result with the verified cash balance at the period end and investigate any difference.

Should I use the invoice date or payment date? For a report of actual cash movements, use the date the money was received or paid. Invoice dates matter for accrual accounting and receivables or payables reports, which answer different questions.

Can I use the same workbook for a cash flow forecast? Yes, but label estimates clearly and keep them separate from completed transactions. Forecast amounts and dates can change; actual results should be recorded independently so a reader can compare plans with what 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.