Excel Formula to dictate what Pick Rate should be

dodo

Registered User
Messages
1,317
I am trying to do a formula within excel to find out Picking rates within a Warehouse to measure the picking team,
I know it is normally done by cases picked within a shift then divide by hours spent on the picking task
eg 1000 cases picked in 8 hour shift equates to pick rate 125 cases an hour,
Examples below Issue I have is eg, This is new position for me in a place where they never had pick rates , Working day 8.5 Hours with 45 minutes break time so actual working time 7 Hours 45 minutes

I have started a new job as Warehouse Manager and there is no benchmark in place for what the case rate an hour should be , The benchmark I could then judge by would be taking the 3 best pickers take their average and set benchmark eg at 85%-90% of their average, But I do not know how to do a formula on the picking task,

Picker A. 70 cases 50 products(50 Lines) on the order which means the picker must go to 50 locations to pick the order, time taken 50 minutes.

Picker B. 90 cases 5 products (5 Lines) on order which means picker must go to 5 locations to pick order, time taken 10 minutes .

Picker C. 200 cases 17 products (17 Lines) on order which means picker must go to 17 locations to pick order, time taken 33 minutes


What formula can I use that will take into account cases picked and also the amount of Lines on the order so all is equal when doing the pick rate measure,

Picker A. Pick's 500 cases that day and picked 400 Lines

Picker B. Pick's 1400 cases that day and picked 110 Lines ,

Picker C. Pick's 778 cases that day and picked 195 Lines

So how to get the perfect formula ?

Any help would be great,
 
I suspect that if you are to get anywhere with this exercise, you will need a lot of data,and will finally come to a rate of say 100 seconds per line plus 15 seconds per case.
I don't think you can come up with a time per case that is meaningful.(As the picker who has to traipse all over the warehouse to pick 60 cases of 55 lines will no doubt tell you!)
 
Does one picker have to complete all of one order?
Could the order be split up into areas and pickers assigned to these areas to speed up ?
 
I'd be into optimising product placement to ensure that the fastest selling lines were the most accessible and could be turned around fastest, thereby improving "effectiveness" rather than measuring "efficiency".

Splitting orders and bringing lines to an order consolidation area works well for automated warehouse systems where you have carousels and robot pickers responsible for "areas" or "zones" but may not be the best way to manage human pickers if ownership and responsibility is for fulfilling "a customer order".

How are lines on an order sorted? Are they just as the customer submitted them or can the lines be sorted to reduce unnecessary travel up and down and across aisles and locations?

Have you asked pickers what are the biggest obstacles to doing a good job and worked at eliminating the blocks? Have you gone out and observed each picker's "style" and asked them to explain why they do the job the way they do? This called MBWA or management by walking around and IME works very well.

Whatever you want to achieve you're going to need masses of data, not all of it suitable for use as cells in a spread-sheet.
 
Warehouse is laid out good also one order one picker, each picker just takes next pick available so it is fair, but surely there must be a formula that takes into account cases picked v lines on order so no matter what 2 pickers who pick at similar speed end up after a 8 hour shift having similar pick rate,
 
What does 'fairness' have to do with it?Are the pickers on some kind of piece rate?
 
"I have started a new job as Warehouse Manager"
Is what you are at in your job description: i.e. to weed out underperforming staff.
Are they unionised?

Anyway the math is pretty straight forward once you know the distance travelled by each picker.
one way is to stick a GPS type unit on them like the rugby players and track them that way ( did I ask: Are they unionised?)
The other way is to measure the distance for where they pick up the orders to the location of each product and have that in the database.
Then you key in the products off the list and that will give you a distance traveled, assuming they come back to the starting point each time which won't be the case but the error will be constant across the dataset.
How are you getting the time elapsed?
You will need to tweak it for multiple items from same point and for items that are not at ground level
 
"I have started a new job as Warehouse Manager"
Is what you are at in your job description: i.e. to weed out underperforming staff.

Seems like the OP is looking to set reasonable performance targets for all staff. There's no mention of weeding-out. Ensuring everyone knows what the expectations are is good management practice, so long as the target are achievable, not like some of the Amazon horror stories you hear (whether they're true or not). Capturing metrics like that will enable the provision of higher rewards for the best performers and ensure they don't get disheartened and leave because they're doing more than their share of the work.

one way is to stick a GPS type unit on them like the rugby players and track them that way ( did I ask: Are they unionised?)

Won't work indoors and not accurate enough in such tight confines anyway.
 
Back
Top