Getting your stock data ready for an AI tool

An AI tool can only work with what's in the file you hand it. Most bad suggestions start with a messy list, not a clever mistake by the software.

One row for each product

The unit stock is managed in is the stock keeping unit, or SKU. A business records its inventory by counting the quantity it has of each unit.¹ So a red mug and a blue mug of the same style are two SKUs, and each needs its own row.

Look for these problems:

• The same item listed twice under slightly different names, such as "Mug large red" and "Red mug (large)".
• One row covering a case and another covering single units of the same thing.
• Rows for products you no longer stock.
• Blank names or codes.

Finding duplicates by eye works in a short list. Software that does it needs a rule for deciding whether two entries describe the same thing.² Sort the sheet by name and look at neighbouring rows first. Sorting puts near-matches next to each other.

One code, used everywhere

A code is only useful if it's the same in the stock list, the sales export and the supplier's price list. Wikipedia notes that these identifiers are not regulated or standardized, and a company can keep the vendor's code or create its own.¹ Pick one and keep it. If your till uses one code and your supplier another, add a column that links them. Then the tool can join sales to stock without guessing.

Counts you can trust

The on-hand number is the starting point for every reorder suggestion. Wikipedia notes that stock data is useful for finding stockouts only as far as it's accurate.³ Before you give a tool the figures, walk a few shelves and compare. Fix what's wrong in the sheet. Write down the date of the count.

The sales history

Most forecasting tools want sales in a regular pattern, such as one total per product per week. Microsoft's forecast function requires a timeline with a constant step between points.⁴ The same page says it's very useful to aggregate raw detailed data before you apply the forecast.⁴ For a shop, that usually means weekly totals, not every till receipt.

Gaps matter. Microsoft says the function supports up to 30% missing data.⁴ That doesn't mean gaps are harmless. A week with no sales might mean none were made, or it might mean you had none to sell. Wikipedia's stockout page points out that substitution disguises true demand.³ Mark those weeks in a separate column.

Steps, in order

The diagram shows the sequence. In plain terms:

1. Make one row per SKU and remove duplicates.
2. Choose a single code for each product and match it across files.
3. Check the on-hand counts against the shelf for a sample of items.
4. Total sales by week, or by whichever period you reorder in.
5. Add a notes column for stockouts, promotions, closures and one-off bulk orders.

Keep a copy of the original export. If the tool's answer looks odd later, you can see what changed.

What to leave out

Customer names, card details and staff information have no place in a stock file. Strip them before you share anything. Whatever tool you use, read the maker's own page on how it handles uploaded files. A short, tidy file of products and weekly sales is all a reorder suggestion needs.

Once the file is ready, Reorder points explained in plain English shows the maths the tool is likely to use.