How To Build A Cash-Flow Statement With Excel From Scratch

 

In this post I will teach you here how you can build the third one through an Excel example so you understand better the relation between the three. However, you can find these two financial statements along with the cash-flow statement in company websites and other databases. To be more specific, you need to get the balance sheet for two consecutive periods and the income statement for the first one as shown in the following Excel example:

https://drive.google.com/file/d/1oBH7YVvJlKG9e82UjL1F2bHfXR5BiGLj/view

The cash-flow statement in this example represents the changes from 2017 to 2018 in the accounts that involve cash inflows and outflows as you can see in the formulas used that I will explain afterwards. Therefore, this model that represents the cash movements and transition from one period to the next has the goal to calculate the final cash balance (364.30) for the second period that is 2018 by adding the 3 different types of cash-flows that occur during 2018 (operations, investing and financing, which together sum 329.70 and whose calculations will be explained afterwards) to the initial cash amount (34.60) in the first period that is 2017.

Once you have checked the Excel example and know the basic functioning of the cash-flow statement, let´s explain how calculations are made. First, we are going to calculate the cash-flows from operations. You need to take the final net income amount for 2018 (422.50 in our example) and we are going to sum the account changes during 2018 that represent cash inflows and subtract the changes that represent cash outflows. Then, depreciation and amortization amounts are added-back to the net income because they are non-cash expenses. Non-cash expenses are expenses that do not involve any cash inflow or outflow to any third party outside the company. Consequently, they are not treated as cash inflows nor outflows either and should be added-back to the net income from which we will start to add inflows and subtract outflows.

Secondly, after this first adjustment is made, we will add working capital changes and other expenses changes such as deferral taxes, which also come from company operations. Account payables are considered inflows because you retain that cash in your bank account till you actually pay your suppliers (and thus they have positive sign in the cash-flow statement). In contrast, receivables and inventories are treated as outflows because you need to fund them at some cost till you finally receive the cash from them (and thus they have negative sign). Finally, deferral taxes will have a positive sign in case the change comes from the liability account (same as payables) while the change between periods will have negative sign in case they are treated as an asset account (same as inventories and receivables). As a result, the sum of all the above will equal the cash you get from operations.

Following this, the cash from investing must be calculated. In general, it includes changes in capital expenditures (tangible assets) and also other long-term assets (intangibles assets in our example). In case these asset accounts increase during the period, the investment increase needs to be funded with some cash outflow. On the one hand, the capital expenditures are calculated separately because we also need to subtract the depreciation expense to the change during the period. On the other hand, the same calculation process is made with intangibles and goodwill by subtracting amortization expense to the change during the period.

Next, the cash from financing includes both equity and debt financing. Th first four changes (increases in bank loans, long-term debt, preferred stock, and common stock) provide more funds/cash to the company to invest so that they are treated as inflows and have positive sign. Nevertheless, treasury stock (also named stock repurchases) and dividend payments involve cash outflows for the firm and thus they will have negative sign. The overall result is the cash for financing.

Finally, as pointed out at the beginning, these 3 types of cash changes (operations, investing and financing), which together sum 329.70, are added to the initial cash amount (34.60) to get to the ending cash balance for 2018 (364.30) that will also be included in the balance sheet afterwards.