Welcome to Finance and Excel Video Number 18.5.
Hey, 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 are the-- this is the second workbook for chapter 3,
it says Days in Cash Cycle, and this is the second PDF.
These are just a two-page PDF and a two-page Excel workbook
to complement the other one we had.
Now we want to talk in this video about inventory turnover,
day sales and inventory, accounts receivable turnover,
days to collect accounts receivable,
and accounts payable turnover in days
to pay off our accounts payable.
And we want to take a slightly different look
at this same problem than we did in the last video, video number
18.
Now first we want to look at what
happens when you buy inventory.
So if you buy inventory and one item cost 25 bucks,
you've got to put a plus on your books.
You bought inventory, so inventory
comes on your book as $25.
But if you don't pay cash, you pay accounts payable,
your liabilities, your short-term liabilities--
accounts payable-- goes up by $25.
When you sell an item, if you sell that $25-item for 50
bucks, right?
We're not collecting right away, but we're
recording an accounts receivable,
so accounts receivable goes up by $50.
And sales-- that's the revenue part of it,
our sales and net credit sales--
goes up by $50.
Well when you sell it, you immediately then
have to record the expense.
Cost of goods sold, so that goes up by $25,
and take the inventory off the books.
So that's actually a subtraction.
Now the point of all this is all these ratios
we're going to do are going to relate to numbers.
And you want to see that sales and accounts receivable are
related, and cost of goods sold is related to inventory,
and AP through inventory is related to cost of goods sold.
Now I want to go over to the PDF.
The ultimate goal in this video is
to calculate the operating cycle in days and the cash cycle
in days.
And get this-- we're going to do this from financial statement
numbers.
Now we're going to get a days to sell inventory of 73 and a days
to collect AR of 36.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 0,
it would be this point in time.
So this is an estimate.
On average, how long does it take us
to sell all of our inventory?
73 days.
But remember, if we're collecting on AR,
we don't collect right away, so for the operating cycle,
you have to add the days to collect the accounts
receivable.
So here it is-- we bought the inventory, sold it all,
and we didn't get the cash until this--
all the cash till 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, 58.4.
So this between-- the distance between here and here,
meaning operating cycle minus the days
to pay accounts payable, give us our cash cycle number.
Now 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 use the numbers we
do to get our calculation.
But for inventory turnover, we take cost of goods
sold and divide it inventory.
Now in accounting, you take the begin number, add it to the end
number, and divide by 2.
In finance, you generally are looking forward,
so you just take the ending number.
So for 2006, cost of goods sold, those
are the cost of goods sold 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 inventory down
to 0 and then immediately restock.
Now that's not really the way it works,
but it gives us a good estimation.
So it would look like this-- you bought
inventory, you sold it, sold it, sold it down to 0,
and then immediately restock, completely restock, sold, sold,
sold, sold down to 0, and then immediately restock.
So in our example, cost of goods sold, $5,000.
$1,000 for inventory, when we do that calculation,
we get five times.
So in essence, we had to have inventory
five different times on our shelf, which means we sold it,
restocked, et cetera.
Now this number is good, that's turnover,
but really, sometimes it's important to know day.
So in this case, we just take 365 and divide it
by the turnover number, and it gives us
how many days it took us to sell our inventory.
On average, 73 days.
Same logic can be used for receivables.
Net sales divided by accounts receivable.
How many times do we collect AR and then loan it out again
per year?
So if we had 10,000 sales, divided by accounts receivable,
$1,000, that means 10 times we collected our AR.
Now again, divide in that 10 by 365
gives us 36.5 days average time to collect AR.
A similar logic for payables, except for we
relate the numbers cost of goods sold in AP,
accounts payable, in division.
We get in our example here accounts payable
or $800 at the end of the year, so we get 6.25.
Divide that by 365 and we get average time
to pay our accounts payable--
58.4.
Here's that diagram, let's go over to Excel
and make our calculation.
All right, here's our net credit sales, $10,000;
cost of goods sold, $5,000; AR, $1,000; AP, $800;
and inventory, $1,000.
So let's go ahead, let me blow this up.
Brroop.
Alt-W G highlights-- that's in 2007 or 10.
Zooms in to selection.
Hey, inventory turnover.
We take our cost of goods sold and we divide it
by our inventory.
Times per year, we sell a full load of inventory, right?
Five times.
And we simply take 365 divided by that--
so 73 days to sell a full load of inventory.
Remember, these are estimates from accounting information,
right?
We're not actually inside the firm
looking at the sales record and counting the days
on our fingers, we're looking at numbers with division ratios
and estimating.
Now a AR turnover, we're going to say net credit sales--
and really, it should be credit sales, because we're
comparing it to our AR.
In the last video, we did analysis of Whole Foods Market,
and we used the numbers on the financial statement
as a blunt--
and we got a blunt number, but really,
Whole Foods, if you're looking at their income statements,
just taking the sales number, that's got a lot of cash in it,
so probably not the best thing here.
So here we have our net credit sales divided
by our accounts receivable.
10 times per year.
Now 365 divided by our 10.
So on average, we're going to collect our accounts receivable
36.5 times per year.
Now how about AP turnover?
Again, cost of goods sold, that's the numerator divided
by our AP.
Again, every time we're buying our cost of goods sold,
we're not paying immediately, we're
putting it on accounts payable.
So that tells us how many times were completely paying off
our payables in one year.
Equals that 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.
109.5.
And finally, the cash cycle in days, this is our goal here.
We're going to say 109.5 minus the time it takes
us to pay our accounts payable.
So the estimate for our cash cycle, 51.1 days.
All right, we'll see you next video.