How to prepare a cash flow statement in Excel?

The dreaded cash flow statement. Most accountants have at some point struggled to balance a cash flow statement and wished there was an easier way! Well, there is and a cash flow statement model in Excel is a great tool for making balancing cash flow statements easy.

A cash flow statement is prepared from income statement and balance sheet movements but it is usually the balance sheet which causes most of the difficulty when the cash flow statement does not balance.

A cash flow statement can therefore only be prepared in Excel if the user enters the relevant income statement and balance sheet information. This information is usually needed for at least the current and previous financial years but if you also need to prepare a cash flow statement for the previous (comparative) financial year, you would probably also require balance sheet balances for a third year.

View all our cash flow projection templates

Once you have all the financial information that you require available, you can create formulas to calculate each of the required cash flow statement amounts. This would involve linking all the required income statement amounts to the section where you specified the income statement values and adding formulas which calculate the movement in balance sheet balances between the current and previous financial years.

Whether you deduct the previous financial year's balance from the current financial year's balance or the other way around will depend on the nature of the balance sheet item. For example, an increase in a trade debtors balance usually indicates that less cash was received and the increase would therefore need to have a negative cash flow effect which means that the current year's higher balance would need to be deducted from the previous year's balance to create the negative cash outflow.

The inverse would be true for amounts owed to trade creditors. An increase in a trade creditors balance usually indicates that less cash had to be paid to suppliers and the increase would therefore need to have a positive cash flow effect which means that the previous year's balance would need to be deducted from the current year's balance to create the positive cash inflow.

Imbalances in cash flow statements frequently occur because of the cash flow movement calculation not being included consistently with the type of balance sheet item that it relates to. Another issue that may cause an imbalance is if a balance sheet item has been omitted from the cash flow statement calculation.

Then there is also the possibility of income statement and balance sheet values not being consistent. For example, you may include the depreciation charges on the income statement in your cash flow statement as a non-cash item but the depreciation charges may differ from the movement in the accumulated depreciation item on the balance sheet. This would then lead to a cash flow statement imbalance if the cause of the difference is not also included in your cash flow statement calculations.

As you can see, cash flow statement calculations can become rather complicated and at times result in hours being wasted attempting to find the cause of a cash flow statement imbalance. It therefore makes a lot of sense to invest in an Excel model that can assist you in preparing cash flow statements which are easy to balance.