Pharmacy Expiry Check Without Switching Billing Software

The near-expiry report nobody opens

Your billing software already knows when every strip in the store expires. Batch and expiry go in when the purchase bill is entered, because the software won't let you sell a batch it doesn't have. So the data's there, every single day.

And yet the near-expiry report sits in a menu three clicks deep. It comes out when the distributor's rep asks for returns, or the night before an inspection, or when someone finds a box of syrup that went out of date last month behind the cough-and-cold shelf.

This post is about closing that gap without touching your billing setup. You keep the software your counter staff already know. You add a small daily habit: one stock export, read and ranked, turned into two or three decisions.

Why the report stays closed

The report isn't broken. It's just built for the wrong moment. Billing software is designed around the sale: scan, pick the batch, print the bill, next customer. Expiry is a field it stores so it can print it on the invoice, not something it pushes at you.

When you do open the report, you get a long list sorted by date. Every batch expiring in the next few months shows up at the same weight: a cheap strip of antacid next to a box of insulin pens. Picture a typical two-counter medical store running that report on a busy Saturday. The list runs to several pages, nobody has time to read it, and it gets closed. Next week it's longer.

There's a second problem. A date-sorted list tells you what is expiring. It doesn't tell you what to do: return it, push it at the counter, move it to the branch, or just accept the loss. We went into this in more detail in the POS expiry blind spot. The short version is that a list of facts isn't a list of decisions.

What the billing system already knows

Before adding anything, check what your software can export. Look under the reports or stock menu for an option to save the stock report to Excel or CSV. The columns you want are:

• Item name and pack size
• Batch number
• Expiry date
• Quantity on hand
• Purchase rate or cost (not just MRP)
• Supplier or distributor name

If cost is missing, ask your software support how to add the purchase-rate column to the export. Without cost, you can't rank by money. You can only rank by date, and that's the report you already ignore. Our guide to stock export CSV columns goes column by column if your export looks thin.

Sales history helps too. A separate item-wise sales report covering the last few weeks gives you a rough sell rate for each product, and that's what tells you whether a batch will clear in time.

Building a daily check from one export

The check itself is simple, and you can do it in a spreadsheet. Each morning, before the counter gets busy:

• Export stock with batch, expiry, quantity and cost.
• Filter to your window, meaning anything expiring within your distributor's return window plus a buffer.
• Add sell rate from the sales report: units sold per day for that item.
• Work out the leftover: units that won't sell before expiry at that rate.
• Multiply leftover by cost to get money at risk for each batch.
• Sort by money at risk, highest first, and act on the top few.

That's it. Step 4 is the one the billing report never does, and it's the one that matters. A batch of a fast-moving painkiller expiring soon might sell through on its own. A slow-moving dermatology cream expiring later might not, and it costs more.

If you'd rather start from a ready sheet, the expiry-tracking spreadsheet template has the columns laid out.

Ranking by money at risk

Worked example: say your export shows these three batches, and your sales report gives the sell rates:

• Antacid strips: 60 units on hand, expiry in 30 days, sells 3 a day, cost ₹25 each. It'll clear in about 20 days, so nothing's at risk.
• Antifungal cream: 18 tubes, expiry in 45 days, sells 0.2 a day, cost ₹180 each. About 9 will sell, 9 won't. ₹1,620 at risk.
• A cardiac tablet pack: 12 boxes, expiry in 60 days, sells 0.1 a day, cost ₹420 each. About 6 will sell, 6 won't. ₹2,520 at risk.

Sorted by date, the antacid comes first and eats your attention. Sorted by money at risk, the cardiac pack comes first, then the cream, and the antacid drops off the list completely because it'll sell through on its own. Same data, very different morning.

The arithmetic is the whole point. If you can see "6 of 12 won't clear at 0.1 a day", you can argue with it. Maybe a regular patient buys that pack monthly and the sell rate undercounts them. Good, then you know to skip it. A list you can check is a list you'll actually trust.

Turning the list into actions

For each batch at the top of the ranking, pick one of four moves:

• Return to distributor. If it's inside the return window, raise it now. Waiting a week can push it outside the window. Log it in the distributor expiry claim register so you can chase the credit note.
• Push at the counter. Make sure staff pick this batch first when billing (first-expiry-first-out), and mention it to doctors nearby who prescribe it.
• Transfer. If you run a second store where it moves faster, send it there.
• Accept and record. Some batches won't clear and can't go back. Write them off cleanly instead of finding them during the year-end count.

How you document the near-expiry stock you set aside is its own topic. Our post on near-expiry medicines register format and policy covers what to record and where to keep it.

Keep the daily list short. If you act on two or three batches a day, every day, the pile never builds up. If you try to clear thirty in one go once a month, you'll put it off. That's exactly how the report ended up unopened in the first place.

Doing it by hand versus with an agent

A spreadsheet can do all of the above. Be honest with yourself about where it breaks:

• Consistency. The check works because it's daily. A spreadsheet depends on someone remembering to export, paste and sort, and on a slow morning or a sick day, it doesn't happen.
• Sell rates go stale. Seasonal items like cough syrups and anti-allergy tablets move very differently across the year. A formula using last month's rate will get them wrong.
• No feedback. The sheet never learns that your "push at counter" calls on a certain category usually fail and those batches should go straight back to the distributor.

What a spreadsheet does well: it's free, it's yours, and building it once teaches you exactly what the numbers mean. If you run a small store with a few hundred lines and a disciplined morning, it may be all you need. Expiry date tracking with your existing POS covers more manual options.

Keeping your billing software, adding a daily read

If you'd rather not run the sheet yourself, this is what ShelfSense by ShelfLifePro is built for. It's a daily inventory agent that works alongside the billing system you already have. Once a day it reads your stock, batch, cost and expiry records from a CSV export and gives you a short list of what to clear, return or reorder, ranked by money at risk, with the arithmetic shown for every call. It recommends and you approve. It never changes a price or places an order on its own, and it learns over time from which calls actually sold.

There's no switching involved. Your counter keeps billing exactly as it does today. ShelfSense's free audit reads one CSV export, no account needed. If you'd like the daily check after that, the Watch tier is free forever at 30 SKUs — a daily scan and a health report. Start with your stock export here.