Working with units: pieces, cartons, kilograms

The report says: "Pencils — 340". What is 340: pieces, packs or cartons? The storekeeper's notebook shows a balance of 300 pieces and 40 cartons, and someone added the two together and keyed in "340". The number looks solid but means nothing. From data with mixed units you cannot calculate balances, turnover or shortages: even with the right formula the answer comes out wrong.

Choosing the base unit

The rule is simple: make the smallest indivisible unit that appears in sales or counts the base unit. For piece goods it is the piece; for weighed goods, the gram or kilogram. Keep all balances, receipts and issues in that unit only. Cartons, packs and pallets are for display: even if a document says "2 cartons", the system converts them to the base unit.

Choosing grams spares you fractions: 250 g instead of 0.25 kg, a whole number.

The conversion table

Keep a separate table for each item: item, unit, quantity in the base unit, date from which it applies.

• Pencil: piece = 1, pack = 12, carton = 10 packs = 120.
• Dried apricots: gram = 1, 250 g bag = 250, carton = 10,000 (net weight).

The formula: base quantity = quantity on the document × factor. In Excel: B2 × VLOOKUP(C2, factor table, 2, FALSE), where B2 is the quantity and C2 the unit code. The separator (comma or semicolon) depends on your Excel settings. If the barcode of a pack or carton differs from that of a piece, the scanner identifies the unit itself.

What the error looks like

Back to the pencils. The correct balance is 300 + 40 × 120 = 5,100 pieces. At 1,500 so'm each the value is 5,100 × 1,500 = 7.65 million so'm. Add them up as "340" and you get 340 × 1,500 = 510,000 so'm: the balance is understated 15 times (5,100 ÷ 340 = 15). The reorder point will signal "running out" while the shelf holds plenty.

Common traps

• The packaging changed. From 1 March the supplier began putting 8 packs in a carton instead of 10: 96 pieces. If you simply overwrite 120 in the table, the old 40 cartons "shrink" by 40 × (120 − 96) = 960 pieces, which is 1.44 million so'm. The right way is to keep the old packaging and add the new one with a new date.
• Gross and net. A carton of dried apricots weighs 10.4 kg with packaging. Record 18 cartons by gross weight and you get 18 × 0.4 = 7.2 kg too much. At 90,000 so'm a kilogram that is 648,000 so'm.
• The name of the unit. "pcs", "sht", "dona" and "шт" are four different units to a system. Do not let people type the unit freehand; make it a choice from a list (Data Validation in Excel).
• Receipts in cartons, sales in pieces. 40 cartons arrived and 4,700 pieces were sold at the till. If the system treats a carton as 1 unit, the balance 40 − 4,700 = −4,660 goes negative. The correct figure is 40 × 120 − 4,700 = 100 pieces.
• The decimal sign. "0,25" and "0.25" turn into text depending on settings and are left out of the sum.

Repairing the data you already have

1. Export a year of documents and build an "item × unit" table (a pivot table in Excel). If one item appears in several units, that is a warning sign.
2. Fill the conversion table, starting with the most expensive group A items.
3. Recalculate balances in the base unit and compare them with the last count. If the gap is a "round" one, like 15 times, the packaging factor is wrong.
4. Make it a rule: no new unit is introduced without a conversion table.

In a report you can show whichever unit suits the reader, but state it in the heading of every column: "Balance, pcs" or "Balance, cartons". If cartons come out fractional (5,100 ÷ 120 = 42.5 cartons), that is not an error but a part-full carton. Calculate totals only from the base unit.

Conclusion

One column, one unit; one item, one conversion table: these two rules prevent the cheapest and most common mistake. During a stock count CountLab checks the unit and packaging of every position, and builds the ABC/XYZ analysis and the management report on the corrected balances.