13.1 – Indirect cashflow

We are at a crucial juncture in our financial modeling journey. This chapter will derive the cash flow statements and plug that cash flow number into the balance sheet. After we do, hopefully, the balance sheet balances. Notice, I used the words ‘derive the cashflow statement’. What do I mean by that? You need to take a few steps back and think about the cash flow statement and its purpose.

The cash flow statement of a company gives the company’s cash position. The cash position itself is estimated after reviewing the cash inflow and outflow from the company’s operations, investments, and financing activities. Each of these activities either generates cash or consumes cash. If you are new to cash flow statements, I’d suggest you look at this chapter – https://zerodha.com/varsity/chapter/cash-flow-statement/.

Think about the high-level summary of cash flow and how the company’s CFO and their team prepare the statement. Like the P&L and Balance Sheet, the cash flow is also prepared by considering the voucher entries, bills, receipts, and bank reconciled statements. Preparing the cash flow statement with bank reconciled statements, invoices, and receipts is called the ‘Direct cashflow method.’

As a financial modeler, you have two options to prepare the cash flow statement in the financial model.

• • Get access to bills and vouchers of the company and prepare the cash flow just like the finance team
  • Hardcode the historical statement just like the way we did for P&L and Balance sheet and then project for future years

Of course, option one is ruled out for obvious reasons. Option 2 is possible, but we miss out on the ‘validation of the model’ part if we take the hardcoded approach. I’ll explain what this means in a bit.

There is a third approach to cash flow. It is called the ‘indirect method’ of cash flow preparation. In the indirect method, we take the P&L and the Balance sheet data of the company as input and process the input based on a series of logical steps. The result of the process is the company’s net cash flow. Here is the good part – the net cash flow derived from the process should match the company’s cash flow stated in the balance sheet. If it does, then it kind of validates the model for us. If the numbers don’t match, then it is because we’d have made an error somewhere in the model, and it allows us to recheck. For this reason, we will use the indirect method of preparing the cash flow statement.

By the way, speaking of validating the model, you may argue that the model is heavily dependent on the assumptions that we make and therefore bound to have errors. Yes, I won’t argue with that. I’m aware of this fact, but at the same time not concerned.

Think about it this way; our main focus is to build the structure of a house with a solid foundation. Once the house is built with the proper foundation, we can mix and match the interiors as many times until we find it to our satisfaction. Extending the same thought, our objective is first to build the model with the right linkages. Once the model is fully built and completely integrated, we will spend time debating each assumption, figuring out if it makes sense, and changing the values accordingly.

I’m sure you have questions about this, but hang on and read through the rest of the chapter (and module), and I’m sure you will get all your answers. For now, let’s look at the indirect method of cash flow statements.

13.2 – Cashflow activities

A company can be looked at from the perspective of its activities. Broadly speaking, the activities are –

• • Operating activities
  • Investing activities
  • Financing activities

Consider Bajaj Auto, for example; what does the company do? It manufactures two and three-wheeler vehicles, sells these vehicles, and services these vehicles. The company needs to invest in plants, machinery, and equipment to carry out the operations. To finance the operations, it may (or may not) needs funds from external sources. If the company borrows money, they have to repay. Then, of course, from the profits, dividends are distributed.

Can you think of any other activity that the company does? You can extend this framework to any company and realize that all the activities are within the scope of these three categories.

Each of these categories either generates cash or consumes cash. For example, consider the inventories of a company. The inventory of a company is directly related to the company’s operations. If the company’s inventory has increased compared to the previous year, then it means that more money is stuck in terms of finished goods. Hence, inventory (which is an operational activity) has consumed cash. On the other hand, if the inventory is less in year two than in year one, inventory has generated cash or conserved cash.

Let us take another example. Assume that a company has borrowed money from the bank to fund operations. Borrowing funds is a financing activity, and by borrowing, cash is credited to the company’s bank account, hence considered as generated cash.

Likewise, when paying dividends (financing activity),  money goes out of the company’s account; hence, it is treated as an activity that consumes cash.

Imagine if you can look at all the line items (mainly from the balance sheet) and –

• • Categories them as operating, financing, or investing activities
  • Figure out if it is consuming or generating cash

Then, by summing cash flow from different activities, you should generate the company’s cash flow statement and get the company’s cash position.

Let’s go ahead and do this for our model.

13.3 – Categorizing line items

The idea is simple, we list all the balance sheet line items and figure out their impact on the cash position if it were to increase or decrease. Eventually, each line item either tends to generate cash or consume cash.

For example, if the company were to issue more shares and increase the share capital (raise more equity), then cash comes into the company, and the cash position tends to increase. If the CAPEX spend were to reduce, then from the perspective of the cash position, it tends to increase cash.

13.4 – Cashflow from operating activity

Using the above framework, we can now derive the cash flow statement in the indirect method. The idea here is simple, we treat each line item basis the activity type and then figure if that particular line item increases or decreases the cash position.

You know the drill, we create a new excel within the workbook and rename it as ‘Cashflow.’ We index it like we did the other sheets. We will start with the operating activities first.

The idea here is to find out if the company’s operation has generated cash or not. We start with the PAT, add back depreciation, and then add the net change in working capital by considering each line separately.

Remember, depreciation is an accounting expense. Hence we need to add back depreciation. Here is the snapshot of the excel sheet –

I want you to notice two things here. First, I’m starting the sheet by directly working on the Year 2 data. There is a reason for this, which you will soon realize. Second, I’ve extracted the depreciation value from the balance sheet and not the P&L, and this is because the P&L depreciation is only for the year, but in the balance sheet, you not only get the yearly depreciation but the depreciation non-expense as well. Alternatively, you can also get the depreciation data from the asset schedule.

Continuing on the operational activity, we now look at working capital changes and their impact on the cash position. Here is the excel setup –

As you can see, since we are calculating ‘increase’ for the previous year, we are starting from Year 2 and not Year 1.

All the line balance sheet items that I’ve considered here are related to the current assets and liabilities. These two together help me identify the net change in working capital. Let me do the very first calculation and explain a particular nuance here.

From the balance sheet, Y1’s Current liability is 73.53 Cr, and Y2’s current liability is 102.74Cr. An increase in current liability is –

Y2 – Y1

= 102.74 – 73.53

= 29.21 Cr

We discussed earlier that if the current liabilities increase, then from a company’s point of view, the company retains the cash as it is deferring payments against its liabilities to a later date. It’s as simple as, ‘I owe you money, but I will pay later instead of paying you now. Hence my bank balance tends to increase.

Therefore, if there is an increase in current liability, we will add it. Now, let us flip the numbers for a momentum –

Current liability of Y1 = 102.74

Current liability of Y2 = 73.53

If we do Y2-Y1

= 73.53 – 102.74

= – 29.21 Crs.

Here is a situation where the company is reducing its current liability, which means it will tend to reduce the cash balance.

If we plug this on our ‘Add: Increase in current liability framework,’ we automatically deduct cash, thanks to the negative sign.

I hope this explanation is clear; else, please do feel free to ask your queries, and I’ll be happy to explain whichever bit you find challenging to understand. I’ve extended the same to all the other line items, and here is how it looks –

One common query at this stage is why we are adding things like provisions and current liabilities and deducting things like inventories and sundry debtors. We are calculating the increase in value in Year 2 over Year 1. Some of these line items tend to increase the cash balance, and some tend to decrease.

The total of all the values of all these line items is the net change in working capital. Cash flow from operations is (indirect method) –

= PAT + Depreciation + net change in working capital

For Year 2, the operating cash flow or operating activity is –

94.36+20.99-147.84

= 32.69 Crs.

At this point, financial modelers will usually quickly check the company’s annual report and compare the stated cash flow from operations to check if it matches.

The numbers won’t match for obvious reasons. But don’t worry about that; in the Indirect cash flow method, or primary concern is to match the overall cashflow number i.e.

Cash from operating activity + Investing activity + financing activity

Here is what the cash flow from operating activity looks like –

Next up is cash flow from investing and financing activities

13.4 – Cashflow from investing and financing activities

The first thing we need to consider while dealing with investing activities is the CAPEX spend. If the CAPEX spend increases, then it consumes cash, and if the CAPEX spend decreases, it generates cash (or conserves cash). We can get the CAPEX data from the asset schedule.

Notice, I’ve specified ‘Less: CAPEX’ to indicate that the increase in CAPEX results in cash consumption. I’d also request that you notice the necessary adjustment in the formula bar.

The company has not disposed of any assets, and we know this from the asset schedule. Hence, the disposal of assets will be zero.

The other two line items, i.e., capital work in progress and investments, are straightforward, and we get that from the balance sheet. The total of all the four-line items is the cash flow from investing activities.

Next up is the cash flow from Financing activities. I’ve completed this on excel, do check the snapshot –

I think you know what’s happening with the increase in share capital, secured and unsecured loans. I’ll focus on the last four line items. Past service cost of employee benefit is a one-time cost specific to this company. Costs such are one time in nature should be dealt with slightly differently. Here, you don’t consider the difference between the two years; instead, take the expense applicable for that year directly.

Dividends, too, are a yearly expense, and the company may even decide not to pay dividends for a year. So all such one-time costs should be treated as is. I’ve highlighted the same in the formula bar above.

We have now calculated the cash flows from all three activities. The sum of these three activities gives us the cash flow for the year. Here is the same –

Now, don’t be in a hurry to plug these numbers into the balance sheet. It won’t balance just yet. Remember, we have calculated the cash position for the given year.

What do we need to do to get the complete cash flow picture? Please look away from your device and think about it for a few minutes.

I hope you got the answer. The number we calculated above is for the current year’s cash position. To this number, we need to add the previous year’s closing balance (of cash position) and then arrive at the total cash position for the year. Yes, we are talking about applying the base rule here.

We can get the closing balance of cash and cash balance for Year 1 from the balance sheet. The exact value is now the opening balance of the cash position in Year 2. Add to this the cash flow for the year (which we calculated); we get the closing balance of Year2.

This net cash flow that we have calculated should match the balance sheet numbers. To clarify the same, I’ve pulled the balance sheet numbers –

The historical numbers match (ignore the decimals), so we can now pull the cash flow numbers back into the balance sheet for future years. Yet again, by linking cash flow back into the balance sheet, we continue to integrate the financial model.

I’ve done the same, and like magic, the balance sheet balances 😊

As I mentioned earlier, this is a landmark moment in our financial modeling journey. At this point, we are at least 80% done with the model. In the next chapter, we will take up the valuations.

You can download the excel used in this chapter here – [Cashflow statement Excel].

Key takeaways from this chapter.

• • One can derive the cashflow from P&L and Balance sheet; this is called the indirect method of cash flow preparation
  • Few line items tend to increase the cash balance, and some tend to decrease the cash balance
  • We should use the depreciation from the balance sheet (or asset schedule ) in the cash flow statement
  • After deriving the cashflow numbers, we need to add the previous year’s cash flow to get the closing balance of the cash position
  • The net cash flow flows back into the balance sheet to balance the balance sheet.