Catalogue quality: one item, one record

In the item list of an electrical-goods shop the same lamp is entered three ways: "LED lamp 9 W E27 cold", "Lamp LED 9W E27 6500K" and "LED 9 watt E27 (white)". To a salesperson there is no difference; to the system these are three separate items, each with its own balance, sales and price. That makes every analysis, of balances, ABC and orders alike, unreliable. The rule is simple: one item, one record.

How duplicates do harm

• ABC breaks. An item sold 90 million so'm in a quarter, but the sales landed on two records as 48 and 42 million. If group A in this list starts at 50 million, both records fall into group B although the item belongs in A.
• Orders become excessive. The first record shows a balance of 0, its "twin" 120 pieces. The system says "out of stock" and orders another 120. At 25,000 so'm each that is 120 × 25,000 = 3 million so'm of surplus stock.
• Counts get muddled. One record is 40 pieces short and the twin 40 over. The real shortage is zero, but two discrepancies appear, each 40 × 25,000 = 1 million so'm, 2 million so'm of "gross discrepancy" in all.
• Negative balances appear. A salesperson picks the "twin" by accident: that record goes negative while the other holds the same amount too much.

The naming rule

Write names in one order: category, type, main feature, size, pack. For example: "Lamp LED, 9 W, E27, 6500K, 10 pcs/carton". Agree on a list of abbreviations (W, mm, kg) and do not add a price, a promotion or the word "new" to the name: they go out of date fast.

Mandatory fields: code, barcode, base unit, category, status (active or archived). Finding duplicates by barcode is easy in Excel: COUNTIF(B:B, B2) > 1. Two records with the same barcode are a clear duplicate. By the rule the three lamps from the start of this article merge into one record, "Lamp LED, 9 W, E27, 6500K, 10 pcs/carton": words like "cold" and "white" become the 6500K attribute.

The procedure for adding a new item

1. Search first: by barcode, supplier code and the start of the name.
2. If nothing is found, submit a request. The right to create records should rest with two or three people.
3. Fill in the record according to the rule.
4. Once a week one person looks through the list of new records.
5. The catalogue needs a clear owner, who sets aside half an hour a month to go through the list.

Vague records

Besides duplicates there are meaningless records: "Cable", "Other goods", "Test", "Lamp (old)". If a balance or sales are recorded against them, nobody knows what they are. They are easy to find: in Excel flag short names with LEN(A2) < 12 and suspicious words with SEARCH("other", A2). Each record found has three ways out: clarify it, merge it into an existing record or archive it. If 35 of 2,600 records turn out this way (about 1.3 percent), it is a day's work.

The annual audit

Say the list holds 2,600 records. Checking 100 random ones, you find that 11 belong to duplicate groups: 11 percent, so roughly 2,600 × 0.11 = 286 records. That is only an estimate; the exact number is known after a full check. Another 640 records (24.6 percent) have not moved in 24 months. Do not delete them; move them to the archive, because historical documents are tied to them, and search gets faster.

To find similar names, sort the list alphabetically and look through rows whose first 10 letters match. In Excel a LEFT(A2, 10) column and COUNTIF speed this up.

The procedure for merging duplicates: choose the main record, transfer the twin's balance to it by document, keep a table that maps the old code to the main one, and put the twin in the archive.

Conclusion

Order in the catalogue is the foundation of analysis: when an item has one record, balances, sales and ABC can be trusted. That order is kept by a rule, an owner and an annual audit. During a stock count CountLab identifies duplicate and vague records and includes them in the discrepancy analysis and the management report.