Welcome to Excel and Finance Video Number 18.
Hey, if you want to download this workbook or the PDF file,
just click on the link below the video
and you can download the files for chapter 3.
Now in this video, we're going to talk about--
I have to go back over here-- we're
going talk about asset utilization
ratios, how efficiently are we using assets.
Now we're going to look at total asset turnover, capital
intensity, which are kind of related,
and then we're going to look at asset utilization ratios that
will give us the number of days in the cash cycle.
All right, let's start with asset turnover.
Now this is just a fancy name.
As we've talked about a lot in this video,
if you know what's in the numerator and the denominator,
the amounts and the units, then it's
easy to figure out what it means,
and this one is sales divided by total assets.
So S divided by A, sales divided by assets.
Well, think about this--
if you-- we always keep the 1 in the denominator, right?
But if you one year are generating
$3 of sales for every $1 you bought
in assets for your business, and the year
before you were generating $2 for every $1 of asset,
which is better?
Pretty much without exception the higher the better.
It means you're using your assets efficiently.
If you go from $2 to $3, the same $1 of asset
isn't now generating $3.
So that is better.
Two things to keep in mind when we look at this ratio here.
If a corporation has newer assets that have not
been depreciated, then there's no-- there's
not much depreciation in this asset.
If you have an older company or a company
with lots of older assets that have been fully
depreciated, remember, depreciated
means the book value goes down.
There's lots of depreciation.
So a new company, just imagine we had
$10 of sales for $5 of assets.
10 divided by 5 is 2.
But what if these were all brand new assets, right?
And you look at the same--
a different company but has similar assets
but they're all old?
They've all been depreciated.
So instead of the new company 10 divided by 5 asset,
it could be 10 divided by 2 asset,
and that would give us a much bigger number, that
would give us 5.
So you've got to also look at what sort of assets,
how old they are, how much depreciation, but in general,
you get the idea.
A company that has a higher sales-to-assets or asset
turnover ratio is going to be better.
Now if we turn this upside-down, take the reciprocal of it,
and divide assets by sales--
oh, assets divided by sales.
That's capital intensity ratio.
And now, the 1 is in the numerator.
So for every $1 of sales, how many assets did we need?
Right?
OK.
Let's go ahead and go calculate this one right here
for our Whole Foods example, and then we'll
move on to the next asset utilization ratios.
I'm going to go over to Excel.
All right.
Now in our last couple of videos,
we had our financial statements over here for Whole Foods--
we had balance sheet and income statement.
I've already taken the numbers from them and put them here.
So we have our sales, total assets, cost of goods sold,
and a few other numbers.
So let's just look at our asset turnover.
For 2006, we said all of the sales
divided by all of the assets.
So, there we go--
$2.74 sales for every $1 of asset.
Let's go ahead-- actually, we can just copy this over.
Brroop.
All right, so it went up $2 the year
before for every $1 of asset-- remember, Whole Foods, you
have some buildings, some equipment inside of the store,
some cash registers, some leases probably
that are capitalized on recorded assets,
but there it is-- $2.49.
If you go up and you're generating
more dollars of sales from the same $1 of asset,
it tends to be much better.
Also, this is a great number you can use to compare--
that you could go compare to other food sellers, right?
And even somebody closer--
we looked at Safeway before, but they
sell lots of other things besides organic food,
they're not a direct competitor.
In the Seattle area, Whole Foods is here,
but there's another local called--
co-operative called PCC, right?
And they're a little teeny small company.
Ah, but when you do the ratio, you
could look at PCC and Whole Foods
and see which one is generating more sales per asset,
$1 of asset.
All right, let's go back over to our PDFs.
Let's look at this, because we're
going to do something clever with our next ratios.
And I drew a little picture here,
this is actually from our textbook.
Let's just think-- we want to be able to look
at the financial statements.
So these over here.
So we have-- and my formatting is not
working here for some reason.
We want to be able to look at the balance
sheet and the income statement and figure out
how many days it takes them to sell their inventory,
how many days it takes them to collect accounts receivable,
and how many days it takes them to collect accounts payable.
Normally you think, well how are you
going to get that information?
I don't see any numbers here that show me number of days.
Well, if we do a little creative ratio figuring,
we can figure it out.
Now let's just look at the diagram first.
Oh, we buy some inventory, and not just for Whole Foods,
but for any--
Target or whoever is buying some inventory.
They-- the inventory period is--
if we sold-- how long does it take
us to sell all of our inventory and then go out and buy
a bunch of new inventory?
Now it never really happens exactly like this,
you don't sell all your inventory,
but we can get an estimate.
So we're just going to--
for our estimate, you'll see that we're
going to get 73 days.
So we buy a bunch of inventory, sell it down to 0,
and then immediately buy a bunch of new inventory.
Now again, that's not the way it really works,
but this is an estimate.
So 73 days it takes us to sell our-- all our inventory.
Ah, but then there's an accounts receivable period also.
That means once we sell to a customer,
how long does it take us until we collect?
So 36 days.
So we get our inventory, and we don't get all the cash
till 73 plus 36 days.
That's called the operating cycle.
Now there's also accounts payable.
Accounts payable is when we buy the inventory,
we don't immediately given the cash, right?
Maybe to here.
So 52 days, let's say.
In our calculation, we'll see that it takes 52 days
to actually pay all of our bills for our inventory we purchased.
The difference between here and here is called the cash cycle,
right?
Here's where our cash went out.
Even though we got the inventory here,
cash didn't go out till here, we didn't collect till here.
Now let's look at our example in the PDFs.
Here are-- let me blow this up a little bit.
The first one is called inventory turnover.
For each one of the number of days--
remember, we have three separate day periods-- the amount
of time it takes to sell all our inventory,
collect our accounts receivable, and pay for our accounts
payable.
So for selling our inventory, we're
going to do a calculation called inventory turnover,
and then went to figure out day sales in inventory, which just
means days to sell inventory.
Here is the ratio--
cost of goods sold divided by inventory.
Huh?
What?
Cost of goods sold?
What is cost of goods sold?
That comes from the income statement.
Oh, that's the total cost of all of the inventory
we sold for the year.
Divided by the inventory?
Now this is an income statement number,
this is a balance sheet number.
Now there's lots of different ways to do ratios,
and we've mentioned that at the beginning of this chapter.
In finance or when you're looking forward,
you usually take the ending number from the balance sheet.
In accounting or when you're looking backwards in time
like auditors like to look backwards and see what
happened, you usually take end minus begin and end plus begin,
which means you add end and began
and divide by 2 to get an average.
In this class, we're not going to do that.
We're just going to take the end.
I think on this next sheet here I have a little note here
about that.
End, and then here's how you would
do an average, which in accounting oftentimes
you'd see this.
But the point is, if you're looking
into the future, which finance people are,
you take the end number.
If you're looking backwards into the past,
you take end plus began divided by 2.
OK, so cost of goods sold, and then we inventory.
So what does this mean?
This is everything we sold--
see, I even put a note here.
This is all the inventory we sold during the year.
Here's the inventory on the shelves at the last day.
Now, this fluctuates a lot, right?
So again, this is just a way to estimate.
But when we do this division, it's going to give us a number.
Let's just see.
We take 5,000 cost of goods sold divided
by $1,000 of inventory left.
When we do this division, that means
$5 a cost of goods sold for every $1 of inventory.
It means it took us five times that we had to have inventory--
here's a better picture.
It means that we had all our inventory
and we sold it down to 0 and then we immediately restocked.
And then we came down to 0, and we immediately restocked.
Again, this is not the way it really works.
The way-- it's not the way inventory
flows, but this is a great measure
to get an estimate of how many times
you go through your inventory, all right?
So five times.
It means if we sold all of the inventory down to 0,
we do it five times in one year.
Well, think about that.
What does that mean?
Now if we want the days to sell inventory,
we just take 365 divided by 5, and we get 73 days.
So that's the first calculation.
That's how long it takes us to sell all of our inventory.
Now, this is called receivables turnover,
and it's going to tell us how long it takes
to collect accounts receivable.
Net sales divided by accounts receivable.
Now again, this is a income statement number
and this is the end balance sheet number.
So sales divided by AR.
Same kind of idea.
It's as if we took just the ending accounts receivable,
did this division, and it tells us that 10 times
during the year we filled up our AR and then collected it.
Filled it up, collected it.
So in essence, we kind of went through the AR
10 times during the year.
Well, if it's 10 times during the year, to figure out days,
we take 365 divided by 10.
So on average, it took us 36.5 days
to collect all of our accounts receivables.
Using that same logic, we can do payable turnover.
Remember, this is when we buy the inventory,
we don't pay it for a number of days.
So we take cost of goods sold, just
like we did with inventory, but now the denominator is accounts
payable.
Cost of goods sold divided by accounts payable,
we'll say it's $5,600 divided by $800.
So we get seven times during the year we have filled up
accounts payable, we pay it off.
We'll fill it up again, pay it off.
Fill it up, pay it, and pay it off.
Again, we take 365, divide it by 7, it takes 52 days.
This is just a way for people outside the firm
to look at financial statements and estimate.
Inside the firm, you have--
you perhaps have much better data to calculate numbers,
but again, looking at financial statements, which is oftentimes
the best information we have, we can make these estimates.
So here's the deal--
the operating cycle it's going to be days to sell inventory
plus days to collect AR.
73 plus 36.5, that's 109 and 1/2 days.
But we have to from the operating cycle--
right here-- to calculate cash cycle,
we then subtract, because we avoid it.
We didn't have to pay cash out right
at the beginning when we got our new inventory on the books.
So we subtract our 52 and we get 57.5.
So about 57 or 58 days is our cash cycle.
Now if we go back to this drawing,
there it is-- the full operating period
from when we get our inventory to when we finally
collect from the customers.
And then we subtract this little bit
because we didn't have the cash coming right out, right?
When we got the inventory.
Cash out, finally cash in.
All right, let's go see if we can calculate this over
in Excel.
OK, so our inventory turnover, cost of goods
sold divided by our inventory.
17-- so virtually identical for each year.
All right, to get this to days, we simply say equals 365
divided by the number of times-- that 17
means the number of times we sold our inventory
and restocked in.
For a store selling food, it better be pretty high, right?
It's not like a jeweler whose inventory turnover is probably
really slow.
OK, so these numbers are going to be exactly the same, too.
Not much change.
Now let's do, again, how many days
it takes us to sell all of our inventory.
Now AR, how long does it take us to collect?
Now first we gotta do the number of times
we actually collect all of our AR during a single year.
We're going to take sales divided by accounts receivable.
So 68 times, 70 times.
Probably extremely high.
Most of the customers coming in here
are not using any kind of AR.
They're paying cash or credit cards or whatever.
Days equals 365 divided by this.
All right?
So what few customers they have doing any kind of AR--
and probably this number is just not
very important for an entity like Whole Foods or Safeway,
whereas for Home Depot, it's probably really significant,
because Home Depot sells a lot of things on account
especially to contractors.
Now AP, accounts payable turnover--
how many times we pay off our bills completely in a year
equals our cost of goods sold divided by our AP,
accounts payable.
Ah.
So we pay off our bills pretty quickly, too.
Again, look at that.
These are all pretty identical numbers, none of these
are changing much from year to year.
Equals 365 divided by this will tell us--
365 equals 365, divided by this, and this
will tell us how many days.
So pay off bills quite quickly, right?
Within 12 days on average.
All right, again, these are all estimates,
but now we get to figure out operating day, operating
cycle in days.
This is days holding inventory plus days until collect AR.
This is just like we saw in that drawing,
so I'm going to say equals-- whoop--
plus that one there.
Well.
Well, I mean, in a Whole Foods, selling food
is much different than Boeing.
The operating cycle that's--
at a store where it sells food, much shorter
than an operating cycle for a manufacturer
that builds airplanes.
All right.
Now let's do the--
take this and we will subtract the number
of days it takes us to pay our bills on average,
and we get our cash cycle in days, 13.
Not very much.
Fully expected in an industry like Whole Foods or Safeway.
All right, that's a little bit about asset utilization.
We'll see you next video.