You run a pharmacy and want to catch stock before its expiry date passes: which batches have already expired, which will in the next 30, 60 or 90 days, and how much money that is, without walking the shelves with a pen. This post shows how the free pharmacy expiry and stock tracker does it. The template and its page are in Arabic. It tracks stock and dates only; it is not a medical reference and contains no treatment information.
This post does not repeat the practice side. For how to record batches, sell the earliest expiry first, and handle expired stock, read pharmacy stock and expiry management. For when to reorder, read reorder point and safety stock for a pharmacy.
The problem it solves
A stock count by item says "20 boxes". It does not say that 8 of them expire next month. The expiry date belongs to the batch, so the useful record is one row per batch. Once you have that, the questions become simple: what is expired, what is close, and what is worth acting on first.
What the template gives you
- Settings: today's date, which updates automatically, or a date you type.
- Items: one row per batch: item name, batch number, expiry date, quantity, reorder level, cost price and sale price. The grey cells show days left, expiry status, stock status, stock value and value at risk.
- Needs action: an automatic list of batches that are expired, close to expiry or below reorder level, ordered from the nearest expiry.
- Summary: the number of batches and their cost value in each status, the total stock value, the at-risk value and the count below reorder level.
A walkthrough with the sample data
The sample data in the template contains 15 batches. The Summary sheet shows them like this (sample numbers, not a real pharmacy):
- Expired: 2 batches, cost value 930.
- Within 30 days: 3 batches, 2,202.
- Within 60 days: 2 batches, 1,250.
- Within 90 days: 2 batches, 3,550.
- Sound: 6 batches, 3,179.
Adding the first four rows gives an at-risk value of 7,932, out of a total stock value of 11,111 at cost. In other words, in this example most of the stock value sits in batches that are expired or ending within three months, and the sheet makes that visible in one table. The statuses work like this: expired once the expiry date has passed; within 30 days up to 30 days (today included); within 60 days from 31 to 60; within 90 days from 61 to 90; anything later is sound.
Features and why they matter
- Automatic days left and status, so you do not recompute dates every week.
- Value at risk, so you know the cost of waiting, not just the count.
- Reorder alert for items at or below their reorder level, in the same place as expiry.
- No manual totals, and a Needs action list you can print or work through.
- Works in Excel 2016 and later, Google Sheets and LibreOffice; free, no sign-up. It holds 500 batches and the action list shows the first 60 rows.
Using the online version
The template page opens on this example. Edit the white (input) cells in the page and the grey (calculated) cells update at once. You can import your own .xlsx made from this template, export to .xlsx, or download the original Excel file. Your data stays in your browser and is not sent to the server. Open the template page, or see the template list.
Common mistakes, and when a sheet stops being enough
Common mistakes: one row for an item with several batches (write each batch on its own row), typing the date in a different format, and forgetting that a sheet only knows what you typed. If you would rather have stock followed item by item with a warning before an item runs out, see Sehaty (MyClinic). If you are choosing software, this checklist comes first.
FAQ
What is the difference between "within 30 days" and "within 60 days"? The status shows the nearest range: up to 30 days, 31 to 60, and 61 to 90. After the date passes it becomes "expired".
How is the at-risk value calculated? Quantity times cost price, summed over expired batches and those within 90 days.
What if an item has several batches? Give each batch its own row with the same item name, its batch number, expiry date and quantity.