Case Study Turnover Analysis

Turnover analysis allows you to determine if the inventory level for each individual inventory item is excessive, too low, or just right. This example shows an inventory analysis turnover schedule, and how the information can be used.

Jeff Hammer, owner of Handy Hardware, has been experiencing some cash flow problems, causing him to draw on the store's line of credit at the bank more than he was expecting. Jeff has noticed a significant buildup in the inventory of the home repair and improvement section of the store. Sales in this department have remained steady, but the average inventory investment period and the inventory to sales ratio have both increased over the last three months. Jeff's goal is to stock the number of inventory items necessary for about 30 days of sales.

Jeff has decided to perform a turnover analysis on the home repair and improvement inventory items. He has just completed his mid-year physical inventory count so the number of items held in inventory is already available. The number of a particular item sold was compiled using information gathered at the time a customer checks out. The following is an excerpt from his turnover analysis schedule:

Handy Hardware
Inventory Turnover Analysis
June 30, 2001
Number in Stock
Description Number Sold in Last 30 Days Days Sales in Inventory Jeff's Action
Paint (gals.) 392 121 97 Reduce level
Paint brushes/
66 54 37 Just right
Paint remover (gals.) 10 15 20 Increase level
Paint thinner (gals.) 130 44 89 Reduce level
Sandpaper sheets 922 192 144 Reduce level

Based on the information from Jeff's turnover analysis, he has determined that his inventory level in three out the five items listed above is too high. Reviewing the results a little closer shows that Handy Hardware had 392 gallons of paint on hand as of 6/30/2001. Only 121 gallons were sold in the last 30 days. The inventory of paint on hand is enough for three months' sales! Jeff could reduce the inventory of paint by two-thirds, and still have enough paint inventory to cover one month's sales amount.

The turnover analysis of paint brushes and rollers showed much better results. Sixty-six paint brushes and rollers were on hand as of 6/30/2001. Fifty-four brushes and rollers were sold during the last 30 days. Using this information, Jeff determined that there was enough inventory of these items to supply 37 days of sales. This is very close to the 30-day level Jeff feels is optimal for his hardware store. No reduction is necessary for these inventory items.

The results for the paint remover showed that the hardware's level of inventory was too low. Only a 20-day supply of paint remover was held in stock at the end of June. Jeff will need to increase the amount of paint remover stock to ensure that at least 30 days' worth of paint thinner sales remains in stock.

The schedule also showed that excessive levels of paint thinner and sand paper sheets were held on 6/30/2001. In both cases, Handy Hardware was holding well over the 30 days' sales amount that Jeff determined to be the best level for the store. A reduction in the inventory of both items is called for.

The turnover analysis shown above helped Jeff determine the specific items creating a buildup in the inventory of the home repair and improvement department of his hardware store. Eliminating the excess inventory levels will reduce the size of Jeff's investment in inventory and improve his cash flow.