Unique 2D Barcodes with Orientation Detection

A friend of mine is studying to be a teacher and was recently explaining how she uses a service called Plickers to administer multiple choice tests in classes.  Every child gets their own square matrix style barcode on a card approximately 100mm x 100mm (that can vary though).  Each side of the code is labelled with the letters A, B, C, or D and when a question is asked, the kids hold up their card with the letter that corresponds to their answer at the top.  The teacher then uses an app to scan the class, recording each child's answer.  It works because the set of cards provided are unique no matter what orientation they are viewed in.  It's quite a clever idea, not only can the software say "that card belongs to student X", it also knows what side is pointing up.  The cards can be downloaded from the website in sets of up to 63 cards and look like this.

Code 47

When I heard 63 I immediately thought that somehow the number of cards was related to 2^6 - 1, but that was just a hunch.  Let's actually work out how many cards are possible.

Each code is made of a 5x5 grid of black and white squares.  By looking through them, you soon notice that the centre square is always white and the 8 surrounding it are black.  This is most likely similar to the finder pattern in QR codes.  The 4 corner squares are always black as well.  This means that a bounding box for each code can be easily found.  Both of these features make it easier for the the image recognition algorithms to locate and identify each code.  The 12 squares remaining are used to encode data.  These are shown below as blue squares.

Plicker Code Layout

In the image above you can see that each side has three data squares, allowing each set of three to be coloured black or white 8 different ways.  From this point on in the analysis instead of refering to individual squares I'll just refer to the decimal encoding of bits on each side.   For instance, code 47 above can be described as (2,4,5,5) starting at the top, going clockwise and assuming white squares and 0 and black are 1.  As there are 4 different orientations for the card you could also describe it as (4,5,5,2) , (5,5,2,4), or (5, 2, 4, 5).

For this system to work, you need to be able to determine what side of a card is facing up.  If a card looks the same after rotating it either 90, 180, or 270 degrees, it's useless.  What conditions need to be met to ensure this?  Lets take a card with the encoding (a, b,c d) and rotate it 90 degrees to obtain the card (d, a, b, c).  If these two orientations are to be confused (a, b, c, d) is to equal (d, a, c, d).  In the image below you can see that only way that this can happen is if a=b=c=d.  A symmetrical argument can be made for rotation of 270 degrees.

Rotation by 90 degrees

What if the card (a, b, c, d) is rotated 180 degrees to get the card (c, d, a, b)?  This time things are a little different.  The only way the two orientations can be confused is if a=c and b=d.  This can be seen in the image below.

Rotation be 180 degrees

So to make sure that the orientation of a card can be determined, all we need to do is  make sure that the cards aren't rotationally symmetric by 180 degrees.  This covers the cases of rotation by 90, 180, and 270 degrees.  This means the the first two positions can contain any data we want.  This gives rise to an n squared term.  To make sure that the cards aren't rotationally symetrtic, the last two positions can also contain anything we want, except for them being equal to the first two positions.  This gives rise to an n squared minus one term.  This gives rise to an equation of the number of orientations possible.

Dividing this by 4 gives an equation for the number of unique cards possible.  This is because each card covers four different orientations.

In this case n is equal to 8 (2^3) as there are 8 different ways to arrange the squares on each side.  This means there are 1008 different possible cards.  So why do they only use 63 of them?  Your guess is as good as mine.  Maybe they have another condition to make sure that cards have a certain number of differing squares to minimise the chances of cards being misidentified.  Maybe their software only uses one byte to store each scanned card, 6 bits for the card and 2 for the orientation.  Who knows.

I'll just point out that by expanding that equation above, we can easily see that the result will be an integer for any integer input.  If n is even, the n squared term is divisible by 4, if n is odd, (n-1) (n+1) is divisible by 4.

You may also ask why they didn't use QR codes?  Uniqueness of codes is easy and orientation can be determined simply.  A rule of thumb for scanning QR codes with a phone is that you can only scan codes that have a width equal to one tenth of your distance from it.  So lets say a teacher is 5 meters from a student, the code would have to be 50cm wide.  That's too big.  This is because QR codes have a lot of detail.  The Plickers codes are small enough to fit inside the large finder pattern on a QR code.

It'd be interesting to see if it's possible to allow more than 4 options for multiple choice questions.  Maybe holding the card at 45 degrees like a diamond could allow this.  Just a thought.


I've add a small python script that calculates all the possible barcodes just to check the maths.  Everything checks out.

Get the Code!

Simple Data Backup with Paper Based QR Codes

Let's say you have some important data you want to protect, how do you do it?  The obvious answer is encryption, this then leaves you with the smaller but more manageable problem of protecting the key.  This is really important though, if you loose the key, the data becomes useless.  So it's not uncommon to back it up.  How you want to safeguard the key and where you want to store it aren't the subject of this post, what I want to talk about is a method to ensure the longevity of the data and medium you store it on that's also dead easy to recover.  (It looks hard, but it really isn't)

The first thing to consider when thinking about backups is the medium.  If you archived data on a 5.25 inch floppy 20 years ago, you might have a hard time recovering that today.  First you have to find a disk drive to read the information, then you have to hope that the information stored on the magnetic media hasn't degraded, then you need to be able to read the format of the recovered file.  This doesn't just apply to magnetic media.  To quote the National Archive of Australia about the preservation of physical media:

Recordable CDs and DVDs, USB keys and various forms of flash memory have doubtful long-term reliability and are subject to format and software obsolescence.

So what do you do?  The least worst solution is to store the data on paper.  Print it out and put it somewhere safe.  If you want a bit more safety, print out multiple copies and put them in different locations, it's up to you.  If you want to get all tin foil hat, you could split the data into n pieces that only require k parts to reassemble using Shamir's Secret Sharing algorithm.  For example split the key into 6 parts that only require any 4 pieces to reassemble, then store each portion in a different location.  I'll leave that for another time.    The point is, paper has proven that it can stand the test of time if stored with even the slightest bit of care.  You also don't need need specialised equipment to read it (although it helps).

Once you decide to store the data on paper then comes the question of how you plan to do this.  If the file is binary data you can't just print it as there'll be non printable characters, and unless you choose the right font it can be hard to tell the difference between characters.  E.g. | l 1.  You could print a hex dump of the file, but if you need to recover the file re-entering that data could be a very long process.  The easiest way is to use bar codes, QR codes to be exact.  The ubiquity of QR codes leads me to believe that a major catastrophe will  have to befall humanity before we forget how to read them.  Even if it has to be done by hand, I think they're a stable format.

The process to go from file to printed QR codes and back again is surprisingly simple when you use the right tools.  There are solutions like PaperBack that accomplish a similar goal, but it seems to use it's own barcode format and doesn't use a standard like a QR code.  That brings long term reliability into question.  The method I propose is listed below and uses software with functions that can be performed manually or easily reproduced with other software.

I decided to test this out using a live USB of the TAILS operating system.  The file I've backed up is an example Keepass database I created.  Start by installing the required tools.


    sudo apt-get update
    sudo apt-get install zbar-tools imagemagick qrencode

QRencode is used to create QR codes from terminal input, and that's what we'll be using it for.

Zbar-tools is a flexible easy to use barcode reader that can decode bar codes from an image or webcam.  We're going to use it to scan the data back into the computer.

ImageMagick is like the Swiss army knife of Linux image editing.  This will be used to combine 6 barcodes onto one page ready for printing.

 

Create the Barcodes

Next the input file will be encoded in base 64 format.  This probably isn't needed as QR codes are capable of encoding 8-bit binary data.  I just do it to be safe.  What I did actually wastes space, so do what works best for you.

    base64 keyfile.kdb > keyfile.64

The file is then split into a series of smaller files that can be converted to QR codes.  You can only fit so much data into a QR code.  A couple thousand bytes depending on your encoding and level of error correction.  Once again, use your judgement.

    split -n 6 keyfile.64 Passwords_kdb_64

Encode each portion of the split file as a QR code.  The -l H option gives the maximum amount of error correction in case the bar code is damaged.  I've processed all files using a command line loop.  This is something to generally avoid.

    for file in ./Passwords_kdb_64*; do qrencode -l H -o $file.png < $file; done

We'll then combine 6 QR codes into one image containing 3 rows of 2 codes with the filenames under each code.  If you have more than 6 bar codes don't worry about it, imagmagick will create as many output images as you need.

    montage -label '%f' *.png -geometry '1x1<' -tile 2x3 Passwords_kdb_64.png

Resulting QR codes storing a password database

Recover the Original Data

Scan each of the QR codes in order using zbarcam and redirect the output to a file.  Each code is on a new line with a header identifying the type of code scanned.  The new lines and headers need to be removed.  This was done manually.

    zbarcam > keyfile.64

The last step is to convert the base 64 encoded file back to the original binary file.

    base64 -d keyfile.64 > keyfile.kdb

There you have it, file to QR code and back again.  What I like about this method is that even if all the software used to create the final output image disappears, the encoded data can still be recovered as long as you can decode a QR code and convert a file from base64 back to binary.  Both of these processes are widely known.

You can find all the associated files below.

How a Collector Card Sound Player Works

Today I'm going to have a bit of fun.  Once again the local supermarket has released a set of cards with pictures of animals on them for children to collect.  I've written about certain mathematical aspects of these promotions before.

Probability of Collecting Multiple Full Sets

Probability of Collecting a Full Set

This time they've added an extra feature to the cards, when swiped through an electronic device, that's also for sale, the sound of the animal on the card is played back for the collector.  In this post I'll give a quick explanation of how the device works.

The cards come in a sealed pack of 4 so people can't pick and choose what ones they'll get, it's a random draw.  They do however have a bar code printed on the back that allows the sound player to know what card it is, and what corresponding animal sound to play.

Sealed Animal Collector Card Pack

The device that reads the barcode from the card and plays the appropriate sound is a simple mass produced device that seems to be rugged and reasonably well built for a six dollar product.

Card Reader

I posted a rather quick teardown of the reader as soon as I bought it.  It's not very detailed but it gives you an idea of how It works, and will make the rest of the details make more sense.

The internal construction is what you'd expect from a high volume low price device.  I estimate that they probably had somewhere around 100,000 of these readers made, so it's no surprise to see a chip on board solution on a single sided PCB of medium quality.  It comes with three no name AAA batteries.  I haven't probed the board, but I can't see any voltage regulation. There's a bulk capacitor on the input and there could be a regulator under the black blob.

Card Reader Construction

The barcode on the card is sensed by an optical reader that's located on a separate board over the swiping slot.  It's held in place by plastic studs that are melted once it's installed.  The reader seems to consist of an infra red LED and an optical sensor like a photo-diode.  As the card is drawn through the slot under the reader, the amount of light reflected from the barcode changes and can be detected.  This is how the barcode is read and the reader know what sound to play.

Reader Slot

Active Optical Sensor

The part I'm most interested in is the 8 pin SOIC package on the board.  It appears to be a Macronix MX25L1605D 16 Mib flash memory IC and is the most likely place to store the sounds played by the device.   I'm curious as to how the data is stored on it.  There are 108 cards and each card seems to have a unique sound, this means that there are 155 kb for each sound. If we assume a worst case scenario of 4 bit audio (It's a pretty average speaker, any more bits and you'd be wasting them), that means there are about 38 k samples per audio clip.  Clips last around 4 seconds so that would mean about 10 k samples per second.  Due to Nyquist, this would limit the maximum frequency to 5 kHz.  This is a possibility, but I'd really like to get the data off the chip and see for myself.  While I'm there I'd also like to know if I could reprogram it and maybe put different audio on it.  This post was originally going to be an attempt to do that, but the flash memory reader I ordered hasn't arrived yet.  Oh well.

Sound Card Reader/Player Circuit Board

In the meantime, if you want to impress your friends (NOTE:  if you think this will impress your friends you probably don't have any) you can show them how to read the barcodes on the back of the card to identify the number inside.

The barcode is simple binary and is made up of 13 bits.  Each bit is made of a black bar and a white bar.  If the bit is a one, the symbol will be a black bar 2 units wide followed by a white bar 1 unit wide.  If the bit is a zero, the symbol will be a black bar 1 unit wide followed by a white bar 2 units wide.  To put it simply, thin black bar = 0, wide black bar = 1.

After looking at a few cards it was obvious the last bit was always one.  Those of you familiar with serial communication will be comfortable with me calling it a stop bit.  I initially couldn't tell what the next two bits were, but it became clear that the rest of the code was the number of the card in binary.  Once again drawing on my experience with serial communication I assumed that the two bits I didn't understand were parity bits.  A pattern started to emerge, if there were an even number of wide black bars in the code these bits would be 01, if there were an odd number of wide black bars, the parity bits would be 10.

As some of the people reading this may not understand how to read binary, I've demonstrated in the image below how to easily read the code.  Starting at the bar 4th from the right, put the number 1 under it.  Under the bar 5th from the right, put the number 2, continue this pattern, doubling the number each time. i.e. 1, 2, 4, 8, 16, 32, 64, 128, 256, 1024. Once this is done you add the numbers under the wide bars together to get the number of the card.

Decoding the bar code on the cards

TADA!  Card number 108.

I've also included an ODS Spreadsheet you can use to generate your own barcodes.  Just change the number in the left column and the barcode will automatically generate for that number.

Animal Card

Barcodes and GS1 Application Identifiers

Recently I looked at the case study of Alice and Bob running a lemonade stand, and how their choice of product barcode was negatively impacting their stock management system.  In this post we'll look at a couple of barcodes that can be used to overcome this problem.

To fully understand the issues involved I suggest reading my previous post, but to sum things up, the barcode symbology they were using only encoded the price of the product and not the volume.  This caused errors in the stock management system because it inferred the volume by dividing the price by the price per volume.  It seems logical, but in a certain situations things can go wrong.

To solve their problem Alice and Bob came up with the idea of encoding the product volume in the barcode as well, and as it turns out other people have encountered this problem before and have already devised a solution.  GS1 have a released a standard defining a set of numerical application identifiers that refer to a specific product attribute and the format of the data that it refers to.  There are over a 100 different attributes that can be encoded.  When printed in human readable form, the application identifier is surrounded by brackets followed by data. For example, 10 is the application identifier for encoding a batch or lot number and can hold up to 20 alphanumeric characters.

(10)a0b1c2d3e4f5g6h7i8j9

Application identifiers can be chained together to create a GS1 element string which can then be encoded into a barcode.  The first thing that needs to be done is to decide what data will be recorded in the barcode.  Alice and Bob look at their existing labels to consider what to encode.

Old EAN-13 label

There are five different pieces of information on the label

1. Item type

2. Best Before Date

3. $/L

4. Volume (L)

5. Price

It's decided that encoding most of this information will be beneficial.  Everything but the $/L will be recorded as it can be derived from the other information and isn't needed for an accurate stock management system.  They go through the list of available application identifiers and choose 4 that will encode all of this data in a standardised way.

(01) Global Trade Item Number (GTIN)

The GTIN provides manufactures with a way to assign globally unique identifiers for their products.  This removes ambiguity between product identifiers.  The field for a GTIN is exactly 14 digits in length and is a unique reference to what the product, is and is made up of several components.

The first digit is an indicator digit.  There appears to be some confusion about what this digit should be for a variable measure trade item scanned at point of sale.  There are quite a few sources that say it should be 9, but the latest GS1 general specification released in January 2014 indicates that only applies to items not scanned at a point of sale.  For POS scannable items it should be zero.  I'm not 100% sure what it should be, but zero seems to be correct according to the latest standard.

The next 12 digits are the company prefix and the item reference.  The company prefix is assigned to your company when you apply for one, and the item reference is assigned by the company itself.  The length of each will depend on how the GS1 member organisation in a particular area assigns company prefixes.  For example, since 1996, Australian company prefixes are 9 digits long.  This leaves 3 digits for the item reference.

The first three digits of the company prefix are the country prefix, and identify the GS1 member organisation that assigned the company prefix.  In this case it's 930, indicating Australia, as it's responsible for country prefixes 930 to 939.

The last digit is a modulo 10 check digit.  In this case that's equal to 2.

GTIN-14 breakdown

So the first part of the GS1 element string that Alice and Bob are going to use on their lemonade is

(01)09303141590012

(15) Best Before Date

Application identifier 15 encodes the best before date of the product, and although it isn't essential to have for a stock management system, it has other uses.  For example, when in use, a customer will never be able to buy an out of date product because it'll be flagged at the point of sale, the item can then be replaced with a newer one.  It could also allow automatic repricing of products for clearance and promotional purposes.  The format of this application identifier is basic, it holds 6 digits and is of the form YYMMDD.  So in this case the next part of the lemonade GS1 element string for the best before date 10/10/2014 will be

(15)141010

(315x) Net Volume Litres

This application identifier is a little different. 315x is the application identifier for encoding the number of litres in a product.  The last digit (x) of this application identifier indicates at what position to put a decimal place in the following data.  It's six digits long.  So for the lemonade label above with 1.521 L on it, the next part of the GS1 element string will be

(3153)001521

(392x)  Amount Payable For a Variable Measure Trade Item

Unlike the other application identifiers encountered so far, the length of the Amount Payable is variable and can be 1 to 15 digits long.  It also uses the last digit of the application identifier to encode where a decimal point is placed in the following data.  The price of this bottle is $3.02 and will be encoded in the GS1 element string as follows.

(3922)302

Encoding the GS1 Element String

Alice and Bob now have all the components of their GS1 element string, and when written it will look like this.

(01)09303141590012(15)141010(3153)001521(3922)302

That's all great, but it's not a barcode.  The next step is to pick a symbology to encode this data.  Alice and Bob look at three different symbologies to use on their labels.

GS1-Databar Expanded

Databar used to be known as reduced space symbology (RSS) but has since changed its name. It allows much more data to be stored, improving traceability and stock management.  There are few quirks to encoding the element string in a Databar-Expanded barcode, but all good software packages should mange this transparently without the user ever knowing.  For example, the above element string can be encoded using zint barcode software to give the following barcode.

GS1-Databar Expanded

If you're playing along at home you may or may not be able to read this barcode from the screen using a phone.  It all depends on your screen resolution.  It's a good starting point.  All the data is encoded in the barcode, but it is rather wide.  It would be great if there was another option.

GS1-Databar Expanded Stacked

Due to space limitations a stacked version of the previous version was needed.  It splits the barcode up into shorter pieces and stacks them on top of each other.  It's not as simple as just splitting the original into small pieces, the information is re-encoded in a different way.  The following barcode was made using an online generator from the site www.terryburton.co.uk/barcodewriter/generator/.  It was the only one that was able to reliably create a barcode that could be decoded.  Zint was able to produce a stacked barcode as was RSSENC from GS1, but there weren't any scanners that could reliably decode them.

GS1-Databar Expanded Stacked

The scanner app from zxing was able to reliably decode this type of symbology once the exposure control was altered.  Below is the result of scanning the barcode above.

GS1-Databar Expanded Stacked scan result

Technically GS1-Databar Stacked Expanded is capable of being scanned by a 1D laser scanner, but the scan line has to be swept over the barcode to read all parts of it.  Not ideal but it works.  The key to reading this type of code is that all of the sub barcodes need to be read and reassembled by the scanner in software.  An omnidirectional scanner is fine for this, as is a high quality 2d scanner.  However If we need a 2D scanner why not go for a 2D barcode?

GS1 DataMatrix

This one is just purely theoretical because it's a 2D barcode.  The last two barcode symbologies are cleared for scanning at a point of sale according to the GS1 specification, this one isn't because it requires the use of a 2D imaging scanner.  It's included because in my opinion it's likely that it will be in the future (and it's such an elegant solution).

Retailers are starting to see the advantages a 2D imaging scanner has.  It allows dense bar codes to be read from phone screens or promotional material, and having an imaging scanner also allows identification to be scanned at point of sale when people are buying restricted products like mobile phones.  The technology is available right now.  The NCR Realscan 78 Hybrid scanner uses a combination of laser scanning and imaging techniques to scan 1D and 2D barcodes.  It's capable of scanning GS1-Databar and GS1-Datamatrix symbologies fast and reliably.

NCR Realscan 78

So what does a 2D datamatrix code look like.

GS1-Datamatrix

The above datamatrix encodes the GS1 element string that Alice and Bob are using to label their lemonade.  In addition to being easy to scan, datamatrix codes also carry error correction coding that make the code more resilient.

Now that they have a barcode it can be compared with the old label.

Barcode comparison

The new datamatrix code has the GS1 element string under it in a human readable form, this allows it to be entered into a computer if the symbol is damaged.  Even that's less likely with a symbology like datamatrix.  A lot of bar codes for variable volume products are printed by an array of printer elements.  It's not uncommon for part of the printhead to fail or become covered in dirt leaving an unprinted stripe down the printout that makes traditional barcodes unreadable while datamatrix codes can still be read. The image below demonstrates this with a vertical white line through each label.  It's visible starting at the a in the word lemonade.

Damaged barcodes

The printhead failure causes the first barcode to be unreadable, while depending on the quality of the reader used the Datamatrix code will be easily read.


Conclusions

Alice and Bob have some decisions to make, GS1 have been aiming for 2014 as the adoption goal for Databar, so it's likely that any recent POS equipment will be able to scan databar expanded and databar expanded stacked.  So their current equipment might be able to scan these symbologies.  By using either of these barcodes they'll be able to improve the integrity of their stock management system while taking advantage of the other features offered.  The food safety benefits of encoding the best before date in the barcode is a compelling reason to further investigate this option.  It also allows the system to monitor things like how well stock is being rotated.  Ideally stock should be sold on a first in first out (FIFO) basis.  The order that stock is sold can be recorded by monitoring the  use by dates that go through the point of sale.

Now that the volumes going through the registers are accurate, they can be used to calculate metrics, such as average bottle volume, this can be fed back into the stock management system to detect errors introduced through other means.  For example if the average bottle of lemonade is 1 Litre and the stock management system says there are 10 bottles with a total volume of 2 Litres you know there's something wrong and the counts need to be investigated.

The benefits are indisputable, it just comes down to how much the barcode migration and initial set-up will cost.  It may or may not be the right fit for their business at the current time, but Alice and Bob should keep it in mind as a long term goal when purchasing or investing in future infrastructure.


Note:

I should make it clear that all labels and barcodes generated for this post are just to illustrate a point.  If you were to actually design a label there are many more things to pay attention to.  Guard spaces, barcodes sizes, and font placement are a couple that spring to mind.

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.