Find your dead stock and decide what to do with it

Reading time: about 9 minutes

The situation. You know there is money sitting in the stockroom. You do not know how much, or where, or where to start — and every time you try, you get lost in a list of four thousand items.

Dead stock is not "what does not sell": it is your money tied up, and every euro sitting there is a euro that is not in the till or in something that does move. This case turns it into a concrete figure and a twenty-line list you can actually decide on.

What you need to hand: Two exports from your software: current stock (code, description, units on hand, retail price and, if available, cost price) and sales for the last 12 months (code, units). Neither carries personal data, so this is one of the clean cases.

Step by step

Export both files and check they share a key

The "key" is the column that lets you join them: the product code. If one file shows 650123 and the other 650123.4 or with leading zeros, the join will fail on half the rows and the result will be a silent mess.

Open both and look at ten codes. If they do not look exactly alike, say so in the prompt: it is one of the few things a model fixes well and instantly.

You define "dead", with a number

Here is the decision in this case, and the AI cannot make it because it depends on your pharmacy. "Dead" can mean:

Zero sales in 12 months — the hard criterion, the one nobody argues with.
Fewer than 2 units a year while holding 5 — two and a half years of cover, which for most things is too much.
More than 12 months of cover (stock ÷ monthly sales) — the finest, and the one that works best.

Pick one. Pick all three and you will have three lists and act on none. And give the model the criterion in numbers, not adjectives: "things that sell slowly" is not a criterion, it is a feeling.

Attached are two files from a pharmacy:
1) STOCK: product code, description, units on hand, retail price
   and cost price.
2) SALES 12 MONTHS: product code, total units sold.

<<The codes in file 1 carry a check digit at the end and those in file 2
do not. Normalise them before joining.>>

JOIN both by product code and tell me FIRST, BEFORE ANYTHING ELSE:
- how many items are in each file
- how many you managed to join
- how many stock items do NOT appear in sales (those are 0 sales, not a
  join error: report them separately)

THEN, using the criterion <<stock covering more than 12 months at the
current sales rate>>, give me the list sorted by MONEY TIED UP (units ×
cost, or × retail if no cost), highest first. Only the top 20.

For each: code, description, units, months of cover, money tied up.

And at the end, the TOTAL money tied up across the whole list, not just
the top 20.

Look at the join counts first, not the list

That is why the prompt asks for the counts before the list: they are the only thing that tells you whether the join worked. If you have 4,000 stock items and only 1,200 joined, the list below is well-formatted rubbish.

The distinction between "did not join" and "joined with zero sales" is the most commonly confused, and they are opposites: the first is a technical failure, the second is exactly what you are looking for.

Split the list into three buckets, because not everything goes back

A list of twenty dead items is not handled the same way throughout. Ask it to split them, and give it the criteria yourself — they are pharmacy criteria, not data ones:

From those 20, split them into three groups and explain in one line
why each goes where it goes:

A) RETURN OR RENEGOTIATE with the supplier (no rotation and not expiring
   soon).
B) CLEAR HERE (expires within 6 months, or is last season).
C) KEEP even though it does not rotate. These are items I must hold by
   obligation or for service, whether they sell or not.

For group C do not argue: if I say it stays, it stays. Just tell me how
much money that group adds up to, so I know what it costs me.

Group C is the one no management tool understands and you do: a pharmacy holds some things because it is a pharmacy. What this step adds is not removing them, it is knowing what they cost — a different and better-informed decision.

Repeat in three months with the same prompt

The value of this case is not the first time: it is the second. The first list is years of accumulation and it will impress you. The one three months from now tells you whether what you did worked, and that figure — total money tied up — is the only honest indicator.

Save the prompt and the total somewhere you will find it. Two numbers three months apart are worth more than twenty different analyses done once.

A full example, and what is wrong with it

Stock and twelve months of sales from a pharmacy with 3,900 items. The first thing it returned is what you read, and it is not the list.

What it replies

"Before the list:

Items in STOCK: 3,912. Items in SALES: 2,744.

Correctly joined: 2,701.

In stock and NOT in sales: 1,211 (zero sales in 12 months).

In sales and not in stock: 43 (probably out of stock or discontinued).

Total money tied up (criterion: cover > 12 months): €18,430 across 1,847 items."

And now, what is wrong with that reply:

The counts add up, and that is the good news. 2,701 joined + 1,211 with no sales = 3,912, which is exactly the stock total. If that sum did not match the total, the list below would be rubbish and you would not know by looking at it. It is the ten-second check that makes the rest of the report worth anything.
But 1,211 items with zero sales is too many. A third of the stockroom with not one unit sold in twelve months is not credible in a working pharmacy. What it nearly always means is that the sales export did not cover the full year — many systems default to a quarter — and the join is technically correct over the wrong period.
And the €18,430 total is both correct and misleading. Correct because it comes from the units and the cost you supplied. Misleading because it includes the 1,211 above, so if the export was wrong, this figure is inflated and looks exact. Decimal places say nothing about whether the input was right.
The 43 "in sales and not in stock" are a gift. You did not ask for them and they are the most actionable finding in the report: things that sell and of which you have none left. It could be a stock-out, a withdrawal, or something rotating that you stopped replenishing. That list gets looked at today; the dead stock can wait until tomorrow.

The rule of this case in one sentence: if a count surprises you, suspect the export before you suspect the stockroom. A third of items dead is nearly always a badly exported period, not a badly run pharmacy.

Go back to the software, check the sales report covers the full twelve months, and repeat. The total usually halves — and that is the figure you actually decide on.

And if your pharmacy is not like that

If you only have the retail price, not cost. It works just as well for ordering the list, which is what you want it for: the expensive stays on top. What it does not do is the total, because you would be counting margin you never paid. Say so in the prompt and call the figure "retail value", not "money tied up" — the label matters when you look at it in three months.

If you carry a lot of seasonal stock. Twelve months of sales against today’s stock unfairly punishes seasonal lines: in October every sunscreen looks dead. Tell it which items are seasonal, or better, run the exercise in the month that season ends — which is also when the decision makes sense.

If you have just taken over the pharmacy. This is the first exercise to do, before any other in the block. The dead stock you inherit you did not buy, and you owe it no loyalty — it is the one time you will look at that list without justifying every line. Make the most of it.

If the stockroom is in two places. Export with the location if your system has it, and ask for the split by place. Nearly always the same thing appears: the parked money is concentrated in the place nobody goes — the loft, the basement, the top shelf. Knowing that changes where you start.

When it does not work first time

It joins very few items and I cannot see why.

Ask it to show you five codes from each file exactly as it sees them. In two seconds you spot the difference — a check digit, leading zeros, a dot — and you tell it. Eyeballing the files yourself works worse, because Excel is already showing them to you formatted.

A huge list comes out and I do not know where to start.

That is why the prompt asks for only the top 20 ordered by money. If it still overwhelms you, cut to ten and set a time limit: half an hour this Friday. A list of 1,800 items does not get managed; twenty lines does, and another twenty next month.

My inventory is not accurate and I feel awkward using it.

Use it anyway. An imperfect inventory crossed with real sales already tells you where the parked money is, and waiting for the perfect one is how five years pass without doing this. Besides, the discrepancies you find when you go looking for the products are the best way to start fixing the inventory.

It suggests clearing things I am required to stock.

That is what group C in step 4 is for, and it is not negotiated with the model: if you say it stays, it stays. The only thing you ask is the total that group represents — not to remove it, but so you know what providing that service costs. It is a different and better-informed decision.

Before you use what it gave you

The number of joined items looks like the number in your stock file.
You can tell "did not join" from "joined with zero sales".
You set the "dead" criterion yourself, in numbers.
You checked two of the twenty lines in your own software before moving anything.
Group C — what stays — was your decision, not the model’s.

It joins badly and presents it beautifully. One extra check digit is enough for half the list to fail to join, and the resulting table looks exactly as it always does. The counts at the top exist precisely for this.

It tells you to clear something you are required to hold. It does not know what your regional authority requires you to stock, what your professional body asks, or that one neighbour uses that product and nobody else. Group C is not up for negotiation with a model.

It adds up units of different formats. If you stock the same product in 20-tablet and 40-tablet packs with different codes, it may group them by description and add the units as if they were the same. Cover in months then comes out double or half. Ask explicitly for grouping by code, never by description.

Now, with your own. Do it today with stock as it stands, even if you suspect the inventory is not exact. An imperfect inventory crossed with real sales already shows you where the money is parked; waiting for a perfect inventory is how five years go by without doing it.

← All practical cases Back to the school →