Oct 4, 2016
How to create an effective ABC analysis
In an earlier post, we discovered that doing an ABC analysis on your inventory is probably a good idea. Not only will you be able to focus your energy on the items that have the biggest impact on your business, but you will also deploy your working capital where it will have the greatest return on investment.
But how, exactly, do you do an ABC analysis? Follow these steps, and you’ll be there in no time.
Remove obsolete items first
Obsolete items are those items that you never plan to stock. You may have stocked them in the past, but you’re not going to stock them again.
You may have a manual indicator which you can use to remove obsolete items, or you can go with a rule-based approach. Here’s an example:
Look at all items that have been in the system for 24 months or more, and then mark what we haven’t sold any in those 24 months as obsolete.
If you still have any stock in the warehouse of your obsolete items, get rid of it. This is excess stock by definition. If you keep it, you’re just spending money warehousing items that you never plan to sell again.
Remove non-stocked items
Our definition of a non-stocked item is one that you keep on your price list but not in the warehouse. That means if a customer orders one, then you order one from your supplier and supply it to the customer. Typically, these are slower moving items that the customer cannot easily get somewhere else and are willing to wait to get them.
You can go with another simple rule to identify items you should non-stock. Something like this:
Look at all items that have been in the system for 12 months or more that we’ve only only sold them in 3 or less of those months as non-stocked.
From now on, you will only order these non-stocked items when it is ordered by a customer and you don’t have any in stock.
Should I use sales history or forecasted sales?
Now that we’ve handled obsolete and non-stocked items, you should be left with all the items that sell regularly. These are the items we want to deal with to create our inventory’s ABC analysis.
For an effective ABC analysis, you have to take two things into account:
- The cost of the item
- How fast the item moves
The first one is easy. You will already have some sort of average cost — or a last cost — in your ERP system. Use that field.
You can look back to the previous 12 months of sales and determine the average sales per month for each item, to determine how fast they move.
However, if you have a forecast of future sales, that’s an even better indicator to use. The last thing you want to do is mark an item as a C item when its sales are trending upwards, and in 6 months that item might be a top-seller.
Similarly, you don’t want to mark an item as an A item when its sales have dried up, and there won’t be any demand for that item in 6 months. Think of a discontinued product line, for example.
That’s why our suggestion is to always use the forecasted sales whenever possible.
The 80/20 principle
We’ve all heard of the 80/20, or Pareto Principle. Nowhere is it more applicable than in inventory. We always find that 20% of a customer’s items make 80% of their sales or profits.
That’s great news, because it means that we only need to focus really intently on the 20% of the items that will make 80% of your profit.
Let’s start by creating a new column for every item with this little formula:
(item's cost price) x (historical or forecasted average demand)
Once you have that for every item, you can calculate the total of that net column.
Next, sort them items by that new column from highest to lowest. Start at the top and accumulate the new column. Once you get to 80% of the total, draw a line and call those items A items.
Continue with that until you accumulate 96%. Draw a line and call those items B items. Why 96%? Because that’s the original 80% and 80% of the remaining 20% — a reapplication of the 80/20 rule to the non-A items.
The rest of the items can now be marked C.
What you’ll notice, is that only 20% of the number of items are marked as A. Isn’t that amazing? That’s the Pareto Principle in action!
What about HML?
You’ve now got the ABC analysis for your inventory, but sometimes you have some really important items that will fall into the C category. That means we have to address the classification from another angle.
Here’s an example: In a typical high-tech store, they sell expensive smart phones (think A items), but they also sell very inexpensive screen protectors for those phones (think C items).
But it’s such a competitive market today that if a customer comes into your store to buy a phone and you can’t sell them the $2 screen protector they were also looking to buy, they could go to a competitor to get the entire purchase they were looking to make. That’s massive loss of revenue for something that probably costs pennies!
So wouldn’t it be better if we rather classify items purely by their velocity or speed with which they move through the warehouse?
That’s very simple to do. Repeat the steps from the ABC analysis above, but don’t use the cost price. This time, use only the average demand.
Now instead of ABC, you may want to call these High, Medium and Low velocity items.
Suddenly your screen protectors are classified as High velocity, and you’ll give them the attention they deserve.
A simple matrix for all your needs
Unfortunately, what we’ve now got are two competing methods of classification. And it becomes very difficult to work with both.
What would be far better is to combine the two classifications into a matrix. We can have ABC down the side of the matrix, and HML across the top, like this:
Now we can very clearly see that the green blocks (AM, AH and BH) are the bread and butter of your business. You want to really keep a close eye on those items, to make sure they don’t run out.
On the other hand, the CL items are really just noise in your business. The rarely sell, and when they do, they don’t make you any money. You may even consider downsizing those product lines.
The two brown blocks are very interesting. The AL items are items that don’t sell often, but when they do sell, they make a lot of profit. Think of a motor vehicle tire outlet: The Porche tires are in the AL corner. You won’t sell them every day, but when you do, you make a handsome profit. Meanwhile, the Toyota Corolla tires are probably in the AH corner.
On the opposite side of the Matrix is the CH items. These are the screen protectors. They don’t make a lot of profit, but they sell often and they are often used as loss-leaders.
In the next installment in this series, we’ll look at how to use this Matrix effectively to improve the performance of your inventory.