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..