The Picking Optimization file using heatmaps has be re-uploaded too.
Same thing for the Cycle Plot example that had Excel crashing ... don't know why though :(
Ooops !
I just made some changes on the v3.3 for XL 2003, in order to propose Transparency parameters to Heatmaps.
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 :
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.
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
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.
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.
Cycle plots with sparklines... in no time !
In January 2008, Naomi Robbins wrote an excellent article on how to display the evolution of seasonal data.
Finally, Charley Kyd proposed a very good tutorial on how to create cycle plots with Excel ... a rather long way, especially in the formatting process.
My turn to propose the Sparklines alternative. It took me less than a minute to create this chart.
It consits in 12 line charts, one per month, displayed in a narrow cell.
All cells obviously share the same vertical scale.
The red and blue points represent the minimum and maximum over 12 years.
It consits in 12 line charts, one per month, displayed in a narrow cell.
All cells obviously share the same vertical scale.
The red and blue points represent the minimum and maximum over 12 years.
Product sales analysis
Alessandro B. has been using Sparklines for Excel almost since the first version.
He is kindly sharing one of his sales dashboard, : Here
Note : not all cells contain Sparklines formulas
He is kindly sharing one of his sales dashboard, : Here
Note : not all cells contain Sparklines formulas
Clearly and Simply... great templates for dashboards.
For the Excel user looking for serious dashboard templates and and first class tips, I strongly recommand to visit Robert's new blog : Clearly and Simply.
My favorite posts so far :
The best of all ? Almost NO VBA involved...
A site to bookmark ... NOW !
Keep the posts coming Robert.
My favorite posts so far :
- Take care of customers : provides a superb, minimalist template of dynamic customer satisfaction dashboard.
- Lithuania at a glance : another brilliant example of clean and complete layout.
The best of all ? Almost NO VBA involved...
A site to bookmark ... NOW !
Keep the posts coming Robert.
Variance analysis example
Here is an example file of a variance analysis I had to do recently, including raw data.
It works pretty well, although I would prefer to have separate reports for sales and margin, and not split in 2 consecutive lines like here.
The file is available here.
Note : this file does not contain any code. The Sparklines add-in must be installed before using it.
I modified the code on my machine in order to identify bars that have in this case more than 100% variance (white triangle in the screenshot).
It works pretty well, although I would prefer to have separate reports for sales and margin, and not split in 2 consecutive lines like here.
The file is available here.
Note : this file does not contain any code. The Sparklines add-in must be installed before using it.
I modified the code on my machine in order to identify bars that have in this case more than 100% variance (white triangle in the screenshot).
Subscribe to:
Posts (Atom)