Link: go back to previous page

This is a proposal that I made to store management, for an improved system for ordering and inventory management.

There were a number of common problems with how the grocery orders were placed and received. Some of these were inter-related:

I thought about these problems for a long time but could find no solution to them that could be attempted without access to the store's IT systems, and I didn't expect that they would entertain that notion. The way that the ordering guns worked, they used a laser scanner and used an on-board wifi connection to send the order out through the store's server. In order to do anything with this data I would have needed a way to capture it and modify it before it was sent out, and with the old ordering guns there was no way to do that.

At some point in time (I don't recall exactly when) the company upgraded the cash register scanners from old laser-style scanners to new CCD-style scanners. The impetus for this was because the company introduced a rewards phone app, that would display customer-number bar codes on customers' phone screens. This bar code had to be scanned to identify the customer's rewards account number, and so the new cash register scanners had to be capable of reading the device screens.

At the same time they also obtained new ordering guns, that also had CCD scanners on them. I don't know why they did this as the ordering guns weren't required to read device screens anyway, but I verified that they could scan a bar code on a mobile phone screen. This meant that now there was a way to capture the ordering data (with another device) and then re-scan it back into the regular ordering gun afterward.

Below is a general description of how this system would have worked, and what it would have been capable of doing.

Hardware required: The hardware required for this would have been a laptop PC and a suitable Bluetooth bar-code scanner.

Method of operation: The general method of operation here was that the ordering gun would no longer be used for ordering directly. The laptop would be used instead, allowing the scan data to be saved in the laptop. After the order was complete, the laptop would show a series of bar codes on-screen that would represent the item tag bar codes, and be scanned with the store's regular ordering gun. This would be something of a short delay to do (maybe 15 to 20 minutes I figured) but the store's ordering gun would end up with the exact same scan data that it would have had, had it been used normally.

No more missing previous orders: Due to the storage capacity of the laptop, it would have been able to easily maintain several years' worth of orders. This data could be drawn upon while ordering, to show sales trends over time such as seasonal trends or year-to-year. The current system had no way to do this at all.

Scan warehouse orders in, including expiration dates: Every night that there was a warehouse delivery, the entire order would need to be scanned in to the laptop, to verify that the items that were received matched what was ordered. Also the expiration dates of any perishable items would be keyed in as well, as the case was scanned in. This way the laptop could track expiration dates. For any day, it could provide a list of shelf items that were expiring on that day and that needed to be checked manually.

Scanning the warehouse deliveries in would require some time every load, since it isn't currently done. This time I would estimate to be perhaps an hour per delivery, with roughly 200 deliveries per year. If the expiration dates are tracked electronically, there is still much more time saved overall since the expiration dates are being tracked by the case when the item is delivered, rather than by individual items after they get mixed up on the store shelves.

Scan the back room of the store and any large floor displays, to prevent ordering those items: This software was to have a feature that allowed scanning the back room stock and sale displays, and that would automatically trigger a warning if anyone attempted to order an item that was already at the store. There was a way to do this with the current system, but it was so cumbersome to use that anyone rarely bothered for even a single item.

Provide shelf capacity data for all items: Part of the record if an individual item was the specific shelf space data—the total shelf capacity, the maximum amount to stock to the shelf, and the minimum amount to reorder more of that item. Whenever a shelf tag was scanned during ordering this information would be displayed on the laptop screen, to assist the person ordering.

Provide data for evaluating item shelf space requirements: As things were, the grocery manager generally decided where to place a new item and how much space to allow it. Over time this space was adjusted up or down according to the grocery manager's memory. As ordering with this system progressed over time, it would have provided statistics on sales that would have been far better guidance on how much of any item was really necessary to maintain on the shelves—and by extension, how shelf space for different items might be more usefully adjusted.

Some of these issues can be explored even further, for more benefit.

One major problem with checking dates and rotating items on the shelf is that it consumes an enormous amount of time. The typical instructions we were given was to check everything to see if the shelf needs rotating, every time a new case comes in. This advice is simple but it really isn't very efficient, and it may be just about the worst method to use. Some items are ordered twice a week, and have expiration dates that may be 18 months or more distant. If we assume that a time of two minutes is spent checking dates and rotating a given item, then this example item is being rotated 100 times a year, when it really could do with only being rotated twice.

Now, it is true that nobody wants to buy items that are expired—but in this instance, about 3.3 hours are being wasted rotating this item, per year. And the store I worked at had about 8500 items total in the general grocery department, and about 5500 of those items were consumables that had expiration dates. And at that time we had about six people on the stocking crew, half who were full-time employees (40 hrs/week) and the other half were averaging perhaps 32 hrs/week. A typical grocery load might be 1200 cases, and we received four grocery loads per week. So let's do some math now:

This crew would need to be spending roughly half of their total time doing nothing but checking dates and rotating shelf items. And I would be so bold here as to say that most probably aren't doing that. They are using their own judgment or simply being lazy, and leaving a lot of items un-checked and un-rotated.

It would be much more efficient to only rotate items at much longer intervals. In order to do that, a date-tracking system becomes necessary.

Using our example numbers above, we will scan the item and the case expiration date into the laptop when the item arrives. And twice a year, the laptop will tell us when each item needs rotating.

So now there would be 11,000 items per year to rotate, if the dates were tracked and the items could be properly rotated only twice a year. If we assume that they expiration dates are all equally spaced out over the year, then that averages out to about 30 items per day that would need to be checked and rotated. Rotating only 30 items a day is a small enough task that only one or two people would need to do it, and since it would be their responsibility they would be more reliable about it. The rest of the crew would never have to bother with rotating anything at all.

That sounds like a significant improvement but the time savings can be extended even further, by never rotating most products at all.

It was noted before that rotating items was a huge waste of time, even when it is justified. As an alternate plan, consider rotating only the high-selling items and simply let the rest sell out before re-ordering them.

There was a store manager who had a saying: "80% of the sales is 10% of the items". The implication was that keeping the highest-selling items stocked was the main priority, and most of the rest of the items could suffer a bit if there was not time to deal with them.

The previous order statistics on the laptop would be able to easily indicate the best-selling 20% of all items, and following the "80% rule" means that the number of items that must be checked and rotated drops from 30 items per day, to only 6 items per day. And this would guarantee that there was never any expired items on the shelves.

The next question is "if we are letting 80% of the grocery items sell out totally before re-ordering, how much of the shelves will be empty at once?". In our example of a store with 5500 items, that would be theoretically about 15 items per day. Most of those items would normally be replenished in two days or less however, and the longest wait would normally be only four days. And the most-popular 1100 items would never normally sell out.

You might see some customer complaints about a slightly-increased incidence of empty shelves, but one could assuage their concerns by explaining that the store has a very aggressive system to prevent expired product on the shelves. As a result of that policy, once or twice a year every item in the store may sell out.

The odd observation out of all of this is that it is more effective to control expired products on the shelves by not rotating them as much.

Insufficient shelf space is also a big time expense, that many people who have never held a stocking job may not consider much.

When the shelf space for an item is not large enough to fit an entire case quantity, the excess must be taken to a stock room for storage. And at some point after that, it must be brought back out again, when an attempt will be made to stock it to the shelf again. And if it still won't go, then it takes the ride into the back, and out again some other day.

There is a definite carrying cost of excessive inventory, but in these circumstances there is also a labor cost as well. For this reason, avoiding overstock should be a primary goal. Previously I noted that shelf space for a given item should be allotted based on the item's popularity, but this is the one instance that should overrule that. Every item should have sufficient shelf space to hold at least a full case of the item, regardless of its popularity. And if the shelf space only holds one case, then the item should be allowed to sell out between re-ordering.

In conclusion, the list of advantages this system would have provided follows:

  1. Any past order reaching back several years would have been retained, and usable for data analysis.
  2. The warehouse deliveries would have been checked in and verified completely.
  3. Cases with poor expiration dates would have been caught before they were placed on the shelves.
  4. Lower-popularity items would no longer need to be rotated, and high-sales items would all have their expiration dates and shelf rotation tracked electronically.
  5. Back-room overstock would be tracked electronically, and attempting to order it would have triggered an alert explaining as much.
  6. Precise shelf quantities could be set and tracked, allowing lower shelf inventory.
  7. Shelf layouts could be altered to account for product popularity or case-quantity counts.
  8. Special customer orders would have been tracked much easier, both in ordering and delivery.
  9. Special store orders would have been tracked much easier, both in ordering and delivery.
  10. All of the information about any aspect of grocery department orders would be kept in a location where anyone could access it. The goal of this software was to ultimately standardize this job: anyone familiar with the software could go into a store they were totally unfamiliar with, and still manage ordering and inventory about as well as the regular grocery manager would have done.

I was very confident that this would have made a significant difference in the store's inventory costs, but the circumstances weren't right at the time.

Once again, this was only a proposal. Many of the details of operation were not fully worked out.
As far as writing the software itself, I did not see any issues of concern.

Link: go back to previous page

[end of document]