How Electrical Distributors Can Forecast Future Demand for Electrical Products

July 1, 2006
Last month's article discussed replenishment parameters for elecrical products with sporadic usage. This month we focus on forecasting electrical products with recurring usage.

You sell these products on a daily, weekly and monthly basis, so it's critical to calculate an extremely accurate forecast for them. Your customers request these products most often, so they present the greatest opportunity for fast inventory turns.

In 1987, Gordon Graham wrote a book, “Distribution Inventory Management for the 1990s.” In the book, Graham described what he considered to be the best methods for forecasting the future demand for seasonal and nonseasonal products. Let's take a quick look at Graham's formulas for nonseasonal and seasonal products.

Nonseasonal Products
  • Calculate demand for the upcoming month by averaging the usage recorded in the past six months.
Seasonal Products
  • Calculate demand for the upcoming month by averaging the usage recorded in the upcoming six months, using last year's demand for each of these months and then applying a “seasonal trend factor” that expresses the anticipated increase or decrease in business experienced over the past year.

These formulas are simple. When Graham wrote his book, simplistic formulas were necessary for distributors to successfully manage their inventory for the following reasons.

1. Many companies could not effectively deal with mathematical formulas or computers. Back then, 10-key calculators were considered state-of-the-art technology. In fact, most purchasing decisions at the time were based on “SWAG” (silly wild-ass guessing). Any inventory-management formula intended to provide consistency in ordering — including Graham's formulas — had to be fairly simple and easily replicated on a calculator.

2. Computers did not have the power to perform comprehensive forecasting formulas for thousands of parts within a reasonable period of time. Calculating Graham's simple average of thousands of items stretched the physical capability of most computer systems.

Graham's formulas produced demand forecasts that were generally more accurate than the predictions of the guy with the dull pencil and clipboard out in the warehouse. But a considerable difference still existed between Graham-based predictions and what was actually sold. At the time, these deviations were considered unavoidable, and there was no way around them. Consider how business conditions have changed since 1987:

  • Because technology has allowed distributors to expand and increase their market areas, they face more competition than ever. This competition has put more pressure on electrical distributors to consistently stock the products their customers want and to have these products when and where customers want them.

  • Increased competition has also put pressure on profit margins. Distributors must offer lower prices to retain current business and to attract new customers.

  • The number of new products introduced to the market continues to increase rapidly.

These conditions present some unique challenges. Decreased margins tend to limit how much money a distributor has available to invest in inventory. They also must spread the available money to invest in inventory over a greater number of products. Customers are less tolerant if product availability does not meet their expectations.

You're obviously in trouble if you don't have the inventory your customers expect you to have. And if you've bought too much of an item, your money is tied up and can't be invested in the other products that allow you to take advantage of new sales opportunities. These challenges require the best possible product forecasting. I was shocked to read an article on inventory management published recently in an industry magazine where a self-proclaimed inventory “guru” and “successor of Gordon Graham” urged distributors to use Graham's formulas. He didn't discuss the accuracy of this forecast method or provide justification for using these formulas. In a recent study, my firm found when using Graham methodology, the average forecast error for items with recurring usage exceeded 380 percent. We calculated the forecast error with this formula:

Absolute value of (forecast — usage) ÷ the smaller of forecast or usage

Usage was the actual quantity sold, transferred or otherwise used in a specific month. The usage in our analysis was smoothed to adjust for unusually high and low usage. To illustrate the calculation of the forecast error, consider the data in Table 1.

Notice the first two items have the same forecast error. We believe it's equally bad to either underestimate or overestimate demand. Also note that none of these examples have a forecast error greater than the average error my firm found in its study.

You can no longer accept great deviations between forecasts and actual sales. Formulas developed specifically to be easy to understand and to be better than a guy with a clipboard must be replaced with more comprehensive methods. Consider the usage history of these four products shown in Table 2.

Item A100 has fairly consistent usage. Item B200 has continual increasing usage from January through December. The third item has high usage every other month, and item D400 has a seasonal-usage pattern with sales peaking in July. My firm found that different stocked products, even items within the same product line, have different patterns of usage. We need to find a forecast formula for each individual product that will minimize the forecast error.

Weighted-average forecasting allows us to address different patterns of usage. Each weighted-average formula places weight or emphasis on the usage history recorded in specific previous months. Let's look at an example of how a weighted-average forecast is calculated. 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 see how the forecast for July is calculated for an item with the following usage history:

June May April March February
148 133 126 110 104
Month Weight Usage/Business Day Extension
June 3.0 148 444.0
May 2.5 133 332.5
April 2.0 126 252.0
March 1.5 110 165.0
February 1.0 104 104.0
January 0.0 98 0.0
Total 10.0 1,297.5

The extension (1,297.5) is divided by the total weight (10.0) to determine our prediction of the demand per business day for July of 129.8 or 130 pieces. When looking at the usage history of this item, you probably aren't happy with this forecast. It's better than the Graham-method forecast of 120 [(148+133+126+110+104+98) ÷ 6], but it still isn't great. That's because this is not the best set of weights for an item with this pattern of usage. And we haven't taken into account the other factors we need to consider in calculating an accurate demand forecast:

  • Increasing or decreasing trends in usage.

  • Collaborative information about changing needs from customers and salespeople.

  • The appropriate time frame or horizon for the forecast.

See Table 3, for five of the most common sets of weights used in weighted-average forecasting. Formulas “D” and “E” in Table 3 work best for items with seasonal usage. They work with the usage recorded in the upcoming two or three months and use last year's demand for each of these months. To determine the best set of weights for each item, I test the product's usage history using several weighted-average forecast formulas.

Upcoming articles will determine the best set of weights for the item in this example, discuss how to accurately apply the other factors and show you how to adopt these accurate forecasting techniques with just about any computer software package.

How far you can reduce your forecast error depends on factors such as the products your company stocks and the types of customers who buy your products. By using these techniques, the distributors my company has worked with have dramatically improved the accuracy of their future-demand forecasts. This allows them to substantially reduce their inventory investment while providing high levels of customer service. Start the process of improving your demand forecasting by examining the accuracy of your current forecast methods.

Table 1. Conventional forecasting methods can sometimes lead to large errors in product-demand estimates.

Item Forecast Usage Calculation Error
A100 100 50 ABS(100-50) ÷ 50 100%
B200 50 100 ABS (50-100) ÷ 50 100%
C300 15 5 ABS(15-5) ÷ 5 200%
D400 10 40 ABS (10-40) ÷ 10 300%

Table 2. Weighted-average forecasting helps distributors project demand, particularly for those products with different usage patterns, such as those shown in this example.

Item Dec Nov Oct Sep Aug Jul Jun May Apr Mar Feb Jan
A100 100 120 80 90 110 105 88 109 98 118 112 108
B200 300 260 220 188 160 142 138 122 109 98 80 76
C300 1020 28 1030 34 990 36 1033 27 1004 39 1034 26
D400 41 85 160 241 370 398 224 129 57 36 24 20

Table 3. These are five of the most common sets of weights used in weighted-average forecasting.

Previous Month→ 1 2 3 4 5 6 7 8 9 10 11 12
A100 100 120 80 90 110 105 88 109 98 118 112 108
B200 300 260 220 188 160 142 138 122 109 98 80 76
C300 1020 28 1030 34 990 36 1033 27 1004 39 1034 26
D400 41 85 160 241 370 398 224 129 57 36 24 20

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 [email protected].