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.

Using a Solar Panel to Keep a Generator Starter Battery Charged

Just a quick post this week.  I thought I'd do an update on the solar project I'm doing for my father.  There isn't anything too complex involved, just a solar panel connected to a charge controller that keeps a battery topped up that's used to start a back up generator.  The generator is used infrequently and the battery has a habit of being flat when needed.  The generator is in the shed that the solar panel will be mounted on. so installation is pretty easy.  To make the project more interesting the load terminal of the charge controller was connected to an LED light to give extra light at night and indicate the condition of the battery.

My previous testing of the MP-3720 solar charge controller can be seen here.

The charge controller was mounted to the frame above an unused door.  It's not an ideal place, but any higher and it would be too close to the hot corrugated iron roof.  The kind of heat involved would significantly reduce the life of the controller.

Solar Charge Controller

As the installation was unplanned, I was flying by the seat of my pants.  The solar panel, battery, LED light and charge controller wiring was bought together in the best way I could.  With more time and planning I could have done a neater job, but you work with what you've got, and what I had was a strip of terminals.  I plan to revisit this and make it neater, but it does the job for now.

Wiring (don't look at it, it's shocking I know)

The LED light was an afterthought, the terminals were there and a small light in the backyard at night seemed like a good idea.  It also serves another purpose, it indicates the condition of the battery.  The light only comes on at night and will only come on if the battery voltage is above 11.5 Volts.  If it doesn't come on it shows that there's something wrong with the system.

LED light connected to the solar charger

There are still a few things to tidy up, but the job is almost done.  I have a high current switch on order that will allow the battery to be switched between the solar charge controller and the generator.  It may not be absolutely necessary,  but I didn't want to start the generator while the charge controller was connected to the battery.  The starter motor draws a lot of current, and where there's large current into an inductive load there's the possibility of voltage spikes caused by inductive kickback.  Yeah, I could come up with some kind of snubber circuit, but this is simple and foolproof.

Solar Panel

100th Blog Post Retrospective

This is my 100th blog post and to celebrate I thought I'd do what all great TV shows do for milestones episodes, a cheesy clip show.  So I'm going to go through a couple of my favourite posts along with my most popular ones.

OpenGL

My attempt at OpenGL seems to be a good place to start.  I wanted to take the timetables from all our public transport providers and calculate a transit time map for Brisbane.  This would make it easy to visualise what parts of the city were over and under serviced.  To do this I needed a way to visualise the raw data and the results, so I thought I'd write my own viewer.  It used a custom set of tiles from the Open Street Map project I rendered myself and with c++ and openGL it was able to swap in and out tiles with appropriate resolution depending upon the users location in the map.  The project didn't get much further than the results below, I underestimated the scale of the task and overestimated my abilities.  To top it off I lost the project due to a corrupt file, but I was proud of the results nonetheless.  Before this I'd never used OpenGL and had only toyed around with c++.  I also learnt the value of backing up projects.

Off Axis Magnetic Field of a Circular Current Loop

Next up is my second most popular post and the one I'm proudest of.  I wanted to work out the off axis magnetic field of a circular current loop so I could simulate the effect of a magnetic lens on an electron beam.  Not only was the maths difficult, but speaking isn't really my thing.  From memory, the 10 minutes of  video took around 200 takes at recording the audio. If it wasn't me getting tongue tied, it was a noise from outside or something like that.

The video itself was done by combining images created with asymptote.  One drawing was made with a parameter I could change, Linux scripting was then used to render images with different values for the parameter.  That in itself was a nightmare.  The best thing about this post is that I've had a couple comments from people that said it was useful.

Surface Mount Circuit Board

Now for my first surface mount circuit board.  I'd been involved in designing a couple of PCB's for university team projects, but this was the first one I'd designed entirely by myself.  It was also the first time I'd used surface mount parts.  Up until this point I was terrified of them, but after this project I wouldn't use anything else.  The circuit wasn't anything fantastic it was just a breakout board for an MCP9808 temperature sensor and a reason to design a PCB.

PCB with parts in position

Vacuum Pick Up Tool

This project follows on  from designing my first PCB.  I wanted a way to place SMT components.  I'd had reasonable success using tweezers but I wanted to try a vacuum pick up tool.  It works for pick and place machines why not me?  So I made my own tool to see what the advantages and disadvantages were.  I learnt a lot in the process, particularly silver soldering.  I was so happy with my soldering results I polished up the hand piece until it looked like something you'd buy in a jewellery store.  If I were to go back and do it again there are a few things I would change, but it works reasonably well.

Vacuum pick-up tool hand piece

Flaring the End of a PVC Pipe

The popularity of my next post was a bit of a surprise.  I was building a chicken coop for my sister and was short on content so I was just documenting what I was doing during the build.  When it came time to put the down pipe on the gutter I had to flare the end of the PVC pipe so I thought I'd show how I did it.  To date, this post is my most popular and has had almost twice as many hits as my next most popular one, and it's only been up about half as long.  To top it off it took about 20 minutes to photograph and post, my second most popular post was the culmination of about six months of on and off work.

Flaring a PVC pipe

Harmonic Elimination Pulse Width Modulation

This is one of the posts I'm quite proud of as well.  Years before I started this site I came across the concept of harmonic elimination PWM.  It allows a designer to alter the switching times of a PWM signal to control or eliminate certain harmonics in the output waveform.  At the time I worked out the equations, scribbled them down on paper and put them away for later.  That's not much use to anyone, I eventually got around to putting them up so they could be useful to others.  There's lots of information about HEPWM out there, but it's rather high level.  I wanted to give people a starting point with some code to generate the switching waveforms.  There are plenty of ways to slightly alter the design for different requirements, but having an understandable starting point to branch off from is helpful for others.

Octave Code For Generating Harmonic Elimination PWM Waveforms
Fourier Series Of Harmonic Elimination PWM Waveforms
Fourier Series of a Quarter-Wave Symmetric Pulsed Waveform
Harmonic Elimination PWM Comparison and Uses

HEPWM example spectrum

Now it wouldn't be a proper post without me doing something new.  I decided to grab a screen shot of each post including this one and assemble them into a single image.  I thought it would be a nice way to visualise my output over the past three years.  Usually I'd try to use some clever trick to accomplish this, but in this case I just brute forced it.  I took screen shots with the awesome screen shot add-on for chrome.  The images were then batch processed with ImageMagick and Irfanview.  The assembly was done manually.

100 blog posts assembled into one image

So where to now for the blog?  I'd like to do more video, but that's unlikely at this point, so I'll just keep doing what I'm doing.  Writing for the site is a motivator for me, committing to writing a post every 11 days gives me a reason to learn new things and in general keep busy.  Without it, I don't really have a reason to do half the stuff I do.  The blog also has an ulterior motive, it's a bit of a showcase of my abilities.  For someone with no formal qualifications I need all the help I can get to get a job that doesn't bore me to death.  If need be I can use this to prove I'm not just a pretty face :-).

The main reason I keep blogging, apart from innate curiosity, is the occasional bit of feedback I get from people that tell me my posts have helped them.  It's a good feeling that people all over the world have benefited from my work in some way.