A practical cash flow format in Excel uses one column for each month and separate sections for operating, investing, and financing cash flows. Add each section’s net total, then calculate the ending cash balance from beginning cash plus the net change; this makes it easier to see when cash came in, where it went, and what remains.
What a Cash Flow Format Shows
A cash flow statement tracks cash received and cash paid during a defined period. It is different from an income statement: income statements generally record revenue when earned and expenses when incurred, while a cash flow report focuses on when money actually moves. A sale on credit, for example, may count as revenue before the customer pays, but it is not a cash receipt until payment arrives.
For a standard business statement, cash flows are grouped into three activities:
- Operating activities: cash connected with the business’s usual operations, such as customer receipts, supplier payments, payroll, and rent.
- Investing activities: cash used to buy or received from selling long-term assets, such as equipment or property.
- Financing activities: cash from borrowing, repaying loan principal, issuing ownership interests, or making owner distributions.
The categories help explain why the cash balance changed. A business might have positive operating cash flow but still see its total cash fall after buying equipment or repaying debt. For internal planning, a simpler receipts-and-payments report can also be useful; just label it clearly rather than presenting it as a formal statement prepared under a particular accounting framework.
Recommended Excel Layout
For a monthly report, put account or activity names in column A and months across columns B through M. Use a separate worksheet for detailed transactions if the workbook needs an audit trail, and use the statement sheet to summarize them. Keep a consistent sign convention: the example below records every input as a positive number and uses formulas to subtract cash payments.
A useful row structure is:
- Rows 5–6: beginning cash and, if needed, a note about the reporting period.
- Rows 7–14: operating activities, including customer receipts, other operating receipts, supplier payments, payroll, rent and other operating payments, followed by net operating cash.
- Rows 16–19: investing activities, such as asset-sale proceeds and asset purchases, followed by net investing cash.
- Rows 21–26: financing activities, such as borrowing, loan principal repayments, owner contributions and owner distributions, followed by net financing cash.
- Rows 28–29: net change in cash and ending cash.
Put the month or period end date in the heading above each month’s column. If the report covers a full year, use monthly columns and optionally add a total column after December. Do not add monthly ending balances to make an annual ending balance: the annual ending balance is the last month’s ending balance, while annual cash flows are the sum of the monthly flows.
Build the Statement Step by Step
- Open a blank workbook in Excel for Microsoft 365 on Windows. Enter a clear title, the business or project name, and the reporting period.
- Type the row labels in column A and month headings in B:M. Add the three activity sections and the net-change and ending-cash rows.
- Enter each month’s beginning cash balance. For the first month, use the verified opening balance for the period. For later months, link beginning cash to the previous month’s ending cash.
- Enter receipts and payments in their relevant rows. Use cash received or paid during the period, not merely invoices issued or expenses recorded.
- Add formulas for the section totals, net change, and ending balance. Check the ending balance against a bank or cash ledger balance where appropriate.
- Save the workbook with a recognizable file name and retain supporting transaction records separately or on a detail sheet.
For an existing workbook, these instructions use standard worksheet cells and formulas. The Excel for Mac ribbon is organized somewhat differently in places, but the same layout and formulas work. The Excel for the web version also supports ordinary cell formulas, though some ribbon controls and workbook features can vary.
Formulas for Monthly Totals
In the layout above, assume B is the first month. Rows 8 and 9 contain operating receipts; rows 10 through 13 contain operating payments, all entered as positive amounts. With those labels and values in place, calculate net operating cash as follows.
With operating receipts in B8:B9 and operating payments in B10:B13, enter this formula in B14. If receipts total 12,500 and payments total 9,000, the result is 3,500.
=SUM(B8:B9)-SUM(B10:B13)
For investing activities, place asset-purchase payments in B17 and asset-sale proceeds in B18. With those two amounts in B17:B18, enter the following in B19. A purchase of 1,000 and no sale proceeds produce net investing cash of -1,000.
=B18-B17
For financing activities, use B22 for borrowing proceeds, B23 for loan principal repayments, B24 for owner contributions, and B25 for owner distributions. With those values in the stated cells, enter this in B26. Borrowing of 2,000, repayments of 500, contributions of zero, and distributions of 300 produce net financing cash of 1,200.
=SUM(B22,B24)-SUM(B23,B25)
With net operating cash in B14, net investing cash in B19, and net financing cash in B26, enter this in B28 to calculate the period’s net change. Using the example totals above, the result is 3,700.
=SUM(B14,B19,B26)
With beginning cash in B5 and the net change in B28, enter this in B29. If beginning cash is 5,600 and the net change is 3,700, ending cash is 9,300.
=B5+B28
For the next month, with the current month’s ending balance in B29 and the next month in column C, enter this in C5. Copy the formula across the remaining months so each beginning balance links to the previous month’s ending balance.
=B29
After entering the formulas in the first month, copy them across the other month columns. Excel adjusts relative references as formulas move from B to C and onward. Confirm the first and last month manually before relying on the full-year totals.
Worked Example: Follow the Cash
Suppose a small business begins a month with 5,600 in cash. It receives 12,000 from customers and 500 from another operating source. It pays suppliers 4,500, payroll 3,000, rent 1,200, and other operating costs 300. That leaves 3,500 of net operating cash. It then spends 1,000 on equipment, so investing cash is -1,000. It borrows 2,000, repays 500 of loan principal, and distributes 300 to owners, giving 1,200 of net financing cash.
The net increase is 3,500 minus 1,000 plus 1,200, or 3,700. Add that to beginning cash of 5,600 and the ending balance is 9,300. This example illustrates why a profit figure alone does not tell the whole cash story: equipment purchases and debt transactions affect cash but are not simply the same as ordinary operating revenue and expenses.
For formal financial reporting, classification rules can depend on the applicable accounting framework and the type of transaction. For instance, some items may be classified differently under different reporting requirements. Check the relevant rules or consult a qualified accountant before treating an internal spreadsheet as a compliant financial statement.
Direct and Indirect Cash Flow Methods
The direct method lists major operating cash receipts and payments, such as customer collections and payments to employees or suppliers. The layout above uses this approach because each line is understandable to a reader who wants to see cash movements directly.
The indirect method starts with net income and adjusts it to arrive at operating cash flow. Adjustments commonly account for noncash items and changes in operating assets and liabilities. For example, an increase in accounts receivable can mean revenue has been recognized but cash has not yet been collected; an increase in accounts payable can mean an expense has been recorded but the cash payment has not yet occurred. Investing and financing sections still show their respective cash flows.
Choose based on the purpose of the workbook and the records available. A small-business cash forecast may benefit from direct receipts and payments, while a report intended to accompany financial statements may need to follow a specified presentation method. The two methods are not alternative ways to change the total cash movement; they present operating cash flow differently.
Format and Check the Workbook
Use formatting to make the statement readable without hiding its logic. Apply a currency number format to monetary cells, bold section headings and subtotal rows, and use a consistent number of decimal places. In Excel for Microsoft 365 on Windows, select the cells and use Home > Number to choose a number format; the Format Cells dialog is available with Ctrl+1. On Mac, Command+1 opens Format Cells. Negative values can be displayed with a minus sign or parentheses, but use one style consistently.
Consider freezing the label and heading rows if the sheet is wide: select the cell below the rows and to the right of the columns you want to keep visible, then choose View > Freeze Panes > Freeze Panes. Avoid merging cells across the data area, since merged cells can make selecting and sorting ranges harder. Add a source or note column on a detail sheet if reviewers need to trace a total to a bank statement, invoice, or ledger.
Useful checks include:
- Confirm beginning cash for the first period against a reliable opening balance.
- Check that the final period’s ending cash agrees with the relevant cash records, allowing for timing differences such as uncleared payments.
- Look for missing transactions, duplicates, and amounts placed in the wrong activity section.
- Verify that payments are consistently treated as outflows and that formulas subtract them only once.
- Reconcile the sum of monthly changes to the difference between first beginning cash and final ending cash.
Common Problems and Practical Alternatives
A frequent mistake is mixing cash and accrual figures. If a sales report includes unpaid invoices, using its revenue total as cash received will overstate cash inflows. Use collections for a cash statement, and keep sales and receivables reporting separate unless you are deliberately building a forecast that estimates collection timing.
Another problem is inconsistent signs. If outflows are entered as negative values but the formulas also subtract them, the formula can turn a payment into a positive contribution. The example format avoids that by entering amounts as positive values and subtracting payment rows in the totals. Alternatively, a negative-outflow convention can work, but formulas and labels must consistently follow it.
For a short-term cash forecast, the same structure can be reused, but replace actual receipts and payments with expected amounts and dates. Keep forecast figures visibly distinct from actuals, and update assumptions such as customer payment timing, payroll dates, rent, and planned purchases. A simple transaction log with date, description, category, and amount can be a better alternative when the main need is to track individual transactions rather than summarize monthly activity. Excel templates can save setup time, but review their formulas, categories, and assumptions before using them.
Frequently Asked Questions
How do I create a cash flow statement in Excel?
List monthly cash receipts and payments under operating, investing, and financing activities. Total each section, calculate the net change, and add that change to beginning cash to get ending cash.
What is the formula for ending cash in Excel?
Ending cash equals beginning cash plus net change in cash. In the sample layout, beginning cash is in B5 and the net change is in B28, so the ending-balance formula in B29 is =B5+B28.
Should cash inflows and outflows be positive or negative?
Either convention can work if used consistently. In the example, both are entered as positive amounts, and payment totals are subtracted in the section formulas.
Can I use this format for a cash flow forecast?
Yes. Keep the same categories and balance calculations, but enter expected cash dates and amounts rather than completed transactions. Label forecast periods clearly and revise estimates as actual information becomes available.
Is a cash flow statement the same as a profit and loss statement?
No. A profit and loss statement reports income and expenses under its accounting basis, while a cash flow statement tracks cash receipts and payments. Differences in timing, noncash items, asset purchases, and financing can make the two totals differ.
Related
- Power Query Text to Columns: Split Data Step by Step
- Data Validation Framework: Design, Rules, and Examples
- What Is Power Query? Excel Data Cleanup Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add Turabian Footnotes in Word, Step by StepTo add a Turabian footnote in Word, place your cursor after the sentence punctuation, choose References > Insert…
- Google Sheets IFS: How It Works and When to Use ItIn Google Sheets, IFS checks conditions in the order you list them and returns the result paired with…
- Data Slicer in Excel: How to Add and Use OneA data slicer in Excel is a clickable on-sheet control that filters a table or PivotTable by the…
- AI Data Validation: How It Works and Why It MattersAI data validation is the process of checking whether data used by an artificial intelligence system is accurate,…
- Outlook Contacts: Export and Import GuideTo export or import Outlook contacts, use a CSV file: in classic Outlook for Windows, go to File…
- How to Double Space in Word for the WebTo double-space text in Word for the web, select the paragraphs you want to change, open Home >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.