KPI Series – Inventory Turnover Ratio

 

If you are a retailer or a wholesaler, your biggest investment is more than likely your inventory.  Turning that inventory over quickly is critical to your success. So it makes sense for you to monitor this measure and do what you can to improve your performance.  In general, the higher your Inventory Turnover Ratio, the better.  A higher Inventory Turnover Ratio means that you have less cash tied up in inventory to support a given level of sales.

Like any other Key Performance Measure (KPI), you have to achieve a balance. If you reduce your inventory dramatically, it could be that you lose sales because of stockouts.  So rather than saying to yourself, “let me shoot for the highest possible Inventory Turnover Ration no matter what”, you really want to shoot for the highest possible Inventoy Turnover Ratio without losing sales or otherwise disrupting your operations.  That is where your judgment comes in, and why you get paid the big bucks.

Inventory Turnover is calculated by taking your annual Cost of Goods Sold and dividing by the ending balance of your Inventory (at cost).  So if you do $1 million per year in sales, and your cost of sales is 45%, that would mean your cost of sales is $450,000 per year.  If you had $50,000 in inventory at the end of the year, your Inventory Turnover Ratio would be 450,000 divided by 50,000, or 9.0.

There are two ways to increase your Inventory Turnover Ratio:

1.      Reduce your inventory level

2.      Increase your sales

What should your Inventory Turnover Ratio be?

It depends on your type of business.  BizStats.Com publishes some national averages broken down by industry.  For Hardware stores, the national average is 3.5.  For Grocery stores it is 12.7.  For wholesalers of electrical goods it is 6.8.  If you are a member of a trade association, they might publish survey results that give you an idea of what the industry norm is.

Also, if you are a member of a Profit Improvement Group (some are called 20 groups), this is the type of measure the group should be collecting and comparing each other against.

What is the difference between Inventory Turnover and Days of Sales in Inventory?

They both measure the same thing – one is just the inverse of the other.  Days of Sales just gives you another way to think of it.  Instead of saying, “I turn my inventory over 12 times a year”, you would say “I’ve got 30 days worth on inventory on hand.”  Some business owners are more comfortable thinking about days worth of inventory.  It tells you how soon you will run out if you don’t re-order.

Article update:  3/30/2008

Days of Inventory on hand is the inverse of Inventory Turnover when you are calculating it at year end.  Most published statistics are based on year end data.  The publishers have a hard enough time getting reliable data once a year, much less on a more frequent basis.

However, you as a manager or business owner want to monitor your performance throughout the year.  As part of your regular financial reporting, you’d like to calculate Inventory Turnover for the latest month, and also the year to date performance.  Same with Days of Inventory. 

I would argue that Inventory Turnover should measure your performance over some period of time, such as the current month, the first six months of the year (year to date), or the latest quarter.  As such, it seems to me that Average Inventory is a better metric than Ending Inventory when calculating Turnover.  Days of Inventory on the other hand really is a measure of  how much Inventory you have at an instant in time,  so that Ending Inventory is more appropriate here.

I propose these definitions for calculating Inventory Turnover and Days of Inventory in a reporting system that outputs separate values for months, quarters, year to date, and year totals.

Inventory Turnover = Annual Cost of Goods Sold / Average Inventory

For Months:

Annual Cost of Goods Sold = Average Monthly Cost of Goods Sold (base on 3 month moving average) times 12

Average Inventory = the average of Last Month’s Inventory and This Month’s Inventory

For Quarters, Year to Date Periods, Year Totals:

Annual Cost of Goods Sold = Cost of Goods Sold for the time period divided by the number of months and times 12

Average Inventory = the average of Each Month’s Ending Inventory balance within the time period (e.g. for the first quarter it would be the average of Jan, Feb, and Mar ending inventory)

Days of Inventory = Ending Balance Inventory / Daily Cost of Goods Sold

For Months:

Annual Cost of Goods Sold = Average Monthly Cost of Goods Sold (base on 3 month moving average) times 12

Daily Cost of Goods Sold = Annual Cost of Goods Sold / 365

For Quarters, Year to Date Periods, Year Totals:

Annual Cost of Goods Sold = Cost of Goods Sold for the time period divided by the number of months and times 12

 

Daily Cost of Goods Sold = Annual Cost of Goods Sold / 365

 

 

 

Appendix A – Calculating Days of Inventory and Inventory Turnover in Excel

Cells with a yellow background are input from your financial statements.  Sales and Cost of Sales come from the Income Statement.  Inventory Balance comes from the Balance Sheet.  Cells with a light green background are calculated.

You can download the Excel spreadsheet with all the formulas, including projections, by clicking on this link:

www.survivalware.com/download/KPI-Inventory-Turnover-Revised.xls

Months

 Inventory Turnover and Days of Inventory - Months

Year to Date 

For a Year to Date view,  all months are taken into account to calculate the Annualized Cost of Sales, not just the last three months.

 

Inventory Turnover and Days of Inventory - Year to Date 

 

Projections

 

For projected time periods, you can enter a value for Days of Inventory and multiply bu Average Daily Cost of Sales to get a forecast of Ending Inventory for each month.

 

 

Inventory Projections

Appendix B – Calculating Days of Inventory and Inventory Turnover in SurvivalWare

 The logic file, AP-PROJ.LOG, controls the calculations for the generic SurvivalWare model, FM2007:

Days of Inventory

{ For months: }‘Last 3 months COS’ = ‘Total Cost of Sales’ + &  

    ‘Total Cost of Sales’ Lag 1 + ‘Total Cost of Sales’ Lag 2

Annualized COS’ = ‘Last 3 months COS’ * 4.0 For_Months

‘Daily Cost of Sales’ = ‘Annualized COS’ / 365.0 For_Months 

 { For ‘caculated columns’ including quarters and year to date: }

‘Annualized COS’ = ‘Total Cost of Sales’ / ‘Months Per Col’ * 12.0 For_Calced_Columns

‘Daily Cost of Sales’ = ‘Annualized COS’ / 365.0

‘Days of Inventory on Hand’ = ‘Inventory’ / ‘Daily Cost of Sales’

 

 

 

Inventory Turnover

 { Special case: the first month in the model.  No previous month to average it with. }

‘Avg Inventory – Month Calc’ = ‘Inventory’ For c1

{ For the rest of the months, calculate average inventory = (Last Month + This Month balance) / 2}

‘Avg Inventory – Month Calc’ = (‘Inventory’ + (‘Inventory’ Lag 1))/2.0 for c2 – c49

‘Avg Inventory – YTD Calc’ { Since this variable was assigned to the “Avg” class, the YTD columns already have the averages in them. }

‘Avg Inventory’ = ‘Avg Inventory – Month Calc’ for c1 – c49

‘Avg Inventory’ = ‘Avg Inventory – YTD Calc’  for_calced_columns 

‘Inventory Turnover’ = ‘Annualized COS’ / ‘Avg Inventory’

 

 

 

SurvivalWare Data Viewer

Click on the “Working Capital” tab, and scroll down to the section labelled “Focus on Inventory.” 

 

 

 

 

 

Days of Inventory and Inventory Turnover in the SurvivalWare Data Viewer 

 

 Days of Inventory Graph

 

 

Learn more about  Survivalware at www.survivalware.com

 

About these ads

48 Responses

  1. Could you please send an example of how you calculate the Days of inventory from the Inventory turns? (71.9 for 5.08)

  2. Days of Inventory = 365 / Inventory Turns

  3. I would like to ask you three questions.
    When you calculate the inventory turn over days for monthly report (see Excel job above):

    1. The result of the above example is 8.8. Does that mean the inventory clears 8.8 times per year or per month?

    2. Is that possible if you can just CGS for Feb, say, divided by the average of Jan and Feb’s ending inventory? If the result gives 1.9, then, I can say the inventory clears 1.9 times per month. Does it make sense? Do you have to annualize the CGS all the time?

    3. Why do you have to annualize the CGS, whereas the inventory is only for the average of the previous month and the current month?

    Thank you for your reply.

    Best regards,

    Jenny

  4. Jeeny,

    1. It means the inventory clears 8.8 times per year.
    2. Your reasoning is correct. If you take CGS just for Feb and divide by the average of January and February ending inventory you can say the inventory clears 1.9 times per month. You don’t have to annualize CGS all the time. The more volatile your sales (and CGS) are, the more you might want to consider more than the current month’s CGS, and annualize the number to avoid distortions in the result. Most published statistics on inventory turnover show the turns per year instead of per month.
    3. You annualize the CGS in order to calculate an annual Turnover (number of times per year it clears). This is so that the measure is expressed using the same units (turns per year) whether you are calculating it based on a single month, quarter, or full year.

    Hope this helps,

    Rusty

  5. Hi, Rusty:

    So if your turnover is 10, and your average profit margin on sales is 5%, does that roughly mean that your overall rate of return on operations would be 50% annually? (I realize that I am greatly simplifying the calculation here with no slippage, “compounding”, taxes, etc. – just making sure I understand the basic principal).

    For instance, we might wonder how grocers can survive when they make “only” 2% net on sales, but if their turnover is 12, that would mean around 24% annually (again, in simple terms), right?

    Thanks!

  6. Brian,

    Not exactly – it really depends on what you mean by “overall rate of return on operations.”

    The “average profit margin” you mention might also be called “return on sales” and defined to be net income as a percentage of sales.

    Other commonly calculated returns are measures of how well your investment is paying off.

    Some examples:

    ROE – Return on Equity – a measure of a company’s overall return on the total amount of investment tied up in the company. It is defined as annual net income as a percentage of ending equity (or the average of beginning and ending equity for the year).

    ROCE – Return on Capital Employed – similar to ROE but uses Pretax Operating Profit as the numerator and Capital Employes (Total Assets minus Current Liabilities) as the denominator.

    Then there is something common in retailing called GMROI – Gross Margin Return on Investment. This is the one that is most directly affected by Inventory Turnover.

    GMROI is calculating by taking Annual Gross Profit as a percentage of Average Inventory at Cost, and is often calculated for products or product types in addition to overall. Let’s look at two examples to see how this measure is affected by Inventory Turnover.

    1. Average Inventory at Cost is $1,000. Say you have gross profit margins of 20%. If you sell $5,000 per year of this product, you make 20% of that or $1,000 in gross profit. Your GMROI is 100%. Your Inventory Turnover is the annual cost of sales ($4,000) divided by average inventory, or 4.0.

    2. Average Inventory at Cost is $1,000. Gross profit margins are still 20%. But now you sell $10,000 per year of this product, you make 20% of that or $2,000 in gross profit. Your GMROI is 200%. Your Inventory Turnover is the annual cost of sales ($8,000) divided by average inventory, or 8.0.

    So the GMROI is proportional to inventory turnover, which jives with your initial comment. You just have to be more specific about what you mean by rate of return on operations

  7. Hi,
    My question is:
    Is there a way to calculate forecasted annual turnover in receivables based on month end balances only? Does the formula involve terms to get to that number? If so, what is it?

  8. Julie,

    Usually you express receivables in terms of Days Sales Outstanding. Annual Turnover in Receivables is simply the inverse – i.e. 365 / Days of Sales.

    For the year, Annual Turnover in Receivables is calculated as:

    Sales for the year divided by the year end Receivables balance.

    To calculate the value as of any month, you would first annualize the sales (either multiply sales for 1 month by 12, or take the last 3 months sales and multiply by 4) and divide that number by the month end Receivables balance.

    – Rusty Luhring

  9. I have two questions about computing a Monthly Inventory Turnover Ratio for a retail product that has consistent unit sales, but can have major monthly variations in cost. As a side note our Inventory is computed at cost.

    This is the formula I’ve proposed to compensate for the cost swings from month to month and still give us an inventory turn number:

    Take just that months COGS multiplied by 12 and then divided by that months ending Inventory.

    The two questions:

    Is this an acceptable formula?

    What are the flaws of using one months COGS and multiplying by 12?

  10. The problem with using one month COGS and multiplying by 12 is that the volatitily of sales is accentuated. It is an acceptable formula. However, I favor something like a 3 month average to smooth things out a bit.

  11. Rusty,

    We have approximately 1000 sku’s and would like to know the turnover rate on each item and a 12 month rolling sales history by month or days of sale per item, will this software be able to help us?

    Christy

    • Christy,
      The standard financial model that comes with SurvivalWare would not be able to handle 1,000 SKUs, but a custom model probably could. We’ll be happy to give you a quote once we find out more about your specific needs (e.g. where is the data coming from, in what format, etc.)
      – Rusty

  12. 1) how would I calculate inventory turnover ratio for a hospital stores?
    2) shall I take the issues to various dept. as COGS ?

    • The calculation should be the same whether for a retailer or a Hospital that carries inventory (supplies, I assume). If you have good inventory and COGS numbers by department, I would calculate it by department.

      Here’s the calculation:

      Inventory Turnover = Annual Cost of Goods Sold / Average Inventory

      For Months:

      Annual Cost of Goods Sold = Average Monthly Cost of Goods Sold (based on 3 month moving average) times 12

      Average Inventory = the average of Last Month’s Inventory and This Month’s Inventory

      For Quarters, Year to Date Periods, Year Totals:

      Annual Cost of Goods Sold = Cost of Goods Sold for the time period divided by the number of months and times 12

      Average Inventory = the average of Each Month’s Ending Inventory balance within the time period (e.g. for the first quarter it would be the average of Jan, Feb, and Mar ending inventory)

  13. We wanted to know difference between the Inventory turn over and days inventory on hand.

    • One is really the inverse of the other. Inventory Turnover is a measure of how quickly you move merchandise if you are a retailer, or cost of goods if you are a manufacturer. You take your cost of sales over a full year, and divide by the average inventory to get this number. The higher the number the better.

      Days of inventory is a measure of how long it would take you to run out of stock if you didn’t reorder. It is calculated by taking the average inventory and dividing by daily cost of sales to express the number in days. In general, the smaller this number the better.

      • Thank you so much,

        As per could you pls. help us how to calculate the Inventory ageing.

  14. I have a small question on calculation of Inventory Turnover ration (ITR). The standard calculation i.e.Annual COGS/Av 12 months Inventory is being followed by us.
    We are trader & mainain certain safety stocks. Parts are imported & supplied against customer order/request within that particular month. Also, that we have some non-moving parts (devauated) in stocks.

    Now for instance. if our COGS is $100 annualy. Out of these $80 are purchases & supplied to customer against orders through our stores. $20 is COGS from our safety stock in cases of emergency etc.

    Our Total inventory is $10..Out of these $5 are safety stock& remaining $5 are non moving (devaluated) parts.

    We are caluclating ITR as 100/10=10. Whether this is correct.

    Also, my question is whether our ITR should be 20/10=2 i.e. by eleminating COGS against customer orders since these are traded against customer orders.& not stocked. However the goods flow (receipts & issues) takes place from our stores.

    • Hello Milind,
      I think you are doing it the correct way – i.e. your inventory turnover ratio is the annual COGS of $100 dividend by the $10 in ending inventory. The higher your safety stock and non-moving parts, the lower your Inventory Turnover will be. The fact that you don’t have to stock 80% of your COGS is what alllows you to show a high Inventory Turnover – which is an accurate portrayal of your inventory performance. If your performance slips and you have to stock more, then a reduced Inventory Turnover number would reflect that. It sounds like you can reduce your inventory level by either keeping less safety stock, or eliminating or reducing the non-moving items.

  15. asmgermany stellt Metallbuchstaben wie Edelstahlbuchstaben, Messingbuchstaben, Aluminiumbuchstaben, Bronzebuchstaben

  16. Hello,

    I am a new buyer in retail dept stores , and will have a test on stock turnover etc…

    My question is normally in dept store what are they concern for the turnover and do you have sample ?

    Thank you very much

    • In a department store you would monitor stock turnover (i.e. inventory turnover) by department. Different departments might turn over their inventory at different rates. Electronics for example, you would expect to have a higher turnover rate than something like Jewelry. The lower the gross profit margins, the higher inventory turnover you need to achieve the same Gross Margin Return on Investment or GMROI. In retailing this is a measure of how much you make back in Gross Profit on your investment in inventory.

  17. is it possible to show us the best ways to reduce inventory. can we apply abc analysis to the total value of the existing inventory thats idle to help reduce the same?

  18. Hi,
    we are a fashion retailer with stores in different countries, as a result we have inventory at various stages. goods in our own central distribution center, in transit from central to region at any given point our 35 to 40 p inventory is not in front our customers. now my question is how do we calculate our inventory turn. do we take all the inventory or only in retail stores

    • Hi Tarique,
      I would suggest that you calculate more than one Inventory Turnover number. One would be for the individual retail store or group of stores to evaluate how that store or group is managing its inventory. Then also calculate Inventory Turnover for the enterprise in total to monitor how you are performing as a company. You might also look at Retail Inventory Turnover and Wholesale Inventory Turnover separately to track performance of the managers responsible for each area.

  19. thank you

  20. what does inventory turnover indicates actuaaly?? what relationship it is telling abt cogs and average inventory? the answer comes in time. ex. 2times. what is it showing?? kindlyb help and do reply.

  21. Inventory Turnover is a rough mesaure of how many times in a year you sell out and replenish your inventory. In general, the higher number the better, because that means you have less cash tied up in inventory for a given level of sales. The basic calculation is Annual Cost of Goods Sold divided by Average Inventory. An Inventory Turnover of 2 times means that you’ve sold goods equal to twice your average inventory in a year.

    The inverse of Inventory Turnover is Days of Inventory on Hand. (which = 365 / Inventory Turnover). So and Inventory Turnover of 2.0 is equivalent of 182.5 days of Inventory.

  22. I have average inventory of $23683330 for the week.
    My shipment for the week is $4351816. How do you calculate the Days on hand for the week

    Can we also calculate expected inventory value if we have DOH and Shipment data for the week

    • Hello Veer.

      For the week, you shipped $4,351,816. Divide by 7 to get average daily shipments of $621,688.

      So, your Average Inventory expressed as days on hand (DOH) is $23,683,330 / $621,688 per day = 38.1 Days

      To calculate expected inventory value given DOH and Shipments, we do a little algebra. First we start with the equation to solve for DOH:

      DOH = Avg Inventory / Daily Shipments

      Multiply both sides of the equation by Daily Shipments to get:

      Daily Shipments * DOH = Avg Inventory

      Then:

      Avg Inventory = (Weekly Shipments / 7) * DOH

      • great answer.

        So based on same data how do we calcuate the inventory turns for the week

      • Veer,

        Inventory turns is generally calculated as an annual number. i.e. Inventory Turnover of 10.0 means you “Turn Over” the inventory 10 times in a year.

        To calculate annual Inventory Turnover, just divide the number of days in a year (365) by the Days of Inventory on Hand.

        In your example:

        Inventory Turnover = 365 / 38.1 = 9.58

        – Rusty

    • What is difference between forecasting and demand planning

      • Forecasting can a component of demand planning. For example, you may forecast elements of your sales funnel (website visitors, leads, closing rates, etc.) in order to arrive at a forecast of sales. Then the forecasted sales numbers become the basis for your demand planning.

  23. I think it quite high for electronics items

    • I’m not sure what kind of business you are talking about – are you a manufacturer? Wholesaler? Retailer?

      You would want to achieve a high Turnover Ratio for electronics items since they may decline in value over time or become obsolete.

      Your industry may have a measure such as Inventory to Shipment ratio which is similar to Days of Inventory on Hand. If you just took avg weekly Inventory divided by Weekly Shipments, that would give you “Weeks” of inventory on hand. It should be the same as Days on Hand divided by 7.

  24. also how do we calculate inventory to shipment ratio for the given data and what is the use of this metrics?

  25. thanks it is helpfull and makes sense as we are retailers

    I did calculate the inventory to shipment ratio for above data and it came a 5.44. Does it tell that i have 5 months worth of inventory in my system

  26. No, it means you have 5.44 weeks of inventory (which is consistent with the 38.1 days).

    The inventory to shipment ratio gives you a value measured in the time period you used for shipments (a week’s worth of shipments, a month’s worth of shipments, etc.)

    • Thank you for the detailed explaination to my query.
      I like your method of putting across the data and it help me to quickly understand the details.

  27. A local bakery Harry’s orders 100 50-pound bags of flour every three months.

    a) What is the average inventory for three months(in bags)?
    b) What is the average inventory for a year(in bags)?
    c) What is the average monthly inventory(in pounds)?

    • Hello Alok,

      We need month by month sales data in order to calculate the inventory levels and average inventory for the month or the year. Also, do we assume no inventory to start?

      What are your sales assumptions?

  28. Hi,

    I work for a medium apparel retail company. I am trying to source international benchmark trends for the following:
    ROI
    Inventory Turnover Ratio
    Cost of Goods Sold
    Shrinkage

    Thank you,

    Jimmy

  29. Its amazing how many SME’s don’t even know how critical Inventory levels are and the impact of overstocks and slow moving inventory has on a business.
    They measure sales but hardly ever measure inventory levels.

    I do have a question on the logic behind the annulized Cost of sales calculation. Is the method used on the spreadsheet correcty?

    • Ian – I reread the article and reviewed the spreadsheet examples and at first i thought something was off. But I think it is the difference between a “Year to Date” and “Monthly” view of inventory turnover. The year to date calculation of annualized cost of sales takes into account however many months have passed so far in the current year, divides by that number to get a monthly average, and then multiplies by 12 to get an annualized number.

      For months, I arbitrarily pick 3 as the number of months to use when calculating the monthly average. The number to pick here depends on your business and how quickly you turn over your inventory. The faster you turn over inventory, the small number you want to use. You might consider the current month only if you tend to keep inventory on hand at less than 45 days.

      I also checked the spreadsheet you can download – and you may be referring to the formulas for Annualized Cost of Sales in months 1 and 2. In month 1, it uses just that month since the history is not available to calculate a 3 month average. You’ll see the 3 month average in months 3 and forward.

      Let me know if I missed something!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 71 other followers

%d bloggers like this: