Cash Flow Statement Template Excel: Build and Use

How-to basics · Updated November 21, 2026

A cash flow statement template in Excel is a worksheet that organizes cash received and cash paid into operating, investing, and financing activities, then reconciles the period’s change in cash to its opening and closing balances. You can build one with monthly columns, labeled transaction categories, and simple SUM formulas; the layout below works for either a cash forecast or a historical summary, as long as you label which one it is.

What a cash flow statement shows

A cash flow statement explains how cash and cash equivalents changed during a period. It complements the income statement, which generally records revenue and expenses when earned or incurred, not necessarily when money changes hands. A profitable business can face a cash shortage if customers pay late, inventory purchases rise, or loan payments come due. Conversely, cash may increase temporarily because of a new loan even when the business is not profitable.

Formal cash flow statements group transactions into three sections:

  • Operating activities: cash generated or used by the entity’s main activities, such as customer collections, supplier payments, wages, and many tax payments.
  • Investing activities: purchases and sales of long-term assets and certain investments, such as equipment or property.
  • Financing activities: cash from borrowing, repaying principal, issuing ownership interests, or distributing cash to owners.

Accounting standards and a business’s circumstances affect how particular transactions are classified and presented. A simple workbook is useful for management planning, but it is not automatically a compliant financial statement. For external reporting, confirm the required treatment with a qualified accounting professional.

Statement template versus cash forecast

The terms are often used interchangeably, but a historical cash flow statement and a cash flow forecast answer different questions. A historical statement summarizes cash movements that already happened, using accounting records and a defined reporting period. A forecast estimates future receipts and payments using assumptions about invoice timing, payroll, supplier terms, debt schedules, and other events.

Many small organizations use a direct-method layout: list expected or actual cash receipts and cash payments by category. This is straightforward for planning because it focuses on when cash is expected to move. A formal statement may instead use the indirect method for operating activities: begin with net income, then adjust for noncash items and changes in operating assets and liabilities. Do not mix these approaches without labeling the method. A forecast based on receipts and payments is not the same calculation as an indirect-method reconciliation from net income.

Recommended Excel layout

In a new workbook in Microsoft Excel for Microsoft 365 on Windows, put category names in column A and periods in columns B through M. Use one column per month and, optionally, column N for the year total. Add a separate worksheet for assumptions or transaction detail if the main statement would otherwise become difficult to review.

A practical row structure is:

  • Row 5: opening cash balance.
  • Rows 8–9: customer collections and other operating receipts; row 10: total operating receipts.
  • Rows 13–17: supplier payments, payroll, rent, taxes, and other operating payments; row 18: total operating payments.
  • Row 19: net operating cash flow.
  • Rows 22–23: equipment or other asset purchases and proceeds from asset sales; row 24: net investing cash flow.
  • Rows 27–30: borrowing, loan principal repayments, owner contributions, and owner distributions; row 31: net financing cash flow.
  • Row 33: net change in cash; row 34: closing cash balance.

Use the same categories and row positions for each month. Put the reporting period and currency near the top, and mark whether the figures are actual, forecast, or a mixture. If forecasts and actuals share a sheet, separate them clearly rather than overwriting assumptions or silently combining the two.

Build the workbook and add formulas

Enter the labels first, then add month headings in B4:M4 and a year-total heading in N4. Format dates consistently and use a currency number format for cash values. On Windows, the number-format controls are on the Home tab in the Number group; Excel for Mac has corresponding controls on its Home tab. In Excel for the web, the available ribbon controls can vary slightly with the browser and workbook.

Enter verified opening cash in B5. For later months, link each opening balance to the preceding month’s closing balance instead of retyping it. With the previous month’s closing balance in B34, the next month’s opening balance in C5 is:

With the prior month’s closing cash in B34 and the next month’s opening cash in C5, enter =B34 in C5. It returns the same amount as B34, keeping consecutive periods connected.

With operating receipts in B8:B9 and the total operating receipts in B10, enter =SUM(B8:B9) in B10. If collections are 12,000 and other receipts are 1,000, the formula returns 13,000.

With operating payments in B13:B17 and total operating payments in B18, enter =SUM(B13:B17) in B18. If the five payments are 4,000, 2,500, 1,000, 500, and 500, the result is 8,500.

With total receipts in B10 and total payments in B18, enter =B10-B18 in B19. Using the examples above, net operating cash flow is 13,000 minus 8,500, or 4,500.

With equipment purchases in B22, asset-sale proceeds in B23, and net investing cash flow in B24, enter =B23-B22 in B24. A purchase of 2,000 and no sale proceeds produces a cash flow of negative 2,000.

With borrowing in B27, principal repayments in B28, owner contributions in B29, and distributions in B30, enter =B27-B28+B29-B30 in B31. This treats borrowings and contributions as cash inflows, and principal repayments and distributions as outflows. Classification and presentation can depend on the reporting framework, so verify requirements for formal statements.

With net operating, investing, and financing cash flows in B19, B24, and B31, enter =SUM(B19,B24,B31) in B33. If those amounts are 4,500, negative 2,000, and 1,000, the net change is 3,500. With opening cash in B5 and the net change in B33, enter =B5+B33 in B34. An opening balance of 6,000 gives closing cash of 9,500.

After checking the first month, copy the formulas across the remaining monthly columns. Relative references will adjust as they move. For an annual total, use monthly values only for flow rows; a year-end cash balance is the final month’s closing balance, not the sum of the twelve closing balances. For example, with monthly net changes in B33:M33, an annual net change cell can use =SUM(B33:M33). Check that the total agrees with the difference between the year’s opening and closing cash.

Use the template for actuals or forecasts

For actuals, use transactions recorded in the relevant period and reconcile the opening and closing amounts to appropriate cash records. Avoid treating an unpaid invoice as a cash payment merely because it appears as an expense in the accounting system. A cash-basis summary should reflect the date cash moved; accounting records may need adjustments or reclassification before they fit the template.

For a forecast, estimate timing as well as amount. If a customer invoice is due in April but historically paid in May, putting the full collection in April may overstate April’s available cash. Likewise, show loan principal and interest separately if that helps you understand upcoming cash requirements, and use the actual contract or lender schedule for assumptions. A forecast is an estimate, not a guarantee; update it as receipts, costs, and dates change.

A useful example is a retailer that expects 20,000 in April receipts and 18,000 in April payments, including a 5,000 equipment purchase. The template shows a 2,000 increase in cash for the month if all those amounts occur in April. The operating and investing sections still matter: showing only the 2,000 net movement would hide the equipment purchase and make the operating picture less informative. The figures are illustrative, not financial or tax advice; verify real terms with the lender or a qualified professional where relevant.

Checks that catch common problems

Before relying on the workbook, inspect the signs, timing, and reconciliation. A purchase should normally reduce cash, while a sale receipt normally increases it. If a category is entered as a negative number and also subtracted in a formula, it can accidentally increase the result. Choose one consistent convention: enter receipts and payments as positive amounts and subtract payment totals, as this template does.

  • Confirm the opening balance agrees with the prior period’s closing balance, except where the reporting scope changed.
  • Check that closing cash equals opening cash plus the net change and that the result can be reconciled to the relevant cash records.
  • Look for missing transactions, duplicate imports, and amounts assigned to the wrong month.
  • Keep transfers between accounts within the same cash scope from being counted as both an external receipt and an external payment.
  • Do not assume a bank balance always equals the statement’s cash and cash equivalents; restricted cash and other accounting presentation issues may require separate treatment.

Use a separate transaction log or source records to explain each total. The statement summarizes activity; it should not be the only place where transaction detail is stored. If formulas are overwritten, restore them from a clean copy or compare with another period. Excel’s formula bar can help inspect a selected cell, and the Home tab’s Fill controls or the fill handle can copy formulas across periods.

Choosing a template and keeping it usable

A suitable template matches the purpose and complexity of the work. A one-person business may need a few operating categories and a short rolling forecast. A larger organization may need departments, entities, multiple bank accounts, scenario assumptions, or a separate indirect-method reporting schedule. More rows are not automatically better: categories should be specific enough to support decisions, but not so granular that people classify similar transactions inconsistently.

Before adopting a downloaded workbook, inspect its formulas, hidden rows or sheets, external links, and assumptions. Confirm that the reporting periods and currency are clear and that totals include the intended rows. Protecting formula cells can reduce accidental edits, but worksheet protection is not a substitute for access controls or a backup. Save a working copy under a clear name and retain source records so the results can be reviewed later.

Alternatives to an Excel template

Excel is flexible and familiar, but it depends on accurate data entry and maintenance. Accounting software may generate historical cash flow reports from properly coded transactions, although setup and classification still need review. A bank cash forecast tool may help with timing, but it may not provide a formal statement or include every account and obligation. Google Sheets can support a shared workbook, but menu paths and some Excel features differ. Choose based on collaboration, auditability, reporting needs, and how reliably the underlying records can be maintained.

Frequently Asked Questions

Where can I get a free cash flow statement template for Excel?
You can create the layout described above in a blank workbook or look for a template from a reputable accounting or business resource. Review formulas, categories, and any hidden content before entering real information; a downloaded file may not match your reporting needs.

What is the difference between a cash flow statement and a cash flow forecast?
A statement reports cash activity for a completed period, while a forecast estimates future cash receipts and payments. A forecast relies on assumptions and timing estimates; it should be updated as actual results and expectations change.

Should loan payments go under operating or financing activities?
Borrowing proceeds and principal repayments are generally presented as financing cash flows in the simple layout here. Interest classification can depend on the accounting framework and circumstances. Check applicable reporting requirements rather than assuming a management template determines formal presentation.

Why does my closing cash not match my bank balance?
Possible reasons include uncleared transactions, missing or duplicated entries, different reporting dates, omitted accounts, restricted cash, or a mismatch between the workbook’s scope and the bank balance being compared. Reconcile the underlying records and clarify what cash the statement includes.

Can I use this template for a business and personal budget?
The same basic structure can help organize either, but keep personal and business records separate where required and use categories that fit the purpose. A personal budget is not necessarily a formal cash flow statement, and business reporting can have accounting and tax requirements.

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.