QED Environmental Systems, Inc. - 2019

Predictive Modeling of Sales and Shipping Costs

Introduction

QED Environmental Systems sells products into numerous Environmental markets (such as landfills, industrial clean-up sites, DOE and DOD sites, etc.). We’ve been in business for more than 35 years and have acquired other companies and their product lines throughout our growth. Our list of parts has grown to thousands of items. US and International sales are both important to our overall business.

On any given day our shipping department ships hundreds of orders and individual packages. The majority of our domestic shipping is with UPS and FedEx and the department uses dedicated shipping systems (PC based) from both carriers to manage all aspects of the process. These systems retain a vast store of historical shipping data (many years of shipment history). Each order is also maintained in our MRP (manufacturing resource planning) system. The Order ID is a unique (key) field in both the MRP and shipping databases.

Our Sales Department is often asked to provide an estimate of shipping costs when equipment is quoted. This is currently a “manual” process, where Sales contacts the Shipping Dept. for an estimate and provides the list of parts, a destination and the desired shipment type (UPS Ground, FedEx Next Day, etc.). The Shipping manager uses various resources (spreadsheets, notes, etc.) to estimate the number of boxes, weights, etc. to feed the shipping systems for a cost estimate. On the surface it would seem that connecting the MRP data to the shipping systems and automating this estimate would be fairly straight forward. The MRP system has fields for dimensions and weights for each part in the database – unfortunately these fields have never been populated. Even if we had better physical parts data there are still barriers to automating a shipment estimate –

1. Packing is variable based on the worker packing the order – for example, worker A might pack 3 part number “99999” items in a 10x12x8-inch box (HWD) and worker B might use a 12x12x10-inch box.
2. The MRP system has HWD dimension fields, but many parts are irregularly shaped.
3. Some parts are already packaged in shipping materials and others are loose and need to be packed in one of many standard size cardboard boxes.
4. Some items are more delicate than others, so the package dimension may vary depending on the amount of protective material (typically bubble wrap and other cushioning) used.

The goal of this project would be to develop one or more algorithms (or data process systems – hereafter the “System”) to automate the shipment estimate. The system would be used by Sales to enter an order ID (which ties to a list of parts, quantities and delivery destination (a ship-to Zip code) and select a shipment type to get an instant cost estimate. The system would have “tunable” elements that could be used to correct high or low estimates as it accumulates performance data. A monthly variance report could be used to evaluate the system and make adjustments as needed.

The efficiency savings in quote and order processing work-flow would be significant if such a system was reliable and able to grow with new parts additions.

Problem Statement

Using historical MRP and Shipper System data sets – develop the correlations between parts, quantities and the resulting boxes and costs that result. The system “input” is a parts list, quantities, a destination Zip code and a desired shipment type. The overall system “output” is a total cost estimate. The shipping system database captures much more than just the overall cost. Each order has one or more rows, including

1. Box dimensions.
2. Box weights (a billable weight and the actual weight).
3. Total number of boxes.
4. Shipment type.
5. List price cost for the total shipment.
6. QED cost for the total shipment.

One approach to this problem might be to construct a “black box” system that would assume a fixed set of parameters for each part item (SHP1 – assumed H1,W1,D1 and M1, weight). Some set of orders having this part would be tested with the starting SHP1 values, which would be iteratively tuned and compared to the actual shipping system data. This process would probably require constraint on some of the elements, such as shipment type. An initial analysis of the data sets may provide a focused sub-set of parts that make up the majority of the shipments. This may be required if some parts only show up infrequently in the data. Published shipper information and on-line tools can be used to take each box size, weight, destination Zip and shipment type and drive the estimated list price cost.