Excel Finance Class 18: Asset Efficiency Ratio and Cash Cycle In Days

Download Excel workbook http://people.highline.edu/mgirvin/Ex...
Learn how to calculate the Ratios: Asset Turnover, Capital Intensity, Inventory Turnover, Days To Sell Inventory, Receivable Turnover, Days To Collect Accounts Receivable, Payable Turnover, Days To Pay Accounts Payable,
Highline Community College Busn 233 Slaying Excel Dragons Financial Management with Excel taught by Michael Girvin.
Finance Excel 2007 2010 . Mike Gel Girvin excelisfun Highline Community College Busn 233 Slaying Excel Dragons

---

Closed Caption:

one on xelon finance video number 18a if
you wanna
download this workbook or the PDF
file just click on the link below the
video
and you can download chest files for
chapter 3
now are in this video were gonna talk
about
after back over here we'll talk about
asset utilization
ratios how affect official we are using
assets
now we're looking at total asset
turnover
capital intensity which are kinda
related and then we're going to look at
I after all utilization
ratios that'll give us the number of
days in the cash cycle
alright what start with arm
asset turnover now there's 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 easy to figure out
what it means and this one
is sales divided by Total
asset so ass divided by a sales about to
buy assets
well think about this if you but we
always keep the one
in the denominator right but if what you
one-year are generating story dollars
have sales
for every one dollar you bought in
assets for your business
and the year before you were generating
two dollars
for every one dollar 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 to to three dollars
X the same one dollar I love
asset is now generating three dollars
so that is better welcome to fix
to keep in mind when we look at this
ratio here
if corporation as newer assets
that have not been depreciated then
there's no there's not much to preciate
in in this asset if you have
are an older company were or a company
with what some alder assets that have
been fully depreciated
member depreciated means the book value
goes down
there's lotsa depreciation so a new
company
just imagine we had I'll
ten dollars %uh sales
for a five dollars have assets
10 divided by five is too but what if
these were all brand new assets right
and you look at the same cut
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 five asset it could be 10
divided by
to asset now give us a much bigger
number that give us five so
you got also look at what sort of assets
how old they are how much depreciation
but in general you can you get the idea
a company that has a higher sales to
assets or
asset turnover ratio or is gonna be
better now
if we turn the sub side down take the
reciprocal other and divide assets by
sales
0 assets divided by sales ascap capital
intensity ratio and now don't one is in
the numerator sold for over everyone
dollars upset sales
how many assets did we need right
okay I am let's go ahead go
calculate this one right here for or
Whole Foods example then we'll move on
to the next
are asset utilization racers I'm gonna
go over to excel
right now in our last couple videos we
had our financial statements some
over here for Whole Foods we had balance
sheet an
income statement I've already are taken
the numbers from them and put them here
so we have our sales
total assets casa good soul and a few
other numbers
so let's just look at our asset turnover
for 2006
we said all the sales divided by all of
the assets
so there we go two dollars and seventy
four cents
sales for every one dollar vasser let's
go ahead
actually in this copy this over
alright so when up two dollars the year
before for every one dollar vasat member
Whole Foods it you have
and some bill being some equipment
inside the store some cash register some
lease is probably that are capitalize
recorded asset
but there it is 2004-09 if you go up
Energen a rating
more dollars have sales from thus the
same one dollar vasat
I tends to be much better also
this is a great number you can use to
compare
I make you go compare to other
arm food sellers right in even
somebody closer you know we looked at
Safeway before but they sell lots of
other things besides
organic true they're not a direct
competitor in the Seattle area
you know Whole Foods is here but there's
another local called IOC
cooperative called PCC by and their
little teeny small-company
I but when you do the ratio you could
look at PCC
and Whole Foods and see which one is
generating more sales process
per asset one dollar vasser alright
let's go back over to our PDF's
let's look at this kids are where do
some clever with our next ratios
and I drew a picture here this is
actually from RI textbook
let's just think I'm what we want I'll
be able to look at the financial
statements
I so these over here
so we have turned my formatting is not
working here for some reason
will be able to look at the balance
sheet and the income statement figure
out
how many days it takes them to sell
their inventory
how many days it takes some dick hole
lacked
accounts receivable on how many days it
takes them to collect
accounts payable
you don't normally think or higher to
get that information I don't see any
numbers here
that show me number of days well
out we do a little up creative ratio
I figure in we can figure it out now
let's just look at that diagram first
be oh we buy some inventory
and not just for Whole Foods but for any
you know our target or whoever's by in
some inventory
they arm the invert or period as
if we saw how long does it take us to
sell all over inventory
and then go on by Bucher new inventory
now it never really happens exactly like
this you don't sell all your mentor
but we can get an estimate so we're just
gonna for estimate you'll see that we're
gonna hit 73 day so we buy much
inventory
sell it down to zero and then
immediately by a bunch in new inventory
now again that's not the way it really
works but this is an estate so 73 days
retakes s to sell our all our inventory
%ah
but then there's an Accounts Receivable
Perian
also I'll that means it once we sell
their customer how long does it take us
into we collect
soap 30 six days so we get our inventory
with her and we don't get all the cash
toll
in of 73 plus thirty six days that's
called operating psych
cycle now there's also accounts
payable Accounts Payable is when we buy
the emitter we don't immediately given
the cash right
rip maybe to hear so 52 days let's say
and our calculation I war see the pics
52 days
to call to actually pay all her bills
for our inventory we purchased
the difference between here and here is
called the cash cycle right
here's where our cash were not even
though we got the inventory here with
casting go out to hear
we didn't collect till here
now let's look at our example in the
PDF's
here are me blow this up a little bit
the first one is called inventory
turnover
for each one of the number of days
member ways we have three separate day
period the amount of
time it's takes to sell all or inventory
collect our
accounts receivable and pay for Accounts
Payable
I so for so in diameter when do
calculation called inventory turnover
and their way to figure out days sales
and inventory which is
just means days to sell inventory here's
the ratio
cost a good soul to buy to buy inventory
her
why cost a bit old but is Costco told
that comes from the income statement
oh that's the total cost I've all 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 lotsa different ways to do
ratios and we
we've I mention that the beginning up to
this chapter in finance or when you're
looking forward
you usually take the nd no member from
the balance sheet
in accounting or when you're looking
backwards in time like autres like to
look backwards and see what happen
usually take
and minus began and I'm and plus began
which major add
and and began and divide by two to get
18
an average in this class we're not gonna
do that we're just gonna take the and
I think on this next sheet here I have a
little no
here about that and and then here's how
you were doing
average which in accounting oftentimes
should
I see this but the point years if you're
looking into the future which finance
people are you take the
and number if you're looking backwards
in the past you take and plus began
about a by two
okay so cost gets old and then we in the
torso was asleep this is everything we
soul
Sea Island put a note here this is all
the install was sold during the year
here's inventory on the shelves at the
last day now this fluctuates a lot right
so
I again this is Justin Wade estimate but
when we do this division it's gonna give
us a number let's just see we take 5000
cost a good soul
I divided by a thousand dollars have
inventory left
when I do this division that means five
dollars a Costco told for every one
dollar
of inventory it means this to it took us
five times
are that we had to have inventory
here's a better picture it means that we
we had all our inventory and we sold it
down to zero and then we immediately
restocked and then we came down is are
we immediately restock
again this is not the way it really
works the way it's not only
inventory flows but this is a great
measure to get an estimate
I love I how many times you go through
your
inventory alright so five times it means
if we sold are all over him it or downs
are we do it five times
in one year well think about that was
me.
now if we want the days to sell
inventory which is take 365
divided by five and get 73 days
so that's the first calculation that's
how long
it takes us to sell all of our inventory
now the this is called receivables
turnover and its gonna tell us how long
it takes dick
collect accounts receivable net sales
divided by a Council CEO now again this
is a I income statement number and this
is the
and balance sheet number so sales
divided by AR
same kind of idea it's as if we took
just the end in
accounts receivable I am
did this division and it tells us that
10 times during the year
we filled up RAR and thence
collected it filled it up collected it
filled it up collected that
cell arm were in essence were kinda went
through the AR
10 times during the year of its 10 times
during the year
to figure out days I take 365 divided by
10
so on average it took us 30 6.5
days to collect all over accounts
receivables
using that same logic we can do hale
ball
turnover member this is when we body in
it or we don't pay it
for a number of days so we take cost a
good soldiers like we did with him it
right now
the denominator is Accounts Payable
Costco told about a by
Accounts Payable will say its 5,600
divided by
800 so we get seven times during the
year
we have our in of filled up accounts
payable with head of
will show up again pair of Phila PA paid
off
gammaTech 365 I by seven it takes fifty
two days
this is just away for people outside the
firm
to to look at financial statements an
estimate inside the firm you have
you perhaps have much better Dada
to calculate I'm numbers but again
look at our financial statements which
is often times the best information we
have we can make these estimates
so here's the deal operating cycles can
be days to sell inventory
plus days to collect they are 73 plus 36
by five at 209
I and a half days but we have two from
the operating cycle
try here to calculate cash cycle we then
subtract cuz we avoided
we didn't have to pay cash out right at
the beginning when we got are
a our new inventory on the books or
subtract our
52 only get fifty 7.5 so about
57 or 58 days is our cash cycle
now for go back to this draw a line
Paris the fall 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 I got
the immature
cash out finally cash in
alright let's go see if we can calculate
this over in Excel
okay so our inventory turnover cost a
good soul
divided by are inventory
17 so virtually identical for
I each year alright to get this
today's recent the sequels 365
divided by number of times at seventeen
mean for a number of times
we sold our inventory and restocked in
for
a the store
selling food it better be pretty high
right
it's not like a jeweler whose inventory
turnover is probably
really slow okay so these numbers gonna
be exactly the same to
not much change now what's to
can how many days it takes us to sell
all or part inventory now
they are how long does it take a stick
to collect now
first we gotta do the number of times we
actually collect
all of our AR during a single year where
tech sales divided by
accounts receivable
so 68 time seventy times up probably
extremely high I'm
you know most the customers coming in
here are not using any kind
a are they're paying cash or credit
cards or whatever
days equals 365 divided by
this right
so what few customers they have
do in any kinda AR and probable that
this number is
this is not very important for eyes
an entity like Whole Foods or Safeway
whereas
I'll for Home Depot it's probably really
significant cuz Home Depot sells
a lot of things on account especially to
contractors
now AP Accounts Payable turnover
how many times we pay offer bills
completely in a year
equals our cost a good soul divided by
are AP Accounts Payable I'll
so we pay off our bills pretty quickly
too can look at that these are all
pretty
at identical numbers not in these are
are changing much from year to year
equals 365 divided by this will tell us
365 equals 365 provided by
this and thus I'll tell us how many days
so
pay off bills quite quickly right within
12 days on average
again these are all estimates but now we
get to figure out
operating day operating cycle in dais
this is days holding inventory plus days
and talk like they are this is just like
we saw in that draw lines on the sequels
book plus now on their
I'll well I mean in a
whole food selling food is much
different than Boeing
the operating cycle that a store worst
sells food
much shorter than operating cycle for a
manufacturer that build
airplanes all right time
now let's do the up take this and we
will subtract
the number of days it takes us to pay
our bills
on average and we get are cash cycle
in days 13 not
very much fully expected in
I industry like Whole Foods or Safeway
alright to that a little bit about term
asset utilization
we'll see you next video