The pharmacy stock register format that actually works: one row per batch

Search for "stock register format pharmacy" and almost everything that comes back is a billing format wearing a register's clothes: invoice layouts, purchase books, sales summaries. Useful documents — but none of them is a stock register, because none of them is organised around the one thing that makes pharmacy stock different from hardware stock: every strip has a date on it, and the dates differ batch to batch.

"Amoxicillin, qty 400" is not information

An item-wise register tells you that you hold 400 capsules of amoxicillin. What you actually hold is, say, 150 from batch A expiring next March, 180 from batch B expiring in August, and 70 from batch C that crosses its supplier return window in three weeks. Those three numbers demand three different actions — sell first, sell normally, return now — and the item-wise view collapses them into one comfortable, useless total. Every decision that matters in pharmacy inventory happens at batch level: what to dispense first, what to return before the window closes, what a Drug Inspector can trace, what is quietly dying in the back drawer.

So the first rule of a pharmacy stock register format is structural, not cosmetic: one row per batch, never one row per medicine.

The columns a batch-wise register needs

A working format carries four groups of columns. Identity: medicine (brand), generic composition, and the schedule classification (OTC / G / H / H1 / X) — the schedule column is your cross-reference to the statutory registers, of which more below. Provenance: supplier, invoice number and received date, so any batch can be traced back to a purchase document in one look. The economics: quantity in stock, purchase cost and MRP, so the register can price your risk, not just count it. And the clock: batch number and expiry date — from which everything else derives.

Derived, not hand-written, is the point. From the expiry date a spreadsheet can compute the days-left countdown, a FEFO pick order (among batches of the same medicine, the earliest expiry is rank 1 — dispense rank 1 first, always), a status ladder, and the stock value at risk. If you are keeping the register on paper you can still run the logic with a weekly pass and a highlighter; in a spreadsheet it runs itself.

The 90-day line: why the useful status ladder is a pharmacy-specific one

Generic inventory templates flag stock at 30 days to expiry. For a pharmacy that is far too late, because the deadline that matters is not the expiry date — it is the supplier return window. Most distributors accept expiry returns only up to a cut-off before expiry, commonly three to six months depending on the supplier and the line. A batch expiring in November may be unreturnable from August. "Not expired yet" and "unreturnable" can both be true, and the difference is whether the loss lands on you or the supplier.

A pharmacy register's ladder therefore looks like: EXPIRED — quarantine, never sell; RETURN WINDOW (≤90 days) — check the supplier's terms and raise the return now; WATCH (≤180 days) — sell first, FEFO; OK. Tune the 90 to your own suppliers' windows, but have the line, and have it early.

What this register is not: the statutory ones

A stock register is an operational document. It does not replace the registers the law prescribes. The supply of Schedule H1 medicines goes in the separate H1 register at the time of supply — prescriber, patient, drug and quantity — retained for three years under Rule 65 of the Drugs and Cosmetics Rules. Schedule X drugs carry stricter handling again: duplicate prescriptions with your copy retained two years, lock-and-key storage, and register-keeping that should support an NDPS-grade audit. The schedule column in your stock register is what connects the two worlds: any row marked H1 or X is a row whose sales must also be appearing in a statutory register, and an inspector can and will check that the two agree.

(We publish free, government-sourced formats for both: the Schedule H1 register template and the NDPS/Schedule X register pack.)

Paper, spreadsheet, or software — an honest ranking

Paper works if the pharmacy is small and one disciplined person owns the register. It fails on arithmetic: nobody recomputes 400 days-left countdowns by hand every week. A spreadsheet fixes the arithmetic — countdowns, FEFO ranks, value tiles all compute — and fails on the counter: at 7pm with a queue, nobody alt-tabs to update a row, and a register that is three days behind reality is a register you no longer trust. Software fixes the counter, because the sale itself updates the batch — FEFO is enforced at the point of sale rather than remembered, the return-window alert fires without anyone checking a sheet, and the statutory registers fill from the same event.

Start with the spreadsheet — it is free and it will genuinely change what you can see. Our batch-wise pharmacy stock register template has the 500 rows, the formulas, the FEFO ranks and the return-window ladder already built. When updating it becomes the bottleneck, that is not a discipline failure; it is the signal you have outgrown the sheet.