This article is part 2 of a 3 part series on ABC Inventory Analysis.
Part 1 introduces the concept of ABC classification.
Part 2 provides a step-by-step walk through of how to conduct ABC Analysis.
Part 3 Shares a few tips on properly conducting ABC analysis.
ABC Classification Steps
While the concept of ABC classification is a simple one, classifying large, complex inventories can seem needlessly tedious and overwhelming. Fortunately, while classifying your inventory the first time, can feel like a lot of work with little return, the payoff is exponential when you consider that it forms the foundation of streamlined inventory management and material flow otherwise not achievable.
Once you’ve committed to conducting an ABC analysis of your inventory, follow the steps below.
Step 1. Separate Purchased Items from the Manufactured Ones
Create two item lists. One list is for items that are purchased and the other is for items that are manufactured. This way, you’ll have a complete and accurate look at the highest, mid-range and low costs in each category. Because the annual cost of manufactured items tends to be much higher than that of purchased parts, manufactured items will dominate the limited class A spots, pushing purchased items that require Class A attention into class B and class C categories.
Keep in mind that is very important that you remember to NOT label parts manufactured by a sister company as manufactured. Your list of purchased items must cover all items that you do not own which are delivered to your plant from another facility. Even if a part is made by a remote facility that is owned by the same company as yours, the part is external and should be labeled as a purchase.
Also, don’t exclude maintenance, repair and operating (MRO) items from your lists. The expense of MRO supplies can add up. You want a complete look at your inventory value, so remember to account for all purchased items – even if they aren’t directly related to production. Remember, if it’s contributing to the value stream then it deserves your attention.
Step 2. Collect the Standard Cost and Annual Usage Data for All Purchased Part Numbers
In a spreadsheet, create three columns. The first column should be a list of the part numbers for every item. The second column should contain the unit cost of each listed inventory item, as it is consumed. For example, part G602’s purchase price might be $5 per pound, but if it is consumed in feet then you want to calculate the per foot cost. This will help you tremendously as you develop your replenishment and material handling plans. The third column is where you input the annual demand for each item. Again, demand should also be in units of consumption.
Step 3. Calculate the Annual Spend for All Parts
Next, create a fourth column in your spreadsheet for the calculation of annual spend. To get this number, simply multiply the standard unit cost of each part by its annual demand. This will give you the annual spend for each item.
Using the sort function of the spreadsheet, sort rows by their annual spend. Do this in descending order so that those with the highest annual spend values are on top. Once your items are sorted by annual spend, in a non-sorted cell calculate the total cumulative annual spend for all parts.
Step 4. Add a Column That Will Show a Cumulative Running Total
The next step is to calculate the cumulative annual spend. The Cumulative annual spend is simply a running total of each item’s annual spend that accumulates to equal the total annual spend of all items. For each row, the cumulative running total should be equal to the sum of the annual spend of itself plus the annual spend of all items before it, when sorted by annual spend in descending order.
For example, in the first item number row, the cumulative total will simply equal that item’s annual spend. The next row will be equal to the sum of the second row’s annual spend plus the one above it. The cumulative annual spend in the third row should equal the combined annual spend of the top three parts, and so on. By the end of the list, the cumulative running total should be equal to the total annual spend for all parts calculated in column 3.
Step 5. Identify Class A Items
To determine which parts should be classified as class A items, you must first find the point that acts as a threshold for 80% of the total annual spend.
Target Total Annual Spend Threshold (Purchased A) = Total annual spend for all purchased items x 80%
Round the calculated Target Total Annual Spend (Purchased A) down to the nearest ones, tens or hundreds place to see a general estimate of the threshold. That is, in order to be considered an A item, the annual spend of that item must be at least this much.
Looking back at the Cumulative Annual Spends, compare them to your target. Find the first row greater than the target acts as your first estimate of the cut-off point for an item to be considered class A.
Compare the annual spend of the items on the threshold between class A and class B. If there is not a significant difference between the two items on either side of the threshold, raise or lower your threshold as needed so that there is a meaningful gap between where A items end and where B items begin. Keep in mind that the threshold should not deviate too far from the calculated target annual spend of purchased class A items.
Once your cutoff point is established, add another column to your spreadsheet that will house the designated ABC classification of each part. Here, enter “A” for each item that qualifies as an A item, based on your analysis.
Step 6. Identify Class B Items
Class B items will represent the next 15% of your total annual spend. To find the threshold separating class B items from class C items, determine the value of 95% of the total annual spend. Using 95% will show us the amount that both class A (80%) and class B (15%) contribute to the total, but since we know our A’s already, we will simply use this as a cutoff estimate between B and C inventory items.
Target Total Annual Spend (Purchased B) = Total annual spend for all purchased items x 95%
This estimated cutoff may include some of your designated class A items depending on how you manually adjusted your threshold. Make manual adjustments so that your cutoff point is meaningful as you did with the A items. Designate the appropriate non-A items below your identified threshold as B items.
Step 7. Identify C Items
All remaining items that do not meet the requirements to be classified as Class A or Class B are simply labeled with a “C” in the ABC Classification column.
Step 8. Repeat This Process for Manufactured Items
Now that the process is clear, repeat step 2 through step 7 for your manufactured items. Keep in mind that it’s not unusual for manufactured items to have an extremely high total annual spend. This methodology is still sound. It’s also why we separated our purchased parts from our manufactured parts in step 1.
The extremely high cost of manufactured items is often the result of duplicated demand. When one part is used in fabricating another, and then those parts end up being used in an assembly and then all end up as a finished good, the costs of the parts associated with the manufactured goods end up being counted several times in the demand column that is entered in the third column.
Still, other features of this analysis might not be what you expect. The number of items that end up classified as A’s, B’s or C’s might seem off, or the proportion of on-hand value that is held by A, B or C items might be quite irregular. This is all typical of manufactured parts, and that is why it’s important to separate them from the purchased parts for classification calculations.
In part 3 of this 3 part series on ABC inventory analysis we cover a few additional points on properly conducting ABC inventory analysis.