How ABC Analysis Supports Inventory Control Best Practices
ABC analysis is an important technique in materials management. It informs a number of supply chain functions including sourcing, procuring, receiving, and inventory managing.
Simply put, an ABC analysis definition is the categorization of items into three categories (A, B, and C) to determine levels of importance.
Category A items are regularly counted and tightly controlled. Category B items are counted somewhat regularly and are somewhat controlled. Category C items are counted less frequently and more leniently controlled.
ABC inventory classification is important in inventory management for several reasons. It allows supply chain managers to:
- Identify the inventory items that pose the biggest business risks due to theft or damage, and pose the largest opportunities from to sales
- Help warehouse managers and other supply chain professionals to properly prioritize their time
- Empower warehouse managers to achieve close to 100% inventory accuracy
Generally speaking, there are two popular methods of ABC clasification.
Some supply chain professionals categorize items based on how frequently they move. For instance, frequently ordered items would sit in Category A, items ordered somewhat often would sit in Category B, and items ordered less frequently would sit in Category C.
The justification for this method is that fast-moving items are more likely to experience stockouts. This means they’re more susceptible to loss, theft, spoilage, or damage. Consequently, warehouse managers keep a closer eye on items with more frequent inventory counts.
Other supply chain managers prefer to categorize items based on their value. The most expensive items fall into Category A; items with an average price fall into Category B; and the cheapest items fall into Category C.
The justification for this method is that these items represent the highest individual sales for a company and, therefore, the biggest potential loss. This would also allow managers to do the right inventory replenishment decisions.
In either case, the same logic applies: Companies have a limited amount of time, so they want to carefully allocate their time, energy, and resources to the areas of the business they value most.
How Do You Conduct an ABC Analysis For Inventory Control And Management?
Most warehouse and inventory management systems or enterprise resource planning systems (ERP) have ABC inventory management capabilities. Warehouse managers enter their specific criteria (e.g., sales volume, unit cost, unit price) and the WMS or ERP categorizes items accordingly.
While using a WMS or ERP is the most effective solution for businesses, if a warehouse does not have the software to calculate an ABC inventory categorization, managers can do so manually using Excel.
How To Calculate an ABC Analysis For Inventory Control And Management In Excel
You might assume that the inventory items that generate the most value are your most expensive items – and that might be true. On the other hand, your most valuable inventory items may be moderately priced items that sell in large quantities. Companies identify this by looking at two factors: the demand of an item and the cost.
When the demand levels between different items is equal or close to equal, companies will focus on the cost.
When the cost of different items is equal or close to equal, companies focus on the demand.
The method we will use to conduct an ABC inventory analysis in Excel takes both factors into consideration: the demand and the cost.
Step 1: Gather All Inventory Data
|1||Item||Annual Demand||Unit Cost||Value|
Table X – Sample spreadsheet of an ABC inventory analysis: Gathering the data
FREE DOWNLOAD: Ready to get started? Download our ABC Analysis Template and start doing ABC analysis today.
Step 2: Find The Total Value of Each Item
You can find the total value of each item by multiplying the Annual Demand by the Unit Cost of each item. There are two ways to do this in Excel. You can either:
- Enter “=B2*C2” into D2, or
- Enter “=PRODUCT(B2,C2)
Whichever method you use, copy the formula across all of the cells in column D. You can do this by clicking on D2, clicking the bottom right-hand corner of the cell, and dragging it down as far as your inventory list runs.
Once you’ve done this, you should have a table that looks like this:
|1||Item||Annual Demand||Unit Cost||Value|
Table X – Sample spreadsheet of an ABC inventory analysis: Calculating the annual value of inventory items
Step 3: Calculate the Total Value of Your Inventory
You can do this by using the SUM function in the next empty cell in column D. In our example, that would be cell D7. In cell D7, we would enter “=SUM(D2:D6)”. This would give me:
- Total Inventory Value = $278,800
Step 4: Calculate the Percentage of Value Each Inventory Item Offers
You can find the percentage value of each inventory item by entering “=D2/D7” into cell E2.
If you want to copy this formula down, you’ll need to add a $ sign before the second D and the 7 to avoid an error sign. So you would enter “=D2/$D$7”.
This will allow you to drag the bottom right-hand corner of E2 and automatically calculate the other values in the column.
If you prefer to work with percentages instead of decimals, select column E, navigate to the formatting bar, and click the ‘%’ sign.
|1||Item||Annual Demand||Unit Cost||Value||Percentage|
Table X – Sample spreadsheet of an ABC inventory analysis: Calculating the percentage value of inventory items
Step 5: Classify Your ABC Inventory
First, you should order your items in descending order (from most valuable to least valuable). Once you’ve done this, it’s time to break up your inventory items into classifications.
While there’s no hard and fast rule for this, there are general guidelines to set the parameters of each class:
- Class A: Items that account for approximately 70% to 80% of inventory value
- Class B: Items that account for approximately 15% to 25% of inventory value
- Class C: Items that account for approximately 5% of inventory value
Based on your parameters, you can proceed to classify your inventory. With larger data sets, you’ll need to use the SUM function to add multiple cells to get to the right percentage amount.
For instance, you’ll want to classify all the items that add up to 70% to 80%, assuming you’ve ordered your table in ascending order.
We’ve classified the data in Table X where Class A is highlighted in green, Class B is highlighted in yellow, and Class C is highlighted in pink.
|1||Item||Annual Demand||Unit Cost||Value||Percentage|
Table X – Sample spreadsheet of an ABC inventory analysis: Ordering inventory items by percentage value
Step 6: Schedule Follow-Up Activities
Since the purpose of conducting an ABC classification is to focus your inventory control efforts, it’s important to make the most of this information by conducting inventory control activities accordingly. So, now that you’ve classified your inventory, you should schedule follow up activities like cycle counts.
Presenting ABC Analysis Data To Your Senior Leadership Team
An ABC inventory analysis is a great way to categorize and visualize data, which works well since most senior leaders want presentations that give them the headlines of what’s happening operationally. An ABC analysis helps you justify how you’ve organized and prioritized your warehouse activities and resources.
For instance, if you need to make a business case for frequent cycle counts, you can demonstrate that these cycle counts are focused on the highest value items in the warehouse. You can also present ABC charts, like a pie chart, that breaks down where most of your inventory value lies.
If you include even more granular data in your ABC data set, you can present even more interesting insights to your management team.
For instance, if you include columns like “Industry” or “Business Model,” you can tell your management team that most of your inventory value is tied up in Consumer Electronics or in the B2C business line.
To present this visually, your team can swap out the simple pie chart in exchange for an exploding pie chart or pie-of-pie chart.
Using ABC Analysis For Cycle Counting
One of the primary purposes of ABC analysis is help focusing your cycle counting efforts. But what exactly is cycle counting, and why is it so important?
For starters, counting inventory is an inventory management best practice. While it’s a critical activity that helps warehouse managers keep tabs on their inventories counting inventory is incredibly disruptive. Business owners have to shut down operations to properly count the merchandise. And, if they conduct their count on a weekend or after hours, they have to pay additional labor costs and possibly overtime.
As a result, businesses have largely settled for yearly stock counts of their entire inventory, but this isn’t frequent enough.
To compensate, many retailers have introduced inventory cycle counts. Cycle counts only tackle one particular area of the inventory at a time, limiting disruption and making use of workers already present during a shift.
Since managing a cycle count is difficult to do manually, most warehouses use an order fulfillment system or inventory management system to keep track of the Class A items they’ve already counted (since they can’t count all Class A items in one sitting).
Using a system also equips warehouse managers to figure out how to space out Class A, Class B, and Class C counts based on labor costs, worker schedules, busy or slow business periods, and other variables.
Using ABC Classifications To Set Order Policies
ABC method is often associated with cycle counting, but it also supports the order management process as well.
Managers don’t have time to sign off on every single purchase order. As a result, they turn to automated systems to send an order to a vendor once inventory levels hit a predefined inventory reorder point.
To support this process, a WMS can identify Class A items that might require sign-off from a manager based on their values.
ABC “Analysis” In Cost Accounting
Inventory management and accounting have a loose relationship.
While they’re both interested in tracking inventory, the warehouse team is more interested in tracking inventory for operational purposes (e.g., getting a product to a customer on time) while a finance person is more interested in it for accounting purposes (e.g., making sure all the numbers add up).
This is why people often assume there is an ABC inventory for cost accounting.
There is, just not in the same way.
What people are typically referring to is activity-based costing. This is only loosely related to ABC for inventory management purposes. Activity-based costing is a cost accounting method used for handling non-direct costs, which are not easily tracked. The opposite of a non-direct cost would be direct costs like material costs and labor costs. The category “overhead costs” is an example of a non-direct cost, and you would use activities like labor hours or machine hours (activities) to understand this cost.
In short, ABC “analysis” for cost accounting is different from the analysis for inventory management.
ABC Analysis Can Help You Make Smarter Purchasing and Inventory Control Decisions
Warehouse owners and supply chain managers have limited time and resources, so they have to allocate their energy in the most efficient way possible. ABC classification helps them identify the areas of their business that require the most attention.
Furthermore, ABC classification helps businesses determine which purchasing decisions require their sign-off. The overall effect is an operation that protects critical areas of business value and uses its time, materials, and people effectively.
Scale up B2C fulfillment operations with an integrated digital warehouse and fulfillment system
Modern digital WMS powers a modern fulfillment experience