Garbage In, Garbage Out

Oct. 1, 2006
When referring to inaccurate information provided by computer systems, the old adage, Garbage in, garbage out truly applies. Accurate forecasts are a

When referring to inaccurate information provided by computer systems, the old adage, “Garbage in, garbage out” truly applies. Accurate forecasts are a critical element of effective inventory management.

The last several articles looked at methods for calculating forecasts for future demand of stocked products by examining past usage history. After all, what a distribution company sold in the past is often a good indication of what it will sell in the future. However, some factors can cause future sales to significantly differ from past sales. To produce the best possible forecast, you must correct usage history for nonrecurring activities. These considerations include:

  • Obtaining and losing customers.

  • Customers' changing needs.

  • The competition entering or leaving the market.

  • New product introductions.

  • One-time-only requirements or events.

The effects these factors have on future forecasts must be determined based on collaborative information supplied by salespeople and customers. Many distributors have a hard time gathering accurate collaborative data. It's viewed as a difficult, if not impossible, task. But good collaborative information is critical to achieving effective inventory management. The next several articles will discuss ways to gather and interpret collaborative information as well as ensure it's as accurate as possible.

Obtaining and losing customers

It's late Friday afternoon when your ace salesperson bursts into your buyer's office with excitement and says, “We just got the ABC Industries MRO (maintenance, repairs and operations) contract. Our business is going to increase 25 percent in the next 12 months. They've committed to $1 million in business. Be sure to have plenty of stock on-hand. They'll be watching us closely.”

What does the buyer do? He knows the volume of some items will dramatically increase, but he doesn't know which items. Despite what the salesperson says, it doesn't make sense to increase the stock of every item in inventory by 25 percent.

The answer is simple. The buyer should call the purchasing agent at ABC Industries and say, “We're looking forward to working with you, and we want to make sure we have the products you want when you need them. Could we please have a print out of the MRO supplies you purchased from your previous supplier last year?”

With the past-usage information from the customer, your company's usage history can be adjusted to reflect the significant purchases the customer made from the previous supplier during each of the past 12 months. Demand forecasts using this information will then meet the needs of ABC Industries as well as your other customers.

Unfortunately, distributors also lose large customers. If a distributor continues to maintain stock quantities based on usage that includes a lost contract, overstocks soon arise. When a distributor loses a large account, usage entries should be adjusted to exclude the sales to the lost customer.

Product promotions

Promotions are designed to increase sales of particular products over a short period of time. For predictions of future demand to be as accurate as possible, it's important to include the anticipated effects of a particular promotion in calculating a demand forecast and exclude the sales resulting from the promotion from usage history.

These four steps will help you develop a system to monitor promotional activity:

  1. Define the promotions that should be monitored. A promotion could be a temporary price reduction, special advertising, an associated giveaway or another strategy to increase sales. Note that a particular promotion can occur multiple times within a year.

  2. Specify the start date and finish date that sales will be affected by each occurrence of the promotion.

  3. After the promotion, calculate the sales per day (in units) for each item during the two weeks before the start of the event, the sales per day during the event, and the sales per day during the two weeks following the event.

  4. With the information from step No. 3, calculate the percent change in items sold for the period before the event and the period during the event, as well as the percent change for the period before the event and the period after the event. See table above.

Determining usage for the weeks after the promotion ends is important because most promotions are followed by a “boomerang effect,” or reduction in sales. After all, people considering buying the product probably purchased it during the promotion. For a promotion to be successful, the usage increase during the promotion must be greater than the boomerang effect after the promotion ends. If it isn't, you have given away profit margins or increased your costs without realizing higher sales.

Usage should be adjusted to take away the effects of the promotion because we cannot be sure the same promotion will be offered at the same time each year. As shown in the table, you must adjust January's usage to reduce usage per day by 26.8 percent for 14 days (Jan. 1 through Jan. 14) and increase usage by 9.8 percent for the 14 days after Promotion-01 ends.

Accumulated promotion history can help guide buyers and inventory planners as they anticipate usage when the promotion is offered in the future. For example, Promotion-01 resulted in an average increase in usage of 20.4 percent [(26.8 percent + 14 percent) ÷ 2]. When Promotion-01 is offered again in the future, a buyer should consider increasing the results from the forecast demand formula by 20.4 percent to compensate for the anticipated additional sales.

New product introductions

When introducing a new product, you must ask, “How will sales of this product affect sales of existing stock items?”

For example, a new product may take some, but not all, of the sales away from an existing product. As a result, the existing product often contributes to excess inventory. Why? Let's look at an example.

ABC Distributors is introducing a new item, the Model #A234 widget that can be used in about half of the applications of an existing product, the Model #A100 widget. Model #A234 is more energy efficient and is less expensive. It's not surprising the new product will be used wherever possible, and it will capture about half of the previous demand for the Model #A100.

With forecast formulas largely based on usage history, what will happen if the buyer replenishes Model #A100 based on its past usage without considering the effect the new product will have on future product movement? The distributor will order twice as much of the Model #A100 as is needed. In other words, ABC Distributors will be ordering excess inventory of Model #A100. When a new product is introduced, it's imperative to remember it will partially replace the sales of an existing product. The usage history of the existing product must be adjusted to reflect the projected sales of the new item. Adjust usage history for the previous 12 months by removing that percentage of sales you feel will be taken by the new item.

One-time-only requirements and events

Suppose an electrical contractor wiring a new 50-story office building plans to order 10,000 #D250 connectors. This is a one-time-only requirement for the product, and you definitely don't want to restock to supply this quantity in the future. Therefore, the sale to the customer must be excluded from usage history.

Although most computer systems allow a salesperson to designate that a specific sale should not be added to usage history, salespeople often forget to mark an order or don't recognize an unusual sale quantity. That's why it's important for buyers to review possible unusual sales activity at the end of every month. Possible unusual usage is typically defined as a significant difference between the forecast quantity and actual sales or usage. For example, if usage is more than three times the forecast quantity, the buyer should review the transactions that made up the usage for the product during the month. He or she may discover what appears to be an unusually large sale.

A quick consultation with the salesperson or customer will determine whether this was a one-time requirement that should be adjusted out of usage history or if this represents a new recurring customer requirement. If so, usage history for the past 12 months should be adjusted to reflect this new need.

Possible unusual usage also includes situations where actual usage is far below the forecast quantity:

  • Was the item out of a stock? If so, usage history should be adjusted to reflect what sales would have been if the item had been in stock for the entire month.

  • Did the customer experience a temporary shutdown or delay? Again, usage history must be adjusted to reflect what would have been sold under normal conditions.

  • Is there a significant change in customers' needs? Past usage history for the previous 12 months must be adjusted to reflect future customer requirements.

To produce the best possible forecast, you must ensure that your usage history be corrected for any activity that will not recur. After all, garbage in means garbage out.

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 via e-mail at [email protected].

Table. For a promotion to be successful, the usage increase during the promotion must be greater than the boomerang effect after the promotion ends. If it isn't, you have given away profit margins or increased your costs without realizing higher sales.Event Start date End date Prior two weeks sales per day Event sales per day Post two weeks sales per day Prior-Event % Prior-Post % Promotion-01 Jan. 1 Jan. 14 82 104 74 26.8% -9.8% Promotion-01 June 1 June 14 86 98 82 14.0% -4.7% Promotion-02 Feb. 7 Feb. 10 74 86 70 16.2% -5.4% Promotion-02 Oct. 14 Oct. 21 93 102 89 9.7% -4.3%