How ABC Analysis Supports Inventory Control Best Practices

ABC analysis is an important technique when managing materials. It informs a number of supply chain functions including sourcing, procuring, receiving, and inventory management. Simply put, ABC analysis is the categorization of items into three categories (A, B, and C) to determine levels of importance.

Let’s break that down a little further. Category A items are regularly counted and tightly controlled, while Category B items are counted somewhat regularly and loosely controlled. Category C items are counted the least frequently and controlled loosely. ABC inventory classification is critical for successful inventory management for several reasons.

It allows supply chain managers to:

Generally speaking, there are two popular methods of ABC classification.

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 Warehouse and Inventory Management Systems?

Most warehouse and inventory management software 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

	A	B	C	D
1	Item	Annual Demand	Unit Cost	Value
2	W1	3000	50
3	W2	4000	12
4	W3	1500	18
5	W4	750	16
6	W5	2200	19

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:

1. Enter “=B2*C2” into D2, or
2. Enter “=PRODUCT(B2,C2)

Whichever warehouse and inventory management system 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:

	A	B	C	D
1	Item	Annual Demand	Unit Cost	Value
2	W1	3000	$50	$150,000
3	W2	4000	$12	$48,000
4	W3	1500	$18	$27,000
5	W4	750	$16	$12,000
6	W5	2200	$19	$41,800

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.

	A	B	C	D	E
1	Item	Annual Demand	Unit Cost	Value	Percentage
2	W1	3000	$50	$150,000	53.80%
3	W2	4000	$12	$48,000	17.22%
4	W3	1500	$18	$27,000	9.68%
5	W4	750	$16	$12,000	4.30%
6	W5	2200	$19	$41,800	14.99%
7				$278,800

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.

	A	B	C	D	E
1	Item	Annual Demand	Unit Cost	Value	Percentage
2	W1	3000	$50	$150,000	53.80%
3	W2	4000	$12	$48,000	17.22%	71.02%
4	W5	2200	$19	$41,800	14.99%
5	W3	1500	$18	$27,000	9.68%	24.68%
6	W4	750	$16	$12,000	4.30%	4.30%
7				$278,800

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.