Perpetual Inventory System Example for Pharmacies

A perpetual inventory system updates the expected quantity of a product every time stock moves, instead of learning what is on hand only when someone counts it. For narcotics and controlled drugs this is the standard way of working: every receipt, dispense, destruction and return changes a running balance, and a physical count is then a check on that balance rather than the only source of it. This guide works through an example, lists the reasons perpetual balances drift away from the shelf, and covers the traps in running one from a spreadsheet.

The formula

Every perpetual inventory for a controlled substance rests on one line of arithmetic. Expected on-hand equals the opening balance, plus quantities received, plus quantities returned to stock, minus quantities dispensed, minus quantities destroyed or wasted. Everything else in the system, from the log format to the reconciliation sign-off, exists to make sure each of those five figures is complete and dated correctly.

A worked example

Take one drug and strength with an opening balance of 48 tablets. A wholesaler order adds 100 tablets. Dispenses over the period total 56 tablets. Six expired tablets are destroyed with a witness and recorded. Six tablets come back to stock from a prescription that was filled but never picked up, after the claim is reversed. The expected balance is 48 + 100 + 6 - 56 - 6 = 92. A physical count finds 92 tablets, so the count agrees and is signed off. Had the returned prescription been put back on the shelf without reversing it in the records, the count would still find 92 but the records would expect 86, and the pharmacy would be looking at an overage of 6 with no obvious cause.

Periodic and perpetual, side by side

A periodic system knows the quantity only at the moment of a count, and between counts there is nothing to compare the shelf against. A perpetual system always has a current expected figure, so a count can be done on any day and immediately shows whether stock and records agree. Most pharmacies run a hybrid: perpetual tracking for every transaction, with full physical counts on the interval their provincial college sets and spot counts in between. The intervals by province are covered in our guide to controlled substance inventory frequency in Canada.

Why perpetual balances drift

When a count and a perpetual balance disagree, the cause is usually one of a short list:

• Part fills recorded as the full quantity, or the balance of a part fill dispensed without its own entry
• Unclaimed prescriptions returned to the shelf without reversing the dispense
• Patient returns added back into stock instead of being set aside for destruction
• A dispense or receipt recorded under an interchangeable brand's DIN, so one product's balance runs short and the other's runs over
• Transactions dated just before or after the count cut-off
• Destructions carried out but not entered, which show as a shortage
• Breakage or spillage cleaned up without a record
• Unit mismatches, such as a box of 5 patches received and entered as 1
• Measuring variance on liquids, which produces small, steady differences rather than whole units

Running a perpetual inventory in a spreadsheet

Many pharmacies start with a spreadsheet, and it can work at low volume. The weaknesses are predictable. Any cell can be typed over, so a balance can be changed without a trace. Most spreadsheets keep no reliable history of who changed what. A formula copied down a column can break when a row is inserted or when someone types a number over it, and sorting a sheet by anything other than date scrambles a running balance. Copies multiply, and nobody is sure which file is current. A few habits reduce the risk: lock the balance column so only the input columns can be edited, add rows for new transactions instead of editing old ones, record corrections as adjusting rows with a reason, keep one master file, and save a dated, read-only copy at every count so the record of that count cannot change afterward.

Adjusting the balance after a count

When an investigation explains a variance, the fix is to add the missing or corrected transaction, for example the unentered destruction, so the balance becomes right for a traceable reason. When no cause is found, many pharmacies record an adjusting entry that brings the balance to the counted quantity, with a note linking it to the investigation and any loss report. Overwriting the balance to match the shelf erases the evidence of what happened, which is why it is best avoided.

A perpetual inventory with NarcCount

NarcCount builds the perpetual balance from imported dispensing, purchase and destruction records, so each expected on-hand figure is computed rather than typed. When a physical count is entered, it flags the variance and keeps a dated reconciliation record, which removes the overwritable cells and missing history that make spreadsheet versions hard to rely on.