ABC-XYZ analysis in Excel combines two ways to classify inventory: ABC ranks items by their annual usage value, while XYZ groups them by how predictable their demand is. To build it, calculate annual demand and value for each item, sort items by value, calculate their cumulative value share, and measure demand variability with the coefficient of variation.
What ABC-XYZ analysis shows
ABC and XYZ answer different questions. ABC asks which items account for the largest share of inventory consumption value. XYZ asks whether an item’s demand is steady, variable, or irregular. Combining the classifications creates groups such as AX, BY, and CZ, which can help distinguish high-value, predictable items from low-value, erratic ones.
ABC is usually based on annual usage value: annual quantity consumed multiplied by the item’s unit cost. It is not automatically the same as sales revenue, profit, stock on hand, or purchase price. Choose the measure that fits the decision, and apply it consistently. For example, a company evaluating inventory investment might use acquisition cost, while a service operation might use the cost of parts consumed.
XYZ is commonly based on the coefficient of variation, or CV: the standard deviation of demand divided by its average. A low CV indicates demand stayed relatively close to its average during the selected periods; a high CV indicates larger fluctuations. The method is a planning aid, not a forecast or a universal rule for setting stock levels.
Prepare the worksheet and choose a period
Use one row per item and one column per comparable time period. A practical starting point is a full year of monthly demand, though weekly or quarterly periods may suit some businesses. Use the same dates and period length for every item. In Microsoft Excel for Microsoft 365 on Windows, arrange the columns as follows:
- A: SKU or item identifier
- B: unit cost
- C:N: demand in 12 consecutive months
- O: annual demand
- P: annual usage value
- Q: demand CV
- R: cumulative value share
- S: ABC class
- T: XYZ class
- U: combined class
Put headers in row 1 and items in rows 2 through 101 in the examples below. Adjust the row limits to match your data. Use actual quantities consumed or sold, rather than shipments into inventory, unless that is the measure your analysis is meant to classify. Record a true month with no demand as zero. Leave a cell blank only when the data is genuinely missing; a blank is not necessarily equivalent to zero in averages and standard deviations.
Calculate annual demand and usage value
First total each item’s demand across the 12 months. With monthly demand in C2:N2, enter this in O2 and fill it down through O101: =SUM(C2:N2). The result for an item with monthly quantities totaling 120 is 120 units for the year.
Next multiply annual demand by unit cost. With unit cost in B2 and annual demand in O2, enter this in P2 and fill it down: =B2*O2. If an item costs $10 per unit and annual demand is 120, its annual usage value is $1,200. Make sure the cost and quantity use compatible units and currency. If costs changed substantially during the year, a single current unit cost may not represent actual consumption value; consider using a suitably weighted cost if the purpose requires it.
For a quick example of the ranking, suppose three items have annual usage values of $6,000, $2,000, and $1,000. Their total is $9,000, so their cumulative shares in descending order are 66.7%, 88.9%, and 100%. This ranking is the basis for the ABC cutoffs.
Assign ABC classes in Excel
Sort the complete data range by annual usage value in descending order, so the highest-value item is first. Select the table, then choose Data > Sort and sort by the annual usage value column from largest to smallest. Sort the whole table, not just column P, so each item’s demand and cost remain on the correct row. If the data is an Excel table, use its column sort control and confirm the entire table is sorted.
With sorted values in P2:P101, enter this in R2 and fill down: =SUM($P$2:P2)/SUM($P$2:$P$101). This calculates the running total divided by total value. Format column R as Percentage. In the example above, the shares appear as 66.7%, 88.9%, and 100%.
For a common starting convention of A through 80%, B through 95%, and C for the remainder, enter this in S2 and fill down: =IF(R2<=80%,"A",IF(R2<=95%,"B","C")). Under this rule, an item receives the class for the cumulative share after it is added. In the example, the $6,000 item is A, while the $2,000 item takes the cumulative share past 80% and is B; the $1,000 item is also B because the cumulative share is still below 95%.
Those cutoffs are conventions, not a required standard. Some organizations use different boundaries or assign the item that crosses a cutoff to the preceding class. Decide which rule makes sense for your operation, document it, and keep it consistent when comparing periods. If one exceptionally valuable item already exceeds the A threshold, the formula above will place it in B; adjust the rule if your policy requires the first ranked item to be A.
Measure demand variability for XYZ
Calculate CV from the monthly demand observations, not from annual usage value. With monthly demand in C2:N2, enter this in Q2 and fill it down: =IF(AVERAGE(C2:N2)=0,"",STDEV.P(C2:N2)/AVERAGE(C2:N2)). Format the result as a percentage. The formula returns a blank when average demand is zero, avoiding division by zero; review such items separately rather than treating them as ordinary X, Y, or Z items.
Here, STDEV.P treats the 12 observed months as the complete period being analyzed. If the months are only a sample of a longer process and you deliberately want a sample standard deviation, STDEV.S is the alternative. For a steady item with demand of 10 units in every month, the CV is 0%. For an item with eleven months at zero and one month at 120, the average is 10 and the population standard deviation is about 33.17, giving a CV of about 331.7%. That signals highly irregular demand, even though both items could have the same annual quantity.
A simple, adjustable XYZ convention is X for CV up to 10%, Y for CV above 10% and up to 25%, and Z above 25%. With the CV in Q2, enter this in T2 and fill down: =IF(Q2="","N/A",IF(Q2<=10%,"X",IF(Q2<=25%,"Y","Z"))). These thresholds are examples, not universal definitions. Set boundaries that reflect your business and the time interval used; changing from monthly to weekly observations can change the measured variability.
Combine the classes and interpret the result
With ABC in S2 and XYZ in T2, enter this in U2 and fill down: =S2&"-"&T2. The result might be A-X, B-Y, or C-Z. In general, AX items have high annual usage value and comparatively steady demand. CZ items have lower usage value and high variability. The other combinations matter too: an AZ item may be valuable but difficult to predict, while a CX item may be inexpensive and steady.
Use the matrix to prompt appropriate review, not to apply a single automatic stocking rule. A team might prioritize accurate records and frequent review for AX items, investigate causes of variability for AZ items, and use simpler controls for low-value items. Actual decisions also depend on supplier lead time, minimum order quantities, service targets, shelf life, obsolescence, criticality, and the consequences of a stockout. A low-value spare part can still be essential to safety or production.
To make the groups easy to review, use Home > Conditional Formatting to apply colors to the combined-class column, or filter the table by column U. Keep the numeric CV and cumulative share visible: a label alone hides whether an item sits just inside a boundary or far from it.
Common problems and limitations
- Sorting only the value column: this disconnects values from their SKUs. Always sort the full range or table.
- Inconsistent cost or units: mixed currencies, pack sizes, or units of measure can distort the ABC ranking. Standardize them before calculating.
- Missing data recorded as zero: this makes demand look lower and may distort CV. Distinguish a genuine zero-demand period from an unknown figure.
- Intermittent demand: CV can become very large or unstable when average demand is small. For slow-moving items, inspect the count and timing of nonzero periods as well as the CV.
- Seasonality and unusual events: a seasonal item may have a high monthly CV even when its demand pattern repeats each year. Compare like periods across years or analyze seasonality separately when relevant.
- Cutoff effects: small changes in values or chosen thresholds can move an item between classes. Treat groups as a screening tool, and examine important boundary cases.
ABC-XYZ analysis also depends on the selected time window. A single year can be distorted by a launch, a shutdown, a one-time project, or a supply shortage that prevented sales. If demand was constrained by stockouts, recorded sales may understate what customers wanted. Keep a note of exceptional events and rerun the analysis on a regular schedule, such as quarterly or annually, using comparable periods.
Frequently Asked Questions
What does ABC-XYZ analysis mean?
ABC classifies items by their share of total annual usage value, and XYZ classifies them by demand variability. Combining the letters gives a two-part inventory segment, such as AX or CZ.
What formula is used for XYZ analysis in Excel?
A common measure is coefficient of variation: standard deviation divided by average demand. For monthly values in C2:N2, an Excel formula that handles a zero average is =IF(AVERAGE(C2:N2)=0,"",STDEV.P(C2:N2)/AVERAGE(C2:N2)). Choose thresholds for X, Y, and Z that fit your data.
Are ABC and XYZ cutoff percentages fixed?
No. Boundaries such as 80% and 95% for ABC, or 10% and 25% CV for XYZ, are practical conventions. Different industries and organizations can use other cutoffs, so state the chosen rule in your workbook.
Can I classify an item with no demand?
Not meaningfully with a CV when its average demand is zero, because the calculation divides by the average. Flag it for review, check whether the zeros are accurate, and decide whether it should remain in the analysis.
Does an A or Z classification tell me how much stock to order?
No. The classes summarize value and variability; they do not calculate a reorder point or order quantity. Those decisions also require factors such as lead time, target service level, supplier constraints, and the cost of holding or running out of stock.
Related
- Excel Slicers: What They Do and How to Use Them
- Excel Slicers: How to Create and Use Them
- Slicer Command in Excel: How to Add and Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Timesheet Hours Formula: Calculate Time WorkedTo calculate a shift in decimal hours in Excel, put the start time in B2 and end time…
- Excel Formula for First Day of Month: ExamplesIf the date you want to use is in cell A2, enter =DATE(YEAR(A2),MONTH(A2),1) to return the first day…
- Excel Date Subtraction: Formulas, Days, and WorkdaysTo subtract one date from another in Excel, subtract the earlier date cell from the later one: if…
- Excel Data Validation INDIRECT: Dependent Drop-DownsYes. In Excel, use INDIRECT in a data-validation list to make a drop-down depend on a selection in…
- How to Select a Filter in Excel Using the KeyboardTo select a filter in Excel with the keyboard, turn on AutoFilter with Ctrl+Shift+L, move to the column…
- Excel Timesheet Formulas for Overtime, Breaks, and PayFor a basic Excel timesheet, put a shift’s start time in B2, end time in C2, and unpaid…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.