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