Pharmacy Stock & Expiry Register (Excel)

Preview

Real view of the file with its sample data (yellow cells are where you type).

What's inside

• Settings: "as on" date (today by default) and the near-expiry window in days (90 to start with)
• Items sheet: medicine or product name, form, pack, rack or shelf, reorder level and the total balance of all its batches with a Reorder flag
• Batches sheet: item, batch number, expiry date, supplier, invoice number, quantity received, purchase rate and MRP
• Calculated for every batch: issued, balance, days to expiry, status (Expired, Near expiry, OK, Nil stock) and stock value
• Issues sheet: date, item, batch (drop-down), quantity sold or issued, and bill or patient reference, 2,000 ready rows
• Expiry summary: number of batches and stock value expired and expiring within 30, 60 and 90 days, to plan returns to suppliers

How to use it

1. On the Settings sheet leave the as-on date as =TODAY() and set how many days before expiry a batch should turn amber.
2. List your medicines and products on the Items sheet with the reorder level for each.
3. For every purchase, add a row on the Batches sheet: item, batch number, expiry date, supplier, invoice, quantity, purchase rate and MRP. Each batch gets its own row.
4. When you sell or issue stock, add a row on the Issues sheet with the item, the batch it came from and the quantity.
5. Open the Batches sheet and sort or filter by Status: amber batches are near expiry, red ones have expired. Sell the oldest batch first or return it to the supplier in time.
6. The Items sheet shows the total balance of each medicine and flags the ones to reorder.

Tips

• Type the item name exactly as on the Items sheet (pick it from the drop-down) or its stock will not add up.
• Expiry printed as month and year (for example 03/2027) means the end of that month: type the last day of the month, 31-03-2027.
• Expired stock must be kept apart from saleable stock and handled as your local rules require. Mark the row "Returned" or "Destroyed" in the Remarks column once it leaves your shelf, and issue the quantity out.
• Patient details are private. Keep this file on your own computer or a drive only your clinic can open, put a password on it (File › Info › Protect Workbook › Encrypt with Password) and never post it in group chats. Follow the health-data rules that apply where you work.
• This is an admin and record-keeping template. It holds no medical advice and does not replace your clinic's medical records or software.

Questions and answers