ABC analysis ranks customers by revenue or margin, accumulates their share, and cuts the list into tiers: A for the few that make most of the revenue, B for the middle, C for the long tail. This page gives the Excel formulas for the ranked table, the cumulative share and the tier, shows how to check whether the 80/20 rule holds for a given base, explains why the ranking should use contribution and potential as well as current revenue, and sets out what each tier should be owed in coverage so that the analysis changes what the sales team does.
ABC analysis is the oldest customer ranking there is: sort by revenue, accumulate, cut. It takes ten minutes in Excel. The useful part is what is done with the tiers afterwards.
Sheet Invoices: customer in A, invoice date in B, amount in C, and gross margin in D if available. On Calc: period start B1, end B2, A cut point 0.8 in B3, B cut point 0.95 in B4.
Customer list in A8 down with UNIQUE, or Remove Duplicates. Revenue, B8:
=SUMIFS(Invoices!C:C,Invoices!A:A,A8,Invoices!B:B,">="&$B$1,Invoices!B:B,"<="&$B$2)
Microsoft 365 can produce the sorted pair in one step, in D8:
=SORTBY(A8:B5000,B8:B5000,-1)
In older Excel, copy the two columns as values and sort descending. The steps below assume the sorted customer is in column D and revenue in column E.
Share, F8:
=E8/SUM($E$8:$E$5000)
Cumulative share, G8:
=SUM($F$8:F8)
H8:
=IF(G8-F8<$B$3,"A",IF(G8-F8<$B$4,"B","C"))
Using the cumulative share before this customer, G8-F8, puts the customer that crosses the line into the higher tier, so tier A reaches at least 80 percent.
The share of customers in tier A:
=COUNTIF(H8:H5000,"A")/COUNTA(D8:D5000)
| Tier | Customers | Share of customers | Share of revenue |
|---|---|---|---|
| A | 190 | 12% | 80% |
| B | 330 | 21% | 15% |
| C | 1,060 | 67% | 5% |
Twelve percent of customers make 80 percent of revenue. That number decides how coverage can be organised.
=SUM(E8:E5000)-SUMIFS(Invoices!C:C,Invoices!B:B,">="&$B$1,Invoices!B:B,"<="&$B$2)
Zero. And roll subsidiaries up to parents first, or a group buying through five entities lands five times in tier B.
Run the same three steps on two more columns.
Contribution. Gross margin less cost to serve. A customer can be A on revenue and C on contribution. See gross margin versus contribution.
Potential. What similar customers buy, less what this one buys. A small customer with a large gap is worth more attention than its revenue suggests. See what is a good share of wallet.
Then cross the tiers:
| Revenue tier | Potential tier | Reading | Action |
|---|---|---|---|
| A | A | Large and still growing | Named owner, senior sponsor, highest cadence |
| A | C | Large and fully developed | Protect; efficient service; watch the trend |
| C | A | Small with us, large elsewhere | The growth list; most under-covered group in any book |
| C | C | Small and fully developed | Low-cost service; portal; no field time |
| A on revenue | C on contribution | Over-served | Minimum order, delivery days, pricing review |
The third row is what revenue-only ABC never shows, and usually where the money is.
A tier is only useful with a service level attached. State it as a cadence and measure coverage against it.
| Tier | Owner | Contact cadence | Reviewed |
|---|---|---|---|
| A | Named field rep; senior sponsor for the top twenty | Every 30 days | Quarterly account plan |
| B | Field or inside rep | Every 90 days | Twice a year |
| C with high potential | Inside rep | Every 60 days until developed | Quarterly list |
| C | Inside team or portal | Every 180 days, or on order | Annually |
Coverage at cadence, the share of each tier actually touched within what it is owed, is then the measure that tells you whether the tiering changed anything. The account coverage guide builds it, and segments and tiers covers the difference between what a customer is and what a customer gets.
Keep last quarter's tier in column I beside this quarter's in H.
=IF(I8="","new",IF(H8=I8,"",I8&" to "&H8))
A to B is a declining major account. C to B is one that is growing without anyone having planned it. Both are short lists worth a conversation.
The chart is the deliverable. A Pareto curve on a slide, and nothing changes in who gets called.
Revenue only. The most attention to the customers who would buy anyway.
Tiers with no cadence. A label on the account record that means nothing operationally.
Boundaries that flicker. Monthly recalculation moves forty accounts between A and B on noise. Recalculate quarterly and use a buffer.
The ranked table is a ten-minute job. Three tierings crossed, parents rolled up, cadence attached and coverage measured weekly against it is not; see the four signs a spreadsheet is no longer enough. Covirage computes the tiers on revenue, contribution and potential from the exports a business already has, and reports coverage at cadence per tier each week.
Roughly, and it is worth measuring. In most B2B bases somewhere between 10 and 25 percent of customers make 80 percent of revenue. The exact figure for your base tells you how many accounts can be given real attention. If 8 percent of customers make 80 percent, named-account coverage is feasible for all of tier A; if it is 30 percent, tier A needs splitting.
All three, as separate columns, and look at where they disagree. A customer that is A on revenue and C on contribution is being over-served. One that is C on revenue and A on potential, because similar customers buy five times as much, is the growth list. Revenue-only tiering gives the most attention to customers who need the least persuading.
Quarterly is enough for the tiers themselves; customers near a boundary should not flip every month. Review movers: customers that changed tier since last time, in either direction, are the interesting rows.