ABC inventory analysis for a practical count schedule

Your count team has time for only part of the warehouse this week. ABC inventory analysis helps you choose where to focus by ranking items on a stated measure and grouping them into priority classes. For a usage-value approach, multiply each item’s annual quantity used by its unit cost, sort the results from highest to lowest, and assign classes using cumulative value thresholds.

Use that result as one input to the count schedule. A low-cost seal can stop a production line even when it contributes little to annual inventory value. Keep a separate criticality flag, review unreliable records, and make the reason for each scheduling exception visible. The classification should help you explain the work assigned to your counters.

1. Choose the ranking measure before exporting data

As the inventory owner, decide what the ranking is meant to control. Annual usage value highlights the cost associated with the quantities consumed over a period. Sales value highlights revenue contribution. On-hand value describes stock held at a point in time. These measures answer different questions, so two reports can assign different letters to the same item without either calculation being wrong.

Microsoft’s Business Central ABC report uses sales amounts in local currency and configurable class thresholds. That documented report is an example of a sales-based approach. The worked example below uses annual usage value instead; do not expect its results to match a sales-based report.

Write a short definition beside the export: included warehouses, date range, usage transaction types, costing basis, currency, and treatment of returns. Have the responsible finance or inventory owner approve the cost basis. For a multi-site operation, decide whether the job is to rank items across the company or item-location pairs within each warehouse. A company-wide class can hide a part that is rarely used everywhere except one busy site.

2. Prepare comparable item records

Ask the data owner for item identifiers, descriptions, base units, qualifying usage quantities, unit costs, and source references. Keep the original transaction detail available behind the totals. Convert cases and individual units onto the same basis before multiplying quantity by cost. A cost per case applied to a quantity in individual units would overstate the ranking value.

Check renamed items and duplicate codes before combining exports. Count a qualifying consumption once; an internal transfer should not become another consumption merely because two warehouses recorded its departure and arrival. Your transaction rule should identify the movements included. The inventory reconciliation process covers the identity, unit, and movement checks needed when records disagree.

Keep missing costs, unexplained negative quantities, and unresolved mappings in an exception list. A missing cost is not a zero-cost item. Record how much of the selected population was excluded and why, because a ranking over incomplete records can look finished while omitting expensive stock. Preserve a dated snapshot so a reviewer can reproduce the result after source records change.

3. Calculate annual usage value and cumulative share

The following hypothetical example uses five items, a single currency, and one consistent unit-cost basis. Annual usage value equals annual units used multiplied by cost per unit. Total usage value is $100,000. Each item’s share is its usage value divided by that total; cumulative share adds the shares in descending value order.

For this example, class A contains the leading items through 80% of value, class B contains the next items through 95%, and class C contains the remainder. Motors and valves therefore form A, seals form B, and brackets and labels form C. These thresholds are an illustrative policy. They do not establish that every business has the same distribution or should use the same boundaries.

Real data may cross a boundary partway through an item. One explicit policy is to keep the item that reaches or crosses a threshold in the group it completes, then start the next group with the following item. A single item worth 90% would remain A under that policy. Also decide how tied values are handled, such as keeping tied items together. Store the rule with the result rather than letting spreadsheet row order decide silently.

Make the spreadsheet calculation reviewable

For this five-row example in Excel or Google Sheets, put annual units in column B and unit cost in C, with data in rows 2 through 6. In D2 enter =B2*C2, then fill down. Sort the whole data range by column D descending so item names stay attached to their numbers. In E2 enter =SUM($D$2:D2)/SUM($D$2:$D$6) and fill down, displaying E as a percentage.

Assign the example’s classes after reviewing the cumulative shares against the declared boundary rule. Check that column D totals $100,000 and the last cumulative percentage is 100%. These formulas assume complete numeric inputs and a positive total. Resolve invalid rows before classification, and extend the ranges when adding items; an omitted final row can leave the sheet calculating without including all stock.

4. Convert classes into a reviewed count schedule

Have the warehouse manager set count frequencies that fit the team’s capacity and required coverage. Oracle’s ABC class documentation describes using classes to count A items more frequently than B items. It does not supply a universal frequency for your operation. Document the chosen schedule and assign someone to review overdue work.

Before releasing counts, review parts with production-critical uses, repeated discrepancies, theft exposure, or difficult pack conversions. A critical bracket can receive more frequent checks while retaining its calculated C class. Keeping the calculated class and the operating exception in separate fields preserves both explanations. The cycle counting process covers physical observation, transaction cutoffs, recounts, and adjustment approval.

5. Review changed classifications before acting

Compare each new run with the prior snapshot. If a part moves from C to A, inspect whether usage increased, cost changed, the scope expanded, or a mapping was corrected. Choose a review cadence that matches how much these inputs change, and retain the effective date of any revised schedule. Seasonal demand and recently introduced products need context beyond a trailing annual total.

ABC classes alone do not calculate an order quantity or a stockout probability. Replenishment also needs demand, lead time, and your approved buffer policy. Use the reorder point formula for that separate calculation. Likewise, a low usage value does not establish that inventory should be written off; condition, future need, and accounting policy require their own review.

Where Permute fits in inventory classification

Permute sells connected business data, custom applications, and governed analysis. We can help scope a repeatable classification report from permitted inventory and usage records. Match item identifiers under declared rules, retain source references, and leave unresolved costs or units visible for review.

We can apply your chosen ranking measure and class policy to the same input snapshot. An inventory manager can inspect the records behind a changed class under current permissions, while a reviewer can see which rule produced it. Keep the approved counting exceptions alongside the calculated result so the report supports a decision someone can check.

Keep inventory policy and physical checks with your team

We do not choose accounting treatments, perform physical counts, or infer which low-cost parts are critical to production. This example does not promise automatic purchasing or warehouse-system updates. Confirm supported access and refresh frequency during scoping, and have the responsible people approve thresholds and schedule changes.

Give each scheduled count an explainable priority

ABC inventory analysis gives your count team a repeatable value ranking when its inputs and boundary rules are explicit. Check the records before assigning classes, then review operational risks before setting frequency. Keep exceptions separate from the calculated letters. The resulting schedule should explain why each item is due for a count and who approved that choice.

Questions about assigning inventory classes