Increasing Customer Satisfaction by Improving Sales Order vs. Inventory Allocations

For background on this particular project, please visit When Greedy is Good.

Situation …

Our client is a rapidly growing medical device manufacturer. For at least two years, demand has exceeded production, creating a backlog of unfilled orders. Even though production has increased and the sales order (SO) backlog is being worked down, inventory and manufacturing order (MO) allocations will inevitably disappoint some customers.

Opportunity …

The tool being used to make these allocations was an Excel workbook, drawing its information from a mix of Microsoft Access databases, Microsoft Excel workbooks, and ODBC (Open Database Connectivity) connections to the ERP (Enterprise Resource Planning) system. The person who developed this allocation tool had recently left our client, and maintenance issues with the tool and its complex data relationships quickly surfaced.

Furthermore, the existing tool only made allocations based on a single criterion, whether or not a customer was a "Priority" customer. Our client had devised and wanted to include two other criteria in its allocations – whether or not a customer required its orders be filled from "One Lot," and whether or not it would accept an "Early Shipment."

Solution ...

The complexity of the existing approach was untenable, so we developed a new approach where all of the calculations and allocations were done by a Microsoft SQL Server stored procedure drawing directly from SQL Server databases. An Excel workbook called the stored procedure and presented the final allocations in an easy-to-digest manner.

We also devised a new allocation algorithm to reflect all three of our client's desired allocation criteria – "Priority," "One Lot," and "Early Ship." The algorithm included various techniques to increase its speed in finding desirable allocations.

1. "Demand" – that is, Sales Orders (SO) – is shown with dark orange column headings.
2. "Supply" – that is, Inventory and Manufacturing Orders (MO) – is shown with green column headings.
3. Lot numbers for the Supply (source of inventory) and Demand (use of inventory) are shown in lighter shading.
4. The first line shows a customer's order (SO 105) for 1,800 units. This order could have been satisfied from the total 2,244 units available through October 25, 2019 – however, the customer has requested the 1,800 units come from a single lot and none exists. The only solution is to create a MO of at least 1,800 units.
5. Conditional formatting highlights constraints either requested by a customer or set by our client. These constraints drive what items can be used to meet SO.
6. Conditional formatting highlights performance against our client's commitments to its customers. If the New Available Date is before (<), equal to (=), or after (>) the Current Promise Date, symbols and colors show how commitments are tracking. If no New Available Date can be assigned, an "X" is shown and highlighted.
7. This line shows a customer order (SO 107) that will be filled from two different lots. This is permissible because the customer does not require all units from a single lot.
8. Presentation focuses on ease of use – information has been ordered and highlighted for quick review, and usage instructions are short and prominently displayed.

Developing on the SQL Server platform provided more functionality than available in Access, so we were also able to show which lots – whether Inventory on hand, or Manufacturing Orders (Jobs) in production – will be used to fill what sales orders.

Presenting the allocation results in Excel allowed use of conditional formatting and other techniques to provide easy-to-use visual indicators of both problems and successes in satisfying customer requests. The three allocation criteria – "Priority," "One Lot," and "Early Ship" – are shown for each SO line, and are color coded to highlight SO lines which received special treatment by the allocation routine.

Results and Benefits ...

The Sales Representatives and Customer Service Representatives quickly came to rely upon this new tool to assign, with confidence, a Promised Ship Date that consistently respects both business priorities and customer preferences.

The new allocations are also consistent with our client's Shippability metrics, which are based on Original Promise Date as opposed to Customer Requested Date. As the sales order backlog is worked down, these two dates will begin to converge.

Conclusion ...

Our client now has a tool to quickly and consistently allocate constrained inventory and production to customers – all while respecting our client's allocation criteria. Client personnel are more confident in the allocations, and can more easily interpret how the business is performing for key products and customers.

More customers are being satisfied – and fewer customers are being disappointed – because of this new allocation tool. That's good news for everyone at our client, and certainly for their customers.