in this video we'll build a cash flow statement from scratch using an income statement and a balance sheet and this is one of the most common interview questions in finance or accounting so let's get into it to build a cash flow statement we're going to need a balance sheet for the current year as well as the previous year which is what we have right over here and also we're gonna need an income statement for the period between those two years that's the information we have over here for a sportswear company and you can download the excel file in the description as you can see under the balance sheet if you go all the way down by pressing control down arrow you'll find that under the check which basically checks if everything is balancing you'll notice that under the 2021 figures there's actually it's not zero so that means that the total assets and the total liabilities and shareholders equity don't match and that's because of the cash flow over here which we currently don't have and we're going to derive based on the cash flow statement that we'll create right next to it to give you a bit of background on the cash flow statement there's three main sections the cash flow from operating activities cash flow from investing activities and lastly cash flow from financing activities and after summing these three you're going to get the change in cash we'll start with the operating section which has to do with cash inflows and outflows from the core business activity so that might be from buying inventory i'm collecting bills from customers etc hopping on to excel over here to the right hand side we're going to create the cash flow statement so firstly let's select this area over here ctrl c just to copy and paste that formatting ctrl v there and then here we're just going to type the cash flow statement there you go and now there's two different ways to calculate the cash flow from operating activities the direct method and the indirect method the direct method only uses cash transactions and ignores all non-cash transactions to reach net income while on the other hand for the indirect method it starts with net income and eventually reaches the same net cash flow from operating activities but it still accounts for non-cash transactions like depreciation overall the indirect method is more popular as most businesses operate using accrual accounting and you might wonder what does that mean it means that they record their revenues or expenses when a transaction occurs as opposed to when the cash is actually received or dispensed for example suppose you delivered an iphone to a customer but you're only getting paid in 30 days time under accrual accounting that would be accounted for as you earned that revenue already but under cash basis accounting it wouldn't because you haven't received the cash up until 30 days later when you do so using the indirect method firstly we're going to have net income under a cash flow statement so let's go ahead and link that which is just going to be under the income statement right over here and we can copy and paste that for the figure as well then just after that we're going to have depreciation and amortization which is added back so we'll go ahead and select it which should be right over here and we'll just copy and paste that as well the reason it's added back is because it's a non-cash expense so that means that there's no actual cash inflow or outflow that occurred as an example if you think about equipment in a factory that's appreciating in value nobody's actually paying cash as the value is being written down and since the cash flow statement is all about tracking cash we want to remove the depreciation expense and so that's why we add it back to reverse its effect next up on the cash flow statement we have changes in working capital and this is simply the difference between current assets other than cash minus current liabilities let's start with the current asset side so for this you can just go to equals and we're going to make this dynamic put a quotation and put change in press the space there close the quotations and then do the ampersand we're going to link that one to accounts receivables and now when we drag this down all the way to other current assets you can see that it dynamically updates and for the figures we're going to take the difference so it's going to be the 2021 figure minus the accounts receivables in 2020 hit enter there the reason we actually need to calculate the difference there as opposed to just linking it like we did for the income statement line items is because the balance sheet is for a snapshot of time and so that's why we need the two different snapshots to find the difference while on the income statement it's just for a period of time so it's accounting for that whole time range when we think of the number under change in accounts receivables if we look over to the balance sheet you'll find that it's actually going up which in terms of cash is not a good thing because if the accounts receivable are going up that means more and more people owe us money and they haven't actually paid us cash for it so for this we're gonna have to change the sign here so press the f2 key and just at the very front we're gonna go e minus and then open the brackets and close them at the very end and hit enter and same thing for the two above they're just gonna follow the exact same pattern in the case of inventory for instance as you can see inventory went down which essentially means that hey we had a lot of issues in our factory and we managed to sell them and so that's cash coming in for us now looking at the current liabilities side first you can just copy and paste this formula and then press the f2 key and we're just gonna drag that down all the way to current liabilities under short term debt which is gonna be the first line item there and hit enter but this time around for the formula it's actually gonna be flipped so we'll go over here we'll go equals and first it will select the short term debt of 2021 minus the short term debt of 2020 and it says that it's a negative 224 and when you think about it intuitively if the short-term debt has gone down that means you paid cash to reduce that amount of debt and so that's why it's negative because it's a cash outflow so that makes sense the same logic applies to the other line items but we're actually gonna go copy and control v there and we're gonna skip the current portion of long-term debt as we'll work on it later with the long-term debt that we have just below so let's go ahead and drag that down to accounts payable and same thing over here we're gonna go equals and first it will select the accounts payable of 2021 minus the accounts payable of 2020. hit enter there and then we can just drag those down all the way to the very bottom which the last one is changing other current liabilities now that we worked out the working capital we can go ahead and sum all of these and this is going to be the cash flow from operating activities and over here we're just going to go equals sum press the tab key and we'll select all of these over here and hit enter while we're looking at just one technical interview question here if you're looking to break into the financial services industry the investment banking interview skills course will walk you through what to expect in the interview process how you can tackle some of the toughest questions and more the course first explains what an investment market does in their day to day followed by the recruitment process where you'll learn to answer all types of questions ranging from behavioral or fit questions to technical questions on accounting and finance and even brain teasers plus the course features a recorded example of a successful interview so you can see what to expect and how to navigate the questions successfully so if you want to ace your interview get 25 off using code kenji25 and the link is going to be in the description all right back to the cash flow statement moving on to the investing activities and these include investing in property plant and equipment buying stocks and bonds etc first let's look up tp e so for that we're just going to type over here purchase of ppe and as you can see over here we've got the gross property plant and equipment all the way to the net down below and for this we're just going to take equals the 2021 figure minus the 20 20 20 figure and again we're going to press the f2 key we're going to put a minus sign in front and we're going to open up those brackets and close them over here like so now the reason we took the gross ppe over here as opposed to the net pbe is because of accumulated depreciation and as you can see up over here we already accounted for depreciation and so that's why we're going for gross ppe up over here let's apply the same logic to the other line items on their investing activities so firstly we're going to have the purchase of other long-term investments and just below that we're going to have other assets and for this the formula is going to be pretty much the same press the f2 key there because we're going to have to drag it down first one is going to be other long-term investments so let's go ahead and select that and for other assets we can just copy and paste that down and then the sum of that is going to be the cash flow from investing activities and we're just going to press the alt equals and that's going to sum it for us moving on to financing activities and this shows how a company funds its operations and is typically either in the form of debt or in the form of equity in this case if we look under non-current liabilities you can see that we've got long-term debt and other liabilities as well so let's go ahead and account for those for long term debt it's increasing so we'll go increase in long term debt press the top key and we're gonna do equals open up the brackets there and we're gonna select the 2021 figure minus the 2020 figure just next to it close those brackets and put a plus sign and the reason we're doing a plus sign is because we also got to account for the current portion of long term debt which we didn't account for earlier so we'll open up the brackets again select this one minus this other one close those brackets and hit enter and when we think of this intuitively if long-term debt has increased that means that we actually got more cash and so we owe more money but we got cash in and so that's why it's a positive sign there for other liabilities it's also an increase so we'll go increase in other liabilities press the top key and that's just going to be equals to this one minus this one and it's a lot simpler there hit enter and from there we can start working on the equity side just below firstly we can see over here that common equity has increased by quite a bit so we're gonna put a proceeds from share issuance press the tab key equals so it's going to be this one minus this one over here and hit enter basically to explain what happened here they issued more shares so that means that in exchange for those shares they got more cash into the company and so as the cash flow that went up then for the line item right below which is actually the other comprehensive income or loss we're not gonna account for it as it's an unrealized gain or loss a simple example of this concept is suppose you bought a house for 1 million and now it's worth 2 million so that house is appreciated in value but because you haven't actually sold it there's no cash inflow for you and so that's why we don't account for it in the cash flow statement lastly we've got retained earnings that you can find down over here and we're not going to account for this one either as part of the formula here includes net income which we already accounted for at the very beginning of the cash flow statement and here's the formula for retained earnings it's the previous year's retained earnings plus any net income minus any dividends now the dividends part of the formula we do need to account for as that's a cash outflow in that the company is giving money to its investors and so the cash is going out typically you'll find dividends in a company's statement of shareholders equity however if you're not given that in an interview so if you only have the balance sheet and the income statement then it's reasonable for you to assume that there's no dividends involved for simplicity in our case we just put it down over here as you can find it just below the income statement so let's go ahead and work on it for this it's going to be dividends paid and we're just going to go ahead and link it which is going to be this figure over here and actually it's going to be a negative figure press the f2 key there and put a negative sign in front from there we can calculate the cash flow from financing activities press the tab key press alt equals and that's gonna sum it up for us now to get our ending cash balance firstly we need the beginning cash balance so type beginning cash balance and that's simply going to be equals to under the balance sheet the 2020 cash balance which is going to be this first one over here and then over here you can put the change in cash which is simply going to be the sum of the cash flow from financing plus the cash flow from investing plus the cash flow from operating activities hit enter there and then that's going to be the ending cash cash balance just below that press tab key and we're going to go alt equals again hit enter and now if we link this figure over here to the top of the balance sheet over here everything should match such that the check here should equal to zero so let's go ahead and give that a try we'll go equals and link that to the ending cache balance hit enter there and now it's all linked so if you scroll all the way down to the bottom you should see that the check is equaling to zero if that's the case that means that you did the process correctly let's now work on some formatting for the cash flow statement so firstly for all the values over here we're gonna go ctrl shift down arrow hopefully it'll get all the way to the bottom from there you're gonna press the ctrl 1 key and under number you can just go under number there again hit this one and then we want to use a separator so hit the comma there we don't want decimal places we'll put a zero and hit enter that's looking slightly more like it then for the totals let's go ahead and bolden them so ctrl b and we'll also put on a top line border so we'll go alt h b p same thing with this one over here so ctrl control b alt h b and this very last one over here as well all to hbp for the ending cache balance let's put a bit more emphasis on it so go ctrl b and then we'll go alt h b d which is going to give us the top and the bottom border and let's also highlight it in yellow so for that we'll go to alt h and from there i'm just going to go under my recent colors and select that yellow now if you go back to control control up all the way to the top i think that's looking a lot more like it for more on financial statements check out this video over here on financial modeling or this other link over here to learn more about finance and valuation smash that like and that subscribe button if you liked it and i'll catch you in the next one