Medical Store Stock Statement: Monthly Format

What a stock statement actually is — and why it's not the register

Most pharmacy owners confuse two documents that drug inspectors treat as completely separate. The stock register records every movement — purchase, sale, return, expiry — line by line, batch by batch. The stock statement is the periodic summary that rolls all those movements into a single reconciliation for a month (or a quarter): opening stock + purchases − sales − expired/returned = closing stock.

Your CA wants the statement to tie to your GSTR-3B purchase and sales figures. The drug inspector wants it to tie to your Schedule H1 and Schedule X registers. If those two reconciliations don't agree, you have a problem — and it usually surfaces during an inspection, not before.

This post shows you the exact column structure, how to build the schedule-wise split, and the three cross-checks the inspector runs on the spot.

The column structure the statement needs

A compliant monthly stock statement has one row per product category (or per SKU for Schedule H1/X) and these columns, in order:

For Schedule H1 and Schedule X drugs, you need two additional columns: Batch number and Expiry date. The inspector will cross-reference these directly against your Schedule H1 register — so if you maintain that register batch-wise (as required under Drugs and Cosmetics Act, 1940, Rule 65), the statement must match it exactly. If you haven't set up that register yet, the pharmacy stock register template gives you a ready format.

The schedule-wise split: why a single totals page isn't enough

Drug inspectors are trained to look at each schedule separately. A statement that lumps OTC, Schedule H, and Schedule H1 into one total is technically incomplete and gives the inspector grounds to ask for the underlying register on the spot.

Structure your statement in three sections:

Section 1 — OTC and General medicines. Summarise by product group (antacids, vitamins, topicals, etc.). Batch-level detail is not mandatory here, but if you track it, include it — it makes the GSTR reconciliation cleaner.

Section 2 — Schedule H medicines. Row per product, monthly totals. No prescription log is required in the statement itself, but your dispensing register must be available to back the sales figure.

Section 3 — Schedule H1 and Schedule X (NDPS). Row per product per batch. Under Rule 65 of the Drugs and Cosmetics Act, 1940, Schedule H1 drugs must be recorded with batch number and expiry date at every transaction. Your statement's closing qty for each batch must equal what's physically on your shelf.

Reconciling to GSTR: the three numbers your CA will check

Your CA reconciles the stock statement to your GST returns every quarter. These are the three figures they'll cross-check:

1. Purchases. The total purchase value (net of GST) in the stock statement must match the sum of your GSTR-2B inward supplies for the period. Suppose you show ₹4,20,000 in purchases on the statement but GSTR-2B shows ₹4,35,000 — that ₹15,000 gap means either a supplier filed late or you missed recording a bill. Either way, your CA flags it.

2. Sales. The total sales value (net of GST) must match your GSTR-3B outward supplies. If your statement shows lower sales than GSTR-3B, the inspector may read it as unrecorded dispensing — a serious compliance issue.

3. Closing stock value. This feeds directly into your balance sheet as inventory. Under Section 145A of the Income Tax Act, 1961, inventory must be valued inclusive of taxes paid — so your CA will want the closing value at cost including GST paid (with ITC adjustment). Make sure the ₹ column in your statement uses a consistent valuation method (FIFO is standard for pharma) and doesn't switch mid-year.

What the drug inspector actually cross-checks on the spot

Inspectors in India follow a structured inspection protocol under the Drugs and Cosmetics Act, 1940, and state Drug Control rules. When they ask for the stock statement, they typically run four checks:

Check 1 — Physical count vs closing stock. They pick three to five SKUs at random — almost always including one Schedule H1 item — and count the physical stock. The statement's closing qty must match, batch for batch.

Check 2 — Schedule H1 register vs statement. The cumulative dispensing total in your Schedule H1 register (purchases minus closing stock) must equal the sales figure in the statement. A mismatch of even a few strips triggers a written query.

Check 3 — Expired stock disposal. If you've written off expired stock in the "Expired / destroyed" column, the inspector will ask for the corresponding disposal documentation. Under CDSCO guidelines on expired drug disposal (2025), destruction must be witnessed and recorded. No record means the qty is treated as unaccounted stock.

Check 4 — Return-to-distributor entries. Returns must appear in both the statement (as "returns to supplier") and in your credit note file. The inspector cross-references the credit note date against the batch expiry — if you returned stock that hadn't expired yet, that's fine; if the credit note is dated after expiry, they'll ask why it wasn't destroyed instead. The post on drug inspector pharmacy compliance checklist covers what else they look for beyond the stock statement.

Building this in Excel without it becoming a mess

A workbook with four sheets works cleanly:

• Sheet 1 — Master movement log. Every purchase, sale, return, expiry entry with date, batch, qty. This is your source of truth.
• Sheet 2 — Monthly statement. SUMIF formulas pulling from Sheet 1 by product and month. Columns match the structure above.
• Sheet 3 — Schedule H1 / X detail. Filtered view of Sheet 2 for controlled drugs, batch-wise.
• Sheet 4 — GSTR reconciliation. Paste your GSTR-2B and GSTR-3B figures; the sheet flags differences automatically.

The weakness of Excel is that it can't alert you when a batch is nearing expiry or flag a dispensing entry that would push a Schedule H1 batch into negative stock. For that you need either a disciplined daily entry habit or software that enforces batch-level controls. The pharmacy stock register format batch-wise post walks through the register side of this in detail.

Closing the loop before month-end

Don't build the statement on the last day of the month. Run a draft on the 25th — it takes 20 minutes if your movement log is current — and look for three things: any batch where physical count doesn't match the calculated closing, any Schedule H1 row where the statement qty and the register qty diverge, and any purchase bill you received but haven't entered yet. Fix those five days before month-end and the final statement takes ten minutes to finalise.

If you want a head start, the pharmacy stock register template includes a monthly summary tab structured for Schedule H, H1, and OTC categories — ready to adapt to the column format above.

One tool worth trying

ShelfLifePro serves pharmacies across India with batch-level expiry tracking, schedule-wise stock summaries, and GST-reconciliation-ready reports. There's a 14-day free trial, no credit card required — enough time to run a full month-end statement and see whether the numbers close faster than they do in your current workbook.

The statement isn't paperwork for its own sake. It's the one document that proves your stock is clean, your GST is correct, and your controlled drugs are accounted for. Get the format right once and every inspection becomes a ten-minute conversation instead of a two-hour audit.