USA Inventory Turnover Calc


Joined
Jul 25, 2019
Messages
6
Reaction score
0
Country
United States
Whats the right formula to calc inventory turnover?

JanFebMar AprMay
COGS2015171816
INV1714121413


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?
 
Ad

Advertisements

Joined
Jul 13, 2019
Messages
6
Reaction score
0
Country
United States
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.
 
Joined
Jul 25, 2019
Messages
6
Reaction score
0
Country
United States
So what's the formula to use for the 5 months (or any length of time) vs a single month?
 
Joined
Jul 13, 2019
Messages
6
Reaction score
0
Country
United States
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.
 
Joined
Jul 25, 2019
Messages
6
Reaction score
0
Country
United States
Sorry I am looking for the turnover ratio.

So then i got it right?
 
Joined
Jul 13, 2019
Messages
6
Reaction score
0
Country
United States
Your monthly calculation for February was right. Your five month ratio was wrong for the reasons I listed above.
 
Joined
Jul 13, 2019
Messages
6
Reaction score
0
Country
United States
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!
 
Ad

Advertisements

Joined
Jul 25, 2019
Messages
6
Reaction score
0
Country
United States
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.
 
Joined
Jul 13, 2019
Messages
6
Reaction score
0
Country
United States
It's a reasonable thing to expect, but they won't be the same.
 
Ad

Advertisements

Joined
Jul 31, 2019
Messages
1
Reaction score
0
Country
United States
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Inventory Turnover 2
Inventory Turnover Rate reporting? 0
turnover 0
A/R Turnover 2
Moneys Calc 10
Tax Calc 1
Open Office Calc 10
mortage calc 4

Top