The last article in this series (July 2006, page 52) explored forecasting future demand of products with recurring usage. Although weighted-average forecasting produced more accurate results than simply averaging usage recorded over the past several months, there is still a high forecast error. A significant difference still existed between our prediction of future demand and actual usage. This article will continue to explore ways to improve forecast accuracy.

Review of weighted-average forecasting

Most electrical distributors utilize simple-average forecasting to predict future usage of stocked items. For example, they may average the usage recorded over the previous six months. This works well if products have fairly consistent usage, but many products experience increasing or decreasing usage over time. Other products have a seasonal pattern of usage, where sales are generally higher during certain times of the year. Some products experience recurring spikes in usage throughout the year.

Weighted-average forecasting allows us to address different patterns of usage in forecast calculations. Each weighted-average formula places weight, or emphasis on the usage history recorded in specific previous months. Here is a common set of weights to use in calculating demand for a nonseasonal item with gradually increasing or decreasing sales:

  • Place a weight of 3.0 on the usage recorded in the most recent period.

  • Place a weight of 2.5 on the usage recorded in the next previous period.

  • Place a weight of 2.0 on the usage recorded in the next previous period.

  • Place a weight of 1.5 on the usage recorded in the next previous period.

  • Place a weight of 1.0 on the usage recorded in the next previous period.

Let's use Table 1, “Weighted-Average Forecasting,” to help understand how to calculate July's forecast for an item with the following usage history. Each weight is multiplied by the corresponding month's usage. The total extension (1,297.5) is divided by the total weight (10) to determine our prediction of the demand for July of 129.75 or 130 pieces. Although this is better than a forecast of 120 pieces derived from averaging the previous six months usage [(148 + 133 + 126 + 110 + 104 + 98) ÷ 6 = 120], it still doesn't appear to be a great forecast. Look at the graph of usage over the previous six months for Table 1. The forecast of 130 pieces is represented by the solid black line.

Usage is obviously increasing over time. No matter which set of weights are used, no average of past usage can result in a forecast greater than the highest month's usage. To best predict future demand, consider all four elements of an accurate forecast:

  1. Past usage.

  2. Increasing or decreasing trends in usage.

  3. Collaborative information about specific future needs from customers.

  4. The appropriate time frame or horizon for the forecast.

Trends can be determined by examining usage over the past several months. In Table 2, notice the continual but erratic increase in usage over the past four months.

The average increase in usage over the past four months is 10.5 percent [(14.5%+ 5.6% + 11.3%) ÷ 3 = 10.5%]. To apply this trend factor, multiply the results of the weighted-average forecast formula (130) by 1.105 to result in a forecast of 144 pieces. Table 3 illustrates the leveling off of the increase experienced over the past several months.

Please note two guidelines in applying trend factors to forecast formulas:

  • In most cases, trend factors should not be applied unless consistent increasing or decreasing usage exists over three or four inventory periods.

  • Any calculated trend factor greater than 100 percent (a doubling in usage) should normally be brought to the attention of a buyer or inventory planner before it's applied.

Trend factors that can be determined by examining past usage history are referred to as “internal trend factors.” But other trends may not be reflected in past usage history. Here are three examples:

  • Your marketing department might estimate the sales of the items in a particular line of products will increase by 15 percent. This may be due to a new sales effort, a change in the economy, current customers' increase in business, a competitor leaving the market, or some other reason.

  • You might anticipate a decrease in usage of 10 percent due to a new competitor entering the market or an increase in interest rates.

  • Weather factors such as temperature extremes or precipitation might cause usage to increase or decrease.

These are referred to as “external trend factors” because the information for them comes from outside of your organization: your salespeople's observations of the market, the financial news in a local paper or the Internet, the weather forecast or some other source. External trend factors often affect an entire product line or all of the products in a branch, but internal trend factors are calculated for individual items.

External trend factors are usually identified by observation. That means salespeople or buyers notice a significant change in usage and start searching for a reason. It's important to record these observations and see if they occur again in the future. Note the specific effect of each external factor each time it affects the forecast. For example, did sales actually increase by the projected 15 percent when a competitor left the market? Or was it 12 percent? The results will serve as a guide in applying the specific factor in future forecasts.

Accurate forecasts help achieve the goal of effective inventory management: to meet or exceed customers' expectations of product availability with the amount of each item that will maximize your net profits. The next article will explore the remaining elements of an accurate forecast: collaborative estimates and the forecast horizon. In the meantime, if you have any specific questions, please let me know.


With more than 36 year of experience, Jon Schreibfeder is president of Effective Inventory Management Inc., Coppell, Texas, a consulting firm dedicated to helping distributors maximize the productivity and profitability of their investment in stock inventory. Schreibfeder is author of the recently published “Achieving Effective Inventory Management — 3rd Edition.” Contact Schreibfeder at (972) 304-3325 or jons@effectiveinventory.com.