Calculating the WMS ROI – A Comprehensive Excel Model

Calculating the WMS ROI – A Comprehensive Excel Model

Several months ago, I wrote an article explaining how to calculate a WMS ROI aimed to convince the owners of a wholesale or distribution business of the savings and returns that a WMS will bring to the company. In this article, I have broken down an entire spreadsheet containing every single parameter to show you just how profitable a warehouse management system can be.

Variables in the ROI Model

These are all the parameters that I asked my customers to provide to me in order to generate their WMS ROI.

Total Number of cases moved: A mid-size distributor can move an average of 10,000 cases through the warehouse per week.

Warehouse employee cost: An average warehouse employee costs $20 an hour.

Total Sales Order per week: Let’s work with 400 sales orders per week.

Number of Purchase Orders: let’s assume that you receive, pick, pack and ship five purchase orders per week.

Average inventory adjustment: You are doing one bulk inventory adjustment to fix any discrepancies manually into your ERP per week.

Average time doing one inventory adjustment: Let’s say that you are spending an average of 20 minutes taking all of those adjustments and keying them in into your ERP.

Time spent on inventory discrepancies: The time spent counting inventory discrepancies is about five minutes per discrepancy, because you have to walk out from your desk to the warehouse, go to the pallet position, count the item, find the discrepancy, then walk all the way back and hand key that in.

Average $ per invoice: For the sake of this exercise, let’s say that it’s $200 per invoice.

Average stops per driver: A driver can make 13 stops a day in an 8 hours shift.

Driver shift: Eight as indicated.

Average driver cost: The average hourly per driver is about $20 an hour.

Average driver stop time: This is the amount of time the driver spends picking the order out of the truck. Let’s say it takes five minutes.

Average warehouse shrinkage per week: The standard at a mid-size company is about $1,000 a week.

Cost of missed picks: This is hard to quantify because a missed pick means that your customer does not get the product and they are either going to be shorted or you have to send them the wrong product. Let’s say that you have $1,000 worth of cost per month due to missed picks.

Pages printed PER Sales Order: If you don’t have a WMS you need to print the sales order. The average number of pages printed per order is 2.

Warehouse inventory discrepancies (%):It is the number of discrepancies divided by the total number of inventory positions. Let’s say that my inventory is 98% accurate week to week.

Warehouse Analyst wage: The average cost is $20 per hour.

Gross Profit: This only applies to Companies that pulls by item, stop, or store, and placing those transactions on a cart. For those who load the carts to the truck, the gross profit on those transactions is 10%.

Average case value: For the sake of the model, let’s say it is $20.

Number of checkers: How many people you have checking or spot checking orders before they get placed on the truck? Let’s say that we have two checkers.

Cases expired per month: How many cases go out of rotation because somebody misplaced the pallet . A pretty standard is 1 pallet per month, and the average number of cases is 120 cases.

Case based on average values

This is how the spreadsheet looks with the values justified above. It also includes the cost of the hardware required and the average implementation costs.

Calculating the WMS ROI

Once the data is input in the spreadsheet, it automatically calculates the WMS ROI based on scanning devices and those using voice picking. For these values, which are pretty standard, the company will be increasing its profits in the order of $150,000 by year 2, and the investment is recovered in 20 months. We have also included in the model a field that allows you to assign a percentage of efficiency in the benefits. For example, if 100% seems to aggressive, you can define a 90%, and all the calculations will be scaled by that figure.

If you are in the process of or replacing, upgrading or acquiring a new WMS and want to know the potential saving and return on investment, click the link and we will call you.

I hope this article on WMS ROI has been helpful to you. I will continue to post information related to warehouse management, distribution practices and trends, and the economy in general.

There is a lot of relevant information on our channel. Check this video that explain in more detail the scope of the spreadsheet.