Run an ABC-XYZ Inventory Classification

A warehouse with two thousand stock items cannot manage every one with the same care. ABC analysis sorts items by value — the few A items that make up most of the money deserve the closest attention — and is covered on its own in Run a Pareto Analysis on Excel Data with pandas. But value is only half the story. An A item that sells a steady 200 units every month is easy to plan; an A item that sells 50 one month and 600 the next is where stock-outs and excess stock come from. XYZ analysis adds that second dimension — how variable demand is — and the combined ABC-XYZ matrix tells a planner which items can be replenished automatically and which need judgement. This page builds both classifications from monthly demand data with pandas, puts them in the nine-cell matrix, attaches a stocking policy to each cell, and writes the result to Excel. It belongs to Creating Pivot Tables from Excel Data.

Prerequisites

pip install pandas openpyxl

demand.xlsx has twelve months of demand, October 2025 to September 2026, for 240 stock items: SKU, month, units issued and unit cost. Some items sell steadily, some seasonally, some in occasional large lumps, and a handful were introduced only in the last three months.

Step 1 — ABC by consumption value

ABC classes rank items by annual consumption value — units times cost — and cut the ranking where the cumulative share of total value reaches set thresholds, commonly 80% and 95%:

import pandas as pd

demand = pd.read_excel("demand.xlsx")
demand["Value"] = demand["Units"] * demand["Unit cost"]
items = demand.groupby("SKU").agg(units=("Units", "sum"), value=("Value", "sum"), cost=("Unit cost", "first"))
items = items.sort_values("value", ascending=False)
items["cum_share"] = items["value"].cumsum() / items["value"].sum()
items["ABC"] = pd.cut(items["cum_share"], [0, 0.80, 0.95, 1.0], labels=["A", "B", "C"], include_lowest=True)
print(items.groupby("ABC", observed=True).agg(items=("value", "size"), value_share=("value", lambda v: v.sum() / items["value"].sum())).round(3))

Sorting by value and taking the cumulative share puts the most valuable items first; pd.cut assigns A to items within the first 80% of value, B to the next 15% and C to the rest. The usual Pareto shape appears: 59 of the 240 items are A and account for 80% of the money, while 109 C items — almost half the range — account for 5%. The thresholds are conventions, not laws — some businesses use 70/90, or classify by margin or by number of picks instead of value — so keep them as named settings.

Step 2 — XYZ by demand variability

XYZ classes measure how predictable demand is, using the coefficient of variation (CV) of monthly demand — the standard deviation divided by the mean:

monthly = demand.pivot_table(index="SKU", columns="Month", values="Units", aggfunc="sum", fill_value=0)
stats = pd.DataFrame({"mean": monthly.mean(axis=1), "std": monthly.std(axis=1, ddof=0),
                      "months_with_demand": (monthly > 0).sum(axis=1)})
stats["cv"] = (stats["std"] / stats["mean"]).where(stats["mean"] > 0)
stats["XYZ"] = pd.cut(stats["cv"], [0, 0.5, 1.0, float("inf")], labels=["X", "Y", "Z"], include_lowest=True)
items = items.join(stats)
print(items["XYZ"].value_counts().sort_index().to_dict())
print(items.groupby("XYZ", observed=True)["cv"].agg(["min", "max"]).round(2))

A CV below 0.5 means monthly demand stays within about half its average — class X, predictable. Between 0.5 and 1.0 is Y — variable, often seasonal. Above 1.0 is Z — erratic, typically items that sell nothing most months and a lot occasionally. The pivot uses fill_value=0, because a month with no issues is real zero demand, which matters for the CV: leaving such months out would make lumpy items look steady. As with ABC, the cut-offs are conventions to adjust to your business.

Step 3 — Items with too little history

A CV from three months of data is meaningless, and items introduced recently would otherwise land in whatever class their few months happen to suggest. Flag them separately before building the matrix:

first_month = monthly.gt(0).idxmax(axis=1)
items["first_month"] = first_month
items["new_item"] = items["first_month"] >= "2026-06"
items.loc[items["new_item"], "XYZ"] = None
print("new items held out of XYZ:", items.index[items["new_item"]].tolist())

Items whose first demand was in the last four months are marked as new and their XYZ class is cleared. They still have an ABC class — their value so far is real — but planning them by variability waits until there is enough history, typically six to twelve months. Listing them explicitly also stops them dragging down the X/Y/Z counts in a way nobody can explain. Look at the list rather than trusting it blindly: besides the eight genuinely new items, it catches SKU-0018, an established lumpy item whose first lump of the year happened to fall in June. A first-demand date cannot tell a late starter from an item that sells rarely, so where the item master holds a launch date, use that instead.

Step 4 — The ABC-XYZ matrix and a policy per cell

Cross-tabulate the two classes to get the nine-cell matrix, with item counts and each cell's share of total value:

established = items[~items["new_item"]]
counts = pd.crosstab(established["ABC"], established["XYZ"])
value_share = (pd.crosstab(established["ABC"], established["XYZ"], values=established["value"], aggfunc="sum")
               .div(items["value"].sum()).mul(100).round(1))
print(counts.to_string())
print(value_share.to_string())

POLICY = {
    ("A", "X"): "automatic replenishment, low safety stock",
    ("A", "Y"): "forecast-driven, review monthly",
    ("A", "Z"): "manual planning, order to demand",
    ("B", "X"): "automatic replenishment",
    ("B", "Y"): "forecast-driven, moderate safety stock",
    ("B", "Z"): "review quarterly, consider make-to-order",
    ("C", "X"): "automatic, larger order quantities",
    ("C", "Y"): "simple reorder point",
    ("C", "Z"): "stock on demand or discontinue",
}
items["Class"] = items["ABC"].astype(str) + items["XYZ"].astype(str).replace("nan", "?")
items["Policy"] = [POLICY.get((a, x), "new item: review after 6 months") for a, x in zip(items["ABC"], items["XYZ"])]
print(items["Class"].value_counts().head(5).to_dict())

The count matrix shows how many items sit in each cell; the value-share matrix shows where the money is. Here 47 AX items carry 64% of all value, and 10 AZ items another 14.6%. Together the two matrices guide effort: AX items are valuable and predictable, so they can be replenished automatically with modest safety stock; AZ items are valuable and erratic — the cell that needs a planner's attention; CZ items tie up little money and are hard to forecast, so they are candidates to stock only on demand, or to discontinue. The policy table is the deliverable a warehouse team actually uses, and keeping it as data makes it easy to adapt. The crosstab technique itself is covered in Build a Crosstab from Excel Data with pandas.

Step 5 — Write the matrix and the item list to Excel

The planner's workbook has the matrix with counts and value shares, and the full item list with class and policy:

from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.styles import Font

with pd.ExcelWriter("abc_xyz.xlsx", engine="openpyxl") as xw:
    counts.to_excel(xw, sheet_name="Matrix", startrow=1)
    value_share.to_excel(xw, sheet_name="Matrix", startrow=7)
    ws = xw.sheets["Matrix"]
    ws["A1"], ws["A7"] = "Items per cell", "Share of total value (%)"
    ws["A1"].font = ws["A7"].font = Font(bold=True)
    ws.conditional_formatting.add("B9:D11", ColorScaleRule(start_type="min", start_color="FFFFFF", end_type="max", end_color="4338CA"))
    out = items.reset_index()[["SKU", "ABC", "XYZ", "Class", "value", "cv", "months_with_demand", "new_item", "Policy"]]
    out.round({"value": 2, "cv": 2}).to_excel(xw, sheet_name="Items", index=False)
    xw.sheets["Items"].column_dimensions["I"].width = 44
print("value share by cell (%):", value_share.stack().sort_values(ascending=False).head(3).to_dict())

The value-share matrix gets a colour scale so the cells holding most of the money stand out; the Items sheet lists every SKU with its classes, the figures behind them and the recommended policy, ready to filter by class. Run the classification monthly or quarterly on a rolling twelve months: items move between classes as demand changes, and an item drifting from AX to AZ is an early warning worth reacting to.

Common pitfalls

• ABC alone. Value without variability treats steady and erratic items the same.
• Leaving zero-demand months out of the CV. Lumpy items then look steady; fill them with 0.
• Classifying new items on a few months. Hold them out until there is enough history.
• Thresholds treated as fixed rules. 80/95 and 0.5/1.0 are conventions; tune them.
• A matrix with no policy. The point is what to do with each cell.

Conclusion

An ABC-XYZ classification combines value and predictability. ABC classes come from each item's cumulative share of annual consumption value; XYZ classes from the coefficient of variation of monthly demand, with zero-demand months included. Hold items with too little history out of XYZ, cross-tabulate the classes into a nine-cell matrix of counts and value shares, attach a stocking policy to each cell, and write the matrix and item list to Excel so planners can act on it — then rerun it regularly to catch items changing class.

Frequently asked questions

What is ABC-XYZ analysis? A classification of stock items by consumption value (ABC) and by demand variability (XYZ). Combining them in a 3×3 matrix shows which items are valuable and predictable and which are valuable but erratic.

How do I calculate XYZ classes in pandas? Pivot monthly demand per item with zeros for months without demand, compute the coefficient of variation as the standard deviation divided by the mean, and cut it at thresholds such as 0.5 and 1.0.

Why include months with zero demand in the CV? They are real demand of zero. Leaving them out makes items that sell only occasionally look steady.

How should new items be classified? Give them an ABC class from their value so far but hold back the XYZ class until they have six to twelve months of history.

Related

• Up one level: Creating Pivot Tables from Excel Data — summaries and crosstabs.
• Run a Pareto Analysis on Excel Data with pandas — the ABC half on its own.
• Build an Inventory Reorder Report — turning the policies into orders.
• Calculate Weighted Average Cost Inventory Valuation — valuing the same stock.