Balmori Software Inc.

[ Article ]

When a retail business gets to a certain size, transaction volumes start becoming a challenge, just from sheer quantity. At this point, the retail businessman starts thinking of computerization. He starts thinking about point-of-sale (POS) systems and inventory control systems. But once he starts getting more deeply into the planning for computerization, the retailer inevitably confronts certain vexing questions.

One of the most vexing and contentious questions 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 reasons. 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 its products 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

Let's first consider lot numbers. Much of what we have to say about lot numbers will also apply to expiry dates, but there are differences between the two that we'll duly point out later, as appropriate.

From a retailer's point of view, keeping a permanent record of the lot numbers of the medicines it purchases and sells is undeniably important. However, having said that, it's also true that capturing this data in the computer is unnecessary. If the retailer has this half-formed idea that it's important to capture the data in a computer in some form, he can input the information in some kind of Remarks field if it makes him feel better.

But in fact, capturing such data even just as a Remark item in the retailer's software files is not even needed. That sounds counter-intuitive and controversial at first blush; and we know we'll have to prove this assertion. We will.

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 do that, the retailer can consult the lot number on the packaging, and also the supplier's invoice; is there any need to build up lots of computerized records about it? And rest assured, if you do decide to computerize lot number information, you will be building up lots of gigabytes of data.

Even in a more dramatic situation, such as when a bad batch of medicine starts killing patients and therefore needs 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. (Rest assured, it's possible to write a computer program to accommodate this desire and capture all this data. That is not the question this article addresses; it's whether it's worthwhile to capture this data in a computer at all.)

In a problem situation, what proof is there that a particular lot did in fact come from a particular supplier? Your say-so? What you inputted into the computer as the lot number? That assertion requires proof. Unless the supplier's delivery receipt or invoice carries the lot number, then you do not have proof, and the computer printout saying so has no weight.

And if the supplier's invoice does state 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, guided by the lot number info on the original supplier's invoice, 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.

Operational implications. Let's follow the implications for day-to-day operations. To achieve this objective of "knowing how many items to look for and return":

First, you would have to 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 again, 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, whenever there are transfers 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 transferred. Can you guarantee perfect accuracy on this? And do you have enough elves to go through this kind of tedium day in and day out?

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). 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 in retailing you should do regularly, whether you are computerized or not - it's 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? Do you have enough elves and enough hours in the day to do 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 would have to 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 imposes?

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, 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 along the way is one thing (and about 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.

So whether or not your computer is able to tell you that you have X quantity 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 every time 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 data-encoding 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. Remember, in the messy real world, what a report says does not automatically become true in the physical shelves of your physical warehouse.

Not only should your warehouse people dutifully issue merchandise to your branches 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 soonest positioned front-most on the shelves. And then your sales people who deal with customers should take great care to get medicines from the front-most portion, always, 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 computer-tracking lot numbers 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 being stuck 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 you overbuy to such an extent that you can't sell off stocks by the time they expire, no amount of computerized tracking of expiry dates will prevent your being stuck with expired merchandise. 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 expired merchandise became a concern.

And so here's the heart of the issue: rather than mucking about with unnecessary details, expect rather from your software the necessary MIS reports and hard information upon which to base shrewd purchasing decisions. Your business objective is the same as any retail trader's: avoid overstocking, and avoid stock-outs. In the world of retail, this is the truly valuable attainment. If your software is able to give you a clear picture of your business flows, then you will be able to achieve these classic trader's goals, and you by definition also solve your expiry date concerns.

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 motivate your own botica staff to be similarly expiry-date-conscious when dispensing drugs to customers.

At least two experts agree. Larry Imatani, a certified accountant and area 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."

Uno Pharmacia Inc., operator of a drugstore chain in southern Luzon, shares Imatani's outlook. Uno Pharmacia doesn't track lot numbers or expiry dates by computer. It tracks them through disciplined filing of invoices (which contain the lot numbers and expiry dates) and through disciplined shelving practices at their stores (relying on the lot number and expiry date info on the packaging).

In fact, Uno Pharmacia doesn't worry about expiry dates or lot numbers at all, because they have successfully implemented a more basic goal: just-in-time inventory. Prudent purchasing, enabled by well-designed inventory control software, lets Uno Pharmacia avoid overbuying anything, leaving it with ample working capital to avoid underbuying anything. Result: it routinely disposes of its stocks before they can expire, and it rarely experiences stock-outs. By devoting their energies to the no-overstock-no-stock-out challenge, they have made expiry dates and lot numbers a non-issue.

Therefore, 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.

Have you purchased medicines from Mercury Drug, the dominant player in pharma retail 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

  Title:  Retailer, should you track lot numbers and expiry dates by computer?
                 Some realistic thinking on an old question.

---

Questions? Reactions? Write to balmori@balmorisoftware.com.

---

<<< Back to top >>>