We recently had the opportunity to work with a supply chain consultant who had been hired by a seafood importer and distribution company.  They hired him to help them with a some challenges they were having around keeping the appropriate stock on high volume items, making sure that they would not run out of product but also not over order so they would have to sit on expensive (and perishable) inventory for long periods of time.

The consultant explained that due to the long lead times of buying product from South East Asia and selling in the United States they needed to be able to forecast out inventory availability many months in advance.   He explained that to come up with the right numbers we needed to consider current inventory available, inventory on the water (in containers on the way to the US), as well as open Purchase Orders with overseas suppliers.  Then we had to compare that against existing open sales (commitments to customers), as well as anticipated future sales.  In addition, he explained, we should be considering lead times based on supplier location and shipping history, safety stock levels, as well as what he called a “Service factor” basically if a products sales are growing or shrinking over time.

All the information was in the system but we had to find a way to get it out and present it to the user in a meaningful and easy to use format.  Here is how we did it:

  • At the vendor level we added a “Lead Time” in weeks of how long it typically takes to receive inventory from the time we place the PO to this vendor to the time it hits the warehouse. This is a dynamic number that changes as orders are processed.
  • At the product level we added a service factor which is defined by the sales team with a ranking of 1.0 for average products and greater levels for products where they feel sales will be increasing in the future and less where they feel sales will be decreasing in the future.
  • At the product level we added a safety stock level which is determined by sales as the minimum they feel they should have in stock at all times. There is also some talk of having this number change dynamically based on historical orders but this was left out of this phase of the project.
  • Then we created an excel based report that can be run by the purchasing team that allows them to filter by warehouse and displays one row for each product. The report considers all the calculations listed above and highlights certain products that require the users attention because either inventory position is too low now or it will be too low in the future.

Using the inventory planning and replenishment report this company is now able to plan out purchases so they keep inventory lean without worrying they will run out of product and be unable to fulfill customer orders.