Constructing a “heat map” of the warehouse
If you draw the layout of a warehouse within an MS Excel spreadsheet and label the storage locations, this program will color the map according to any table of data, such as historical frequency of visits. Figure 1 below is an example.
Figure 1: The darker storage locations were visited most frequently.
This can be used to display any location-based statistic, such as annual pick-lines, cubic volume of product removed, frequency of restock, weight of stored product, age of stored product, travel distance from shipping, etc.
Download the program
Please read the license and disclaimers, then click here to download the program. It will appear as a
jar file, which most systems will run if you double-click on it.
If the program does not run, make sure you have the latest version of Java installed and your security settings allow execution of Java programs.
How to use the program
- Draw a map of the warehouse within a spreadsheet and label every section of rack/shelving with a unique address. Save the result in
xlsx format (the newest MS Excel format), as in this example. (You need do this only once.)
- Prepare a location-based statistic that you want to see, such as pick-lines per section of rack/shelving. This should be formatted as a text file in
csv format, with each line containing the unique address of one section of rack/shelving, followed by a comma, followed by the statistic of interest, such as this example, which lists the number of picks at each location during an interval of time.
- Start the Heat Map program and follow the three steps (load the data file, choose the color scheme, color and save the map).
- The new version of the program expects input to be in
csv format rather than the tab-delimited format used previously. Also, the map should be in
- The program colors a copy of your map. The spreadsheet that holds your warehouse map is not altered in any way and so can be used again later.
- The program colors only cells that contain an address that matches one in the data file. Nothing else is changed.
- Write the addresses of the rack/shelves exactly the same way in both the spreadsheet map and the data file. The program will treat “A-213-18” as a different address than “a-213-18”. Also, note that the program expects to find the addresses in the spreadsheet as text. It will ignore any entry, such as “21318”, that contain only numeric characters.
- Besides coloring the map, the program helps identify data flaws by listing any duplicate location addresses as well as any addresses in the data that are not found on the map.
- Here are some additional controls that you may find useful:
- Click on the table headings of the input data (panel 1) to sort location addresses alphabetically or the statistic by size. One click sorts in ascending order; a subsequent click sorts in descending order; a third click returns to the initial sequence.
- Control-click on the chart (panel 2) to copy, save, print, zoom, or re-format it.
Remember that you can display any location-based statistic. Figures 1–4 show some examples.
Figure 3: This heat map shows all levels of the rack so that you can see not only where but how high the picks were during the year. The red square to the far right represents a pallet location on the 2nd level that was visited frequently despite being far from shipping (top left).
If you make an interesting map, please send us a copy.
You can find more information and tools like this in our book.
- Can I use some other spreadsheet program?
Yes, you can use any spreadsheet program that can read and write files in MS Excel
- Why did the program change some of the formatting in cells that it shaded?
Sometimes you may need to touch up the final result to ensure that attributes, such as alignment or orientation of text, are correct.
- Why won’t the program read my data file?
Did you forget to prepare it in
Figure 4: The shaded storage locations hold the oldest pallets and reveal how obsolete product is forcing increased travel.