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