Excel Finance Class 17: Leverage & Solvency Ratios: Debt To Equity, Equity Multiplier, more...

Download Excel workbook http://people.highline.edu/mgirvin/Ex...
Learn how to calculate the Debt Ratio, Debt To Equity Ratio, Equity Multiplier Ratio and Times Interest Earned Ratio.
Highline Community College Busn 233 Slaying Excel Dragons Financial Management with Excel taught by Michael Girvin.

---

Closed Caption:

welcome to exhale and finance video
number 17
if you want to download this workbook or
the PDF file that we're going to use
just click on the link below the video
and you can download the workbook
business at all chapter 3 stuff
hey we're still in chapter three were
doing ratio analysis we get to talk
about leverage or solvency ratios very
important that will help us figure out
whether someone might have too much debt
leverage just means how much debt do we
have solvency same thing but slightly
different point of view do you have
enough money to cover your expenses and
debt in solvent means that you cannot
pay off all of your accounts that you
owe and you're probably headed for
bankruptcy the variables were going to
use for all these calculations are
assets liability and owner's equity and
we're going to tend to use a for acid d4
debt and ce4 equity now the first ratio
total debt ratio is just all the debt /
all the assets total liabilities totally
total assets
we're just going to use d over a now
what does this do we talk about Rachel's
before whenever you do division you keep
a 1 here 1 dollar of asset and then
whenever you get up here let's just say
you get fifty cents fifty cents of debt
for every one asset if it's 50 cents the
other side is equity right so 50 Cent's
equity fifty cents debt so it means the
meaning of this
d divided by a is the amount of debt for
every one dollar of assets generally
above fifty cents is not so good when
you get fifty cents exactly it means
it's if all of the assets are funded
with half debt and half equity now again
it always depends you gotta look at the
situation
sometimes above fifty cents is just fine
sometimes below is not fine now let's
just think of an example here during the
financial crisis some people had forty
dollars of debt for every one dollar of
equity so this would be 40/40 one which
is almost one so the closer you get to
12 more debt you have the more trouble
you are in Bear Stearns for example had
this ratio would have been 40 billion
our trainer or 40 billion / 40
1,000,000,000 pretty close to the number
one so probably not very good
debt-to-equity ratio slightly different
point of view now we're comparing the
two funding sources right we get we buy
our assets with your debt or equity so
we get the cast and the one of these so
now we can compare these again the same
thing if you keep the one here one
dollar of equity this means for every
one dollar equity how much debt do we
have generally above one is not good
we'll wait a second if it's 5050 right
50/50 that means have all the assets are
half finance bye dad have financed by
equity so that would be as soon as we
get more debt and equity probably not so
good now this is the one where you know
when we did this division and for bear
stearns during the financial crisis 2007
to 10 we got the number 40 so when I say
generally above one is not too good
right for most businesses it's not but
when you get up to 40 that's just crazy
right now
banks are usually have quite a high
number here because they're in the
business of taking deposits in and you
know most of what they have our
liability so there's some businesses
like banks where you're going to have a
much bigger number but 40 probably not
too good by the federal regulation in
the 2004 get exactly when used to be a
much lower number and it was moved up to
allow banks to get 30 and 40 times
as much dead as equity only in the two
thousands but after the financial crisis
that people reduced that amount
another important ratio remember where
these are all leverage and solvency and
were either using assets equity or debt
so this one is assets / equity now think
about this assets are all everything we
have right and we either bought it with
debt or equity so when we have equity on
the bottom this number if the firm has
zero debt then it's one if it has any
dead at all it's always going to be more
than one now equity multiply the reason
why it's called multipliers because
think about this and we'll see lots of
examples in just a moment if you invest
one dollar but you also the company also
has one dollar of debt that would be 2/1
right so it's as if the person who's
investing can invest one dollar of
equity but they get to buy two assets so
there's a an advantage there in the
assets of the extra assets from having
debt are helping to return earn a return
and also there's the tax deductible
advantage we talked about so this ratio
means for every one dollar of equity how
many assets did we have now we're going
to do this over an excel but let's just
think about the relationships between
these ratios if you know debt to asset
this is the debt ratio this one right
here let's just say we know it's point2
well point2 we can kind of do in our
head that's 2/10 so then if you know
this you can deduce this right
d a 2/10 well now if you know this you
also know equity right because all the
assets are brought up or bought with
either funds from debt or equity so if
this is too that means equity has to be
eight and then from that you can also
conclude do the debt-to-equity you got
the two in the eighth you know its point
25 and finally just from this original
you know des amal here we can also
calculate this at soon as we know equity
we can do asset to equity alright
that's not the right answer right there
this is cut off this PDS PDF is cut off
you apps it's not 1.2 it's 1.25 i redid
it down here see assets spotted by
equity it's 1.25 so there's 1.2 and then
there's a five right there 10/8 now we
do that let's look at it this with the
numbers right so if you know this you
can calculate the rest
anytime you know the a and Annie or the
A&E you can figure out all the related
ratios here doing a little math let's
look at something else we want to notice
something equity multiplier so if you
have more if you have any debt at all
this is always going to be greater than
1 because 1 dollar equity plus some
extra debt will give us more assets
always greater than one but this let's
just think about this what's assets
equity plus debt so we could write it
that way well we can break this apart
since this is addition we can rewrite it
as this with Annie and both of the
denominators here
well anything / anything is what 1 so 1
plus debt-to-equity equals the equity
multiplier so oftentimes if we have this
number we can just immediately figure
out what this is that's if we know that
debt to equity is point two five we
don't even need to go to the balance
sheet and do this calculation we just go
1 plus and later in finance you will see
a lot of there are many calculations
that do exactly that you have debt to
equity and then you add 1 to it to get
the equity multiplier finally there's
two other leverage or solvency ratios
Abbott / interest
this is just how many times over you you
can pay interest right because this is
our earnings when you put this in the
denominator and then for every one
dollar of interest how much earnings did
we have if you have a lot of
depreciation in your business then you
definitely want to add depreciation so
Abbott plus depreciation / interest
alright let's go over to excel and
going to click on the sheet a hopeful to
marketing international leverage our for
ratio these are actually uh financial
statements got from the SEC and then
condense them for our purposes here now
we're into a bunch of calculations and I
don't want to keep going back and forth
so I'm going to get all of our numbers
up here and then blow up the screen so
it's easy to see notice we really only
need you know that assets equity but we
also need these two items from the
income statement we need two thousand
six and five so 4 2016 total liabilities
or total debt come down here there's
there it is right there now notice
that's a relative cell reference right
so i can copy this over and the reason
why is because that just moves over
there and points there
same thing here told assets i'm going to
go right there and then copy it over you
can see when I put this in an emotive
move the blue box was there and when I
copied this over it moved to their
equity come down to get total
stockholder equity looks like be 39 and
then copied over now we need to get a
couple numbers for 2006 Abbott equals
and then earnings before and then
interest
Wow not very Wow look at this
lots of interest boom must of gotten rid
of some debt
alright so let's go ahead and calculate
our Rachel's 2000-2005 and then we'll
look at how they change member ratios in
isolation usually don't tell you much
or put another way they tell you much
more if you look at the ratios as they
change over time or as you compare them
to other companies or industries so we
want debt ratio so we're just going to
take 4 2016 total debt / our total
assets this means for every one dollar
of asset we have point 3127 31.2 seven
percent if we formatted that so if we
went like this
and then increase the decimal right I'm
going to get rid of that formatting
general it also means 30 1.27 sense of
debt for every asset so every single
asset has 31 sense of debt and the rest
is equity right
sixty-nine cents
alright let's go ahead and do this
calculation oh wait a second not notice
with this formula right here is is
saying this many up in that many over
blue box / green box so when I copy this
over the blue and green box will move to
hear right so there we go so 2005 we had
2720 almost twenty-eight cents of debt
for every acid and now in 2006 we have
31 cents so the the debt one up a little
bit as a percentage of total assets
now we want debt-to-equity and this is a
very common we're going to take for 2006
debt / equity so for every one dollar of
equity there is 45.5 sense of debt now
i'm going to copy this over so we went
from 38 sense of debt for every one
dollar of equity 245 now the equity
multiplier we can do this two ways let
me do it the long way here equals total
a total assets oh right there / our
equity again this tells us for every one
dollar equity how many assets to where
we able to buy every time we invested
one dollar we were able to buy one
dollar and forty-five cents of assets
now that'll work and we can copy that
over so we went from 1.38 to 1.45 I was
going to make this bigger i'll have to
edit this and make that that little bit
bigger so old WG that was just a quick
way of going to zoom to selection if you
highlight something goes zoom to
selected on the view to tab it will let
sumit now it once we know debt-to-equity
we simply could just add one so i'm
going to say equals one plus
debt-to-equity
and why are these different this is a
formatting i'm showing fewer decimals if
i were to highlight all of this right
here and apply the general you can see
them the decimals are exactly the same
but however you want to do it you know
we don't need to see all those decimals
how about we go like this
there we go that's a little bit better
maybe one more you can see those
decimals are still there were just not
seen a loss at 1.45 so 1.38 to 1.45 so
little bit more levers when you take on
more debt you certainly are able to buy
more assets again the meaning of this is
one dollar of invested equity and we got
one $45 one-dollar forty-five cents
worth of assets now let's again these
are help us look how much leverage there
is how solvent they are well this is
even more specific that says how many
times over can we pay interest so let's
try and do this one
whatever the evidence in this were just
doing 2006 / our interest
wow so we can paint many times over you
can see for whatever reason we had very
little debt so during this period we
probably had some transition of if we go
look at our debt so liabilities i went
from five to six upset long-term debt so
it definitely went up and this is total
debt so it's it's all of this right here
accounts payable other current
liabilities definitely went up but for
whatever reason we did not pay a lot of
interest maybe a bunch of interest we
paid off a bunch of data are paid the
interest in the period before something
like that but that's quite huge let's go
ahead we don't have the other oh yes we
do have the other numbers and forgot to
put them here we can just drag them over
there we go so this is probably a more
normal 1i can drag this over to right so
now this is taking a bit / interest and
so now we can pay a hundred and seven
times over so this is an anomaly there's
something odd going on here in our
timing of pain interest now cash
coverage we're going to have to do and
earnings before depreciation interest in
tax so we're missing one number here i'm
just going to do it this way equals and
then in parentheses Abbott minus 1 i'm
going to go find the depreciation over
here
amortization is another word for
depreciation so I have that oops I'm
sorry
add back in member non-cash so we have
to add it back in and we're going to
divide it by our interest
ok so the book that all the boxes are in
the right place a little green blue and
purple ones lavender ones so there we
have it
this is probably more realistic and
probably want to go to a few years back
and see but there's just an anomaly in
this period right here but again they
have the ability to pay off their
interest many many times over so that
the meaning of this creditors looking at
this are going man they're they're in
good shape and this is under point 5
which again is generally a good sign so
it looks like in terms of their solvency
and leverage ratio they're doing great
now to just see the relationship of
these different ratios again we saw this
in the PDF if you know that the debt to
asset is point2 and we could it's point2
weekend from that conclude a number of
things equals 2/10 right so it does
require that you know how we got a point
1 this is just roughing it right we know
that too
well / 10 gives point2 but it gives us
not the exact numbers but the
relationship between them right so now
if we know that
let's just fill this out actually these
cells shouldn't be green because we're
going to have no Phil I put no Phil
there
well if debt is too and total assets is
10 and actually we can put some green
right there because that's it means a
formula
you know that you can just go assets
minus debt equals our equity now if you
know this we knew the debt-to-equity we
have all of our number so we can simply
say debt-to-equity equals R
debt-to-equity our leverage ratio by our
equity multiplier we simply go 1 plus so
just in analysis a lot of times you just
do not have complete information so
seeing the relationship between the
numbers and understanding how to take
one single decimal like this and then
you know guesstimate they're not the
exact numbers but they can't give us a
relationship and then from that we can
deduce the rest of these ratios
alright we'll see you next video