Kohl's EDI 852 Reporting

If you are a vendor supplying to Kohl's, you are eligible to receive product sales activity and inventory data via EDI 852. Preparing to setup and receive the EDI 852 files can be confusing, and creating usable reports for your team can be very time consuming. Fortunately, Accelerated Analytics® provides a simple, outsourced service for all your Kohl's EDI 852 reporting needs.

Using Accelerated Analytics® makes all your reporting headaches go away. With Accelerated Analytics®, we handle all the data conversion, database hosting and reporting. We even provide training and the end user reporting tools. 

Accelerated Analytics® benefits:

• Eliminate manual data entry and manipulation
• Consolidate all Kohl's store data on all your SKU's into one reporting database
• Pre-built exception reports with color coded dashboards
• No software or hardware to purchase
• Sophisticated charts and graphs

Available reports:

• This weeks sales and inventory by store and SKU
• Last weeks sales and inventory by store and SKU
• This months sales and inventory by store and SKU
• 6 week rolling sales and inventory by store and SKU
• Sell-thru
• Inventory turns
• Days supply on hand 

Accelerated Analytics® will give you the ability to anticipate changes in sales and inventory, so you can make adjustments before a costly mistake occurs. Our EDI 852 reporting is the best on the market. 

Gaps still exist between vision and reality for EDI 852 data

In a recent conversation with a well known CPG vendor, it became very clear that vendors are still confused by data quality when it comes to EDI 852. Simply put, vendors are struggling with the known and unknown data quality issues that exist with the data retailers send out via EDI 852. Specifically, vendors are increasingly aware that on-hand inventory levels reported in EDI 852 are often suspect at best.
 
This vendor asked a very pointed question; "Why can't retailers get this right, and more importantly, how do they expect us to make decisions on bogus data?" This is a serious challenge and one that needs to be addressed by retailers. If they expect vendors to monitor in-stock and make accurate decisions, the data quality must improve. Fortunately, there are strategies to improve upon the data reported by retailers in order to arrive at a useful decision making tool.
 
First, each vendor has an accurate count of how many units the retailer purchased and they know when the inventory is to be shipped. By subtracting the units sold as reported in the EDI 852 from the shipped inventory units, you can calculate an on-hand value. The key to this work-around is a good starting point. In most cases, this can be determined by examining inventory history and requesting from the retailer a one time inventory position count. It's not a perfect system, but over time, the calculated on-hand becomes more and more accurate and it is a useful decision making tool. There are other strategies as well, but we see this as the most common method for handling poor on-hand reporting accuracy.
 
What strategies are you using to deal with poor data quality?  More importantly, what retailers are you working with that are not reporting accurate on-hand?  Many times, the starting point is simply identifying what data you can trust and what data you can't. 

JC Penney EDI 852 Reporting

If you are a vendor supplying to JC Penney, you are eligible to receive product sales activity and inventory data via EDI 852. Preparing to setup and receive the EDI 852 files can be confusing, and creating usable reports for your team can be very time consuming. Fortunately, Accelerated Analytics® provides a simple, outsourced service for all your JC Penney EDI 852 reporting needs.

Using Accelerated Analytics® makes all your reporting headaches go away. With Accelerated Analytics®, we handle all the data conversion, database hosting and reporting. We even provide training and the end user reporting tools. 

Accelerated Analytics® benefits:

• Eliminate manual data entry and manipulation
• Consolidate all JC Penney store data on all your SKU's into one reporting database
• Pre-built exception reports with color coded dashboards
• No software or hardware to purchase
• Sophisticated charts and graphs

Available reports:

• This weeks sales and inventory by store and SKU
• Last weeks sales and inventory by store and SKU
• This months sales and inventory by store and SKU
• 6 week rolling sales and inventory by store and SKU
• Sell-thru
• Inventory turns
• Days supply on hand

Accelerated Analytics® will give you the ability to anticipate changes in sales and inventory, so you can make adjustments before a costly mistake occurs. Our EDI 852 reporting is the best on the market. 

Retail Sales Increasing

Despite uncertain economic reports, retailers are expected to report increased sales, according to an article in the WSJ.  Of interest to this blog, the article noted retailers have been doing a good job of managing inventories in a difficult environment.  As we work with our clients, a great deal of emphasis has been placed on forecasting sales demand and identifying out of stocks.  We have blogged a lot about how to use EDI 852 and point of sale data to manage inventory effectively, and now is a great time to get started if your organization is not fully using data to improve your business.  With instock rates in the 98% range, a vendor still has the opportunity to increase sales significantly with better inventory management.  Don't miss your opportunity to get that last 2 or 3% of sales.

Home Depot EDI 852 Reporting

If you are a Home Depot vendor, you are eligible to receive product sales activity and inventory data via EDI 852. Preparing to setup and receive the EDI 852 files can be confusing, and creating usable reports for your team can be very time consuming. Fortunately, Accelerated Analytics® provides a simple, outsourced service for all your Home Depot EDI 852 reporting needs.  Our team works with dozens of Home Depot vendors like Quickie, W.M. Barr, Techtronic Industries, Worthington Cylinders, Trex, and many more.  

Using Accelerated Analytics® makes all your reporting headaches go away. With Accelerated Analytics®, we handle all the data conversion, database hosting, and reporting. We even provide training and the end user reporting tools. 

Accelerated Analytics® benefits:

• Eliminate manual data entry and manipulation
• Consolidate all Home Depot store data on all your SKU's into one reporting database
• Pre-built exception reports with color coded dashboards
• No software or hardware to purchase
• Sophisticated charts and graphs

Available reports:

• Sales by BYO
• Sales by Market
• Sales Velocity by Store (A,B,C,D,E)
• This weeks sales and inventory by store and SKU
• Last weeks sales and inventory by store and SKU
• This months sales and inventory by store and SKU
• 6 week rolling sales and inventory by store and SKU
• Sell-thru
• Inventory turns
• Days supply on hand 

Accelerated Analytics® will give you the ability to anticipate changes in sales and inventory, so you can make adjustments before a costly mistake occurs. Our EDI 852 reporting is the best on the market. 

Store Level Merchandising Analysis Using EDI 852

The following is a step by step process to aid replenishment vendors in identifying stores on an item level basis, that are losing sales due to inventory stock outs or inventory that is present but unavailable for sale.  Such unavailable inventory may include lost or damaged items or items on the shelf but not available to the customer for any of a variety of reasons.  This process assumes that the vendor is receiving accurate and detailed EDI 852 Product Activity Data (or POS data via Retail Link or Partners Online, etc) on no less than a weekly basis from their retailing partners.  This article will focus on identifying and addressing underachieving stores. 

Step 1
The vendor will calculate average weekly sales velocity (Avg WS) at an item level across all stores.  This is best calculated using the most recent twenty-six weeks of sales.  Thus, for a given item, the calculation would be:

            Sum(last 26 wks. unit sales) = Avg WS
                               26

Step 2
Calculate the average item sales velocity (Avg WS) for each item for all stores for the last ten weeks of sales.  For each item, look at the last ten weeks of unit sales at the store level and separate the items by store into five categories.  For ease of identification, label these categories A-E.  The categories are as follows:

A.  Most recent two weeks of sales.

• Stores with sales in the last two weeks for any given item will fall into this category

B.  Most recent four weeks of sales.

• Stores with no sales in the last four weeks for any given item will fall into this category

C.  Most recent six weeks of sales.

• Stores with no sales in the last six weeks for any given item will fall into this category

D.  Most recent eight weeks of sales.

• Stores with no sales in the last eight  weeks for any given item will fall into this category

E.  Most recent ten weeks of sales.

• Stores with no sales in the last ten weeks for any given item will fall into this category

The total percentage of sales of any given item for a given category can be accurately calculated by dividing the number of stores per item in any category by total stores (TS). 

            Total Stores in a Category  = % each category is of the total
                         (TS)

This percentage calculation is a better, more accurate way to judge relative performance of each category than by comparing unit sales.

Identifying & Addressing Underperforming Stores
The remaining article focuses on underperforming stores, that is, stores that fall into categories D or E.  Now that you know how many stores are in categories D or E, go back to the list of items and the last 10 weeks of sales, and identify what store numbers are present in the bottom two categories and not in any of the other categories. These stores are stores with no sales in the past 8-10 weeks.  Pull the current inventory on hand for each store.

Out of Stock Stores
Stores with no sales and zero inventory on hand are most likely out of stock stores.  Vendors will want to identify the last week that a given store recorded a sale for a given item in categories D-E.  The vendor can then estimate lost sales by unit for that item/store combination by multiplying the number of weeks since the last sale by the average weekly sales (Avg WS) calculated in Step 1.

            (Avg WS) *[Sum(weeks w/o sales)] = Lost sales by unit due to stock-out (LU)

Lost sales by unit (LU) can also be multiplied by the price of the item to determine lost sales in terms of revenue (LR).

            (LU) * (price of given item) = LR

Inventory stock-out problems are typically due to one of two things: Inaccurate inventory replenishment reorder points or inventory availability issues on part of vendor.  If that item was out of stock due to high reorder quantity, then a vendor can contact the replenishment manager at the retailer responsible for the underperforming store(s) and suggest changing the inventory replenishment set point, using lost revenue (LR) as the rationale for the recommendation.  This exercise can be performed for all item/store combinations that had few or no unit sales for an 8-10 week period (categories D-E) and showed no inventory on hand.

Stores with Inventory on Hand, But No Sales
Some of the stores are going to reflect no unit sales in the past 8-10 weeks, but still have on hand inventory. This typically indicates inventory which is misplaced, lost, stolen or stock on the shelf, but out of view of the customer for whatever reason.  It may also include damaged inventory and inventory otherwise unavailable for sale.  In this case, the vendor would contact the retailer and investigate the problem.  The inventory replenishment system from the retailer will not release an order for new merchandise until the vendor visits the store directly or contacts the store manager to investigate the problem and demonstrate that the product is not available for sale.  It is useful, when contacting the store manager, to know the date of the last unit sold.  This date, and the average weekly unit sales (Avg WS) calculated in Step 1, will indicate to the store manager when a sale should have occurred.  That is, if, on average, a given item is sold every other week, and 8-10 weeks have passed at a given store without a sale despite recorded inventory on hand, this is indicative of a problem, since 4-5 units should have been sold during that timeframe. 

Business Rationale for Store Level Merchandising Analysis
Conducting a store level merchandising analysis can be a time consuming effort for a vendor.  Many vendors have trouble rationalizing the expense, especially vendors with very good in-stock rates.  But, even a vendor with an in-stock rate of 98.5%, still has 1.5% of stores out of stock.  In a typical 3,000 store chain, this could represent as many as 45 stores out of stock.  If those stores averaged just one unit sold per week, that translates to as many as 2,340 units of lost sales per year.  Since this represents only a single item, and out of stock stores typically are out of multiple items and average significantly more than one unit sold per week per item, this vendor is looking at hundreds of thousands, or potentially, millions of dollars of lost revenue (LR) per year, despite a very high in-stock rate of 98.5%.

Resources:   Whitepapers on SKU Sales Analysis, Store Analysis, Out of Stock Analysis and SKU Forecasting are available.  http://www.acceleratedanalytics.com/download-whitepapers/

Retail sales improvement requires careful forecasting

The WSJ reported retail sales Rose 3.3%, showing signs consumers are returning to stores.  This is a great sign for the retail market as it seems a turnaround may be in the works.  Macy’s posted a 3.4% increase, Saks reported 7% and Costco 8%.  As demand begins to increase, vendors need to keep a careful eye on the supply chain.  Retail buyers have been operating on low open to buy for over a year, so inventory levels may be below where they will need to be to satisfy demand.  Vendors using EDI 852 data for forecasting need to make some careful adjustments to their forecasting model to not be caught by surprise.  Here’s why.  Forecast models use historical demand as the foundation for current year predictions, but last January was a terrible month for retail sales, so a simple look at comp year demand will give a misleading result.  To correct for this, vendors should be considering not only last year’s demand, but also the prior year’s demand and the current period trend.  By combining these three numbers, vendors will have a more accurate model and hopefully not get caught by surprise.  But even with a good forecast, we expect sales to be unpredictable for the foreseeable future, so vendors must carefully watch demand and inventory levels by analyzing the EDI 852 data weekly or even daily and making push order recommendations to their buyers.

Analyzing Home Depot Market Sales

Over the past month or so, we have been rolling out new sales analysis for our Home Depot vendors on market performance.  Vendors to Home Depot using EDI 852 know buyers often want to receive sales analysis at a market level.  What we often find, however, is the analysis conducted does not take into consideration the number of stores in the Home Depot market.  To conduct a proper POS analysis of Home Depot markets, you really have to take into account the number of stores in market.  For example, Home Depot market 48 has 80 stores, while market’s 138 and 160 have one store.  By summing the units sold from the EDI 852 to a market level and then dividing by the store count in that market, you can get an average sales per store for the market.  This metric is very useful in comparing the performance of the markets in a meaningful way.  In addition, you can use the SKU count in your plan-o-gram to further compare the performance of the stores at a market level.  You might also consider a store level sales analysis, so you can rank your stores by sales volume A to E.  This lets you not only compare the store against the average sale in the Home Depot market, but also against its sales volume peers.  POS sales analysis by Home Depot market can yield some very actionable results, give it a try.

Use twitter to keep up to date #edi852

Follow news on EDI 852 and retail pos analysis on twitter. You can find us @rainmakergrp or #edi852.

Lesson on the Golf Course

I went golfing the other day and got paired up with a guy named Henry. During the course of the afternoon, I came to find out Henry is a very successful business man who spent part of his career in insurance and now owns more than a dozen hotels in ‘retirement’. To me, retirement means nothing but golf and sitting on the beach, so I’m not sure Henry is ‘retired’, but anyhow. He shared an interesting story with me.  He was having lunch with another very prominent business man, which if I wrote his name 80% of you would recognize. Henry asked him, “What is the key to your success?” This man considered the question for only a brief moment and said, “Oh it’s very simple”. “If a job is worth doing, then I hire someone to do it, if it’s not worth doing, then I don’t do it at all.” I was struck by how profound and simple that was. He had created a simple method for deciding how to focus his energy where it counts the most and avoid becoming entangled in the day to day details. I was thinking about this in relation to our business and was struck by the fact that many vendors that come to us for EDI 852 and POS reporting, are only doing so because their buyer told them they needed to, or because an executive at their company thought it might be a good idea. They are not committed to analyzing and using the data to improve their business. On the other hand, we have vendors who dig into the details of the data every week and find out of stock issues and sales opportunities. They are making the most of the data and the results show in their growth and inventory GMROI. So, the bottom line is this; if you are considering EDI 852 / POS data analysis, don’t stick your toe into the water, jump in all the way and make the most of the data, and your business will improve.

Store Sales Analysis

The primary purpose of a store analysis is to identify the stores which are making the largest contribution to total sales. When the highest contributing stores are identified, an analyst can study the characteristics of those stores, including SKU assortment, demographics, promotions, min/max on hand, and make recommendations on how other stores can be improved to enhance performance. An important objective of a store analysis is to grade stores by performance into major categories, to save time and focus out of stock and forecasting on the highest contributing stores in future analysis. The data provided in an EDI 852 document provides all the necessary information to conduct a store sales analysis.

Buyers often have a store list with categories defined A,B,C,D based on sales performance. However, the buyer’s categories are typically assigned based on the total sales for a given store or total sales for a product category. This may result in a different performance ranking than the analysis on your specific SKU’s. Because plan-o-gram decisions are made based on the retailer’s store categories, you may find that an A store for your items is considered a B or C store based on total sales. Request a list of store categories from your buyer and compare to the categories from your analysis. If there are variances, we recommend you meet with your buyer and discuss adjustments to the plan-o-gram based on store performance for your SKU’s.

Why should you conduct a store sales analysis using your EDI 852 data?
· Identify variances between the retailers general store grade and the actual store grade for your SKU’s.
· Understanding a store's performance relative to its peers allows you to focus your attention where it is most needed.
· Identify the best allocation of promotional dollars
· Work with the buyer to put the right plan-o-gram in each store
· Retail replenishment decisions are usually made based on their store grade, if it’s not correct for your SKU’s, there is a problem you can correct
· Compare store grades across retail partners in the same geographies so you can identify new expansion opportunities.

Additional information and detailed instructions are available at http://www.acceleratedanalytics.com/download-whitepapers/

Top Questions About Point of Sale Data Analysis

Vendors are working hard to understand how to best use retail POS and inventory data, which is made available via EDI 852 or a web portal. Here are five very common questions vendors ask as they work with our team to put a data analysis solution in place.

What is the difference between EDI 852 and data available on my retail customer's web site? The most obvious difference is the format of the data. EDI 852 is a standard document template, but it is encoded using line identifiers and other language necessary for computers to make sense of the data. EDI 852 must be parsed and translated to be of any use to a business user. Data available in a retail portal is typically either presented on screen or saved into a text or spreadsheet format. These files do not require translation and can be opened in a variety of Windows programs. A second difference is the level of detail available. An EDI 852 document always includes units sold by UPC but it may not include on-hand data. And receiving store level EDI 852 data is often an additional selection and cost. Most retail portals will provide detailed store level data files, or presentation of detailed data on the screen. Finally, and most importantly, EDI 852 values for each UPC can be different than the values reported in a file available on the portal. This can be due to different reporting periods, different source and/or additional source system data, or a different method of handling of returns.

If I can choose between EDI 852 and a file from my retailers' portal, which one should I choose? This decision comes down to a few factors. First, does the retailer charge a fee for sending data via EDI as opposed to accessing the data on the portal. Second, does the EDI 852 data provide less information than the portal. For example, as noted above, some EDI 852 files do not include on-hand or store level data. Finally, research the data accuracy of the two sources and choose the one which will best support your decision making process.

What types of reports should I be using? There are three reports that form the backbone of retail POS data analysis: item sell-thru by store, inventory on-hand by item and store, and top selling items. From these three reports you can create a library of very useful decision support tools segmented by geographic region, product category, and by retail partner.

Why should I consider an outsourced service for POS data analysis? For most vendors, working with POS data falls outside their IT organization's typical scope of expertise and tools. Simply put, there is a fairly large volume of data which requires translation, scrubbing, and organization into a sophisticated data warehouse. The data does not fit into most organization's ERP, forecasting, or accounting system so the IT department is faced with building a custom application. Then, end users need a simple and quick tool to access the data for analysis and decision making. An outsourced service can deliver the necessary engineering and software tools in a very short period of time without an expensive investment. And outsourcing provides a cost effective monthly expenditure which aligns with your cash flow instead of a large capital expense.

Why can't I just use a spreadsheet for analyzing POS data? Spreadsheets have many limitations when it comes to analyzing POS data, not the least of which is simple row and column limitations. But more importantly, there is a significant amount of work required each day or week to accept, transform, format and analyze data in an Excel spreadsheet. Time which your staff can avoid all together by using more sophisticated tools and/or an outsourced service. In addition, spreadsheets are generally not well suited for team based collaboration on data. Each time a spreadsheet is opened, the user has the opportunity to change/edit data, which can rapidly deteriorate the quality of the data and cause significant duplication of effort.

To stay current on EDI 852 topics, follow us on twitter: @rainmakergrp and visit our blog.

Brighter Days Ahead for Retail in 2010

Retail economists now predict solid growth for 2010. The International Council of Shopping Centers projects annual sales will increase 3% to 3.5%, the biggest jump since 2006. "It's a story of the turning of the corner for the retail industry," said Michael Niemira, the group's chief economist. "We are probably now outside of the recession, and getting the first slow recovery."

"There is more stability and more visibility," Macy's Chief Executive Terry Lundgren said in an interview. "Last year, it was like we were all falling off a cliff, grabbing for vines to catch on to something, because we couldn't really see what was happening."

The less-is-more strategy is expected to continue well into this year as merchants cautiously move forward in light of continuing high unemployment and a recent spike in energy prices that is reducing consumers' disposable income.

Maintaining high fill rates and carefully watching inventory on hand will be critical for success in 2010. EDI 852 provides the raw materials necessary to analyze sales and inventory on hand, however vendors will need to be diligent in analyzing and working with replenishment managers to ensure out of stocks do not occur.

Take action now:
1. Identify your A and B stores by sales volume.
2. Perform an out of stock analysis for A and B stores for your top SKU’s.
3. Review your fill rate for these stores/SKU’s and take any corrective actions necessary.
4. Work with your replenishment manager to ensure order min/max will support expected sales and avoid out of stocks.

Additional resources:
· Detailed instructions for store analysis and out of stock analysis can be found here
· Tools for analyzing EDI 852 can be found here

#edi852

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 Planogram Compliance

Many of the vendors we work with have a specific planogram they agree to with a buyer on an annual or semi-annual basis. After reaching an agreement with the buyer on the planogram, 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 planograms, 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 planogram 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 planogram compliance. Here’s how. Create two planogram files; one file will list the SKU’s that are included in each planogram, the second file will list each store and the planogram 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 planograms 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 planogram compliance percentage by dividing the active SKUs / planned SKUs. Then sort the final table descending by planogram 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 planogram. You do not want to get to review time and find out that poor execution on the planogram caused lost sales.

Note: If you want to take planogram 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.

Home Depot EDI 852 Reporting

If you are a Home Depot vendor, you are eligible to receive product sales activity and inventory data via EDI 852 from Home Depot.

The Home Depot EDI 852 document will contain SKU/UPC level units sold, units on hand, and dollars sold for every store where your products are sold. The files are sent one time per week, summarizing the prior week’s activity.

What can you do with EDI 852 from Home Depot?
· Analyze SKU/store level sales
· Analyze SKU/store level on hand
· Analyze average unit selling price by SKU/store
· Analyze plan-o-gram compliance by verifying on hand and selling at planned stores
· Identify out of stock stores, and even forecast demand based on prior sales
· Group stores into A, B, C categories based on SKU level sales volume.

Home Depot merchandisers expect vendors to receive and use EDI 852 data to analyze and manage their SKU activity. If you are not already using the data, of if you are not using it as well as you could be, then you are missing sales opportunities. Don’t wait for your merchandiser to call you and ask a question you can’t address – start working with the data today.

Learn more about what your organization can do with Home Depot EDI 852 click here

Handling On-hand inventory

While EDI 852 is set up to report on-hand inventory quantities, many vendors come to realize the quality of the data reported can be suspect at best.  In this case there are strategies to handle the missing or poor quality data.  One such strategy is to use the shipping quantity and then subtract reported EDI 852 product sales to arrive at a 'calculated on-hand.'  This method has some limitations, but over time this can become an effective decision making tool.  Our Accelerated Analytics service provides the vendor with a simple and effective means to accomplish this data matching.  By incorporating purchase order and shipping data, we are able to synthesize the on-hand value.

Don't allow the on-hand to become a barrier to using the EDI 852 data. Reporting and tracking sales alone is valuable, and the on-hand issues can be dealt with by using simple business rules.