The Power of Clean Data

April 1, 2005
Two electrical distributors pumped up profits when they analyzed and cleaned errors in their product data.

Challenged by price-matrix management, many electrical distributors' profit levels drop year after year. They have fallen victim to out-of-control profit leakage, which occurs as a result of poor product and pricing data.

The importance of fixing this problem has caught the attention of industry leaders, including Bill Elliott, president, Elliott Electric Supply, Nacogdoches, Texas. When asked to name the three biggest challenges facing the electrical industry in Electrical Wholesaling's ElectroForecast 2005 (January, page 16), Elliott, also the chairman-elect of the National Association of Electrical Distributors (NAED), St. Louis, identified managing the price matrix as one of the biggest.

Surveys exploring distributor pricing and product data management practices conducted by Allen Ray Associates in 2004 indicate many electrical distributors have no clue regarding the purity of the product and pricing data populating their systems. Simply put, managing this data has not been a priority for many distributors. That's too bad — because most would enjoy an increase in gross margin of at least 1 percent if they gave it priority.

The downward data spiral can start in several places. Bad data is brought forward when electrical distributors switch or upgrade ERP systems; third-party pricing services sometimes provide bad data as well. To compound the problem, many electrical distributors were forced to reduce staff when the electrical business climate declined at the beginning of this century. Personnel in charge of maintaining prices were often among the first to go. With fewer employees available to manage pricing and product data, some distributors lost control of this important information. It had a huge impact on bottom-line profitability.

Additionally, distributors want and need “net-into-stock” prices to stem the wave of incorrectly claimed special pricing authorizations (SPAs). Many manufacturers' pricing catalogs provide both pricing multipliers (the distributor must figure his price using a multiplier and a column price) and net-into-stock prices (an already heavily discounted real price that requires no math on the part of the distributor). When manufacturers mix and match, using both multiplier prices and net-into-stock prices, it can become confusing, plus it's labor intensive for a small distributor to put into the ERP system.

When bad pricing data is used by distributors to build sell prices, it gets ugly. Many distributors do not understand why they're not making money. Part of it is they don't truly understand their pricing.

The Road to Recovery

By studying several key areas of their businesses, electrical distributors can quickly determine if they have a problem with inaccurate pricing and product data.

  • Examine counter sales to see if they produce lower margins than the company as a whole. Who in your organization overrides established pricing? You may have personnel/training problem.

  • Look at the how much time employees spend changing or correcting prices. Is there overtime associated with this function? Your company may be spending more time on this than you realize.

  • Take a small sample of your “C” and “D” gear or controls and compare them to product data that has gone through the Data Audit Certification (DAC) process developed by Industry Data Exchange Association, Rosslyn, Va., for the Industry Data Warehouse (IDW). Comparing your product data to this data, which comes directly from the manufacturer to the Industry Data Warehouse, will give a true indication of the data problems in your system.

When examining a small sample “C” and “D” data, mistakes you may find include:

  • UPC errors
  • Fragmented descriptions
  • Wrong units of measure
  • Inaccurate published distributor cost
  • No cost in the cost field of “Column 3” pricing

Two Real-Life Examples

Two large electrical distributors — one with $164 million in annual sales and the other with $198 million — recently embarked on a quest to correct pricing and data. Each realized inaccurate data cost them plenty in decreased profits. Each was willing to allocate resources to fix the problems.

With the help of Allen Ray Associates, retained as a consultant for the data-cleaning project, the electrical distributors began a journey to clean their data.

Both distributors' current ERP systems had inherited data mistakes from previous versions during system upgrades. Regardless of whether the distributor built a sales price from a reference column with a multiplier or from cost up, the average of bad data for “C” and “D” items was between 37 percent and 42 percent. With this much bad data, when selling “C” or “D” items either from stock or special order, there's no way a distributor will make the profit level needed for the carrying time or special orders.

Each distributor ran between 45,000 and 88,000 items in their ERP systems. Both companies needed at least two employees to maintain this information. Incidentally, only 8,000 to 14,000 items were in stock. The variance between the large product databases is explained by the number of branches and dissimilar lines within those branches. Both distributors were having trouble finding and keeping current net-into-stock prices and sell prices because of the multiple sources of product and pricing data they received from their manufacturers, reps and third-party database companies.

Both distributors also run “parallel catalogs” with more than a million items. The product and pricing is supplied from third-party pricing services. Instead of customizing the data feed from the pricing service companies to receive only the information for the products carried in stock, the distributors wanted all the data to grow their product lines. However, loading data for more than a million items into an ERP system would cause it to run at a crawl.

Instead, the distributors load the data into parallel catalogs — a file that is separate but linked to the lookup reference screen. Salespeople can look up an item and price it even though the distributor doesn't stock it. Although adding the products seemed like a good business decision, the added products cause headaches when supplied data is bad.

Neither distributor had ever focused on cleaning its product and pricing databases. Instead, errors were corrected on the fly — often after products had already been invoiced. Even though both distributors had product managers on staff, they were still challenged with inaccurate product data descriptions and pricing.

Both distributors understand that once they normalize their product and pricing data, synchronizing their data with what is provided (preferably directly from the manufacturer through IDX2 from IDEA) is an on-going process.

The Grim Details

Incorrect UPC numbers or manufacturers' product codes accounted for 36 percent of the data mistakes; the remaining errors were in the lack of a populated cost field or incorrect sales prices. The sheer size of these two databases required constant attention, and required dedicated personnel. Unfortunately, the cost of this personnel contributed directly to overhead and not to the value-added services for which the distributors' customers will pay. The vast majority of data mistakes occurred with “C” and “D” items.

Another problem area was the counter. At both distributors, more than 50 percent of price changes were made by counter sales personnel. Counter sales gross margins for both distributors averaged between 21 percent and 24 percent. A distributor needs to make at least 28 percent gross margins on counter sales.

Cleaning up the Mess

Although product and pricing data may come from various sources, an examination of this data and synchronization with the manufacturer is of prime importance if distributors are to increase margins and grow business. Synchronizing distributor data with the data provided by the manufacturer through IDEA reduces transmission errors, which in turn reduces “kick-out rates” or errors on the order, acknowledgment and billing between manufacturer and distributor. Correcting these errors can easily cost between $75 and $150 per occurrence. Every time an error is generated, it costs both the distributor and the manufacturer.

If a distributor enters an order with the wrong price, unit of measure or UPC code, the manufacturer must manually fix the mistake and notify the distributor. If a distributor does not fix the error in its system, the order will not be received correctly. It will cause late payment because it does not match what's in the distributor's system. The distributor will end up either partially paying the invoice, issuing a debit to the manufacturer or sitting on the invoice until there is some resolution. Either way, it becomes a costly transaction that can be avoided by synchronization with certified manufacturer data through IDEA.

IDEA now has the necessary fields in its IDW database to supply net-into-stock cost down to the branch level. This means a distributor can potentially automate to receive net-into-stock pricing at the branch level from a single secure source via EDI in a timely fashion. Distributors need not wait for reps to authorize pricing, or handle various conversion routines to produce accurate branch-level pricing.

Both distributors increased profit at the end of these projects. When they ran their final tabulations and looked back across sales of the items for which there had been mistakes for years, they wondered why they hadn't gotten this project off the ground sooner. Each company would have enjoyed an increase of 1.00 to 1.84 percentage points in gross margins, respectively, at their currently operating ratios.

Both distributors now have committees that look at pricing on a regular basis. This makes sense. Distributors are in the business of selling product at a profit — why not have employees examine their cost, sell prices and inventory on a regular basis?

One distributor is working with counter personnel to not be the customers' “good buddies” and automatically lower prices. The distributor is also restricting which employees have the authority to discount prices, and have reevaluated acceptable margins.

Synchronized product and pricing data through IDEA allows a distributor to drive business faster. The reduction in data mistakes means less overtime, less headcount, lower postage, fewer aggravating phone calls and better customer service.

The author is president of Allen Ray Associates Ltd., Arlington, Texas. For more information about product and price synchronization, contact him at (817) 652-1143, e-mail him at [email protected] or visit his Web site at www.allenray.com.

Cleaning up Your Data

A good tool to ensure clean data is available through the Data Audit Certification (DAC) process developed by Industry Data Exchange Association, Rosslyn, Va., for the Industry Data Warehouse (IDW). DAC is growing in scope and size, with over 910,188 certified items from more than 120 manufacturers and 109 distinct, brand-name product lines. The very idea that manufacturers are placing “certified data” at the Industry Data Warehouse is an open invitation for the approved distributor and manufacturer to talk the same language from the start of the order, acknowledgment, and invoice to a SPA claim back.

To emphasize the importance of clean data, the following procedure was followed to test the accuracy of product and pricing data provided by a third-party pricing service. Product data for more than 100,000 items from one manufacturer was selected from a third-party pricing service and from the IDW. UPC numbers were matched from those products and then tested for accuracy of the data from both sources.

The DAC-certified data from the IDW obtained directly from the manufacturer was 100 percent accurate. The third-party pricing service data contained inaccurate minimum order quantities in more than 60 percent of the items and a disturbing variety of pricing errors.

For more information on DAC data, contact Mike Rioux, IDEA president at [email protected].