Build a Cash Flow Statement From Scratch Using a Balance Sheet and Income Statement

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

Channel: Kenji Explains

Views: 182,212

Keywords: create a cash flow statement, build a cash flow statement, cash flow statement from scratch, build a cash flow statement using a balance sheet and income statement, build a cash flow statement using an income statement and balance sheet, cash flow statement, indirect method cash flow, excel cash flow statement, cash flow statement interview, interview test cash flow statement, operating activities cash flow statement, investing activities, financing activities, cash flow

Id: DShnAeHd4BU

Length: 14min 1sec (841 seconds)

Published: Sun May 22 2022