Cash flow statement template in Excel: indirect method

Cash flow statement template in Excel: indirect method

When someone asks why the business made money and still could not pay the month's bills, the answer is almost never in the income statement. It is in the cash. And cash does not move because of what you earn; it moves because of what you collect, what you pay, what you invest and what you finance. Explaining that journey, from the result for the year to the balance that shows up at the bank, is exactly the job of the cash flow statement.

This cash flow statement template in Excel works with the indirect method. It is a single sheet of fixed rows, with the concept in column A and the value in column C: three linked sections, operating, investing and financing, plus a final check against the real cash in the bank. You type eleven figures and everything else is calculated for you.

What it is and who it is for

The cash flow statement is the report that explains where the cash came from and where it went during the period. It differs from the other statements in its starting point: it is not built from account balances, it is built from movements. Its underlying question is a very concrete one. If the year started with 500,000 at the bank and ended with 2,100,000, what produced that change? The statement answers that question in three stages and keeps its last line to prove the answer is true.

It serves, first, the owner or manager who wants to know whether the business sustains itself with what it sells or whether it is living on debt. It serves, next, the person reviewing the numbers who needs an orderly explanation of the gap between profit and cash. And it serves, at closing, as proof that the balance the books show in the bank matches movements that exist and can be traced. It is a tool for analysis and control before it is a document for delivery.

Why the indirect method

There are two ways to build this statement. The direct method rebuilds the cash by adding up inflows and outflows one by one by their origin: collections from customers, payments to suppliers, payroll, utility bills. It reads very clearly, but it demands the full detail of every movement and, in practice, forces you to keep a parallel set of books that almost nobody maintains month after month.

The indirect method does the opposite, and that is why it is the most widely used: it starts from the result for the year, the figure already calculated in the income statement, and corrects it with two layers of adjustment. First it removes what was recorded as income or expense but never moved cash. Depreciation is the classic case: it is an expense that lowered profit, but the money for the machine was paid the day it was bought, not every month, so it is added back. Provisions work the same way: they are an estimate, an expense recognised before a single unit of currency leaves.

Second, it adds the effect of the working capital accounts, which are the ones that explain why profit and cash drift apart. Here it pays to notice one detail that is often overlooked: what is adjusted is not the account balance but its change, the movement from one period to the next. A large receivables balance says nothing on its own; what matters is how much it grew or how much it fell against the previous period.

Movements: cash decides the sign

Here is the rule that causes the most mistakes, and the one worth understanding once and for all: in this statement the sign does not describe the account, it describes the cash. If receivables go up, the company sold but did not collect; it did sell, and that income is already inside the result, but the cash was left on the road. That is why the change is written with a minus sign: with a 200,000 increase in receivables, the line reads -200,000.

The same logic applies evenly across the block. Inventory going up is cash left in the warehouse: minus sign. Suppliers going up is the opposite, because the business financed itself with its suppliers and has not paid out yet: plus sign. And the items that never passed through the bank, such as depreciation and provisions, enter with a plus sign to neutralise the expense that had already lowered the result. The proof that the rule was applied well is simple: net cash from operations has to make accounting sense, not just arithmetic sense.

The three sections of the statement

The operating block gathers the day to day of the business: the result for the year with its adjustments and the effect of receivables, inventory and suppliers. It is the most important section, because it is the one that tells you whether the business produces cash from its own activity or depends on other sources to stay on its feet.

The investing block records what was bought and what was sold in fixed assets. The purchase goes with a minus sign, because buying a machine consumes cash, and the sale goes with a plus sign. The wear of the asset is not recorded here, since it was already adjusted above as depreciation: what is recorded here is the money that changed hands during the period.

The financing block shows how debt and the owners' contributions moved. A loan received enters with a plus sign, because it is cash coming in; repayments and profit distributions go out with a minus sign, because they are cash leaving. At the end, the three net figures are added to the opening cash and the closing cash appears, which is the figure the statement set out to explain.

The full example, line by line

With the figures for one period, the build looks like this. You type the result for the year and the concepts that adjust it; the file adds the three blocks and checks the outcome against the bank.

The reading is straightforward. The business earned 1,800,000, added back 300,000 of depreciation and 100,000 of provisions, deducted 200,000 that stayed in receivables and 150,000 that stayed in inventory, and recognised 250,000 it had not yet paid to suppliers: the cash produced by operating was 2,100,000. It then bought fixed assets for 800,000, received a 500,000 loan and repaid 200,000. The increase in cash was 1,600,000 and, with 500,000 available at the start, the period ended with 2,100,000.

The last line of the file is the one that gives value to all of the above. It compares the calculated closing cash with the real bank balance at closing: here both are 2,100,000, the difference is zero and the check ties. When it does not tie, the sheet warns you, and that warning is useful, because it means a movement was recorded at the bank and not reflected in the statement, or the other way around: an adjustment was written without the money having moved. The cause is almost always a transfer between accounts, a cheque not yet cleared or an expense paid from another account.

How to fill it in five steps

1. Write the result for the year in the first row of the operating block, exactly as it appears in the income statement.
2. Type the depreciation and the provisions for the period with a plus sign, because they are expenses that never left the bank.
3. Enter the change in receivables, in inventory and in suppliers, respecting the sign rule: whatever consumed cash goes with a minus and whatever freed it goes with a plus.
4. Record the purchase or sale of fixed assets in the investing block and, in the financing block, the loans received and the repayments.
5. Type the opening cash, which is the bank balance at the start of the period, and read the final check against the real balance.

With that, the statement is built and explained. If any figure changes, you fix the row and everything automatic recalculates by itself, so the check can be read again in seconds.

What this statement is not

Do not confuse it with day-to-day cash flow or with a forecast. The daily flow is an operating tool: it tells you whether tomorrow's money covers payroll and helps you decide which invoice to collect first. The forecast looks forward and estimates what will come in and go out in the coming weeks. This statement, by contrast, looks back: it projects nothing, it explains a change that already happened and is already in the books.

That is why its figures are verifiable and not debatable. If the calculated cash does not match the bank, it is not a matter of judgement or rounding: a record is missing. That is precisely the difference between a statement that explains and a list of movements that merely enumerates.

Before you deliver it

One last note worth keeping in mind: this template is a working tool and an internal control aid. It helps to order information, to find differences and to support conversations, but it does not replace an official document or a filing. Anything presented to third parties must come from the formal books, with the supporting documents and the signature that apply.

This statement leans on the income statement, where the result for the year comes from, and it talks to the cash flow template all the time: one explains what already happened and the other helps anticipate what is coming. The Kardex Tauro templates follow the same idea of fixed rows and a final check, so that closing each month is a review and not a puzzle.