Optimize warehouse picking efficiency with heatmap

Lets take a warehouse where goods are stored on shelves or pallets.

To prepare orders, you need to walk between the shelves and pick small items that are on your picking list and put them in a box.

Obviously, the shorter distance you need to walk to prepare an order, the more efficient you will be : less distance per order = more orders per hours.

One measure to implement is to setup 2 circuits :

• The short circuit, on which all fast movers products can be found. Ideally those products should cover 80% of your orders.

• The long circuits, on which all products, including slow movers can be picked

Heatmaps can help to quickly identify the slow movers and most ordered products.

A very optimized warehouse layout should look like this :

Fast movers (red) on the short circuit. (blue) Slow movers (green) on the long circuit (orange).

In this Excel file, I have :
a 2 columns table, with the picking place ID and the picking frequency.

The simple layout of the warehouse in 2 versions :

• One with cells containing a VLookup formula in order to retrieve the picking frequency of each location
• One with the picking locations ID

I defined 3 colors intervals, with their corresponding picking frequency threshold.
Finally, 2 cells containing the Heatmaps formulas.

The transparent heatmap is layed over the layout showing the picking IDs.


With the data set I used, you can clearly see that the product on location A20 shouldn't be there.

It should be at the very beginnig of the circuit, replacing the product located in B1. Same thing for D15, D12 and the orange location on the left of the blue dashed line.

I implemented this is a fruit and veg. warehouse some time ago.
Productivity increase was spectacular and workers happy to reduce their mileage ...

Now... why bother with heatmaps when this can be done with simple conditionnal formatting ?
Well, you might want to regroup products according to another criteria, that requires more than 3 colors.

File available here and on Sourceforge. (Sparklines add-in required ... again)