Warehouse Labor Planning – A Spreadsheet Template

Warehouse Labor Planning – A Spreadsheet Template

In this article, I will explain a simple spreadsheet template for warehouse labor planning. I will use a sample warehouse of a small retailer. Usually, the bottleneck in a retail warehouse is picking or packing process. So, it will be better to focus on picking and packing processes in our warehouse. I will also provide a link to download the template at the end of the article.

Let’s assume, we have 2 different types of orders: Single item orders and multi-item orders. Basically, single item orders are the orders which have just one SKU whereas multi-item orders have multiple quantities of the same SKU  or multiple SKUs. In our sample warehouse, we will have the picking process followed by the packing process. And the shipment labels are printed during the packing process.

 

Single Item Order Picking

 

Single item orders are picked in a batch of 50 orders at a time into a picking cart. The batch size may differ from one warehouse to another. The main idea behind batch picking is to reduce the number of locations for a picking trip. The warehouse management system should consolidate the same SKUs in a batch to reduce the number of visits to the locations.

 

Multi Item Order Picking

 

There are several options for multi-item picking. You can check various picking methods in this article. I will use cluster based batch picking for multi-item orders. The trolleys for cluster picking include 12 bins or totes and the batch size is considered as 12. The picking batch size may differ from one warehouse to another. I will try to explain how to calculate optimum picking batch size for cluster picking and batch picking in the next articles.

 

 

 

3 Points to Consider When Deciding the Picking Batch Size

 

  • Consider the size of the picking car (or container) and the products in the batch. It is better to have one picking car (container) for a batch.
  • Consider the total duration to execute all the batch. The total duration for a batch should not be more than an hour.
  • It is always better to create a separate batch for large and small products.

 

Defining the Input Parameters for Warehouse Labor Planning Spreadsheet

 

Parameter Value Explanation
Daily Shift Hours 7 The effective working hours for your warehouse excluding breaks.
%Single-Item Orders 15% Percentage of orders which has 1 product.
#Avg. Items per Multi-Item Order 3 Average number of products in an order
#Single-Item Orders for a Single Batch Picking Batch 50 Number of single-item orders to be picked together in a single-item picking batch.
#Multi-Item Orders for a Cluster Picking Batch 12 Number of multi-item orders to be picked together in a multi-item cluster picking batch.
#Orders to be shipped daily 500 Daily number of all shipment orders(Single-Item and multi-item orders)
#Single-Item Order Lines 75 Daily number of single-item orders. Formula: #Orders to be shipped daily * %Single-Item Orders
#Multi-Item Order Lines 1275 Daily number of multi-item order lines. If an order includes 3 products, the number of order lines is 3. Formula: #Orders to be shipped daily * #Avg. Items per Multi-Item Order

 

How to Use the Warehouse Labor Planning Spreadsheet?

Order Picking Process

 

ogiwa-WMS-warehouse-labor-planning-Order-picking-process

 

Step 1: Start with estimating an average “Setup Time” to start the next batch ( Picking the next job, picking the trolley etc.) and to finalize the batch ( Moving the trolley to the packing station etc. ).

Setup Time to Start and Complete a Batch(Seconds): 120

Step 2: Estimate input parameters for picking process.

Total Time To Pick an Order Line From a Location(Seconds): 15

#Avg. Distinct Items in a Single-Item Order Batch: 30

#Avg. Distinct Items in a Multi-Item Order Batch: 20

#Avg. Unique Items in a Single Item Order Batch: To explain the parameter, let’s use a sample. Assume that we have 10 single item orders. The order and SKU combination is shown below.

 

Order SKU
Order1 A
Order2 A
Order3 B
Order4 B
Order5 C
Order6 A
Order7 C
Order8 B
Order9 D
Order10 A

 

Avg. number of consolidated item quantity for a single item picking job is calculated as below:

#Avg. Unique Items in a Single Item Order Batch= number of Orders/number of distinct SKUs = 10 / 4 = 2.5

This parameter is an estimation based on the number of active SKUs in the warehouse. If you have 100 active SKUs in the warehouse, the parameter may be 10 (It is not “1”, because the distribution of the SKUs will follow a normal distribution and warehouse management system will try to consolidate similar SKUs into the same batch). If you have more SKUs for instance 1000 than the parameter may be 5.

#Avg. Unique Items in a Multi Item Order Batch: We can use the same formula which we explained in the “#Avg. Unique Items in a Single Item Order Batch”. Usually, this parameter depends on the size of the picking batch and the number of active SKUs. You can test different values to estimate your average value.

Total Time To Pick From a Location(Seconds): This parameter may differ from one warehouse to another. It indicates the total time to walk between warehouse locations and to pick the products from the location. When a picker arrives a location which is suggested by the warehouse management software,  the user stops by and picks the products from the location. In this template, the walking duration from one location to another is included in the parameter.

 

Calculated Values for Order Picking

 

These values are calculated automatically to estimate the duration of the order picking process.

Total Time to Execute a Single-Item Batch(Setup + Execution): 570 seconds

Total Time to Execute a Multi-Item Batch(Setup + Execution): 420 seconds

Total Time to complete a Single Item Batch: This value shows the total time required to execute a single item batch. And can be formulated as below:

Total Time = Setup time + (#Avg. Unique Items * Total Seconds To Pick From a Location)

 

Order Packing Process

 

Logiwa-WMS-warehouse-labor-planning-Order-packing-process

 

Setup Time to Start Packing of a New Order (Seconds): 30

Total Time to Select and Prepare a Shipment Box for an Order (Seconds): 10

Time Required to Pick the Total Quantity of a Product From a Location (Seconds): 5

Total Time to Wrap, Print and Stick Shipment Label for an Order (Seconds): 30

Setup time to start packing of a new order: The parameter indicates the time that is required to start next packing task.  

Total time to select and prepare a shipment box type for an order(Seconds): The parameter indicates the time that is required to check the products and select a suitable box type for shipment.

Time to scan an item and put into the box: The parameter indicates the time that is required to pick an item from the tote, scan the barcode and locate it in to the shipment box.

Total time to wrap, print and stick shipment label for an order:  The parameter indicates the time required to wrap the box, print a shipment label and mount label on the shipment box.

 

Calculated Values for Order Packing

 

These values are formulated to estimate the packing process.

Total Packing Time for Single-Item Orders (Execution + setup) (Seconds): 5,625

Total Packing Time for Multi-Item Orders (Execution + setup) (Seconds): 36,125

Total packing time for single item orders: This value shows the total time required to pack all the orders in a single item batch. The result is a function of setup time to start, box selection, product scanning, shipment labeling and wrapping.

Total packing time for multi item orders: This value shows the total time required to pack all the orders in a multi item batch. The result is a function of setup time to start, box selection, product scanning, shipment labeling and wrapping.

Results

 

Results for Order Picking Process

 

This part of the sheet gives you a summary of the estimated results.

 

Logiwa-WMS-warehouse-labor-planning-Order-picking-process-estimation

 

The number of single item batch picking jobs: The value shows the number of single item order batches according to your daily shipment orders. This parameter uses the single item order percentage and the single item batch size to calculate the number of batches.

The number of multi item batch picking jobs: The value shows the number of multi-item order batches according to your daily shipment orders. This parameter uses the multi-item order percentage( 1- Single Item Order percentage)  and the multi-item batch size to calculate the number of batches.

Total time to execute single item batch picking jobs: This value shows the total hours to complete all the single-item batch orders.

Total time to execute multi item batch picking jobs: This value shows the total hours to complete all the multi-item batch orders.

The Number of Pickers: This value shows the estimated number of pickers to pick the given shipment orders. The formula uses daily shift hours to estimate the total number of pickers. I did not round the numbers to give enough room for productivity in the warehouse. Usually, we round up the numbers.

 

Results for Order Packing Process

 

Logiwa-WMS-warehouse-labor-planning-Order-packing-process-estimation

 

This part of the sheet gives you a summary of the estimated results.

Total time to pack all single item orders: This value shows the total hours to complete all the single-item packing tasks.

Total time to pack all multi-item orders: This value shows the total hours to complete all the multi-item packing tasks.

Number of Packers: This value shows the estimated number of packers to pack the given shipment orders. The formula uses daily shift hours to estimate the total number of packers. I did not round the numbers to give enough room for productivity in the warehouse. Usually, we round up the numbers.

 

Number Of Estimated Labors

 

The last part of the sheet shows the estimated number of employees for picking and packing.

 

Logiwa-WMS-warehouse-labor-planning-Results

Conclusion

 

In this article, I explained a useful spreadsheet to estimate the number of labors for picking and packing in an online retailer’s warehouse. We can design more templates and your processes may differ from the one I explained. But I guess, you had an idea of how to do it for your own operation.

 

Download The Spread Sheet