Forecasting Future Demand of Products

This is a continuation of a series we started several months back. Now, in addition to theory, we're going to get into actual effective forecast formulas.

In 1987, Gordon Graham wrote a book, Distribution Inventory Management for the 1990s. In this book, Graham described what he considered to be the best method for forecasting the future demand for both seasonal and non-seasonal products. Let's take a quick look at these formulas:

Non-Seasonal 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, last year, and then applying a "seasonal trend factor" that expresses the anticipated increase or decrease in business experienced over the past year.

These are simple formulas. And at the time Gordon wrote the book, simple formulas were necessary for distributors to successfully manage their inventory:

• Many buyers could not effectively deal with mathematical formulas or computers. Ten-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 formula (including Graham's) introduced to provide consistency in ordering had to be fairly simple and easily replicated on a calculator.

• 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 for thousands of items stretched the physical capabilities of most computer systems.

The demand forecasts produced by the Graham formulas were generally more accurate than the predictions of the guy with the dull pencil and clipboard out in the warehouse. But there was still a considerable difference between Graham-based predictions and what was actually sold. At the time, these deviations were considered "unavoidable," and there was no way around them.

Now consider how market conditions have changed since 1987:

• Technology has allowed distributors to expand and increase their market areas. The result: You face more competition than ever. This competition has created more pressure on distributors to consistently have the products their customers want, when and where they want them.

• Increased competition has also put pressures on profit margins. Distributors have to offer lower prices in order to retain current business and attract new customers.

• The number of new products introduced to the market continues to increase at a rapid rate.

These conditions present some unique challenges:

• Decreased margins tend to limit the amount of money a distributor has available to invest in inventory.

• Distributors must spread the money available 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. You can no longer accept as "inevitable" great deviations between forecasts and actual sales. Formulas developed just to be "easy to understand" and "better than a guy with a clipboard" have to be replaced with more comprehensive methods.

Products with different patterns of usage, and different replenishment methods require different forecasting formulas. We need more than one formula for non-seasonal products, and one formula for seasonal products. For example, a product whose sales mirror local economic conditions requires a different formula than a product with steady, fairly predictable sales. And just as important, each formula needs to be easy to understand.

During the next several months, we'll look at some of the 29 different forecast demand formulas developed by EIM. We're going to start with a formula for non-seasonal products with fairly consistent usage. These are items that sell regularly and whose volume has increased or decreased less than 20% per month during the last several months.

When forecasting the usage of non-seasonal products with fairly consistent usage, we want to average the usage that was recorded during the past several inventory periods. But we also want to "weight," or place more emphasis on, the most recent month. Why?

1. There are often trends in a product's usage as it becomes more or less popular over time. For non-seasonal products, demand in the upcoming inventory period will more likely be similar to the usage recorded in the past several inventory periods than what happened six, eight, or twelve months ago.

2. At the same time, there is usually a certain amount of random variation in a product's usage from one inventory period to another. Notice how the usage of the item in the first example below has fluctuated over the past five months. This "up-and-down" pattern of usage is common for inventory items with moderate-to-high sales. If we were to use just the most recently completed one or two inventory periods in our calculations, the random fluctuations in usage would probably have too great an influence on the forecasted demand. We want to include enough history to ensure that random fluctuations do not have a significant impact on a product's forecast.

Here is a common set of weights to use in calculating demand for a non-seasonal item with moderate-to-high 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 an item is calculated with the following usage history. Usage is the quantity of a product sold, transferred, used in assemblies or repair orders, or otherwise taken from stock.

Note that we've specified the number of business days in each month, and determined the usage per business day. Utilizing usage per business day provides more accurate forecasting than traditional forecasting methods that rely on total monthly usage or usage per calendar day. After all, if a company is closed for several days during a month (remember the Christmas holidays?), considering that month's lower usage equally with the usage recorded in other months tends to underestimate future forecasted demand. For example, in the chart displayed above, total usage recorded in May (133 pieces) is about 5.5% higher than total recorded in April (126 pieces), but the demand per business day is the same.

We will apply the weights of the demand calculating formula to the usage per business day for the five preceding months to determine the forecast demand for July:

The extension (66.4) is divided by the total weight (10.0) to determine our prediction of the demand per business day for July (6.64 pieces per day). And this demand per day is multiplied by the number of business days in July (21) to predict the demand of 139.4 pieces for the inventory period.

Compare the results of this calculation to the demand predictions provided by other forecast formulas and methods. We think you'll be impressed with the results. Next month we'll look at non-seasonal products with significant increasing or decreasing usage. In the meantime, if you have any specific questions, please let us know.

©1999, Effective Inventory Management, Inc., 215 South Denton Tap Road, Suite 230, Coppell TX 75019. All rights reserved. This article cannot be reprinted, or reproduced, in whole or in part, without the expressed written permission of Effective Inventory Management, Inc.