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

 

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.

 

 

 

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.

 

 

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.” 

 

 

 

 

 

 

 

 

 

 

Learn more about  Survivalware at www.survivalware.com