| [Article] |
d
Retailer, should you track lot numbers and expiry dates by computer?Some realistic thinking on an old question.
Updated on 14 August 2009
One of the most vexing is whether and how to track lot numbers and expiry dates. Most businessmen dealing with perishable goods think they should be tracking these two parameters for various motivations. But is there clear thinking backing up these impulses? The insights in the following article are useful to anyone who deals with any kind of perishable product; but for concreteness’s sake we use the example of the retail pharmaceutical sector – the botica.
To understand the issues fully, we start with the concerns of the manufacturer – the pharmaceutical company.
THE PHARMACEUTICAL COMPANY. For a pharmaceutical company, marking the medicines it produces with lot numbers and expiry dates is unquestionably important. The lot number enables the company to keep track of the different batches of medicines produced. By stamping the lot number on medicines, the company will be able to get back to its records to know the date and time of manufacture, the specific batches of raw materials that went into the making of the lot, possibly even the specific plant or machine or people at the floor during manufacture, among others.
The expiry date, on the other hand, will allow manufacturers to arrange the deliveries of their products, letting manufacturers ship out sooner the medicines with the earliest expiry dates. The wholesalers and retailers buying these items in turn minimize the risk of having expired medicines on stock.
Thus, the lot number serves as an "audit trail", in case any questions arise from that lot, while the expiry date serves to plan and schedule the sales and deliveries. From the manufacturer’s standpoint, keeping track of expiry dates is only one part of the logistics task. The more important part is physically sorting these lots by expiry date, storing them together and then, at the time of delivery, making sure that these are delivered in the order of expiry date. This is the job of the warehouseman.
THE DRUG RETAILER (or botica)
A. The Lot Number
From a retailer’s point of view, a persuasive case can be made that capturing in the retailer’s records the lot number of the medicines it purchases and eventually sells is an FYI issue, and not critical to operations. To achieve this non-critical purpose, the retailer does not need specific input fields in his software to enter the lot number. This information, if indeed desired, can be simply placed in the remarks field.
More to the point, this exercise of capturing such data in the retailer’s software files is not even needed. That sounds counter-intuitive, but for the retailer, the primary purpose of knowing lot numbers is simply this: when there is a problem with a specific lot, then, based on the lot number, the retailer can go back to the supplier and get a replacement or refund. But to get that done, the retailer can consult the lot number on the packaging, and also the invoice; is there any need to consult any other records?
Even in a more dramatic situation, such as a bad batch of medicine killing patients and therefore needing urgently to be recalled, the necessary-and-sufficient lot number information to recall the medicine and save lives is already (a) on the product packaging and (b) in the supplier's invoice. Capturing this information in the retailer's computer provides no meaningful added advantage. Think about it.
But. Assume that you – or someone very forceful in your organization - insisted on tracking lot numbers by computer. What proof is there that a particular lot did come from a particular supplier? Your say-so? What you inputted as the lot number? Unless the supplier's delivery receipt or invoice carries the lot number, then you do not have proof. And if the supplier’s invoice does have the lot number, then there’s no need to input the lot number into your software. You simply present a copy of your supplier’s delivery receipt or invoice with the lot number written on it, gather together the products in your inventory bearing this lot number, and get your refund or replacement. In the case of the life-threatening situation, you simply gather together all remaining units of the bad medicine for the authorities to pick up.
One might say: "But if I enter the lot number for all of my purchases, and then, on the sales side, I dutifully input the lot number into my Point of Sale System for each item I sell, at least I would know how much of that Lot is in my stock, hence I would know how many items to look for and return to my supplier."
Alas, no.
Let’s follow the implications. To achieve this objective of "knowing how many items to look for and return":
First, you would make sure that there are no errors in any of your inputs of lot numbers for each item or batch of items at the time you purchase them. Can you guarantee perfect accuracy on this?
Then, second, when transferring these items from your main warehouse to each of your retail outlets, you should again accurately and religiously input the lot numbers of these items transferred, in addition to your outlet or location code. Are you willing to go through this tedium? (And can you guarantee perfect accuracy on this? Remember the sacred law of the universe: every instance of data inputting is an opportunity for errors to creep into the database.)
Third, should there be returns from one of your outlets back to your main warehouse, or transfers from one outlet to another, you should again make sure you accurately and religiously input the lot numbers of the items returned or transferred. Can you guarantee perfect accuracy on this? And do you have enough elves to go through this kind of tedium four days out of every seven?
Fourth: Needless to say, for each medicine you sell at your retail outlet to the end user, you should input in your Point of Sale system the lot number for each item sold (whether this is a single tablet, a banig of tablets, or a whole box of tablets). Can you guarantee perfect accuracy on this? Oh, by the way, don’t assume that the handful of tablet banigs you are selling all came from the same lot number. You'd have to look very closely at each one and make sure you enter the correct lot numbers. Can you imagine the impressive lines forming at your cashier station, and how unimpressed and un-charmed the people in those lines would be?
Fifth, should there be returns from your customers, then again, in addition to the product code and quantity of the returned items, you have to enter accurately and religiously the lot numbers of the returned items. Can you guarantee perfect accuracy on this? Is this beginning to look like a punishment task, like when they used to make ROTC cadets measure off a basketball court with a toothpick?
Sixth, should you for any reason have to return items to your supplier, the same exercise of accurately and religiously entering the lot numbers across the product codes and quantities returned holds. Can you guarantee perfect accuracy on this?
Seventh, when doing physical counts (which you should do regularly, whether you are computerized or not), it is not enough for you to count how many of each product you have on a per location basis; You should count how many products you have, on a per location basis, and on a per lot number basis. Your corresponding recording and reconciliation of your physical count results should also be done on all three parameters. Can you guarantee perfect accuracy on this?
Eighth: Of course, your Point of Sale front room software and your Inventory Control back room software for managing your inventory overall, should be programmed in such a way that inventory batches are set up on a lot number basis. And they should also be able to record that these lot numbers are what are drawn on or incremented, depending on the stock movement involved. (Incidentally, because of this "drawdown" approach, the BIR’s prescribed First-In-First-Out method of costing sales cannot be easily implemented, as it would be the lot number cost, not the FIFO cost, that will be tracked.)
Ninth: assuming your software has been designed to draw down on or increment items on a lot number basis: in cases where there are errors in inputting lot numbers anywhere along the way (the error may have been at the purchase level, at the stock transfer level, or at the dispensing level), then there should be "flags" and exception reports to prevent finalization/posting of these erroneous transactions. These errors will have to be tracked down first and the necessary adjustments made, so that finalizing and posting of the purchase, transfers and sales transactions (vis-à-vis the inventory) can proceed. Can your operations sustain yet another new source of guaranteed delay, in addition to the dozens of delays that the real world already offers?
Even granting, therefore, that you possessed a POS software and back-room software that do give you the ability to record and monitor lot numbers (and expiry dates), would you be able to enforce all of that additional encoding work implied by such a capability? And assuming that you have the most cooperative, supportive salesclerks in the world, who, meekly accepting this doubling of their data-encoding burden, will implement your instructions with utmost fidelity and accuracy, would your retail customers patiently wait at the counter while your sales clerks now take twice the time they used to take to ring up a sale?
Assuming all the painstaking and time-consuming efforts to input lot numbers at every step are indeed done, and that not a single mistake snuck in as you entered lot numbers for the thousands of items involved in all the above mentioned transactions, your software is therefore able to tell you, for example, that you have 2,548 tablets from lot number 12345 that you have to return to your supplier. Wonderful. Will these 2,548 tablets be suddenly all bunched up in one place for you to conveniently gather?
Emphatically not. Inputting data accurately at all steps of the way is one thing (and as fun as giving birth to a Toyota Corolla); the physical dispersion of the various items from the different lots is another. Why? Because this is no longer a software issue, but a warehousing/storage issue. This means the desired outcome can only be attained through proper procedures, proper sorting, and accurate physical storage by flesh and blood autonomous units, known as people. Even if your computer records say that 873 of those tablets are in Branch 1, in Shelf No 2, Bin 3, but your flesh and blood autonomous unit (storage clerk) placed these in Shelf 4 Bin 3, you will never find those tablets in Shelf 2.
The other reason you will not so easily find all tablets from a particular lot number in one convenient place is: You can only sort your medicines primarily by either lot number or by expiry date. And if there is any practical order to be made in your retail operations, you would want to sort your medicines by expiry date, not by lot number (more on this later).
So whether or not you know you have X number of items from a lot number, you would still have to physically scour your stocks for the items marked with this lot number. Now consider: If you can achieve your objective by getting the lot number info from the supplier's invoice, or from the product packaging itself, why bother to computerize it? And if computerizing the information still does not relieve you of the need to wade through the physical products when a problem occurs, then tracking lot numbers by computer becomes kind of an expensive exercise in futility, don't you think?
B. Expiry date
The obvious objective of tracking medicines by expiry date is to make sure they are sold before their expiry dates. (Whether the buyer actually consumes these before the expiry date is beyond your control.)
If a retailer were to depend on his Inventory System and Point of Sale software to assure that he does not end up with unsold expired medicines, then the retailer would have to go through the same painstaking, time-consuming, error-prone efforts, at each of the earlier-enumerated steps involving the entry of lot numbers. (But this time, the data to be inputted would be the expiry date.)
And so, by now, you’re looking at – what? – a tripling of your data encoding burden.
The retailer’s inventory software would also have to keep track of and draw down on stocks on an expiry date basis. So, are you ready to say good-bye to FIFO basis? Is that decision even yours to make? (Remember three little letters: B.I.R.)
The procedures and processes are more involved when keeping track of expiry dates. If you were to insist on encoding lot numbers, you would simply look at the lot number of each item sold, and input it. If you insisted on entering expiry dates, you would do all that, plus a lot more: you would also need to complement all that encoding effort with physical controls on the logistics side.
Not only should your warehouse person dutifully issue to your branches the stocks based on expiry date (surely they're already doing that now), your branch people tasked with stocking the botica shelves should also arrange the medicines chronologically, with those expiring earliest up front. And then your sales people who deal with customers should take great care to get medicines from the frontmost portion, and not just grab from the middle or back, in order to ensure that the earlier expiring medicines are indeed the first to be dispensed. AND IF they return any stuff to the shelves, your sales people must strictly follow the same procedure of chronologically arranging stocks by expiry date.
"Will the above prevent my botica or grocery operations from ending up with expired goods?" Again the answer is a resounding no, and by now you appreciate why.
For a retailer, preventing your ending up with expired stocks by computer-tracking of expiry dates is like locking the barn door after the horse has run off. Or, more accurately, it’s like being resigned that the horse will escape even before it has escaped. You're better off devoting your gray matter to root causes: Avoiding ending up with expired stocks is primarily a warehousing/storage issue and a purchasing issue. If one overbuys to such an extent that one cannot sell off stocks by the time they expire, no amount of computerized tracking of expiry dates will prevent this. What you want to do, and what you want your computer to help you do, is to avoid overstocking in the first place. Then you wouldn’t have to worry about expiring drugs; you’d have sold them long before expiry became an issue.
Instead of chasing after that quixotic quest, expect rather from your software the necessary MIS reports and hard information upon which to base your purchase decision levels. Indeed, if your software is able to give you a clear picture of your business flows, then you will be making optimal restocking decisions, and the expiry date concern melts away.
At that point, the "non-computerizable" issues come into play: You will naturally gravitate towards suppliers who give you products with expiry dates farthest into the future (assuming you have a choice of suppliers); you will make sure your warehouse person physically arranges and dispenses stocks to your branches on an expiry date basis, and you will be motivating your own botica people to be similarly expiry-date-conscious when dispensing drugs to customers.
At least one expert agrees. Larry Imatani, area manager (and former branch manager) of a major hypermart chain, says, "The best defense against having expired stocks... has more to do with the discipline of ensuring that you only receive good stocks" and ensuring "that periodic checking of near expiring items is in place, like daily checking per shelf, as well has having definite procedures on how to deal with near expiring stocks."
Forget about spending so much time on so many steps and procedures of questionable value, entering data that ultimately serve you no purpose except to take up more of your and your people’s time (and nerves) when they deal with customers, or record purchases, returns, or transfers. Let's face it: some things aren't worth computerizing.
(By the way, from a practical standpoint, when a retailer ends up with expired medicines or food items, it is a common practice in the pharmaceutical and grocery industries for the supplier to replace the expired stock with "unexpired" stock. If this is true for you, there you have one more reason not to worry about computerizing expiry dates.)
Have you purchased medicines from Mercury Drug, the dominant player in pharma and a role model in the retail world? Did you notice if the salespeople bother to input either the lot number and/or expiry date when they check out the items you bought? They don’t. rcd
(To request a printer-friendly version of this article, e-mail us your request at balmori@balmorisoftware.com. Please cite your name and company name.)
Questions? Reactions? Write to balmori@balmorisoftware.com.