What Your Buyers Should be Doing – Part V

by | Mar 15, 2017 | Forecasting

Determine the Best Forecast Formula

Last month, we discussed the fact that a single forecast formula will not accurately predict future usage for all your stocked products.  But how do you determine the best forecast formula to use for each item?

We have found that the best way to predict future demand of an item is to test several forecasting formulas against past usage history.  This is a fairly easy analysis to perform.  If we are forecasting demand for December this year, we know what actual usage was in the previous several months, namely November, October and September.  We can forecast demand using several formulas for each of these months and compare the results to actual usage.  That formula with the lowest average forecast error will be used to forecast demand for upcoming months. 

The forecast error is calculated with the formula:

[Absolute Value of (Forecast – Usage)] ÷ Lower of Forecast or Usage

Let’s look at an example.  Suppose that the usage for November is 120 pieces and the forecast calculated by using one of the formulas is 100 pieces:

Forecast Error % = [Absolute Value of (100 – 120)] ÷ 100 = 20%

Note that the numerator of the equation is the absolute value of the difference between the forecast and usage.  Remember when you learned about absolute values in elementary school?  They always result in a positive number.  The denominator is the lower of the forecast or actual usage.  Our equations treat situations where the forecast exceeds actual usage exactly as circumstances where actual usage exceeds the forecast.  So, if the numbers in our example were reversed (usage of 100 pieces and a forecast of 120 pieces) the resulting forecast error is the same:

Forecast Error % = [Absolute Value of (120 – 100)] ÷ 100 = 20%

You probably will find that:

  • Products with significantly changing (i.e., increasing or decreasing) usage will be best forecast by averaging usage over the last several months; emphasizing or “weighting” the most recent history more than usage recorded in previous months.
  • Items with steady demand often do best with a “rolling” average, taking the mean average of usage recorded over the past six months.
  • With seasonal items, it is usually best to consider usage in the upcoming months last year, adjusted for the difference in demand between this year and last.

Look at each item that experienced a high forecast error (e.g., over 25% – 30%).  If the error was not due to unanticipated high demand, a problem with obtaining adequate inventory or some other factor outside of your control, you probably want to investigate a better forecasting method for the product.

Next month, we will examine factors other than past usage that will affect forecasts of future demand of stocked items.