Pharmacy near-expiry stock: batch-level FEFO analysis in pandas

Why "90 days of stock" can still mean expiry

Take a hypothetical cough syrup selling 2 units a day with 180 units on hand. Days of cover is 90, which sounds fine. But if 120 of those units belong to a batch whose last realistic selling day is 34 days away, only about 68 can sell in time, and 52 bottles will expire regardless of the healthy SKU average. The worked example below reproduces exactly this case. Expiry is a batch problem; purchase and reorder reports are SKU reports. That mismatch is the whole reason this analysis exists.

The two exports you need

From the pharmacy software (most Indian retail and hospital pharmacy systems can export these as CSV):

Batch stock — sku, product_name, batch_no, expiry (often month-year only), qty_on_hand, purchase_rate (per unit, excluding tax), mrp, supplier.

Sales lines — bill_date, sku, batch_no, qty, returned_qty.

Use net quantity (qty − returned_qty) for the run rate. Do not use the batch column from sales to build the forecast; batch choice at the counter depends on which strip the pharmacist picked up, not on demand.

The metric definitions

• Run rate (r) = net units sold in the last 90 days ÷ 90.
• Days to expiry (dᵢ) for batch i = expiry date − as-of date − a return buffer (the days before expiry by which your supplier must receive a return; this depends on your distributor terms, so it is an input, not a constant).
• Projected sellable (sᵢ) under FEFO = min(qtyᵢ, max(0, r × dᵢ − sum of s for earlier-expiring batches)).
• At-risk units = qtyᵢ − sᵢ. At-risk value = at-risk units × purchase_rate.

The FEFO formula works because total demand up to batch i's cut-off is r × dᵢ, and every earlier batch has already taken its share.

Worked example (synthetic stock, invented products)

import pandas as pd

as_of = pd.Timestamp("2026-09-27")
return_buffer_days = 30   # replace with your distributor's return window

stock = pd.DataFrame({
    "sku":      ["SYR01","SYR01","SYR01","TAB07","TAB07","CRM03"],
    "batch_no": ["B221","B305","B340","T88","T91","C12"],
    "expiry":   ["2026-11","2027-06","2027-11","2026-11","2028-01","2027-02"],
    "qty_on_hand": [120, 40, 20, 300, 200, 60],
    "purchase_rate": [58.0, 58.0, 60.0, 4.2, 4.4, 95.0],
})
# Month-year expiry: treat as the last day of that month (check your own policy)
stock["expiry_date"] = pd.PeriodIndex(stock["expiry"], freq="M").to_timestamp(how="end").normalize()

sales_90d = pd.DataFrame({"sku": ["SYR01","TAB07","CRM03"],
                          "net_units_90d": [180, 1350, 18]})
stock = stock.merge(sales_90d, on="sku", how="left").fillna({"net_units_90d": 0})
stock["run_rate"] = stock["net_units_90d"] / 90
stock["days_left"] = ((stock["expiry_date"] - as_of).dt.days - return_buffer_days).clip(lower=0)

def fefo(group):
    group = group.sort_values("expiry_date").copy()
    absorbed, sellable = 0.0, []
    for _, row in group.iterrows():
        capacity = max(0.0, row["run_rate"] * row["days_left"] - absorbed)
        s = min(row["qty_on_hand"], capacity)
        sellable.append(s)
        absorbed += s
    group["sellable"] = [round(x) for x in sellable]
    return group

out = pd.concat(fefo(g) for _, g in stock.groupby("sku"))
out["at_risk_units"] = out["qty_on_hand"] - out["sellable"]
out["at_risk_value"] = (out["at_risk_units"] * out["purchase_rate"]).round(0)
cols = ["sku","batch_no","expiry_date","qty_on_hand","days_left","sellable","at_risk_units","at_risk_value"]
print(out[cols].to_string(index=False))
print("Total at-risk value (Rs):", int(out["at_risk_value"].sum()))

sku_cover = stock.groupby("sku").agg(qty=("qty_on_hand","sum"), r=("run_rate","first"))
sku_cover["days_of_cover"] = (sku_cover["qty"] / sku_cover["r"]).round(0)
print(sku_cover)

What the script prints, as a table:

Total at-risk value: ₹6,341. Now compare the SKU-level cover the script also prints: SYR01 shows 90 days of cover and TAB07 only 33. A buyer reading that report would worry about TAB07, which is fine — it sells 15 a day and clears its short-dated batch — and relax about SYR01, which is where the loss sits. CRM03, a slow cream, is the opposite pattern: 300 days of cover and a single batch that simply cannot sell in time.

Traps specific to pharmacy data

Month-year expiry. A strip printed "EXP 11/2026" is read differently by different systems — some store the 1st of the month, some the last day. The script assumes month-end; if your software stores the 1st, every batch looks a month shorter and you over-report risk. Check a few batches against the physical packs.

Returns inflating the run rate. Hospital pharmacies see ward returns of unused medicine. Gross issues overstate demand; always net them off.

Seasonal products. A 90-day run rate for an anti-allergy tablet computed in March will be wrong for the monsoon. For clearly seasonal SKUs, use the same months last year as the run rate, or show both.

Stock at other locations. In a chain, a batch at risk in one store may sell in another. Run the FEFO projection per store, then flag batches where another store has a higher run rate and no stock of an earlier batch — those are transfer candidates, not write-offs.

Valuation basis. Value risk at purchase rate, not MRP. MRP overstates what the business loses and makes the report look alarmist to anyone in finance.

A weekly near-expiry sheet the purchase head will read

Keep it to three blocks, sorted by at-risk value:

1. 1Return now — batches with at-risk units and days left below your distributor's return cut-off plus a week.
2. 2Transfer or promote — batches with at-risk units that another location could absorb.
3. 3Stop reordering — SKUs where the newest batch is already at risk, meaning purchase quantities are too large for demand.

The third block is the one that changes behaviour. Expiry losses are mostly decided at the moment of purchase, and a buyer who sees the same SKU there three weeks running will adjust the order size without being told.

Batch-level logic like this is a good first portfolio project because the data is familiar to anyone who has worked a pharmacy counter; see pharmacist to data analyst for how to frame it. The pandas patterns used here — grouped loops, period handling, merges — are part of the Data Analyst programme.