Excel Finance Class 18.5: Ratios For Cash Cycle In Days.

Download Excel workbook http://people.highline.edu/mgirvin/ExcelIsFun.htm
Learn how to calculate the Ratios: 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.

---

Closed Caption:

welcome to finance an Excel video number
18.52 if you want to download this
workbook
or download the PDF
click on the link directly below the
video and you can download these
now these are the this is the second
workbook for Chapter three eighths's
days in cash cycle and this is the
second
PDF I these are just now
a two page PDF and I i two-page
Excel workbook to complement the other
one we had now we wanna talk
in this video about inventory turnover
day sales and inventory I
accounts receivable turnover days to
sell a catheter collect accounts
receivable
an Accounts Payable turnover in days to
to pay off our accounts payable we want
a car
slightly different I am look at this
same problem
than we did in the last video video
number eighteen a first we want to look
at
how what happens when you buy inventory
so
if you buy inventory in one item cost 25
bucks you gotta put
a plus on your books Ebadi mentors
inventory comes on your book
as a 25 but if you don't pay cash to pay
accounts payable
your liabilities a short term
liabilities Accounts Payable
goes up by 25 when you sell an item
if you sell that twenty five dollar item
for 50 bucks right we're not collecting
right away
but or recording and accounts receivable
so accounts receivable goes up by fifty
and sales that's the revenue part
internet sales and net credit sales
goes up by fifty dollars well when you
sell it you immediately then have to
record the expense cost it's also that
goes up by 25 and take the inventory
of the books so that's actually a
subtraction
now the point of all this is all these
ratios were gonna do are going to relate
two numbers
when you wanna see that sales and
accounts receivable or related
in cost a good soul
is related to inventory and
AP through inventory is related
to cost of goods sold now wanna go over
to the PDF
the ultimate goal in the studios to
calculate the operating cycle in days
and the cash cycle in days and get this
we're gonna do this
from financial statement numbers now
we're gonna get a days to sell inventory
of 73
and the days to collect AR of 30 6.5
when you add those together you in
essence get the whole
operating cycle now here it is we
purchase our inventory
if we were to sell it all the way down
to the to zero it would be this point in
time
so this is an estimate on average how
long does it take us to sell all over in
Mentor
73 days but remember if were collected
on AR
we don't collect right away so for the
operating cycle
you have to at the days tick to collect
the accounts receivable
so here is we bought the inventory sold
at all we didn't get the cash told this
all the cash talk here at this point in
time %ah
but this operating cycle is not the cash
cycle because when we buy inventory
we're actually not paying for it
immediately so we need to calculate days
to pay
accounts payable 50 8.4
so this between the distance between
here and here
mean operating cycle minus the days to
pay accounts payable give us our cash
cycle number
now I'll you can look up here these
are notes on how to calculate inventory
turnover
receivable turnover and payables
turnover let's look at the first one
the logic is the same for each one of
these
for why we are use a number to do to get
our calculation
but for inventory turn on way to cost a
good so long divided by
inventory now in accounting you take the
began number
added to the end number and divide by
two and financially generally are
looking forward to just take the
any number so for a 2006
cost gets all those are the cost a bit
old for the whole year
you take the ending 2006 inventory
number: that's a looking forward number
now this tells us how many times did we
run in the Tory down to zero and then
immediately restock
now that's not really the way it what
hurts but it gives us a good estimation
so
it would look like this you bottom it or
is so little taller down to zero and
immediately restock complete restock
sold sold sold sold down there on the
immediately restock
so in our example cost to get sold 5,000
thousand for inventory want to do that
calculation we get
five-time so in essence we saw we had
to have inventory five different times
on a shelf which means
sold it restock X era now this number is
good thats turnover but really
I sometimes it's important to note days
so in this case we just take
365 and divided by the turnover number
Nick assess how many days it older
it took us to sell or inventory on
average seventy three days
same logic can be used for receivables
net sales divided by counts receivable
how many times do we collect they are
and then loaned it out again per year
Sophia 10,000 sales divided by accounts
receivable thousand that means
10 times we collected our a are
now again divide in that ten my 365
gives us
30 6.5 days average time to collect
AR a similar logic for payables
except for real relate the numbers cost
we are told in AP
Accounts Payable in Division we get in
our example here
accounts payable 800 at the end of the
year so we get 6.25
to either by 36 time we get time average
time to pay our accounts payable
58.4
hears that diagram let's go over to
excel and make our calculation
right here's our net credit sells 10,000
casa gets all five thousand AR
a thousand a DP eight hundred and
inventory at 1000
so let's go ahead let me blow this up
good
alt WG highlights are that's in 2007 or
10
zooms into selection hey inventory
turnover we take our cost a good soul
and we divided by are inventory
times per year we sell a full load of
the inventory
right five times and listen protect 365
divided by
that 73 days to sell a full load of him
but remember these are
estimates from accounting information
right
or not actually int inside the firm
looking at
you know the sales record and counting
the days on her fingers were lookin at
numbers with division ratios and
estimating
now a AR turnover we're gonna say net
credit sales and really it should be
our credit cells cover comparing it to
are AR
in the last video we did analysis are
Whole Foods Market and weed Res the
numbers on the financial statement as
a blunt are we and we got a blunt number
but really
Whole Foods you're looking at a ring on
statements just taken the sales number
that's gotta lotta cash in it so
probably not the best thing here so here
we have our net credit
credit sales I divided by
are accounts receivable
10 times per year now 365 divided by our
10
so on average we're going to collect our
accounts receivable 30 6.5 times per
year
now how about ATP turnover
can cost a good soul that's the
numerator divided buyer
ATP again every time
whereby in are cost good so we're not
pay immediately
were are put in on Accounts Payable so
that tells us how many times
were completely pain of our payables
in one year equal to 365 divided by that
that'll give us 58.4 days now we can
calculate our
operating cycle days to sell all the
inventory
plus our days to get cash without
all that inventory we sold on AR
100 9.5 and finally the cash cycle in
days this is our goal here
were gonna say 109 .5 minus
the time it takes us to pay our Accounts
Payable
so the estimate for our cash cycle 51
point one days alright we'll see annex
video