ABC Analysis for a Distributor: Rank SKUs and Customers With AI

ABC analysis sorts items or customers by annual value and splits them into three classes: A is the few producing roughly 70% to 80% of the total, B the middle band up to about 90% to 95%, and C the long tail. A distributor uses it to decide which SKUs get tighter stock control and which customers get first call.

The classes only help if each one triggers a different behaviour. A ranking that sits in a spreadsheet and changes nothing is a report, not a method. In practice an A SKU gets a monthly count and a named buyer who checks it weekly, while a C SKU is counted once a year and reordered by a simple rule, and the same split applies to who gets a call back first when stock runs short.

Follow me on Instagram@sagnikteaches

What the three letters actually mean

The method comes from the Pareto principle, the observation that a small share of causes usually produces most of an effect. In a distributor's range, a small share of SKUs usually produces most of the revenue or profit. ABC analysis turns that observation into a ranked list with two cut-off lines.

Connect on LinkedInSagnik Bhattacharya

Three terms come up constantly, so it is worth pinning them down.

Subscribe on YouTube@sagnikteaches

• SKU (stock-keeping unit) is one distinct product you can order, including each size, colour or pack. A tap in three finishes is three SKUs.
• Cumulative share is the running total of value, expressed as a percentage of the grand total, as you walk down the ranked list. It is the number the cut-offs are applied to.
• Consumption value is the measure you rank by. Usually this is annual sales revenue, annual gross profit, or annual units multiplied by unit cost. Pick one and say which.

Commonly quoted cut-offs are 70/20/10 or 80/15/5 of value, but there is no fixed standard, and published examples vary from one company to the next. Treat them as starting points that you adjust to how your range behaves. A distributor with a very flat range, where the top 20% of SKUs produce only 55% of sales, will find the A class hard to define, and that is itself a finding: your attention is not concentrated, so a tiered approach buys you less.

What the classes usually look like in numbers

These ranges are rules of thumb, not measured industry benchmarks. Your own data decides, and the next sections show how to read it.

The 200-SKU example, start to finish

Take an illustrative specialist food importer that sells to delis and restaurants. It carries 200 active SKUs and invoiced $1,800,000 over the last 12 months. The owner wants to know which lines deserve a weekly look and which can be ordered once a quarter.

Step 1: pull a clean export

Export 12 months of invoice lines, not summary reports. You need one row per invoice line with SKU, description, date, quantity, net sales value and, if you have it, cost. Use a full 12 months so seasonal lines are not judged on their best or worst quarter.

Before anything else, deal with credit notes and returns. A credit note shows as a negative quantity. If you net it off against the SKU it belongs to, the total is right, but a SKU that is returned heavily can look like a modest C when it is really a quality problem. Keep a separate column of returns so you can look at them later.

Step 2: total by SKU

Create a second sheet with one row per SKU. In a recent version of Excel, the quickest route is a PivotTable: put SKU in Rows and net sales in Values. If you prefer formulas, list the SKUs and use SUMIFS.

=SUMIFS(Invoices!$F:$F, Invoices!$B:$B, A2)

Check that the sum of the column equals the sum of the invoice sheet. If it does not, you have SKU codes that differ by a trailing space, a lower-case letter or a supplier prefix, and the unmatched value is sitting in nobody's row. This is the commonest silent error in the whole exercise.

Step 3: sort and calculate the cumulative share

Sort the SKU totals from largest to smallest. Add a column for each SKU's share of the grand total and another for the running total. With the sorted values in column D (rows 2 to 201) and the grand total in a cell named Total:

Share (E):       =D2/Total
Cumulative (F):  =SUM($E$2:E2)
Class (G):       =IF(F2-E2<0.7,"A",IF(F2-E2<0.9,"B","C"))

The class formula looks at the cumulative share before the row is added (F2 minus E2). That choice matters: an item that straddles the 70% line is treated as A, so the A class always reaches the threshold instead of falling just short. Whichever convention you use, write it down so the next quarter's run is comparable.

Step 4: read the result

The illustrative figures for the importer come out like this.

Two readings follow straight away. First, 24 SKUs carry 71% of sales, so 24 lines are worth a weekly check; the other 176 are not. Second, the 120 C lines sell $180,000 between them, an average of $1,500 each a year. If each one ties up a pallet position and a supplier account, some will not be earning their place. That is a question for the dead-stock work in finding dead stock and slow movers, not for the ranking itself.

Using AI for the spreadsheet work

AI is useful here in three narrow ways: writing the formulas, spotting data problems, and explaining the output to a colleague. It is not useful as a black box that you hand a file and trust. Whichever assistant you use, paste in a sample of the data, not the whole customer list, and remove anything you would not want leaving your business. Check your tool's data and training settings first.

A prompt that produces working formulas

I have an Excel sheet of 200 SKUs sorted by annual net sales, largest first.
Column A = SKU, column B = description, column D = annual net sales (numbers).
Write formulas for: share of total (E), cumulative share (F) and an ABC class (G).
Class rule: A while the cumulative share BEFORE the row is under 70%, B under 90%, C otherwise.
Use plain cell references, not tables or named ranges. Then give me three checks I can run to prove the totals are right.

A sample reply, trimmed and illustrative, gives formulas for E, F and G close to the ones above, followed by three checks. Typical checks are that the last cumulative value equals 100%, that the class counts add to 200, and that the sum of A, B and C sales equals the grand total. Run all three. If the assistant used the cumulative share including the current row, change the class formula yourself; both are defensible but you need to know which one you have.

A prompt that finds data problems before they bite

Here are 40 rows of my SKU totals (SKU, description, annual sales). Look for: duplicate or near-duplicate SKUs that are really the same product, descriptions suggesting a bundle or kit, and anything that looks like a freight or admin charge rather than a product. List each with the row and a one-line reason. Do not change anything.

In the importer's data this kind of prompt would catch, say, two codes for the same olive oil where one is simply a re-coded successor of the other, and a "delivery surcharge" line that had been entered as a stock item. Neither belongs in an ABC ranking of products. Merge the successors, drop the surcharge and rerun. If your Microsoft 365 plan includes Copilot in Excel, open its chat pane and ask the same question; in chat it analyses the workbook without changing it, which is a convenient way to ask about the sheet in front of you, but you still verify what it says.

For more on handing spreadsheet data to an assistant safely and checking what comes back, the ChatGPT in Excel guide goes through it step by step.

Ranking customers the same way

The method is identical, with the customer in place of the SKU. Total net sales, or better gross profit, by customer over the same 12 months, sort, compute cumulative shares and assign classes.

Take the same importer with 64 active accounts. The illustrative result: nine A customers account for $1,260,000 (70%), 15 B customers for $342,000 (19%) and 40 C customers for $198,000 (11%).

Customer ranking carries a risk that SKU ranking does not: concentration. Nine customers producing 70% of revenue means losing one A account removes about 8% of the business. That is worth knowing and worth planning for, but it is not a reason to neglect the rest.

Rank by profit, not only by revenue

Revenue ranks the big accounts that negotiate hardest. Profit ranks the ones that pay you well for the effort. Two customers who each buy $60,000 a year can differ wildly: one takes full pallets on a standing order and pays in 14 days, while the other orders 30 times a year in single cases with free delivery and pays at 60 days. If your system holds costs, rank by gross profit and then take one extra step, subtracting an honest per-order handling cost.

A quick sum, illustrative: a customer who places 120 orders a year, at $9 of pick-and-pack cost per order, costs $1,080 to serve before delivery. If that customer's gross profit is $4,200, the net contribution is $3,120, a very different picture from the headline revenue. Your own per-order cost will differ; measure it from a typical week rather than guessing.

Cross the two rankings

Once both lists exist, a grid tells you more than either ranking alone. Rows are the customer class, columns are the SKU class of what they buy, and each cell counts order lines or value.

A craft brewery supplier, as an illustration, might find that its A customers are the production breweries buying malt and hops in bulk, while its C customers are home-brew shops buying one-off cartons of the same lines. The grid shows at a glance that the home-brew shops are the cost-to-serve risk, and that the bulk buyers are exposed if the A grains run out.

Turn each class into a service rule

This is the part that changes behaviour. For each class, decide what is different about how you buy, count, forecast and serve. Here is a filled-in set for the importer.

The cover figures are illustrations of the pattern, not recommendations: lead times, shelf life and supplier minimums change them completely. Work out your own with the method in setting reorder points with AI, and apply it to A items first.

Rules for customers

Customer rules look different. For A customers: a named account contact, a monthly check-in on forecast orders, first priority in an allocation (when stock is short and you have to share it out) and a quicker quote turnaround. For B customers: a regular order reminder and a quarterly review. For C customers: a minimum order value, standard terms and self-service ordering where possible. Keep this as service design, not favouritism: C customers should still get an accurate price and an honest delivery date.

A distributor that sells to trades gets an additional benefit, because counter staff can see the class on screen. The example in AI counter quotes for an electrical distributor shows how quote speed is itself a service rule.

When the ranking misleads you

ABC analysis is blunt, and the failure modes are predictable. Here is how the first run of a typical distributor goes wrong, and what to do.

A strategic SKU lands in C

A low-volume item may be the one that gets a high-value customer to order everything else from you, such as a specialist fitting that a kitchen-fitter customer cannot finish a job without. Revenue says C, but a stockout on that fitting loses the whole order. Fix it with an override column: a manual "critical" flag that bumps a SKU's service level regardless of class. Keep the list short, or the flag stops meaning anything.

A new SKU looks like a C

A line launched in month 10 has only two months of sales and will rank low. Exclude SKUs with fewer than, say, six months of history from the ranking and review them by hand. Do the same for lines that were out of stock for part of the year, because zero sales while unavailable is not zero demand.

Seasonal lines swing between classes

A brewery supplier's cleaning chemicals might sell evenly all year while its seasonal bottle labels sell in two months. A 12-month ranking will put the labels in B, though in their peak months they behave like A. Rank on 12 months, then run a second ranking on the last 3 months and compare. Items that are B on the year and A on the quarter need A treatment ahead of the season.

The mistake of ranking only by revenue

Imagine a distributor whose top A SKU is a bulk commodity with a 4% gross margin, while a C-class specialist line earns 38%. Ranked by revenue, the commodity gets weekly attention and the specialist line is ordered "when someone remembers", and the stockouts hit the line that earns the profit. The sign that this has happened is a margin report that does not match the ranking. Run a gross-profit ranking beside the revenue one and look for items that move two classes or more.

Adding a second dimension: how steady the demand is

Value says how much an item matters; it does not say how predictable it is. Many distributors add a second letter code based on demand variability, commonly called XYZ analysis. X items sell steadily, Y items vary, and Z items are lumpy or occasional. An item can be AX (valuable and predictable, easy to automate) or AZ (valuable and lumpy, the one that needs a human).

A simple measure is the coefficient of variation: the standard deviation of monthly sales divided by the average monthly sales. In Excel, with 12 monthly figures in B2 to M2:

=STDEV.P(B2:M2)/AVERAGE(B2:M2)

A common convention puts X below 0.5, Y between 0.5 and 1, and Z above 1, but those boundaries are choices, not standards. Try them on your data and see whether the groups look sensible; if they do not, adjust the boundaries rather than forcing a fit. An ABC-XYZ matrix has nine cells, and most distributors only need to act differently on three or four of them. Forecasting errors often start with lumpy Z items being treated like steady ones, as covered in why AI stock forecasts go wrong.

Check the ranking is right before anyone acts on it

Before sharing the result with the warehouse or the sales team, run through these checks. They take about 20 minutes and prevent the embarrassing version of the meeting where someone finds a missing product.

1. The sum of all SKU totals equals the sum of the invoice export.
2. Class counts add up to the number of SKUs, and class values add up to the grand total.
3. The top five SKUs are ones everyone on the team would expect to see there. If an unfamiliar one appears, look it up before believing it.
4. Pick five random SKUs from the C class and confirm their annual sales against the system by hand.
5. Look for SKUs in the A class that were out of stock for more than a month; their true demand is higher than the data shows.
6. Look for SKUs with negative totals. They mean returns exceeded sales or a data error.

Then keep the previous quarter's ranking beside the new one. The most useful output of a repeat run is the movement list: SKUs that jumped a class, customers that dropped one. A customer that slides from A to B over two quarters without anyone noticing is an early warning, and a SKU climbing from C to B is a candidate for a bigger buffer before it becomes a stockout.

A small cadence that keeps it alive

ABC classes go stale when no one owns them. A workable routine for a small distributor takes about an hour a quarter.

• Export the last 12 months and rerun the sheet (the formulas stay; only the data changes).
• Produce the movement list and read it.
• Update the A-class list that buyers and the warehouse use.
• Review the override flags and remove any that no longer apply.
• Write one sentence per class on what changed in how you handled it, so next quarter you can see whether the rule made a difference.

The measure of whether it worked is practical: fewer A-class stockouts, fewer pallets of C stock bought in bulk at a discount and then left, and counts that match the system more often on the lines that matter. If after two quarters none of those has moved, the rules are too vague, not the ranking too crude.

Once A customers are identified, you can also decide who gets quick answers on stock and delivery; answering stock and delivery queries safely with an agent picks that up. And if the ranking shows a heavy tail of slow C lines you can no longer justify, selling the excess to trade buyers is the practical exit (see the further reading below).

Questions distributors ask once the first ranking exists

How often should I rerun an ABC analysis?

Quarterly suits most small distributors, with a quick look at A-class movers monthly. Rerun sooner after a large new customer, a lost account or a supplier change, because one of those can push a dozen SKUs across a boundary. Keep the same 12-month window each time so results compare.

Should I rank by revenue, margin or units?

Rank by gross profit when you have reliable cost data, because revenue alone promotes high-turnover, low-margin lines. Use revenue when costs are shaky, and units picked when warehouse effort is the question. Run two rankings and compare; the SKUs that are A on one and C on the other deserve a conversation.

What do I do with SKUs that have no sales in 12 months?

Give them their own class, often called D or N, instead of leaving them in C. Check whether they are new, seasonal or genuinely dead, then decide to keep, discount or delist. Dead stock is a separate decision from ranking.

Can AI do the whole ABC analysis for me?

AI can write the formulas, build the pivot table and explain the result, but you supply a clean data export and decide the cut-offs and service rules. Always spot-check the top and bottom rows against your system, because a tool can silently drop or double-count rows.

Further reads

• Stock Count Variance: Find Why Your Count and System Disagree — Find why counted and system stock disagree on A items.
• Backorder Notification Email: A Template AI Fills From Stock Data — Tell customers about backorders on short lines.
• MRO Inventory Management: AI-Set Minimums for Maintenance Spares — Set minimums for critical low-value spares.
• AI Tools for Warehousing: Which Suit a Small Warehouse or 3PL? — Match warehouse tools to how your A and C lines move.
• Excess Inventory: How to Sell It to Trade Buyers With AI-Drafted Offers — Clear slow C-class stock by selling it to trade buyers.
• Hardware Store Inventory Management: SKU Clean-Up With AI — Export, de-duplicate and categorise a hardware store's SKUs, then set reorder points from real demand and let AI audit the numbers before you load them.
• Best AI Tool for Pricing Wholesale Goods: Chat Assistant or App? — Chat assistant plus a sheet, an inventory-system price list, or pricing software: the right pick depends mostly on how many SKUs you carry.
• Builders Merchant Software: Trade Accounts, Quotes and Where AI Fits — Buy trade-counter software for accounts, stock and pricing; use AI on the edges: emailed materials lists, statement chasing and stock queries.
• AI for Ecommerce Inventory Management: Reorder Points and Stockouts — A small-store SKU example works through average demand, safety stock, stock position and a spreadsheet check before ordering.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: ABC analysis and Pareto definitions (general reference, which notes there are no fixed class thresholds); Microsoft Support pages for SUMIFS, STDEV.P and Get started with Copilot in Excel (checked November 2026).