Archive for December, 2009

Extending the value of EDI 852 data with non-POS data

December 13, 2009

After you have the basics of EDI 852 data analysis covered it’s time to think about adding in non-point of sale source data to extend the value of data analysis. By linking secondary data you can create a 360 degree view of what’s happening in the business.

Non-point of sale data that can be linked to Edi 852 data includes: plan-o-gram files, shipping data, sales force organization, store attributes, warehouse inventory, forecast, demographics, and weather data.

· Shipping data indicating inventory quantities shipped to stores. Shipping files can include the selling price and cost of each item so gross margin calculations can be calculated. [database key:
UPC/SKU and store number]

· Sales force organization describes each sales representatives store management responsibilities and sales quota. [database key: store number]

· Store attribute files provide additional information beyond the city, state and zip code like mall, region, and supplying distribution center. [database key: store number]

· Warehouse inventory details quantities available in the vendors warehouse which is available to be shipped to retail stores. [database key: UPC/SKU]

· Demographic data describes factors like income, age, ethnicity, and language for each store.

· Weather history and forecast for each store. [database key: store number]

· Plan-o-gram file describes the SKU assortment for each store. [database key: UPC/SKU and store number]

By using the UPC/SKU and store number provided in the EDI 852 data you can link together the files above using the database keys indicated. Then very interesting analysis can be conducted like finding the key demographics for top and bottom performing stores, or sales quota attainment at a store and sales representative level. In addition with plan-o-gram data linked to store sales and on hand activity you can determine if the retail is properly executing the plan-o-gram and work with the replenishment manager to fix any issues.

Follow us on Twitter: #edi852 or our blogs and



December 13, 2009

Started a new category on Twitter for EDI 852 research and information. Follow rainmakergrp and watch for #edi852 to stay up to date on all things regarding point of sale data analysis and specifically EDI 852. Topics will include how to best use EDI 852 for analyzing retail sales performance, on hand inventory forecasting and replenishment, and how to turn the data into actionable plans for your buyer.

Using EDI 852 data to audit Plan O Gram Compliance

December 8, 2009

Many of the vendors we work with have a specific plan o gram they agree to with a buyer on an annual or semi-annual basis. After reaching an agreement with the buyer on the plan o gram, the vendor estimates sales and then creates a production forecast based on the number of facings and estimated sales. The problem many vendors encounter is that with multiple plan o grams, each with a different SKU assortment at thousands of stores, auditing the execution can be a challenge—so much so that most vendors do not conduct a detailed review on a regular or even semi-regular basis. If the plan o gram is not executed properly, sales are lost, the vendor can end up with extra inventory, and ultimately the retail buyer may conclude incorrectly that the vendor’s products are not performing well.

The good news is that with some effort the EDI 852 data provided by the retailer can be used to audit plan o gram compliance. Here’s how. Create two plan o gram files; one file will list the SKU’s that are included in each plan o gram, the second file will list each store and the plan o gram assigned to the store. The layout for the first file should be SKU # | SKU description | POG Name 1 | POG Name 2 with as many distinct plan o grams as you have. The second file should be Store # | City | State | POG Name. Using these two files and the EDI 852 sales data, you can load these into a database and then analyze compliance, where compliance means that a store has the intended SKU(s) on hand, or has sold the item(s). If either of these conditions are true, the SKU is “active.” (Depending on where you are in the roll out process and how quickly your SKUs move, you may need to consider more than one week. For example a 4 week average of OH and sales might be appropriate.) For any store that does not have all SKUs “active,” calculate a plan o gram compliance percentage by dividing the active SKUs / planned SKUs. Then sort the final table descending by plan o gram compliance percentage. This provides the top down action list you need to go back to your buyer or replenishment manager and address execution issues.

Retail buyers and replenishment managers are busy. They don’t have the time, or in most cases the tools, to ensure proper execution of your plan o gram. You do not want to get to review time and find out that poor execution on the plan o gram caused lost sales.

Note: If you want to take plan o gram compliance to the next level you can also include the minimum inventory OH for each SKU in the first file described above. That will allow you to not only check if the SKU is “active” but also if the SKU is properly stocked at the stores.