HML analysis of inventory control: classify by unit cost

HML analysis of inventory control groups items into high, medium and low bands using cost per unit. Start with approved dollar thresholds, apply them consistently to an item export, and then summarize the quantities and value in each band. The class describes an individual unit's cost; it does not establish demand, criticality or an appropriate order quantity.

Define the question and the cost basis

HML helps a procurement team find items whose individual loss, purchase or issue deserves review because of unit cost. A published manufacturing case study distinguishes HML classification by unit cost from ABC classification by consumption value.

Keep that distinction in the worksheet title. A report labeled only high-value stock could mean expensive units, high total stock value or high annual usage value. Those are different populations.

Choose one unit-cost basis: accounting cost at a specified date, approved standard cost, or a documented purchasing price. For this example, finance supplies accounting cost per each at September 30, 2026. The stock-value check therefore uses the same basis as the inventory export.

Acquisition cost is not necessarily the supplier's headline price. For purchased merchandise, IRS Publication 538 describes cost as invoice price after appropriate discounts plus transportation and other acquisition charges. Have finance approve the cost field; an HML worksheet does not change the company's accounting policy.

Prepare the stock rows

Use item ID, description, location, quantity, unit of measure, unit cost, currency and valuation date. Preserve the original item ID even if descriptions appear identical. A purchasing pack must be converted to the stocking unit before its price is compared with a threshold.

Use one consistent item cost across locations if the policy classifies the company catalog. If the policy instead uses location-specific costs, label the result as an item-location class. Do not count a SKU appearing at several sites as several distinct catalog items.

For an initial review, keep missing costs, negative quantities and disputed conversions in an exception list. A blank cost is unknown, not evidence that an item belongs in the low band. The wider inventory analysis guide explains how this classification fits beside demand and service measures.

Set explicit bands and classify each row

An illustrative Pennsylvania distributor approves these thresholds for its review:

These are company assumptions, not industry standards. Put the medium lower boundary, 100, in J2 and the high lower boundary, 1,000, in J3. In the table below, A is item, B unit cost, C on-hand units, D class and E stock value.

=IF(OR(B2="",NOT(ISNUMBER(B2))),"Review",IF(B2<0,"Review",IF(B2>=$J$3,"H",IF(B2>=$J$2,"M","L"))))

Stock value in E2 is:

=B2*C2

Fill both formulas through row 7 after resolving quantity exceptions.

The pump and bearing deliberately sit on boundaries. Confirm their classes before reviewing the rest of the catalog. Reversing a greater-than-or-equal sign can move an entire boundary population.

Summarize the classes and prove the total

Place H, M and L in G2:G4. Class quantity in H2 and class stock value in I2 are:

=SUMIFS($C$2:$C$7,$D$2:$D$7,G2)
=SUMIFS($E$2:$E$7,$D$2:$D$7,G2)

Microsoft's SUMIFS documentation specifies that the function sums rows meeting the stated criteria and requires matching range dimensions. Fill through row 4; all input ranges here cover the same item rows.

The H value is 6,400 + 8,000 = $14,400. Class share is class value divided by $52,400, rounded to two decimal places. Unit totals are a completeness check across these each-based rows, not a measure of interchangeable capacity.

The check is 14,400 + 20,000 + 18,000 = $52,400, matching the item table. Quantities must also tie: 12 + 140 + 4,500 = 4,652. Then reconcile the item table to the approved source export and investigate every Review row before calling the classification complete.

Use the result without confusing HML and ABC

H items may warrant a review of purchase approvals, custody or issue records. L items still hold $18,000 here, so a low unit price does not make their aggregate balance immaterial. Assign the action to a person and a policy; a band alone does not authorize a purchase or a stock reduction.

The same item can look different under annual usage analysis:

The seal remains L under HML even though 20 × 5,000 = $100,000 exceeds the drive's 1,600 × 5 = $8,000 annual usage value. Use ABC and XYZ analysis for value and demand-pattern questions. Use inventory aging by site to investigate whether either item's stock is inactive.

Where it goes wrong

• Pack prices meet each-based thresholds. Normalize the price and quantity to the same stocking unit first.
• Cost bases change between reviews. A new purchasing price replaces accounting cost and appears to be an operational class movement.
• Unknown costs become L. Keep missing or invalid costs visible as exceptions until an owner resolves them.
• Classes replace criticality. An inexpensive seal can still stop a production line; unit price cannot measure that risk.
• Thresholds change silently. Save the approved bands and effective date so the next review separates policy changes from cost movements.

Covirage is not affiliated with Microsoft.

Review your cost bands in Covirage

Bring a dated inventory export, its unit-cost basis and your approved HML thresholds. Covirage maps the columns, and its tools compute and check quantities and stock values by the supplied bands; the external AI model explains the result with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Covirage for supply-chain teams for the supporting workflow.