Today, there are 30 major ERP systems marketed to retailers, manufacturers and
distributors. In addition, there are literally hundreds of smaller packages offered to specific
markets and industries. You would think that there would be at least one solution available
to meet all of each organization’s specific needs.
But, over our 30 years in business, we have rarely found a situation where an organization
was totally satisfied with the features and capabilities in the software they purchased.
During the demonstrations and interviews, the salespeople and technical staff of the
software provider appear to understand, and have solutions, for each challenge faced by
the organization. But after implementation, the company discovered that software
features were not available to address specific situations. To deal with each of these
“discovered” challenges, most companies consider:
* Manually performing the task outside of the system.
* Investing in an expensive software modification or enhancement to address the situation.
We have found that in many instances a single challenge did not justify a major investment,
but there were so many of these challenges that accommodating them manually lead to
significant lost time and inefficient purchasing.
In many cases these unique requirements can be addressed, at a reasonable cost, with
customized solutions that can interface with, or compliment, a company’s ERP software
package. This is a middle ground. We are developing processes to handle the specific
challenges outside of the fully automated system, but not working through each one
manually.
Over the next several months, we will present some spreadsheet solutions that
supplemented one of our client’s ERP system to solve a specific problem. This month, we
deal with buying a product from both domestic and overseas sources.
One of our clients uses an overseas supplier as the primary source of supply for a major
product line. The significant lower cost (compared with domestic vendors) compensates
for the longer lead times, container constraints, and less reliable availability of popular
items.
Their ERP system maintains a reorder point quantity (ROP) for each item. This ROP is
calculated based on average lead times and order cycles from the primary source of supply
(i.e., the overseas vendor) and a safety stock quantity:
Average Lead Time – The average amount of time between placing the
replenishment order with the vendor and receiving the shipment. The average lead
time for this vendor is 120 days.
Order Cycle – Frequency of placing orders with the vendor. In this case they place
an order that meets the target order requirement every 30 days.
Safety Stock – Reserve inventory to prevent stockouts due to unusually high
demand or delays in receiving a replenishment shipment during the lead time.
A replenishment order is issued by the ERP system when the “net available quantity” (On-
Hand – Committed on Outgoing Orders + Quantities on Current Replenishment Orders)
drops to the ROP.
But the vendor’s inconsistent performance resulted in stock shortages that could not be
fulfilled with safety stock. There was a need to supplement on-hand inventory of the
product with shipments from a local, higher cost supplier. But our client’s buyers were
faced with two challenges in working with a second replenishment source:
* When do you issue a supplemental replenishment order?
* What is the minimum quantity to buy to prevent a stockout?
Buyers couldn’t determine when to issue supplemental replenishment orders by
comparing the current net available quantity to the ROP. Why? Because while there might
be a suicient quantity on replenishment orders to keep the net available quantity above
the ROP, the overseas shipments might not arrive on time to prevent a stock out. They also
needed some way to determine what minimum quantity to buy to satisfy customer demand
before the anticipated receipt of a replenishment shipment from the overseas vendor.
The ERP vendor proposed an expensive enhancement to their system to provide a solution
to this problem. We delivered a comprehensive solution in a spreadsheet that would lead
the buyers to make intelligent decisions:
* Notify the buyer when the available quantity (On-Hand – Quantity Committed on
Current Outgoing Orders) is not adequate to meet anticipated demand before the
projected delivery of the product from the primary source of supply. We refer to this
as a “shortfall” situation:
[(Anticipated Receipt Date – Current Date) * (Anticipated Demand/Day)] > Current
Available Quantity
* Calculate the “short fall” purchase quantity. That is the quantity needed to fulfill
customer demand before the next shipment replenishment arrives (or suicient
quantities from the overseas vendor to fulfill the shortfall quantity:
[(Anticipated Receipt Date – Current Date) * (Anticipated Demand/Day)] – Current
Available Quantity
Buyers review this spreadsheet every day. They then place “fill-in “orders with local
vendors to prevent anticipated stockouts. Note that existing “fill in” order quantities are
added to the calculated current available quantity to prevent items from inadvertently
being listed as having shortfalls after a fill in order has already been placed.
This simple spreadsheet provided an aordable solution to a major problem. Next month
we will deal with another replenishment issue that was solved with the implementation of a
simple spreadsheet. In the meantime, please let us know if you have any questions or
would like to schedule a time to discuss your organization’s specific inventory-related
challenges.
