How does this template classify items into A, B and C?

It ranks items by annual usage value (annual usage x unit cost), works out the share of the total held by the items ranked above each one, and compares that with your cut-offs. With cut-offs of 80% and 95%, an item whose higher-ranked items hold 76% is A, one that starts at 84% is B, and one that starts at 96% is C. The top item is always A, and the item that crosses a cut-off belongs to the class it starts in.

What cut-offs should I use?

There is no standard; 70% to 80% for A and 90% to 95% for B are common choices. Pick the ones that give a number of A items your team can really control. In the example, an 80% cut-off makes 7 of 40 items A; lowering it to 70% would make the A class 5 items, and the curve on the Dashboard shows how value builds up so you can see the effect before you change it.

Does it match the ABC Inventory Value calculator?

Yes. The calculator's example is 100 units a year at 45 each in a portfolio of 100,000 a year: an annual usage value of 4,500 and a share of 4.5 percent. The Verify sheet includes that item in a test list whose total is 100,000 and gets the same two numbers; the workbook adds the rank, cumulative share and class for every item at once.

How do I turn the classes into a cycle-count plan?

Set counts per year per class on Setup and each item gets its frequency. In the example, 7 A items counted 12 times a year, 8 B items 4 times and 25 C items once give 84 + 32 + 25 = 141 counts a year, about 3 a week over 50 weeks. The Cycle Count Tracker template then records the counts and their accuracy.

Why show stock value as well as usage value?

Because the class says how important an item's usage is, not whether its stock is right. In the example the C items carry 4.5 percent of usage value but hold 21.8 percent of stock value, turning about 1.9 times a year. That is cash tied up in low-usage items, and a reason to cut their order quantities or minimums.

Can I use it without Excel?

Yes: a CSV with live formulas of the Item List opens in any spreadsheet, with each item's usage value, share, rank, cumulative share, class, stock value and counts per year as formulas against the cut-offs and count policy listed below the rows. The dashboard, charts, dropdowns and self-check are in the .xlsx.

Planning and Supply Chain

ABC Inventory Analysis Excel Template

Paste your item list and the workbook ranks every item by annual usage value, splits it into A, B and C classes at the cut-offs you set, and shows where usage value and stock on hand sit.

Overview

This ABC inventory analysis template is a working Excel workbook for inventory planners, materials managers, buyers and warehouse leads who need to decide which items deserve the most control. It ships with example data for a fictional valve maker with 40 items, from castings and stainless bar to seals, fasteners and packaging, so the ranking, classes, dashboard and charts are populated the moment you open it; paste your own item list and everything recalculates.

Each item's annual usage value is its annual usage times its unit cost, the same number the ABC Inventory Value calculator on this site gives for one item. The workbook ranks the items by that value, works out the cumulative share of the total, and assigns a class by where each item starts on the cumulative curve: A while the items ranked above it hold less than the A cut-off, B while they hold less than the B cut-off, and C for the rest. The cut-offs are yours to set on Setup; the ranking uses RANK and COUNTIFS with an item-code tie-break, so it works in every version of Excel and LibreOffice and for any number of items.

In the example, 7 of the 40 items (17.5 percent) carry 80.0 percent of the 2.61 million of annual usage value, while the 25 C items carry only 4.5 percent of usage value but hold 21.8 percent of the stock value. Their stock turns about 1.9 times a year against about 12.6 for the A items. With a count policy of 12, 4 and 1 counts a year for A, B and C, the list needs 141 item counts a year.

What this template includes

Item List table: item, description, category, annual usage, unit cost and units on hand, one row per item (paste an ERP usage and stock export of any length)
Per item: annual usage value, share of the total, rank, cumulative share, ABC class, stock value and the cycle counts per year your class policy implies
Your own A and B cut-offs on Setup (80% and 95% in the example), with a check that B is above A, and your cycle-count frequency per class
Dashboard KPI tiles: annual usage value, the A class's share of usage value and of items, and the share of stock value held in C items
Two native charts: the ABC curve (cumulative share of usage value against share of items, with the cut-offs) and the share of items, usage value and stock value by class
A by-class table (items, usage value, stock value, value turns, counts per year) and the ten items with the highest usage value
Check column that names problems in words: category not on the list, an item listed twice, a unit cost of 0, a missing value
Method sheet with every formula, and a Verify sheet that re-checks the ranking and classes against an independent reference, including the ABC Inventory Value calculator's example (Self-check: PASS)

Suggested use case

Use it to decide which items get tight control: how often each class is counted, how closely it is reviewed and forecast, and where safety stock and buyer attention should go, and to show where cash is tied up in slow, low-value items.

How to use it

On Setup, enter your company name and currency label, check the A and B cut-offs, and set how many times a year each class is cycle counted.
On Item List, paste your items with annual usage (last 12 months or a forecast), unit cost and units on hand.
Fix anything the Check column flags; rank, cumulative share and class update for every item as you paste.
Open the Dashboard: read the ABC curve, compare what each class holds, and start with the top ten.
Use the class and the counts per year in your cycle-count plan and review policies; move a critical item up a class by hand and say why in Notes.

Frequently Asked Questions

How does this template classify items into A, B and C?: It ranks items by annual usage value (annual usage x unit cost), works out the share of the total held by the items ranked above each one, and compares that with your cut-offs. With cut-offs of 80% and 95%, an item whose higher-ranked items hold 76% is A, one that starts at 84% is B, and one that starts at 96% is C. The top item is always A, and the item that crosses a cut-off belongs to the class it starts in.
What cut-offs should I use?: There is no standard; 70% to 80% for A and 90% to 95% for B are common choices. Pick the ones that give a number of A items your team can really control. In the example, an 80% cut-off makes 7 of 40 items A; lowering it to 70% would make the A class 5 items, and the curve on the Dashboard shows how value builds up so you can see the effect before you change it.
Does it match the ABC Inventory Value calculator?: Yes. The calculator's example is 100 units a year at 45 each in a portfolio of 100,000 a year: an annual usage value of 4,500 and a share of 4.5 percent. The Verify sheet includes that item in a test list whose total is 100,000 and gets the same two numbers; the workbook adds the rank, cumulative share and class for every item at once.
How do I turn the classes into a cycle-count plan?: Set counts per year per class on Setup and each item gets its frequency. In the example, 7 A items counted 12 times a year, 8 B items 4 times and 25 C items once give 84 + 32 + 25 = 141 counts a year, about 3 a week over 50 weeks. The Cycle Count Tracker template then records the counts and their accuracy.
Why show stock value as well as usage value?: Because the class says how important an item's usage is, not whether its stock is right. In the example the C items carry 4.5 percent of usage value but hold 21.8 percent of stock value, turning about 1.9 times a year. That is cash tied up in low-usage items, and a reason to cut their order quantities or minimums.
Can I use it without Excel?: Yes: a CSV with live formulas of the Item List opens in any spreadsheet, with each item's usage value, share, rank, cumulative share, class, stock value and counts per year as formulas against the cut-offs and count policy listed below the rows. The dashboard, charts, dropdowns and self-check are in the .xlsx.