Why a cash flow statement matters as much as profit
A business can report a healthy profit and still run short of money. Sales made on credit sit in debtors, stock ties up cash, and a tax instalment or loan repayment can fall due in the same month that a large supplier payment is made. The profit and loss account records income and expenses when they are earned or incurred, while a cash flow statement records when money actually enters and leaves the bank. Building one in Excel is the quickest way for a small business to see that difference for itself.
The three sections of a cash flow statement
Every cash flow statement sorts movements into three activities, and the order matters because it shows where cash came from and where it went.
- Operating activities: cash from the day-to-day business, such as collections from customers, payments to suppliers, salaries, rent and tax.
- Investing activities: cash spent on or received from long-term assets, such as plant and machinery, buildings or a fixed deposit held for more than a year.
- Financing activities: cash received from or repaid to lenders and owners, such as a term loan drawdown, the principal portion of an EMI, or fresh capital brought in.
The net movement across the three sections must equal the change in the bank and cash balances for the year. That single control check is what makes the statement trustworthy.
Indirect method and direct method
There are two ways to build the operating section. The indirect method starts with net profit and adjusts it for non-cash items and working capital movements. The direct method adds up actual receipts and payments. Most small businesses and most bank formats use the indirect method because it reuses the profit and loss account and the balance sheet that are already prepared, and because the reconciliation to profit is visible on the face of the statement.
Step-by-step: preparing the statement in Excel
Step 1: Set out the columns and rows
Give each financial year its own column, with the previous year beside the current year so movements are easy to compare. Leave one column for workings and notes. Build rows for each line item under the three activity headings, then reserve the last rows for the opening balance, the net movement and the closing balance.
Step 2: Start from net profit before tax
Link this figure to the profit and loss sheet with a cell reference rather than retyping it. A formula such as ='P&L'!D20 keeps the statement tied to the accounts, so a correction in the profit sheet flows straight into the cash flow statement.
Step 3: Add back non-cash charges
Depreciation and amortisation reduce profit without using cash, so they are added back. The figure should match the depreciation schedule in the balance sheet; a mismatch is usually a sign that a fixed asset was written off or revalued during the year.
Step 4: Adjust for changes in working capital
Compare this year's debtors, stock and creditors with last year's and record the movement. An increase in debtors or stock uses cash, so it is subtracted from profit. A decrease releases cash and is added back. An increase in creditors means the business is holding on to cash longer, so it is added. Keeping each of these as a simple subtraction of last year's figure from this year's makes the working clear.
Step 5: Enter investing and financing movements
Add the purchase or sale of fixed assets under investing activities, and the loan drawdown, EMI principal and capital movements under financing activities. Interest paid is shown as a financing outflow in many presentations, so decide the treatment once and follow it every year.
Step 6: Reconcile to the bank balance
Opening cash plus net movement must equal closing cash. Check that closing cash agrees with the bank and cash figures in the balance sheet. If it does not, look first for a working capital line that has been treated as cash, or a fixed asset purchase that was entered without its matching payment.
Excel habits that keep the sheet reliable
- Keep all assumptions on a single input sheet, and let every statement read from it.
- Use one sign convention throughout, so an outflow is always negative.
- Type no totals by hand; use SUM over a fixed range so a new row is included automatically.
- Colour-code input cells and calculated cells so a reviewer can see what may be edited.
- Keep a visible check row that shows zero when the closing balance ties to the bank figure.
Common mistakes to avoid
- Recording the full EMI as a cash outflow while the profit and loss account carries only the interest, which double-counts the principal.
- Treating goods on which GST has been charged as a simple cash sale, without separating the tax collected.
- Mixing the personal drawings of a proprietor or partner with business expenses.
- Leaving the opening balance hard-coded, so next year's sheet cannot be rolled forward.
Cash flow statement and fund flow statement
The two statements are often confused. A cash flow statement explains the movement in cash and bank balances alone, and it can swing from month to month as debtors pay or a large purchase is made. A fund flow statement looks at the longer-term sources and uses of funds, such as a term loan raised to buy machinery, a subsidy received, or profit retained in the business instead of being drawn out by the owners. A banker reviewing a working capital limit usually asks for both, because one shows short-term liquidity and the other shows how the business is financed over time.
For the related preparation guidance, see automated cash flow management.
When a spreadsheet stops being enough
Excel is a good fit for one business and one or two years of history. It becomes fragile when a bank asks for projections in a fixed CMA format, with the cash flow statement, fund flow statement, DSCR and MPBF computation all agreeing with the same balance sheet. At that point the linking work multiplies, and a single changed assumption has to be carried through every sheet by hand.
Finxora's CMA report software builds the cash flow statement from the same figures as the operating statement, fund flow statement, DSCR and MPBF, so one assumption change updates the whole pack. Audited and provisional years can be prepared in the balance sheet software and exported as PDF, Excel or Word. A filled sample report shows how the cash flow page prints before you start your own file.
