Small Business Finance

Cash Flow Statement Template in Excel: Indirect Method, Formulas and a Reconciled Example

Build a cash flow statement template in Excel with the indirect method: operating, investing and financing sections, formulas that pull changes from two balance sheets, a reconciliation to the bank balance, the direct method alternative and a worked example.

Published 4 min read

Quick answer: a cash flow statement explains why the cash balance changed over a period. In Excel, the indirect method starts from net income, adds back non-cash costs such as depreciation, and adjusts for changes in working capital (receivables, inventory, payables) to get cash from operating activities. Then it adds investing cash flows (buying or selling equipment) and financing cash flows (loans, owner contributions and draws). The result must equal ending cash minus beginning cash on the balance sheet, which is the built-in check.

This is a management template for small businesses; formal statements follow the accounting framework that applies to you. The example uses the same business as our balance sheet template and is illustrative.

The three sections

Section What goes in Typical lines
Operating activities Cash generated by the day-to-day business Net income, depreciation, change in receivables, inventory, payables, accrued expenses
Investing activities Buying or selling long-term assets Equipment purchases, vehicle sales
Financing activities Money from or to lenders and owners Loan proceeds, loan principal repayments, owner contributions, owner draws or dividends

Step 1: Two balance sheets and one P&L

You need the balance sheet at the start and end of the period and the profit and loss statement for the period. Put the opening balance sheet in column C and the closing one in column B of a sheet named BS. Each working capital line on the cash flow statement is then a difference between the two columns.

Step 2: The formulas (indirect method)

Line Formula Logic
Net income =PL!NetIncome From the P&L
Depreciation =PL!Depreciation Expense without cash outflow, added back
Change in accounts receivable =-(BS!B6-BS!C6) An increase means sales not yet collected: cash goes down
Change in inventory =-(BS!B7-BS!C7) Buying more stock uses cash
Change in prepaid expenses =-(BS!B8-BS!C8) Paying ahead uses cash
Change in accounts payable =BS!B20-BS!C20 An increase means bills not yet paid: cash stays
Change in accrued expenses and taxes payable =BS!B22-BS!C22 Same logic as payables
Cash from operating activities =SUM(...)
Purchases of equipment Negative amount from the fixed asset list Cash out
Cash from investing activities =SUM(...)
Loan principal repaid / new loans =(BS!B24+BS!B25)-(BS!C24+BS!C25) Change in total loan balance
Owner contributions and draws From equity accounts Contributions positive, draws negative
Cash from financing activities =SUM(...)
Net change in cash Operating + investing + financing
Check =IF(ROUND(NetChange-(BS!B5-BS!C5),2)=0,"Reconciled","Difference: "&TEXT(NetChange-(BS!B5-BS!C5),"$#,##0.00")) Must match the change in the cash line

The sign rule is easy to remember: an increase in an asset (other than cash) reduces cash; an increase in a liability increases cash.

Worked example: October

Line Amount
Net income for October $4,200
Add back depreciation $700
Accounts receivable increased by $1,500 −$1,500
Inventory increased by $2,800 −$2,800
Accounts payable increased by $1,200 $1,200
Accrued wages increased by $300 $300
Cash from operating activities $2,100
Purchase of display equipment −$3,000
Cash from investing activities −$3,000
Loan principal repaid −$500
Owner’s draw −$2,000
Cash from financing activities −$2,500
Net change in cash −$3,400
Cash at October 1 $21,900
Cash at October 31 $18,500

Check: 4,200 + 700 − 1,500 − 2,800 + 1,200 + 300 = 2,100; 2,100 − 3,000 − 2,500 = −3,400; 21,900 − 3,400 = 18,500, which matches the cash line on the October 31 balance sheet.

The story behind the numbers: the business made a profit of $4,200 but cash fell by $3,400, because profit was partly tied up in more inventory and receivables, and cash went into new equipment and the owner’s draw. That is exactly the kind of insight a P&L alone does not give.

The direct method

Instead of starting from net income, the direct method lists actual cash receipts and payments for operations: cash received from customers, cash paid to suppliers, wages paid, rent paid, taxes paid. If your bank transactions are categorized (for example in your bookkeeping template), you can build it with SUMIFS on the bank data by category. Both methods give the same operating total; the indirect method is easier to tie to the balance sheet, the direct one is easier to read.

Using the statement

  • Operating cash vs net income: if operating cash is regularly below profit, look at receivables and inventory.
  • Free cash flow: operating cash minus equipment purchases. In the example, 2,100 − 3,000 = −$900 for the month.
  • Owner draws: compare with free cash flow; drawing more than the business generates reduces cash over time.
  • Forecasting: the statement looks back; for the months ahead, use a separate cash flow forecast.

Common mistakes

  • Wrong signs on working capital changes.
  • Counting loan principal as an operating expense.
  • Forgetting non-cash items such as depreciation or write-offs.
  • Not reconciling to the actual change in the bank balance.
  • Mixing periods: the P&L and the two balance sheets must cover the same dates.

FAQ

Can a profitable business run out of cash?

Yes. Growth that requires more inventory and gives customers longer payment terms can consume cash faster than profit creates it. The cash flow statement shows this clearly.

How often should I prepare it?

Monthly alongside the P&L and balance sheet is ideal for a growing business; quarterly is a reasonable minimum.

Does it work in Google Sheets?

Yes. The formulas are simple sums and differences, plus IF, ROUND and TEXT for the check.

More guides: Small Business Finance.