Future Demand of Some Products Cannot be Accurately Forecast
By Jon Schreibfeder

During the last several months, we have been exploring the 15 steps to achieving effective inventory management. This month we are going to identify and deal with stocked items whose future demand cannot be accurately forecast.

By definition, a demand forecast is a prediction of the quantity of a stocked product that will be sold, transferred or otherwise consumed in an upcoming time period, usually a week or a month. An accurate forecast is a critical element in achieving effective inventory management and is usually based, in part, on what you sold or used in the past. Consider an item with the following usage history (displayed with the most recent history on the left):

Jan | Dec | Nov | Oct | Sep | Aug |  Jul | Jun | May | Apr | Mar | Feb | Jan
  ?   | 30   |    0   |   0   |   50 |    0   |  40 |   0   |   0    |  45  |    0   |  0    |  30

We are trying to forecast future demand for January. The average monthly usage over the past 12 months is about 16 pieces. [(30 + 50 + 40 + 45 + 30) ÷ 12] = 16.25. In fact, most demand forecasting formulas will take some average of past usage and provide a forecast of somewhere between 16 and 20 pieces per month. But is 16 or 20 pieces a month a good prediction of future demand of this product?

It appears that customers buy somewhere between 30 and 50 pieces at a time. Including the months with no usage in a calculation of future demand results in a ridiculously low forecast. This item experiences sporadic usage. That is, it is not sold or used on a predictable, recurring basis. Items with sporadic usage can typically be identified as having the following characteristics:

  • They are not new items. That is, they have not been introduced within the past six
    months.
  • They do not experience seasonal demand.
  • The typical sales quantity is greater than monthly average usage (as we see in the
    example above).

These items cannot be accurately forecast. We can’t predict with any certainty what will be sold or used in a given month. But when a customer asks for one of these products, we want to have the quantity they need. Stocked products that experience sporadic usage should be maintained in inventory based on a multiple of the normal quantity sold or used in one transaction. The normal quantity sold or used in one transaction can be determined by using the larger of two averages:

  • Median Average = The “middle value” after non-zero usage quantities are sorted in
    ascending order:   30 | 30 | 40 |45 | 50
  • Mode Average = The most common non-zero value:   30 | 50 | 40 | 45 | 30

The median average of 40 pieces is greater than the mode average of 30 pieces. The typical ornormal order quantity is 40 pieces.

More than one multiple of this normal order quantity may be maintained in inventory because of:

  • An extended lead time for replenishment
  • Frequency of sale (e.g., the item sells four or six times annually rather than once or twice a year)
  • The cost of the item (i.e., you may decide to keep more of a less expensive item in
    inventory)
  • The item is critical to operations (i.e., lack of the product might shut down a critical
    process or operation)

If we decided to maintain three normal order quantities in stock the result would be a minimum stock quantity of 80 and a maximum stock quantity of 120. When the net available quantity (OnHand – Committed + On Replenishment Order) of a product fell to or below 80 pieces (i.e., two normal order quantities), we would order enough of the product to bring the net available quantity up to 120 pieces. That is three times the normal order quantity of 40 pieces. If we only wanted to keep two normal order quantities in stock the result would be a minimum of 40 pieces and a maximum of 80 pieces. Keeping only one normal order quantity in stock would set the minimum to 39 pieces and the maximum to 40 pieces. That is if the net available quantity
dropped below one normal order quantity, we would replenish stock with enough material to have one normal order quantity of the product in stock.

Often more than half of a distributor’s stocked products experience sporadic usage. It is critical that you maintain an adequate inventory of these items. Stocking them based on a multiple of the normal quantity sold or used in one transaction will ensure that you maintain your reputation as a reliable supplier.