Supply Chain & InventoryFree worksheetPDF, Excel, Word exportWholesale Distribution

Distributor ABC Inventory Classification Worksheet

ABC classification tells a distributor which items deserve tight control and frequent counting. This worksheet computes annual dollar volume per item so you can rank them and assign classes with a defensible rule.

Download:

Item ID	Description	Annual units sold	Unit cost	Annual dollar volume	Class (A/B/C)	Count frequency
				$125,120
				$93,375
				$30,720
				$23,100
				$4,320
				$2,485

Total annual dollar volume

$279,120

Total units

56,850

Average dollar volume per item

$46,520

Largest item share of volume

44.8%

Share of the single largest item in total dollar volume

Sort by annual dollar volume from high to low, then assign A to items making up roughly the first 70 to 80 percent of cumulative volume, B to the next 15 to 20 percent and C to the rest. Cut-offs vary by business. Class and count frequency are set by you, not calculated.

Automate ABC classes from ERP history

Optional: a short conversation about producing ABC and count lists from your own sales history.

Optional. The tool and every export work without this. No spam.

How to use it

Paste or type item IDs, annual units sold and unit cost, in the same period. The worksheet multiplies units by cost to give annual dollar volume. Export to Excel, sort descending, calculate the cumulative percent, and set the class for each row. Choose a count frequency per class and record it in the last column for your cycle count plan.

Use 12 months of sales or usage
Use cost, not selling price, for control purposes
Sort descending before assigning classes
Review the classes every quarter or half year

The method

This is Pareto analysis. A small share of items usually produces most of the dollar volume. A typical split is A for the top 70 to 80 percent of cumulative volume, B for the next 15 to 20 percent, and C for the remainder. The exact cut-offs matter less than applying them consistently. Some sites add an X, Y, Z demand-variability layer to separate steady from erratic items.

Worked example

With the sample rows, the pipe and valve lines together produce well over half of the volume, so they are A items counted monthly, while gaskets and labels make up a small share and are counted once or twice a year. A single dollar-volume column also hides items that are critical but cheap, so mark those as A by exception when a stockout would stop a customer's job.

Common mistakes

Typical errors are using selling price for the volume, mixing periods, classifying brand-new items on a few weeks of data, and never re-running the analysis so that classes go stale. Another is giving every class the same count frequency, which defeats the purpose.

Frequently Asked Questions

What cut-offs should I use for A, B and C?

A common starting rule is A for the top 70 to 80 percent of annual dollar volume, B for the next 15 to 20 percent and C for the rest. Adjust to your catalog size and service goals, then keep the rule fixed.

Should I use sales or cost for dollar volume?

For inventory control, cost is usual because it reflects the money tied up. Some distributors rank by gross margin dollars or by line count too, which gives a different view of importance.

How often should I recalculate classes?

Quarterly or twice a year is common. Re-run sooner after a large customer win or loss, a catalog change or a price change that moves item cost significantly.

What about slow items that are critical?

Override the class. A cheap item that stops a customer's project when missing can be handled as A for service purposes even if its dollar volume is low.

If you would like ABC classes and cycle count lists produced automatically from your ERP sales history, we can discuss a read-only setup.

Talk to an Expert Explore More Free Tools

Related Tools

Supply Chain & Inventory

More Supply Chain & Inventory tools

See all

Back to All Tools