ABC Inventory Analysis
Some companies that have a lot of different types of items (with different costs as well) like to rank their items to understand which items within inventory represent the greatest portion of inventory costs. A common way of ranking items is by using the ABC method (Pareto). This posting focuses on inventory value as the basis for ranking, but as companies get used to the concept, they tend to develop their own equations to determine the ranking. Companies also use the ABC ranking to analyze sales figures as well, but that will be saved for another post. What you will need to perform a basic ABC ranking of inventory: Cost of product (this can be base cost or total cost = base cost + holding + order cost) The quantity on hand The analysis can be done as a snapshot (point in time), but it is better suited for over a period of time. If there is a lot of variability in the inventory quantity and cost of the product, then a longer period of time would be a better choice. For an analysis over a period of time, use average numbers (i.e., Average Annual Cost and Average Quantity on Hand). So lets get started already! Below is the finished analysis for a fictitious company. I have highlighted the "A" and "B" items to help them stand out to the warehouse manager. The warehouse manger wanted to see the items that accounted for 80% of their inventory (A), then the next 15% (B), and finally the "C" items were those that represented 5% of the total inventory. To experiment, first try out this report in Excel to make sure you have it working before starting an actual SBO report.
|
Item#
|
Quantity
|
Unit Cost
|
Inventory
Value
|
% Total Inventory
|
Cumulative %
|
|
ABC
|
| 34543 | 1412 | $80.00 | $112,960.00 | 55.0% | 55.0% |
|
A
|
| 3213 | 1100 | $30.00 | $33,000.00 | 16.1% | 71.1% |
|
A
|
| 8082 | 888 | $20.00 | $17,760.00 | 8.6% | 79.7% |
|
A
|
| 6576 | 544 | $16.00 | $8,704.00 | 4.2% | 84.0% |
|
B
|
| 345553 | 423 | $20.00 | $8,460.00 | 4.1% | 88.1% |
|
B
|
| 86723 | 333 | $25.00 | $8,325.00 | 4.1% | 92.1% |
|
B
|
| 890532 | 99 | $30.00 | $2,970.00 | 1.4% | 93.6% |
|
B
|
| 24234 | 245 | $10.00 | $2,450.00 | 1.2% | 94.8% |
|
B
|
| 11424 | 234 | $10.00 | $2,340.00 | 1.1% | 95.9% |
C
|
|
| 353555 | 51 | $36.00 | $1,836.00 | 0.9% | 96.8% |
C
|
|
| 685595 | 42 | $40.00 | $1,680.00 | 0.8% | 97.6% |
C
|
|
| 664434 | 110 | $15.00 | $1,650.00 | 0.8% | 98.4% |
C
|
|
| 25252 | 77 | $20.00 | $1,540.00 | 0.7% | 99.2% |
C
|
|
| 2233 | 72 | $16.00 | $1,152.00 | 0.6% | 99.7% |
C
|
|
| 897785 | 34 | $10.00 | $340.00 | 0.2% | 99.9% |
C
|
|
| 2526 | 16 | $12.00 | $192.00 | 0.1% | 100.0% |
C
|
|
| Total Inv Value | $205,359.00 | ||||||
| A= | 80% | ||||||
| B= | 15% | ||||||
| C= | 5% | ||||||
| Total % | 100% |
Steps to create this Report:
- Multiply the Quantity by the Unit cost to get the Inventory Value.
- Sort the Inventory Value descending (Highest to lowest).
- Divide the Inventory Value of each item by the Total Inventory Value at the bottom of the Inventory Value column to get the % Total Inventory
- for the first line of the report (item = 34543) the Cumulative % = % Total Inventory
- For all other values in the % Total Inventory the equation is: The % of Total Inventory for the item being calculated + the Cumulative % amount from the item just above the current item.
- So, for Item # 3213 (line 2) the Cumulative % = 16.1% + 55.0%, for a total of 71.1%
- The ABC ranking (Last Column) references the percentages at the bottom (A=80%, B=15%, C=5%). You can do this visually at first to make sure your eventual calculation for correctly. Any items 80% and below will be "A" items, 80% through 95% is a "B" item, and finally the last 5% are the "C" items. Note: if you do not sort the Inventory Value column descending first, this report will not produce the correct information.
Enjoy! -Ed Monk