Excel Finance Class 16: Liquidity, Current Ratio and How Current Ratio Can Be Manipulated

Download Excel workbook http://people.highline.edu/mgirvin/Ex...
Learn how to calculate liquidity Ratios including Current Ratio, Times Interest Earned and Cash Ratio. Also see how Current Ratio changes when certain transactions occur like buying inventory, paying a supplier or Incurring Long Term Debt.
Highline Community College Busn 233 Slaying Excel Dragons Financial Management with Excel taught by Michael Girvin.

---

Closed Caption:

welcome to finance an Excel video number
16
hey if you want to download this
workbook or the pdfs click on the link
directly below the video and you can
download the workbook off and PDF for
chapter 3 and we got to talk about
liquidity ratios liquidity what's
liquidity how quickly something can be
converted to cash can they cover their
short-term bills right we talked about
working capital working capital is
current assets minus current load
liabilities but here's a ratio we talked
about a little bit in the last video but
more in the context of learning ratios
here we want to talk about liquidity
ratios in particular the current asset
commonly used in debt contracts where
you see it the current asset has to be a
certain amount or perhaps they have to
pay some of their debt early see a
current assets divided by current
liabilities for everyone all of current
liability
how many dollars of current assets are
there for us to potentially use that to
pay now above when we do this division
above one is generally good right
if it was 10/10 it's one which means we
have 10 current assets for every 10
current liabilities if it's 20/10 that's
to that means you have more current
assets right so that's pretty good below
one is generally not good now it's a it
depends are as always if it's a small
business maybe they need more if it's a
large business or the business has lots
of access to borrowing funds then maybe
below one is ok right now notice says
big corpse okay in the financial crisis
that's a bear stearns at the height of
it had one / don't know what it was but
their debt they had 40 times as much
debt is equity so I don't know what
their current ratio was but it was well
below one right so sometimes in that
case it turned out to be
not good but sometimes businesses can
get away with it usually common-sense
above one is good
there's a bunch of interpretations of
current asset and I have the will do a
bunch of examples but here are some
notes if you want to look through they
have my little scribbles about what
happens if certain things like if you
incur long-term debt if you pay off
short-term creditors we're actually
going to look at these examples in excel
so you can read that if you want later
so current ratio
what about the quick ratio or acid test
same thing except for we subtract out
inventory why we subtract out inventory
well you know this takes a little bit a
little while to sell right or maybe hot
it may be hard to sell its obsolete so
people take it out and this is like more
immediate measure of short-term
liquidity still another ratio the cash
ratio we just take cash / current
liabilities that means if you had to pay
it all off now could you do it now let's
go over to excel and look at an example
here's our whole foods market example
2005 and $YEAR 6 data let's go ahead and
calculate first for 2005 our current
ratio equals this is 2005 so we got our
cash accounts receivable inventory other
and then there's the total so that / our
current liabilities right there
ok so 1.6 we have one point six dollars
in current assets for every current
liabilities that's looking pretty good
now let's do for the next year
alright here's our total current assets
/ our total current liabilities
ok so one down a bit maybe they're
selling inventory more maybe they use up
some of their cash as we saw from the
balance sheet they actually bought a
bunch of assets so that makes sense that
it went down now let's take out the
inventory now equals and we're going to
open parenthesis because we need to do
subtraction before we do division so i'm
going to say total current assets
this is for I'm actually supposed to be
doing 2006 here so I'm gonna go Boop
that minus the inventory lot of
inventory for whole foods / total
current liabilities right there was 2006
now let's do 2005 I kind of do that in
Reverse we take our total current assets
minus our inventory and divided by total
current liabilities
so again that makes sense to and went
down if they're using cash to buy assets
it makes sense that it went down now
let's do the cash rate ratio here's all
of our cast for 2005 all / our total
current liabilities now this is if we
had to pay everything else right now we
have 82.5 sensing cast for every one
dollar of liability
that's not so bad and then here for 2006
what happened
I bet you went down let's see so cash is
right there / total current liabilities
well so went down a lot right so cash
went down a lot in relation to our
current liabilities alright those are
liquidity how quickly how you know
bankers are going to look how if they if
whole foods can pay interest suppliers
are going to look and they pass pay
their bills etc now let's go talk about
what accountants and what the people
inside the firm can do to one measure
current ratio since this is in a lot of
contracts of people know how to do a lot
of things or tricks right before the
balance sheet is prepared to maybe make
their current ratio look better
i'm going to click on the sheet current
ratio and you can use this sheet to go
ahead and try these calculations for
yourself i'm just going to go through
the end
result now we want to look at a bunch of
situations we want to say what happens
to current ratio when we purchase
inventory of course the answer is it
depends and then we'll look at three
depends i will say what happens when a
supplier is paid when short-term uh bank
loans are paid and a bunch of other
examples and again this is what can the
in general this is what happens to the
ratio when you do this so if your
manager and it's important to have a
certain current ratio then you need to
be aware of what these actions do to our
ratio
all right what if you purchase imagery
well of course it depends it will not
change if you pay cash and the reason
why is current assets right if you pay a
dollar cash
it's going to go right back into
inventory a dollar
they're both current assets so nothing's
going to change we add some inventory we
decrease some cash we go from a ratio of
2 22 now if you pay on credit
there's two possibilities if your
current ratio is greater than one it's
going to go down let's see how we
started for current assets we bought
inventory so it goes up by one so we end
up with five current liabilities
ap goes up by one so it's 3 & 5 divided
by three is 1.67 which means it will go
down but notice we started above one if
your current ratios is below one so
right now you have . 8333 of current
assets $41 of current liability so it's
less than one now look what happens we
started five let's say current assets we
ad inventory once we get six current
liabilities were at six we add one to
that we get 7 15 / 6 is . 8 36 / 7.85 it
goes up
alright next example
uh-oh suppliers paid well we're going to
pay some cash so current assets are
going to go down and current liabilities
going to go down right so we're playing
paint supply not a long-term debt will
talk about that one later
well again it depends depends on if
current ratio is greater than one so
greater than one we have a current ratio
of two right so we started current
assets of one cast goes down by one we
get three current liabilities or two we
pay off a dollar of that we go down to
three so what's 3/1 it's three so it
went up so if you you know
yeah that's what happens now the
opposite happens if we start at our
current ratio below one so we started
five cash goes down by 12 get for
current liability 6mm we paid it off so
it was down five 4/5 is . eight so it
actually will go down so that's what I'm
supplier is paid now short-term bank
loan
well if its short-term that it's
classified as current liability and
simply what's going to happen is your
cash goes down by a dollar if you pay
off you started for you go to three
current liability oh that's can be alone
goes down so we end up with one so it
goes from 2 to 3
how about the same thing just as a
moment ago if we're starting off less
than one we had five we paid off a
dollar of cash went down before we had
six current liabilities which included
that loan and went down by one we go 2.8
now long-term debt i'm going to scroll
down here long-term debt paid early now
it's important that it's paid early
because any long-term debt is on the
balance sheet but if any of it is due
within the next year it gets moved to
current liabilities so it's only when
it's you know
pay something early here's what happens
right
cash will go down by dogs so we start at
four we go to three so pay off but why
is the cash going down
oh because we paid off some debt but
look at this
the current liability stays the same
that didn't change it was the long-term
debt so we go from 4 3 2 22 no change we
get a ratio of 1.5 so that the current
ratio will go down when you pay off
long-term debt early now what about if
book they are is paid well it's kind of
a wash right because current assets we
have accounts receivable and cash so
cash goes up by a buck but they are goes
down so there's no change
what about inventory sold it costs now
usually you don't sell inventory accost
right but if you do what happens well
again it's a wash cash goes up by a
dollar inventory goes down remember
inventories recorded usually at what you
bought it for right so in this case
inventory down exactly what we paid cash
in so there's no change but if we sell
inventory at a profit
well we like that look at this cash goes
up by two we sold for two bucks
inventory goes down by one dollar so we
go from four we actually have to add
just to show you the formula we take the
40 yeah we add the cash by up by two
which is 26 subtract one we get five
current liabilities the same so goes up
from 2 to 2.5 finally if companies are
in trouble sometimes they're there right
on the cusp they're not supposed to have
their current ratio go down and they're
about to publish their balance sheet
well what do they do they issue
long-term debt to pay off short-term
debt so really what they're doing is to
go out and get let's say they are 10,000
in current liabilities that go out and
take a long-term loan
and pay off so really they're
transferring long-term debt are
short-term dad and put into the
long-term category so what happens here
for current assets don't change it all
even though the cash comes in from the
loan and meeting goes off to pay the
current liability so it goes from 42 4i
but if you pay off that CL current
liability
let's say one bucket goes from 21 it
dramatically increases so sometimes
people will do this trick right before
the balance sheet is supposed to be
created in order to raise up their
current ratio
perhaps they were in in you know about
to violate some contract or something
like that
alright that's a little bit about
current ratio in our next video we'll
come back and do turnover ratios a lot
of really cool to kind of create
creative ratios they get interesting
information from financial statements
alright see you next video