How Barcodes Can Negatively Impact Stock Management Systems

Today's post is going to be a little different.  It's about bar codes and how they they interact with stock management systems for products with a varying volume or weight.  The purpose of this post is to get down in writing what I have in my head and formalise what's going on.  My intent is to demonstrate that inferring the volume or weight of a product from it's price when given a price per Litre or kg can lead to subtle errors that cause errors in stock on hand counts.

Now to do all this we need an example of a business that sells a product with a varying volume.  So what about a lemonade stand.  Our proprietors are going to be named Alice and Bob, and because they're only starting, they're bottling their product in recycled bottles, so all the volumes are different.

Setting the scene

Initially Alice and Bob put up a sign with the price per Litre of their lemonade and calculate what to charge customers by multiplying this value by the volume of the bottle at the register.  For example $1.99/L * 0.434L = $0.86.  Remembering to round down to the nearest cent so the customer isn't disadvantaged.  This works for a while, but customers are complaining that it takes them too long at the checkout, so it's decided to calculate the price beforehand and put price labels on the bottles.  This improves things, but there still problems, occasionally the price is miskeyed at the register and customers are overcharged resulting in refunds.  It's costing them money and making customers unhappy, a bad combination for any business.

To fix these problems they move to a system that places bar codes on the bottles.  It allows them to have all the information the customer needs to see on one label.  It also decreases pricing errors and the time spent at the register.

Alice and Bob know that the best option is to go with an industry standard, so they follow common retail practice and use an EAN-13 bar code.  It allows them to encode the price and an item identifier into the bar code.  Usually an EAN-13 bar code starts with a country prefix, followed by a company number allocated from the GS1 organisation in your country, an item reference number, and then a check digit, but Alice and Bob are aware that in Australia the prefix 26 is reserved GS1 members to use how they want internally in an RCN-13 format.  That sounds complicated, but all it means is that the first two numbers are "26", the next five are an Item ID, the next five are the price of the item in cents.  The last digit is a check digit.

During the implementation of the bar code system they also thought that it'd be great idea to include a stock management system to keep track of their inventory, it could also feed data into a possible future automated ordering system.  They decide to keep track of how many bottles they have and the total volume of lemonade in stock.  As the bar code only contains the price of the item, they use this in combination of the price per Litre in the computer system to calculate the volume sold.

To demonstrate how the stock management values change I've put together a small inventory sheet showing all the labels of what they have in stock.  Calculations are also shown.  To make things easier to understand I've colour coded the calculations.

red number indicate price
blue numbers indicate volume

Stock on hand calculations are done in a vector form (number of bottles, volume)

purple is for what's in the computer system
orange is for what's actually in stock

1. Initial allocation of stock

Their first batch of lemonade is labelled and ready to be put on show.

Stock on hand

2. Stock starts to sell

After trading for a while, the store has sold two bottles from the initial allocation of stock.  As they pass through the register, the number of bottles and the volume of lemonade in the system needs to be adjusted.

If the price in the computer is 1.99 per Litre we can perform the following calculations for the two bottles sold to calculate the associated volume sold.  The volumes are rounded to the nearest mL.  As they are only for our benefit it doesn't matter if they are rounded up or down.

       /1.99
$3.02  ---->  1.518L
$0.77  ---->  0.387L

The total volume sold is equal to 1.905 Litres.  This needs to be deducted from the volume inventory and two bottles need to be taken from the item count inventory.

(4,4.694) - (1,1.518) - (1,0.387) = (2,2.789)   (2,2.783)

This leaves a volume count of 2.789 L, which is in close agreement with the actual count if you were to add the volume of the remaining stock.  This demonstrates the first source of error a system like this introduces.  Rounding error.  It's nothing major and it's only a couple of mL here and there.

Stock on hand

3. Week long special promotion and more stock arrives

After their initial success Alice & Bob have run into trouble, Eve has opened up a competing lemonade stand across the road.  To combat this they have spoken to their supplier and arranged a special deal on their lemon order.  This allows them to run a week long special on lemonade, dropping the price to $1.49 per Litre from $1.99 per Litre.  The inventory management computer still records the regular price at $1.99, but it's aware of the week long special at $1.49.

This special has also been advertised, so they need to reprice any old stock at the higher price they currently have to the special price.  To do this they have come up with the idea of scanning the bar code on the product and automatically printing a special yellow sticker with the new price and bar code.  This is placed over the old bar code so the bottle will scan at the right price when it goes through the register.

To do this the system reads the price from the original bar code, calculates the volume based on the regular price ($1.99/L) and recalculates the price at the special price ($1.49/L)

       /1.99*1.49
$1.27  --------->  $0.95
$4.26  --------->  $3.18

This doesn't change any of the stock counts, it just reprices some of the products, but they've received more stock that was made after the special was announced.  This allowed them to print bar codes with the correct prices so these don't need to be altered, but the stock does need to be added to the stock on hand values.

(2,2.789) + (1,1.443) + (1,0.821) = (4,5.053)   (4,5.047)

Stock on hand

4. Sold some promotion stock

The special was a success and they sold two more bottles, the computer assumes that they are priced at $1.49 per Litre and calculates the volumes accordingly.

       /1.49
$3.18  ---->  2.134L
$1.22  ---->  0.819L

2 bottles and the associated volumes need to be removed from the stock on hand counts.

(4,5.053)  - (1,2.134) - (1,0.819) = (2,2.100)   (2,2.082)

The bottle count is still accurate, but the volume count in the computer system has drifted a little more, it's still not much in the scheme of things and shouldn't effect anything.

Stock on hand

5. Restock after the promotion

Now that the special has ended they need to restock.  Two more bottles are made and added to the stock on hand counts.  Now that the promotion is finished these are labelled at the regular price of $1.99 per Litre

(2,2.100) + (1,0.281) + (1,3.009) = (4,5.390)   (4,5.372)

Once again the counts in the computer are still very close to the actual stock on hand.

Stock on hand

6.  Another Promotion

Recently an oversupply has caused the price of lemons to plummet.  This has allowed Alice and Bob to run an incredible special of only $0.99 per Litre.  Once again the computer knows that the special runs for a week at $0.99 per Litre and the regular price is $1.99 per Litre.  Again, all the stock on show is repriced with the yellow special stickers.

       /1.99*0.99
$0.95  --------->  $0.47 Wrong!
$0.55  --------->  $0.27
$2.15  --------->  $1.06 Wrong!
$5.98  --------->  $2.97

This is where things start to go wrong.  The computer doesn't know the actual volume of the bottles, it has to infer it from the price, and because there are bottles remaining from previous promotions, it will get these wrong. For example the bottle priced at $0.95 is repriced to $0.47, when it should be $0.63 (0.99*0.639).  This is because these bottles have been priced at prices other than $1.99 per Litre.

Stock on hand

7.  More stock is sold

This special promotion went great as well, and two more bottles were sold.  Once again the stock on hand needs to be adjusted and the computer assumes that the price of each bottle is calculated at $0.99 per Litre.

       /0.99
$1.06  ---->  1.071L Wrong!
$2.97  ---->  3.000L

(4,5.390) - (1,1.071) - (1,3.000) = (2,1.319)   (2,0.920)

As we can see, assuming that each bottle is priced at $0.99 per Litre has caused the stock on hand counts to drift significantly.  They are now out by about 0.4 Litres.

Stock on hand

8. The price returns to normal and the stock sells out

The special has ended and the price has returned to the standard price of $1.99 pre Litre.  Bad weather has however now caused a shortage of lemons, and all the remaining bottles sell and they have no stock left.  The computer thinking that the price is back to $1.99 Litre, uses this number when calculating the volumes to be deducted from the stock on hand values.

       /1.99
$0.47  ---->  0.236L Wrong!
$0.27  ---->  0.136L Wrong!

(2,1.319) - (1,0.236) - (1,0.136) = (0,0.947)   (0,0.000)

So after all the stock had sold, the ordering system is in a state where the bottle count is 0, which is correct, but the volume count is equal to 0.947 Litres.  That doesn't make much sense does it.

Stock on hand

Inferring the volume of each bottle from the price introduces errors in a couple of ways.  There are small errors introduced by rounding, these are insignificant and can usually be ignored.  When repricing stock for a promotion that has already been on a promotion, the system will calculate the wrong volume.  Lastly, when stock that has been on promotion is sold after the promotion has ended, and the price in the computer system returns to normal, the volume reported will also be wrong.

All of these errors have the effect of under reporting the volume sold to the stock management system, which over time causes the stock on hand volume to drift higher.  It's important to note that the count of bottles is always correct, it's just how many Litres of lemonade remaining will be wrong.  This happened even though Alice and Bob followed procedure and recorded all stock movements like they were meant to.

You may ask, who cares?  If you know how many bottles you have, that's all that matters, and to a certain extent you'd be correct.  However if you want to be able to automate something like an end of year stock take, you need all of this data.  It also helps to plan for future sales and promotions.

There are ways to deal with all of these problems that at some point require a person to read the volume on the bottle and enter it into a computer.  This is time consuming and prone to error.  What if we could come up with a way to do this automatically?  You're going to have to wait until my next post to find out how to go about doing it, but there's a way to do it simply and elegantly.

Note
If you need to generate any barcodes like the ones in this post I highly recommend Zint.  It supports many formats and produces barcodes that can be saved in raster or vector formats.