# Distributor ABC Inventory Classification Worksheet

> Source: https://netray.co/tools/distributor-abc-inventory-classification-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Enter annual units and unit cost per item to compute annual dollar volume, then assign A, B or C class and a count frequency.

## Category

supply-chain

## Industry

Wholesale Distribution

## Audience

- Inventory planner
- Purchasing manager
- Warehouse manager

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Item ID

**Key:** sku

**Type:** text

#### Description

**Key:** desc

**Type:** text

#### Annual units sold

**Key:** units

**Type:** number

#### Unit cost

**Key:** cost

**Type:** number

**Format:** currency

#### Annual dollar volume

**Key:** adv

**Type:** number

**Expr:** units * cost

**Format:** currency

#### Class (A/B/C)

**Key:** cls

**Type:** select

**Options:**

- A
- B
- C

#### Count frequency

**Key:** freq

**Type:** select

**Options:**

- Monthly
- Quarterly
- Twice a year
- Yearly

**Rows:**

**Sku:** FIT-1042

**Desc:** Brass ball valve 1 in

**Units:** 18400

**Cost:** 6.8

**Cls:** A

**Freq:** Monthly

**Sku:** PIP-2210

**Desc:** PVC pipe 2 in x 10 ft

**Units:** 22500

**Cost:** 4.15

**Cls:** A

**Freq:** Monthly

**Sku:** ELE-0733

**Desc:** Wire connector kit

**Units:** 9600

**Cost:** 3.2

**Cls:** B

**Freq:** Quarterly

**Sku:** HDW-5018

**Desc:** Hex bolt 1/2 in zinc box

**Units:** 4200

**Cost:** 5.5

**Cls:** B

**Freq:** Quarterly

**Sku:** GSK-8801

**Desc:** Flange gasket 3 in

**Units:** 1800

**Cost:** 2.4

**Cls:** C

**Freq:** Twice a year

**Sku:** LBL-0099

**Desc:** Warning label roll

**Units:** 350

**Cost:** 7.1

**Cls:** C

**Freq:** Yearly

**Totals:**

#### Total annual dollar volume

**Key:** adv

**Agg:** sum

**Format:** currency

#### Total units

**Key:** units

**Agg:** sum

**Format:** integer

**Summary:**

#### Average dollar volume per item

**Key:** avg_adv

**Expr:** sum_adv / count

**Format:** currency

#### Largest item share of volume

**Key:** max_share

**Expr:** max_adv / sum_adv * 100

**Format:** percent

**Description:** Share of the single largest item in total dollar volume

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use it

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

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

**Faq:**

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

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

abc inventory classification excel, abc analysis worksheet distributor, annual dollar volume abc, abc classification template, inventory abc analysis template, cycle count frequency abc, distributor inventory classification, abc xyz analysis excel

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json