USA Inventory Turnover Calc

[jayman80]

Whats the right formula to calc inventory turnover?

	Jan	Feb	Mar	Apr	May
COGS	20	15	17	18	16
INV	17	14	12	14	13

So for a monthly calc for Feb I would take COGS/Avg Inv = 15/Avg (17,14) = .96 and then for the 5 month window as sum (Jan - May COGS)/Average Jan to May Inv) = Sum(20,15,17,18,16)/avg(17,14,12,14,13)=6.14? And then the monthly calcs should add up to the 5 month inv turn over?

 

[zugo]

To get the 5 month inventory turn over you would need the beginning balance of inventory for January, so you would need 6 terms in the average in your denominator. Also the sum of the monthly calculations would not equal the 5 month inventory turn over figure. You can see this if you try the sum in Excel.

Imagine you were doing the inventory turn over calculation over 24 months, and in one month, either the inventory was astronomically low or the COGS was extremely high, causing the turnover to increase to a level that could be considered an outlier. Imagine this took your turnover from 0.96 to 10. Perhaps a warehouse was burned down and all inventory was lost, etc. Now, over the course of the 24 months the lowered inventory (or increased COGS, same effect) for that particular month will not play as significant a role in the turnover ratio as they are being averaged over more periods.

Play around with the scenario in excel using the formulas, you what you suggested does not equal, except in the instance when COGS and Inventory are exactly uniform across all periods.

 

[jayman80]

So what's the formula to use for the 5 months (or any length of time) vs a single month?

 

[zugo]

You had the right idea, COGS/(Average Inventory) and your monthly calculation was correct. However, your five month calculation was wrong because you took the total COGS over the average of the 5 months you listed. You need 6 months of inventory data - the prior year's December ending balance of inventory will be the opening balance for January.

The average you took would be applied if you were looking for the turnover ratio for the four months Feb- May.

Notice, how with the data you listed you could not find the turnover ratio for January because you did not have the opening balance. Same concept.

 

[jayman80]

Sorry I am looking for the turnover ratio.

So then i got it right?

 

[zugo]

Your monthly calculation for February was right. Your five month ratio was wrong for the reasons I listed above.

 

[zugo]

Also note that in practice, typically the average inventory is calculated by taking the average of the beginning balance of the period and ending balance of the period, instead of averaging the balances at every month. Best wishes!

 

[jayman80]

So 1 added month for the avg inv value in the denominator. I guess what I was expecting was that the sum of the months turnover would be the same as the turnover for that period of time.

 

[zugo]

It's a reasonable thing to expect, but they won't be the same.

 

[jayman80]

Thanks!

 

[jayman80]

Can you multiply a month calc by 12 for a full year turnover?

 

[willrich14]

Can you multiply a month calc by 12 for a full year turnover?

You can as long as you have the beginning inventory and ending inventory for the month, however, it will not likely be very accurate because most inventory turnovers vary from month to month rather than it being a constant.