prophet 21 software featured

Prophet 21 Software: Using Min/Max for Sporadic Items

In the world of demand forecasting, sporadic items can be among the most difficult items to deal with.  These items do not have regular enough usage data for a forecasting formula to have a reasonable level of accuracy.  Users of  Epicor’s Prophet 21 software, like any other ERP system, often spend too much time trying to make judgement calls about quantity suggestions that don’t always feel intuitive or reasonable.

This condition often causes issues for buyers, and ends up eroding trust in the system.  If a buyer doesn’t believe that they are getting reasonable suggestions on a series of items, they can start to lose confidence in the entire system, leading to lost productivity and erratic purchasing habits.

What Is A Sporadic Item?

Simply put, sporadic items are those that do not have regular usage.  I tend to follow the EIM definition of sporadic usage.  If an item does not have usage in 8 out of the last 12 forecast periods, it is sporadic.  Eight periods of usage is usually the point at which forecasting formulas start getting very inaccurate.

The challenge for many distributors using Prophet 21 software is that a large percentage of items usually fall into the sporadic category.  I have have seen the percentage of stockable items with sporadic usage vary anywhere from 60% to 90% of SKUs.  If you cannot make a relatively quick decision on sporadic item quantity suggestion, buying these items is going to take forever.

How Do You Stock Sporadic Items?

The goal for any item is to have the least amount of inventory you can that still satisfies customer demand.  With sporadic items, you have to think a little differently.  The Prophet 21 software is wired to think in terms of period usage.  This is not going to work well when the usage is all over the place.

Instead, think of it in terms of customer transactions.   What is the normal monthly sales quantity for that item when it does sell?  Then, multiply that the number of times you are likely to sell the item before you could get a replenishment order received.

For instance, if you sell the item in 2 months out of 12, and the normal quantity is 40 units, you may want to keep 40 units on the shelf, and then order 40 more when you run out.  By contrast, if you sell the same item 5 months out of 12, you may want to have 2 or 3 sales quantities on the shelf and reorder when you sell one of those quantities.

Long Lead Times Matter

It is also important to take lead time into consideration.  If this is a long lead time item, more stock will be needed to service customer demand while you are waiting on your next shipment from the vendor.  However, if you can get stock replenishment in a week, then you can afford to carry less of the item since the restock is just a few days away.  Having accurate lead times in your Prophet 21 software is critical if you want to make good decisions about replenishment quantities.

What is a Normal Quantity?

Again, I think John Schreibfeder of EIM has done the most solid work on this concept.  His advice is to use the greater of:

  • Mean: the mathematical average
  • Mode: the most frequently occurring non-zero value
  • Median: the number in the middle
  • Adjusted Mean:  A mathematical average that ignores periods with zero usage.

This number becomes the basis of your Prophet 21 software stocking parameters.

Using the Prophet 21 Software Min/Max

prophet-21-software-min-maxOnce you have determined your stocking parameters for the sporadic item, you can use the Min/max replenishment feature of Prophet 21 software to mange the purchase calculation.  For example, if your goal is to keep one normal usage quantity in inventory.  In this case,  would set the min to zero and the max to one normal usage quantity.  In another example, if your normal usage quantity is 50 pieces and you want to keep between 3 and 5 usage quantities on hand, set your min to 150 and your max to 250.

Do not round min/max values to the nearest package quantity.  This will cause you to overbuy the item.  Rounding should occur when you are issuing the purchase order.

Maintaining the Data

The real challenge with managing sporadic items is maintaining the data.   Remember, sporadic items make up such a significant part of the average item master.  Trying to keep up with these min/max settings could be overwhelming.  It is probably not feasible to do this manually.   The best way to manage this is a stored procedure that is capable of implementing the EIM sporadic item stocking logic.  Run the procedure on your database monthly.  At least then the dynamic nature of these min/max settings would be getting attention every 30 days.

Last Thoughts on Prophet 21 Software Replenishment

To sum up, managing sporadic items in your Prophet 21 software will most likely need to be done using min/max.  I recommend using the EIM approach to determine what the correct stocking quantity is, and then using an automated procedure on a monthly basis to update the min/max levels for your sporadic items.   Doing this will help you get better buying suggestions from P21.  This will also help you take better care of your customers.

For more information on Prophet 21, visit our knowledge base.

Posted in Inventory Management, Prophet 21.