Welcome to Excel and
Finance Video Number 17. Hey, 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– all chapter 3 stuff. Hey, we’re still in chapter 3,
we’re doing a 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? Insolvent means that you cannot
pay off all of your accounts that you owe and you’re
probably headed for bankruptcy. The variables we’re going to
use for all these calculations are assets, liability,
and owner’s equity. And we’re going to tend to
use A for asset, D for debt, and E for equity. Now the first ratio,
total debt ratio, is just all the debt
divided by all the assets– total liabilities, total assets. We’re just going
to use D over A. Now what does this do? We talked about ratios before. Whenever you do division,
you keep a 1 here. $1 of asset. And then whenever
you get up here, let’s just say you get $0.50. $0.50 of debt for
every one asset. If it’s $0.50, the other
side is equity, right? So $0.50 equity, $0.50 debt. So it means– the meaning
of this, D divided by A is the amount of debt
for every $1 of assets. Generally above
$0.50 is not so good. When you get $0.50 exactly,
it means all of the assets are funded with half-debt
and half-equity. Now again, it always
depends, you’ve got to look at the situation. Sometimes above
$0.50 is just fine, sometimes below is not fine. Now let’s just think
of an example here. During the financial crisis,
some people had $40 of debt for every $1 of
equity– so this would be 40 divided by 41,
which is almost 1. So the closer you get to
1, the more debt you have, the more trouble you are in. Bear Stearns, for example, had– this ratio would have
been $40 billion or– $40 billion divided
by $41 billion. Pretty close to the number
1, 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 either debt or equity, so we get the cash from
either one of these. So now we can compare these. Again, the same thing–
if you keep the one here, $1 of equity, this means
for every $1 of equity, how much debt do we have? Generally above 1 is not good. Well wait a second– if it’s 50-50, right? 50 divided by 50, that means
all the assets are half-financed by debt, half-financed
by equity. So that would be as soon as
we get more debt than equity, probably not so good. Now this is the one where when
we did this division for Bear Stearns during the financial
crisis, 2007 to ’10, we got the number 40. So when I say generally above
1 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 usually have
quite a high number here, because they’re in the
business of taking deposits in, and most of what they
have are 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 way, federal
regulation in the 2000s– I forget 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 debt as equity only in the 2000s. But after the financial crisis,
people reduced that amount. Another important ratio. Remember, these are all
leveraged insolvency, and we’re either using
assets equity or debt. So this one is assets
divided by 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 0
debt, then it’s 1. If it has any debt at all, it’s
always going to be more than 1. Now equity multiplier. The reason why it’s
called multiplier is because think about this– and we’ll see lots of
examples in just a moment. If you invest $1 but you
also– the company also has $1 of debt, then it would
be 2 divided by 1, right? So it’s as if the
person who’s investing can invest $1 of equity, but
they get to buy two assets. So there’s an advantage there. The assets– the extra
assets from having debt are helping to earn a
return, and also there’s the tax-deductible advantage
we’ve talked about. So this ratio means
for every $1 of equity, how many assets did we have? Now we’re going to do
this over in Excel, but let’s just think
about the relationships between these ratios. If you’ve know debt-to-asset– this is the debt
ratio, this one right here, let’s just say
we know it’s 0.2. Well 0.2 we can kind of
do in our head– that’s 2 divided by 10. So then if you know this,
you can deduce this, right? DA2 divided by 10. Well, now if you know this,
you also know equity, right? Because all the
assets are bought up– or bought with either
funds from debt or equity. So if this is 2, that
means equity has to be 8. And then from that,
you can also conclude– do the debt-to-equity. You’ve got the 2 and the
8, you know it’s 0.25. And finally, just from
this original decimal here, we can also calculate this. As soon as we know equity,
we can do asset to equity. Ah, that’s not the right
answer right there. This is cut off,
this PDF is cut off. You– it’s not 1.2, it’s 1.25. I re-did it down here, so
the assets divided by equity, it’s 1.25. So this is 1.2, and then
there’s a 5 right there– 10 divided by 8. Now we do that, but let’s
look at this with the numbers, right? So if you know this, you
can calculate the rest. Anytime you know the A
and an E or the A and a D, 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 equity
plus some extra debt will give us more assets,
so always greater than 1. 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 an E in both
of the denominators here. Well anything divide
by 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. Thus, if we know that
debt-to-equity is 0.25, 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. EBIT divided by interest. This is just how many times over
you can pay interest, right? Because this is our
earnings where you put this in the denominator, and then
for every $1 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 EBIT plus depreciation
divided by interest. All right, let’s
go over to Excel, and we’re going to click on
the sheet Whole Foods Market International
Leverage, R for ratio. These are actually financial
statements I got from the SEC, and then condensed them
for our purposes here. Now we’re going to do 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 debt, assets, equity, but we also need these two
items from the income statement. We need 2006 and ’05– so for
2006 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, total assets,
I’m going to go right there and then copy it over. You can see when I put
this in Edit mode and move, the blue box was there, and
when I copied this over, it moved to their. Equity. Coming down to get total
stockholder equity, it looks like B39. And then I’ll copy it over. Now we need to get a
couple numbers for 2006. EBIT equals, and
then earnings before. And then interest. Wow, not very–
wow, look at this. Lots of interest– boom, must
have gotten rid of some debt. All right, so let’s go in and
calculate our ratios, 2005, and then we’ll look
at how they changed. Remember, 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
for 2006 our total debt divided by our total assets. This means for every $1
of asset, we have 0.3127. So 31.27% if we
formatted that– so if we went like this and then
increased the decimal, right? I’m going to get rid
of that formatting– General. It also means 31.27 cents
of debt for every asset. So every single asset
has $0.31 of debt and the rest is equity, right? $0.69. All right, let’s go ahead
and do this calculation. Oh wait a second, notice,
this formula right here is saying this many
up and that many over. Blue box divided by green
box, so when I copy this over, the blue and green
box will move to here. All right? So there we go. So 2005, we had 27– almost $0.28 of debt for
every asset, and now in 2006, we have $0.31. So the debt went up a
little bit as a percentage of total assets. Now we want debt-to-equity,
and this is a very common one. We’re going to take for
2006 debt divided by equity. So for every $1 of equity,
there is 45.5 cents of debt. Now I’m going to copy this over. So we went from $0.38 of
debt for every $1 of equity to $0.45. Now the equity multiplier,
we can do this two ways. Let me do it the long way here– equals total assets–
so right there. Divided by our equity. Again, this tells us
for every $1 of equity, how many assets
were we able to buy? Every time we
invested $1, we were able to buy $1.45 of assets. Now that’ll work and
we can copy that over. So we went from 1.38 to 1.45. Oh, I was going to
make this bigger. I’ll have to edit this and
make that a little bit bigger. So Alt-W, G. That
was just a quick way of going to zoom to selection. If you highlight
something and go Zoom to selection on the View
tab, it will zoom in. Now once we know debt-to-equity,
we simply can just add 1, so I’m going to say equals
1 plus debt-to-equity. And why are these different? This is formatting, I’m
showing fewer decimals. If I were to highlight
all of this right here and apply the General,
you can see then the decimals are exactly the same. But however you
want to do it, we don’t need to see
all those decimals. How about we go like this? There we go, that’s
a little bit better. Or maybe even one more. But you can see, those
decimals are still there, we’re just not seeing them all. So 1.45– so 1.38 to 1.45, so
a little bit more leverage. When you take on more
debt, you certainly are able to buy more assets. Again, the meaning of this is– a $1 of invested equity, and
we got $1.45 worth of assets. Now let’s– again, these are– help us look how much leverage
there is, how solvent they are. This is even more specific, this
says how many times over can we pay interest? So let’s try and do this one. Whatever the EBIT
is, and this, we’re just doing 2006 divided
by our interest. Wow. So we can pay it
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 5 to 6. Long-term debt– so
it definitely went up. And this is total
debt, so 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 debt or paid the interest
in the period before or 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. I forgot to put them here. We can just drag them over. There we go. So this is probably
a more normal one. I can drag this over, too. All right, so now
this has taking EBIT divided by interest, and so
now it can pay 107 times over. So this is an anomaly,
there’s something odd going on here in our
timing of paying interest. Now cash coverage
we’re going to have to do an 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, EBIT minus– and I’m going to go find
the depreciation over here. Amortization is another word for
depreciation, so I have that. Whoops, I’m sorry–
add back in, remember? Non-cash, so we have
to add it back in. And we’re going to divide
it by our interest. OK, so all the boxes are in the
right place, the 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 the meaning of this,
creditor’s looking at this, they’re going, man,
they’re in good shape. And this is under 0.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 0.2– and we could– it’s 0.2. We can from that conclude
a number of things. Equals 2 divided by 10, right? So it does require that– how we got a 0.1. This is just roughing it, right? We know that 2 divided
by 10 gives us 0.2, 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 fill– I put no fill there. Well, if debt is 2 and total
assets is 10, then actually, we can put some green right
there because that’s– that means a formula. If you know that, you can
just go assets minus debt equals our equity. Now if you know this, we
knew that debt-to-equity, we have all of our numbers, so we
can simply say debt-to-equity equals our debt-to-equity. Our leverage ratio– sorry,
our equity multiplier, we simply can 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 guesstimate– they’re not the exact
numbers, but they give us the relationship, and then
from that we can deduce the rest of these ratios. All right, we’ll
see you next video.