Build vs. Buy: Outsourcing your POS data-reporting and analytics is faster, easier and less expensive than building an in-house solution

Retail POS data holds the key to understanding how your products are performing at a store level. But it can be a daunting task, not to mention a drain on resources, to gather, analyze and report on EDI 852 and POS data from all of your retailers. Our newest infographic compares building an in-house solution to outsourcing your POS data-reporting an analytics. While there are beneifts to each approach, outsourcing the solution is faster, easier and less expensive. Complete the form below to request our FREE infographic!

 

Warm Weather Expected to Boost Consumer Spending and Apparel Sales

Despite an expected snow storm in the mid-Atlantic States and New England, consumer spending and apparel sales should both rise as the weather improves, according to analytics firm Planalytics. In its weekly report, Planalytics said that warmer weather is resulting in “many consumers thinking and purchasing spring. The warming conditions during the Easter run-up period will help drive demand for seasonal apparel as well as live goods.” Looking ahead, the analytics firm said, “western locations can expect strong gains for both spring apparel and consumables.”

Last week, the warmer weather already had an impact on retail sales – but not in all regions. Chief economist of the Retail Economist LLC,  Michael Niemira said spring-like “weather continued to drive interest in spring clothing in the east over the past week, but cool and wet weather in the west curtained demand.” Easter sales are getting a slight boost due to the holiday falling early in the season this year.

Looking ahead, Planalytics said that businesses throughout North America can expect above normal temperatures in most markets over Easter weekend. “Sandals, short sleeve shirts, cold beverages and sun care will be in demand as temperatures rise above normal,” researchers said

Source: planalytics.com

The Leading Beauty Industry Solution for Retail POS Reporting

What is Collaborative Planning, Forecasting and Replenishment (CPFR)?

CPFR is a business methodology which integrates multiple parties in the planning and fulfillment of customer demand.  The idea behind CPFR is that by coordinating activities throughout the supply chain inventories can be moved more efficiently, in the correct quantities, to the correct inventory locations to meet customer demand.  CPFR establishes a common language, common processes and metrics to assist the trading partners to achieve these goals. 

The CPFR model

The customer, as the creator of sales demand for a product, is at the center of the CPFR model.  Surrounding the customer is the retailer and the supporting activities provided by the retailer: Category management, POS forecasting, Replenishment Management, Buying, Logistics & Distribution, Store Execution, Supplier Scorecard, and Vendor Management.  The outside ring of the CPFR model is comprised of the manufacturer and their activities.  The model is broadly organized into four quadrants comprised of Strategy & Planning, Demand & Supply Management, Execution, and Analysis.    The retailer, manufacturer, and supply chain partners interact through a series of eight business activities: Collaboration Arrangement, Joint Business Plan, Sales Forecasting, Oder Planning & Forecasting, Order Generation, Order Fulfillment, Exception Management, and Performance Assessments. 

Information Sharing in CPFR

Information sharing is a critical requirement to make a CPFR initiative successful.   Consumer demand must be quantified at a UPC/store level and quickly communicated from the retailer to the manufacturer.  The orders for new inventory must be placed quickly in the correct quantity and the orders must be fulfilled and shipped on time to ensure delivery to the shelf when the consumer is ready to make the purchase.  Any breakdowns in the communication process, or a lack of visibility into consumer demand in the cycle, has the potential to create an out of stock and lost sales will result.   

Successful Inventory Allocation in CPFR Requires Constant Monitoring and Adjustment

CPFR is not a one-time event, it is a business process which follows the entire life cycle of a product and which must be continuously monitored and adjusted.  All parties including the retailer, manufacturer and supply chain participants must be involved in the planning and communication cycle.  Participants should coordinate and agreed on the initial order quantity to establish the on shelf inventory position.   All parties should carefully monitor demand and adjust the regular on shelf replenishment rules based on local demand which govern the flow of inventory.  Proactive pre-planning for promotions, markdowns or price changes which may impact the regular consumer demand for a product are essential to avoid out of stocks.  

Is the EDI 852 document Sufficient to Enable CPFR?

The EDI 852 document (also referred to as the Product Activity Transaction Set) is the most common method for retailers to communicate retail point of sale data and inventory to manufacturers.   The most common elements of an EDI 852 document include units sold, dollars sold, and inventory on hand by UPC and store.   While the EDI 852 document provides a wealth of useful information to inform the participants of a CPFR initiative unfortunately the implementation of the EDI 852 is often incomplete.  The EDI 852 document outlines standard elements and technical details of the file structure but the implementation by each retailer varies.  One retailer may provide inventory on hand and units on order, while another may provide only on hand, or in some cases no on hand at all.  The problem is not the EDI 852 document or the standard, the problem is the implementation is not consistent.  Another problem with the EDI 852 document is the frequency of transmission.  In nearly all cases the EDI 852 document is transmitted weekly and summarizes sales for the period.  This creates a significant delay in the manufacturer’s ability to sense and react to changes in consumer demand.   If an out of stock is encountered early in the reporting period the manufacturer will not be alerted to that for several business days.  Another very significant gap in the implementation of the EDI 852 document is units on order data.  Unfortunately, a majority of retailers do not provide this data in their EDI 852 document.  So while a manufacturer may identify a spike in sales demand they do not have order information to know if the problem has already been identified by the retailer and an action taken.  The manufacturer can separately consult their purchase order data from the retailer but with today’s modern supply chain most retailers place large orders which are destined for a distribution center which obscures the store level order information.  The retailer may have placed an order but are those units going to the store which most needs them?  This is a critical gap in the information flow which is required for a successful CPFR implementation.    

Replenishment System Barriers to CPFR

Most retailers have invested heavily into information systems to forecast demand, monitor sales, and place automatic orders based on min/max inventory rules.  These systems can be very sophisticated and accurate at an aggregated level, but they are not typically monitoring individual store and product inventory positions.   A replenishment manager at the retailer is responsible for monitoring and adjusting the replenishment system to ensure inventory levels are maintained.  However in reality an open to buy budget has a large impact on the decisions the information system or the replenishment manager can implement.  Far too often inventory has built up in one area while other stores are starved for inventory but the overall financial position of the retailer is constrained and additional purchase orders cannot be issued.  Manufacturers may identify inventory out of stock situations and communicate the problem to the replenishment manager but the replenishment manager may be powerless to do anything to react.  For a CPFR initiative to be successful the retailer and manufacturer must defined the communication process and action steps before the inventory shortages begin to occur.  The action plan must identify who has the authority to override the replenishment system and place an order even if that means temporarily exceeding the total desired inventory position.  The allocation and redistribution of inventory must also be discussed prior to starting the CPFR initiative.  While it may be counter intuitive to create inventory positions which are significantly different by retail store location the inventory must follow, and react to, consumer demand. 

CPFR – the Bottom Line

There are many case studies which point to the benefits of CPFR.  Some of these case studies demonstrate inventory reductions of 10% to 40% with corresponding improvements in sales between 5% and 20%.   It is hard to dispute that when all the parties involved in the supply chain plan, coordinate, and act that business benefits will not be realized.  The difficulty it seems comes down to efficient and consistent communication, and pre-planned agreements on what actions will be taken based on consumer behavior.   Our experience has demonstrated even when all participants are aware of a problem it does not necessarily translate into productive actions to solve the problem within a meaningful timeframe to make a significant impact.  If an out of stock occurs on a Tuesday and the manufacturer identifies it the following Monday when the EDI 852 is transmitted, and the retailer places an order on Tuesday, the shelf has been empty for a week.  That is the challenge of CPFR – communicating and acting rapidly.  This does not diminish the value of CPFR by any means; however the real world implementation is anything but easy. 

Getting Started with CPFR

There are some practical steps manufacturers can take to begin on the path to CPFR:

1. Work with your retailer to identify the gaps in the retail point of sale activity data they are providing and how they can be filled.  These gaps usually revolve around inventory on hand and on order, and the frequency of the data transmission.
2. Work with your retailer to understand the steps involved to prevent, or at least fix, an out of stock.   Who has the authority to place an order?  Who has the authority to override the replenishment system?  Who has the authority to reallocate inventory from poorly producing locations to high producing locations?  What is the turn time from order to on shelf by region?  What are the min/max rules and how were they established?
3. Create a system for proactive monitoring of sales and weeks of supply inventory by store and UPC.   When will the analysis be conducted each day or week?  Who owns this analysis and what actions they will take based on severity of the shortage?  If the retailer will not accept and act on the order advice is there an escalation process and who’s involved?
4. Automate the analysis in step #3 above.   Analyzing sales and inventory at a UPC/store location presents a significant data challenge due to the sheer volume of data for most manufactures.  For example, if you have 45 UPC’s selling at 2500 retail stores there will be 112,500 rows of data to review, analyze and report.   Most manufacturers start with a spreadsheet as their tool for this process but quickly find it is a time consuming and difficult task.  As a result the analysis is not completed quickly and accurately and opportunities can be lost.   A more sophisticated solution is required which is exception based.  Predefined exception reports which alert the analyst to only those items/stores which are below desired levels can be developed.   This saves time and allows the analyst to work on the problem rather than on a spreadsheet. 

Demand Driven Planning in 2013

The availability of retail point of sale data over the past several years has created the opportunity for vendors to gain a detailed understanding of consumer demand at the retail point of sale.  Actual consumer demand at the retail point of sale presents a more accurate and timely picture of how your SKU’s are selling than retailer forecast advice or even retail purchase orders.  So why don’t all vendors collect EDI 852 or retail POS data from their customers and use it for creating forecasts and managing sales?  There seem to be several myths holding vendors back….

Myth #1: Collecting and analyzing EDI 852 / retail POS data is expensive and complex.  In a few limited cases like Home Depot and Menards it is true that the simple process of collecting the data has some expense.  Home Depot EDI 852 for example must be collected using a VAN so there are data transmission charges.  Menards charges a vendor to purchase a RSA SecurID.   But most retailers make EDI 852 or retail POS data available for free and even when there is a fee the benefits exceed the expenses.   Extracting the data, matching to item catalog details and store details does require some expertise but there are many SaaS applications now like Accelerated Analytics which will outsource the technical requirements for an affordable monthly fee.  By monitoring the consumer demand and inventory on hand at a SKU / store level of detail a vendor can proactively work with the retail replenishment manager to avoid out of stocks.  Every sale you get that would have been lost due to an empty shelf is returning value and paying for the expense of collecting and using the EDI 852 data.  How many lost sales do you need to recover a monthly data management fee that is typically less than $2,000?   At a chain like Home Depot with roughly 1900 stores in the USA the answer is not very many.

Myth #2:  My buyer won’t accept replenishment recommendations.  We hear this all the time – “I realize I could probably increase my in stock rate using EDI 852 / POS data but my retail customer uses automated replenishment or has a fixed open to buy plan so my recommendations fall on deaf ears”.  Several things are at work with this myth.  First, most vendors are operating on an assumption that if they talked to their buyer, they would discover is inaccurate.  I’ve talked to buyers at many retailers and I get a consistent answer – if the vendor can quantify the problem and provide an accurate order recommendation I will take it into consideration.  Second, the vendor has to demonstrate a competency in using the data for basic tasks like sales monitoring before they try to recommend orders.  I’ve seen countless examples of a vendor providing sales reporting and value to a buyer who then gains confidence the vendor can get the demand forecast right.  Finally, you have to start off slow.  Start with your highest turn products at your A volume stores and calculate the lost dollars sold for an 8 week period.  Then go to your buyer with a summary of your findings and actions to improve in stock and quantify the sales opportunity for both of you.  Make conservative recommendations to increase the WOS by one week so you gain back some sales but avoid loading the store with inventory and dropping your GMROI.  They have the same goal as you – to sell more product!

Every vendor that sells a product through a retail store should invest into analyzing retail point of sale data and using it for creating detailed action plans.  The data acquisition and reporting costs are very low when you consider them as a percentage of your retail sales and the upside benefits of increased sales, better assortment planning, and optimal inventory on hand are huge by comparison.  Let’s make 2013 the year that all vendors make the investment.  

The Role of Analytics in Retail

The role of analytics in retail has evolved substantially over the past few years and it’s having a significant positive impact.  The days of hearing a vendor say “Oh, we get an EDI 852 but we don’t really do anything with it” are starting to fade into the rear view mirror.  This blog post will discuss some of the mega trends we see occurring in business intelligence in retail and their impact on demand planning and forecasting.   

Retailers are much more open to sharing point of sale (POS) data with vendors now than they were a few years ago.  Wal-Mart paved the road with Retail Link, which gives vendors access to a wealth of data, and most other retailers use EDI 852 or a web site of some kind to make data available.  [As a side note there are some major retailers like ACE Hardware and Publix that still refuse to share POS data, which is pretty amazing]  Mega-trend:  retailers will begin to expand the metrics they share and they will slowly move toward providing daily data.  We have recently seen retailers begin to share on hand data and sales dollars which they had not shared previously.  Providing those additional data elements enables category management and demand planners to greatly expand their analytics.  We are also seeing retailers begin to make daily data available, which is probably the most exciting development in business intelligence for retail.  Demand planning and forecasting for retail is dramatically improved by daily data vs. weekly data and daily data creates the opportunity for things like weather analysis.   

Key performance indicators for retail are pretty easy to define and calculate.   Sell-through, weeks of supply, year over year comp or % change, gross margin, gross margin return on investment, etc.    We find however that many demand planners do not have the time or tools to monitor KPI’s at the store / SKU level of detail which diminishes the value that should be realized.   Mega-trend: vendors are using cloud based software as a service (SaaS) to get access to sophisticated retail reporting without having to invest into business intelligence tools and a bunch of expensive development.   Retail point of sale reporting and analytics can basically be purchased ‘out of the box’ and then customized to fit your precise business needs in a very small amount of time.  When a large customer like The Home Depot is asking you to get into the POS data, you don’t have the luxury of waiting on your IT team.   Outsourcing your retail POS reporting and analytics provides a very fast path to keeping your customer happy. 

Mega-trend: Vendors use of EDI 852 and POS analytics will become more and more sophisticated.    Not that long ago, when a vendor invested in POS reporting, they were getting ahead of their peers by using technology to improve their business.  They would build relatively simple retail dashboards with key performance indicators for retail stores, like units and dollars sold.  Today, however, we are seeing increasingly complex analysis for demand planning and forecasting, complex retail replenishment models, category management and even weather and demographic analysis.  This is a natural evolution of business intelligence in retail and it is driven by the availability of SaaS tools and very real results that vendors are experiencing.

Dollar General Profits Up 33%

Dollar General reported quarterly profits were up 33% to a record $299 million for the quarter.  Total sales and same store sales were also up significantly.  

The value Dollar General offers to cost aware consumers is clearly paying off for them.  Our teams provide reporting and analytics on Dollar General EDI 852 and we have noticed strong sales as well.   When you are a vendor to a retailer like Dollar General with 9,800+ stores, using EDI 852 to closely monitor sales and inventory is critical.  Imagine if you have just 5 SKU's at every Dollar General store - you would have about 50,000 SKU/store combinations to manage and keep an eye on.   Dollar General offers both a daily and weekly EDI 852 feed so you can get visibility into store sales performance and help partner to grow the business.

Improving EDI 852 On Hand Data for Useful Inventory Reporting

Nearly all EDI 852 files report activity of SKUs or UPC's.  While this probably seems like an obvious statement, it does create some work to create a data set which is suitable for good inventory on hand reporting.  EDI 852 activity based reporting means that a SKU which is on hand, but which did not have a sale or return in a given week, will not be reported in the EDI 852 file because there is no 'activity' to report.  Without some work on the data this creates null on hand records which impact the ability to do good out of stock and inventory exception reporting.  For example, a SKU might have 100 units on hand but not sell for a two week period and so the current week on hand will be null.  To improve upon the data one must carry forward the last known on hand value that was reported in the EDI 852 so that each SKU has an on hand. 

The downside to this approach is that your database will grow in volume over time, and so it is also necessary to put one or more rules in place to decide when on hand for an item will no longer be carried forward.  This can be accomplished using the SKU status - for example do not carry forward the on hand for an item when it changes from 'active' to 'inactive'; or you can create a rule to discontinue the on hand carry forward automatically after some number of weeks with no activity reported in the EDI 852.

Our team has implemented these types of rules to improve upon the EDI 852 for Home Depot, Macy's and a number of other retailers and it has greatly improved our ability to produce accurate and useful inventory out of stock and exceptions reports.

Weather Analytics and Retail Sales

After crunching the numbers, the National Climatic Data Center (NCDC) has found that January 2012 was the fourth warmest January on record across the contiguous United States. This is also the mildest January since 2006, which was the warmest in records dating back to 1895.

States with a top 10 warmest January (9 total) - AZ, KS, MO, MN, ND, NE, OK, SD, WY

The Weather Channel, LLC

Weather can have a significant impact on retail sales.  Consumer’s behavior changes, distribution can be impacted, regular seasonal selling can shift, etc.   Our team has recently completed analytical projects with customers using precipitation, temperature, humidity, and many more weather data points to understand retail sales patterns and then use that understanding to create forecast models.  This is the beauty of store / UPC grain retail sales data.  Combining retail point of sale (EDI 852) demand data with weather data, you can identify fascinating and very useful insights.  Some things to keep in mind….

Useful weather analytics almost always requires day grain retail data.  Week grain data is useful for some weather analytics but there are significant limitations.  EDI 852 is often weekly grain, but sometimes day grain is available.  Portals like Retail Link can provide daily grain (or lower if you want) retail sales reports so target your project to your retail customers that provide day grain retail sales data.

Studying the data carefully to identify statistical significance is critical.  Antidotal or observational research is helpful to inform your statistics but be careful about over simplifying what you see (e.g. it rained and sales are up) until you have run the numbers.

Do apply your industry and product knowledge.  If you sell a product that conventional wisdom says is impacted by precipitation or temperature, then use that as a starting point for building the model.  If the output of the model challenges the conventional wisdom, then dig into the model and look for holes until you are satisfied with the accuracy of the results.

A quality weather analytics project is not an inexpensive project, so be prepared to make an investment.  But on the flipside, we have seen these investments provide huge returns for highly weather dependent product categories.  

Getting Your Buyer to Agree to A Test

Getting your buyer to agree to push order recommendations, modular changes, SKU assortment changes, etc can be a challenge.  Here is some practical experience on how to make it happen.

Running a test with your buyer can be a very effective way to ‘sell them’ on new ideas.  Many times our clients want to use our forecasting tools to push recommended orders to replenishment managers, but the replenishment manager is not receptive to adding extra work to their day and they certainly don’t want to risk overloading stores with too much inventory.   Many times our clients want to change the modular assigned to a store or SKU assortment within an existing modular, but again, the buyer is reluctant to make a change that could have negative results.  Proposing a test is a good way to limit their risk and overcome their concerns.  If the test is properly designed and the control group is selected to provide a proper comparison, your idea should receive a fair vetting.

I spent considerable time today helping a client build a list of stores for a modular test at Wal-Mart.  My client has modulars at Wal-Mart in the following widths: 40’, 36’, 32’, 20’ and 12’.  The SKU assortment grows based on the width of the modular, so a 20’ modular has all the SKUs of a 12’ modular plus some extras.  They have gained the agreement of their buyer to test 25 stores with a larger modular than the store would otherwise qualify for to see if the demand for their products is deserving of more square feet.   The test stores were identified by the buyer and are in close proximity to my client’s office, so they can easily visit the stores.  The test stores have all been promoted to 36’ modulars, which is larger than they had in 2009 and larger than they would otherwise be traited for based on their profile.  The task today was to identify 25 control stores so we can test the sales lift over an 18 week period.  To identify the control stores, the following information was pulled out of Retail Link: 2009 total units sold by store for all stores in my client’s home state.  The first thing we did was calculate the minimum, maximum, average, and median 2009 unit sales for the test stores.  We then eliminated all potential control stores which were not within the min/max, and then further narrowed our list by looking for stores that were +/- 20% of the median 2009 test store group unit sales.  All stores in the test group are Supercenters, so we then eliminated all stores under consideration for the control group that were not Supercenters.   The next consideration was the demographics of the test store group compared to the potential control stores.  We pulled a list of demographics for the test store group, using the store zip code for each of the 25 test stores, and looked at the following traits:  population density, median income, dominate race, and median age.   We created a profile using the averages for these traits.  We then cross referenced the possible control stores demographics along the same traits to identify the closest matches. 

The key is that by using UPC/store level EDI 852 or Retail Link data, the vendor is often in a better position to analyze the demand of individual stores and make recommendations to a buyer on things like orders, modulars and SKU assortment.  Store level planning is the holy grail of maximizing sales, but I’ve not met a buyer yet that has the time or resources to do that.   So the responsibility falls on the vendor to make it happen and proposing a test is often the way to get the ball rolling.

Vendor Questions on EDI 852

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 customers 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.

Managing Inventory: The Highs and Lows

When vendors think about managing inventory, quite often they immediately think of those stores with insufficient inventory and how to resolve that.  Of course, this is a natural and valuable consideration, and a correspondingly considerable effort is made to eliminate inventory outages and prevent lost sales.   
 
But what of the flip side of that coin?  A June 26th article in the Wall Street Journal, titled “Retailers Cut Back on Variety, Once the Spice of Marketing,” cites Walgreen Co., Wal-Mart Stores, and Kroger Co. as examples of how retailers are concerned about too much inventory in addition to their concern about too little.  The article goes on, “these and a few of the other largest retailers are expected to slice the assortment of products in their stores by at least 15%, industry executives and analysts say.”
 
The difficulty for vendors, then, is how to manage both the highs and lows of their inventory throughout their supply chain.  Indeed, inventory ought to be managed at an item by store level, which in and of itself is a vast amount of data.  This is further complicated by the use of third party distributors and the various distribution facilities and warehouse networks used by each different retailer.  Simply getting the raw shipping and inventory information from each retailer and/or distributor is often a substantial task, and making use of the disparate types and formats of data is more often than not the task of a whole team of analysts, who in turn rarely do any analyzing, spending the majority of their time collating and standardizing formatting.  As a result, by the time the inventory situation is discerned, it’s often stale data and virtually useless.
  
This need for accurate, rapid, actionable inventory information has caused vendors to turn to third party partners like Accelerated Analytics to quickly identify those items that are both under-stocked and overstocked.  The Accelerated Analytics® Inventory On Hand Exceptions report continues to be one of our most popular reports because it allows you, the manufacturer, to define any inventory exception you might be interested in and get a report for every item at every store that falls into that category.  Accelerated Analytics integrated use of data received from a vendor, its retail partners, and its distributors, allows our clients to see what the current inventory situation is as recently as the current Week to Date.  But more than the one-dimensional EDI files, Accelerated Analytics® provides a multi-layered inventory look incorporating your own warehoused, shipping history, your distributors’ warehouses and shipping history, and your retail partners’ warehouses and receipts, so you don’t push a new order to a store that is low today. but will be receiving a shipment tomorrow of several new cases for the same item.  Using this type of exception report, in addition to Accelerated Analytics unique Sales Velocity Analysis reports, your analysts can actually analyze your information and pinpoint the items and stores that need your immediate attention in time to do something about it.  This, in turn, will increase your sell-thru, which just might keep your item(s) on the shelf at Wal-Mart, Walgreens, or Kroger!

Understanding EDI 852 Data

EDI 852 files are typically provided on a weekly basis. These files are usually referred to as "product activity data." The challenge is that each retailer uses a slightly different format, data descriptions, and code identifiers. EDI 852 files routinely contain the following information.

For each item
- Item description
- Item UPC

Data can be summarized by:
- Store
- Distribution center

Key product activity measures
- Quantity sold ($)
- Quantity sold (units)
- Quantity on hand ($)
- Quantity on hand (units)
- Quantity on order ($)
- Quantity on order (units)
- Quantity received ($)
- Quantity received (units)

Key forecast measures
- Quantity ($)
- Quantity (units)

Key metrics which can be calculated to evaluate performance against goal
- Days supply
- Weeks supply
- Sell-thru percentage (%)
- Sales to forecast variance
- Month to date (MTD)sales
- Quarter to date (QTD) sales
- Year to date (YTD) sales
- This month vs. last month
- This quarter vs. last quarter
- This year vs. last year

Work efficiently with EDI 852 data requires a good analysis tool like Accelerated Analytics®. Otherwise, the merchandise planner is left to sort through line after line of data to find problems and opportunities. By using Accelerated Analytics®, the routine tasks of formatting and consolidating data are eliminated, and exception logic can be used to save time.

The Accelerated Analytics® team understands how to work with EDI 852 data. We eliminate all the headaches and give you the preformatted reports you need to run your business.

 

Lowe's Integrating Planning and Execution (IPE)

Lowe’s announced poor 2Q11 financial results with anemic growth and flat same store sales recently.   To improve performance, CEO Robert Niblock and EVP merchandising Bob Gfeller are implementing Integrating Planning and Execution (IPE), which places an emphasis on putting the right product in the right store at the right quantity.  This new focus got our attention since we provide EDI 852 data analysis and reporting to Lowe’s vendors.  Putting the right product in the right store at the right quantity is exactly what vendors use EDI 852 to accomplish.  Localized merchandising is the right strategy for Lowe’s, but they may run into some challenges executing the strategy with vendor’s assistance.  

Many vendors we work with have Lowe’s as a customer, as well as other ‘big box’ retailers.  Across the board, these vendors get EDI 852 from their big box retail customers and we help them analyze the data at a SKU/store level.  But many of these vendors choose not to use Lowe’s EDI 852.  Instead,  they opt to pull reports from LowesLink®.  LowesLink® is a fine system for pulling reports.  The problem is the reports offer a snapshot of performance, not an analysis system.  If the vendor does not have a database to store weekly SKU/store data, it is nearly impossible for them to analyze weekly sales effectively and efficiently enough to participate in localized merchandising strategies.  Lowe’s does have Vendor DART which offers analysis tools, but the most powerful tools are reserved for large vendors.  

Weekly analysis of EDI 852 at a SKU/store level is the foundation of a successful program like Lowe’s IPE.  Vendors know their products best and there are simply too many products for Lowe’s staff to conduct weekly SKU/store level analysis.  For Lowe's Integrating Planning and Execution (IPE) to be successful long term, they must get vendors actively using the EDI 852.

LowesLink® is a registered trademark of LF, LLC.

Using Retail data for Forecasting Demand and Merchandising Planning

Many vendors have started to using EDI 852 data or retailer portal data for sales and retail merchandising, but so far, only a few are using EDI 852 data for forecasting of demand.  But the reality is, vendor inventory at stores is often too low to meet demand and the rates of out of stocks have been increasing. It’s not a big surprise that retailers are maintaining less inventory in stores in this retail environment; the cost of excess inventory is simply too high and open to buy dollars are at an all time low. But with proper forecasting of demand, a vendor can help the retailer to better manage inventory and avoid out of stocks. The great benefit of EDI 852 for merchandising planning is that it is store/SKU level data. Since a typical retailer is forecasting demand at a category and market level, the variability in the rate of sales among stores in a market can be large.

A more accurate model for forecasting of demand is to start at the store/SKU level, calculating an average rate of sale for the store/SKU and then based on the inventory on hand at that store, a weeks of supply. When the weeks of supply for a store/SKU has been calculated, the vendor can compare against the lead time to replenish the store and work to put a true demand driven supply chain in place. This model, while more intensive for the vendor to manage, usually creates a far different picture of inventory needs than simply market level min/max replenishment.

Home Depot EDI 852

Home Depot vendors gain a critical advantage using Accelerated Analytics for point of sale data analysis. Home Depot vendors have the opportunity to use EDI 852 data to analyze their business and be very proactive in working with their merchants. A standard Home Depot EDI 852 document contains units sold, units on hand and dollars sold for each SKU and store. By storing this data each week and cross referencing the Home Depot store list, a vendor has the opportunity to understand store and SKU level selling trends and inventory consumption. The Home Depot EDI 852 data provides all the necessary ingredients to calculate key metrics like: inventory weeks of supply on hand, average rate of sale by store and SKU, and if you add your cost information you can arrive at GMROI as well. It has been our experience that Home Depot merchants expect a high degree of data analysis from their vendors, and it has also been our experience that they are very supportive of vendors who use the data to make recommendations on how to improve the business. The key to success is selecting a service provider like Accelerated Analytics that can help you store the data each week, calculate key metrics, and make the analysis and reports available to your sales teams in a timely fashion. Accelerated Analytics also provides advanced analysis like GMROI by plan-o-gram, which is critically important in working with your merchant. Armed with this data, we have seen vendors dramatically increase sales and optimize inventory levels.

Frequently Asked Questions about Accelerated Analytics

What is EDI 852?   EDI 852 is a standard data format used to transmit product activity data. Files are typically sent daily or weekly and will include sales activity by product, and for some retailers, inventory on-hand.  Activity is typically summarized at a distribution center level, unless store level data is deliberately selected. Some EDI 852 forms will also include pricing information, inventory on-hand but unavailable for sale, order point, order quantity, and order status. EDI 852 is provided as a text data file using special character sets to describe the coded data to the decoding software. 

My organization is a manufacturer and our retail customers are offering to send us point of sale data.  Can we use Accelerated Analytics® to analyze POS data? 

Absolutely! Accelerated Analytics® was designed to provide business users with a simple and effective means to analyze POS data from both a buyer and manufacturer/supplier perspective. Our engineers can work with your team as well as the retailer to load the data into Accelerated Analytics® and format your custom reports.  

 

Can we use Accelerated Analytics® to analyze EDI 852 data?

Yes.  As a part of our service we accept EDI 852 data and provide the translation into a useable format for reporting and analysis.    

 

What's the difference between point of sale data and EDI 852?

First, the format of the data is very different.  EDI 852 is provided as a text data file using special character sets to describe the coded data to the decoding software.  If you open an un-translated EDI 852 file, you will have a very hard time understanding what you are looking at.  POS data, on the other hand, is typically provided in a text file with descriptive column headers, which can be easily opened and used in Excel.  Second, EDI 852 contains a basic set of product activity data, while a POS file is usually much more rich.  POS often will include cost and price information, and more detail inventory.

 

What retailers are you working with today?

A list of our currently covered retailers can be found here.

 

What industries do your vendor customers work in? 

Our customers include apparel, footwear, consumer products, specialty hardlines, health and beauty, pharmaceuticals, and grocery.

 

Do we have to setup our own reports?

Not unless you want to.  Our service includes many pre-configure template reports that we customize during the on-boarding process to meet our customers precise needs.  Templates are included for sell-thru, stock-out exposure, inventory on-hand, period over period sale and inventory comparisons, top selling items, and much more.  All reports can be viewed by product, product category, store, geography, time, etc.  The reports are saved and available to end users with one click of the mouse. 

 

What is collaborative forecasting, planning and replenishment (CPFR)?

(CPFR) Collaborative Planning, Forecasting, and Replenishment is a business practices that combines the intelligence of multiple trading partners in the demand planning and fulfillment of customer demand. CPFR was pioneered by Wal-Mart as a next step to efficient consumer response (ECR) and vendor managed inventory (VMI) and is now promoted by the Voluntary Interindustry Commerce Standards Association (VICS). CPR is a proven retail supply chain improvement process.  

What is the bullwhip effect and why is it important?  

The bullwhip effect among supply chain partners is a situation in which the supplier has a clearer view of demand than the retailer, but a less accurate forecast. Traditional supply chains are extremely prone to this bullwhip effect; typical order fluctuations of +/-5% on the customer end can easily balloon to +/-40% on the manufacturer end, thus showing an increasing demand variation of 2:1 at each level of the supply chain. Accurate forecasting can help to eliminate the bullwhip effect and increase overall profitability by 5%. The most effective way of smoothing out bullwhip effect oscillations is for suppliers to understand what drives demand and supply patterns. Understanding demand and supply patterns is best accomplished through a detailed look at POS data.   

 

What makes Accelerated Analytics® unique?   

Accelerated Analytics® connects buyers and suppliers in a collaborative environment, where point-of-sale data is used to improve forecast accuracy, demand planning, and decrease stock-outs. The Accelerated Analytics® environment is a hosted service including pre-configured reports, world-class analysis tools, and color coded exception dashboards. These tools quickly turn data into actionable information and promote data based decision making.  With Accelerated Analytics®, there is no software to buy or install and Rainmaker Group does all the data processing. Read our full list of benefits. 

 

Who are some companies that have implemented collaborative planning forecasting and replenishment (CPFR)?   

Over 150 companies have implemented collaborative planning forecasting and replenishment (CPFR) including: Sara Lee, Wal-Mart, Schering-Plough, Walgreens, Kmart, Target, Eckerd, Safeway, Ace Hardware, Manco, Canadian Tire, Johnson & Johnson, Carrefour, Henkel, Kimberly-Clark, Marks & Spencer, Metro, Proctor & Gamble, Sainsbury's, Nestle, Best Buy, Scan Disk, and Federated. In all likelihood, there are many more unpublished implementations as well.  

 

How is my retail supply chain improved by demand planning using EDI, DDSN, or CPFR?   

Studies of retailers by Harvard Business, Grocery Manufacturers Association, National Retail Federation, and AMR Research show results of 15% less inventory, 17% better perfect order performance, and 35% shorter cash-to-cash cycles. The close collaboration between buyers and suppliers makes these improvements possible. Accelerated Analytics® provides the technology in a hosted service so there is no hardware or software to purchase.  

 

If our suppliers are not asking for POS data, why should I consider Accelerated Analytics®?  

It's not a surprise your suppliers are not asking for data. Most suppliers are intimidated by the prospect of asking for POS data and they do not have the tools to manage and analyze that volume of data. Successful business transformation does not begin as a reaction, but rather because business leaders have the vision to proactively invest in tools which drive their business forward faster than their competition. Research shows that when retailers proactively engage suppliers to collaborate on demand forecasting, 57% report improved relationships. Demand planning in the retail supply chain and collaboration between buyers and sellers, leads to more accurate forecasts and higher sales.  

  

Why can't we just use our electronic data interchange (EDI) system to send suppliers demand planning data?   

Many retailers have tried using EDI 852 to take advantage of collaboration and demand planning opportunities with suppliers. This is a natural first step; the infrastructure for EDI 852 is already in place, serving as the communication medium between retailers and suppliers. But most retailers are finding that sending out an EDI 852 document with summarized POS and inventory replenishment does not provide much benefit. Why? EDI does not add any new information; EDI is summarized at such a high level, it provides about the same detail as the purchase orders already in the system. The best a supplier can do with EDI 852 is load it into excel, because they do not have an analysis tool. In addition, parsing out a separate EDI 852 file every week for each supplier is time intensive. Most importantly, the supplier rarely has the tools necessary to accept the data and conduct effective analysis.

Heading in the right direction

Retail sales consultant Retail Metrics Inc. predicts that the 30 national retailers it tracks will this week post a 3.4% gain in sales for December at stores open for at least a year. That is atop a 3% gain for December 2009.

Overall, the National Retail Federation trade group forecasts that retail sales in November and December increased by 3.3% this year to $451 billion.

The positive gains made this holiday season appear to be sustainable. In December 2010 and already in the New Year our customers are engaging us to help with detailed custom data analysis for line review preparation. This is always a good sign of an improving economy. It means business leaders are ready to compete. They are looking for opportunities and making sure any weaknesses are explained, along with a plan for improvement. Underlying all of this is very careful point of sale data analysis, so that plans and recommendations are fact based. We have been carefully analyzing products across retailers, identifying trends in product sales, geographic trends, pricing variances, etc.  It's amazing what EDI 852 can tell you if you have a good database with several years of history. Dig into your EDI 852 and get ahead of your competition.

Von Maur EDI 852 Reporting

If you are a vendor supplying to Von Maur, 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 Von Maur 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:

• We do the EDI 852 translation
• Eliminate manual data entry and manipulation
• Consolidate all Von Maur 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
• Stove level stock-out exposure
• Stove level overstock
• Fast/slow selling items
• Sell-thru
• Inventory turns
• Days supply on hand 

Ulta EDI 852 Reporting

If you are a vendor supplying to Ulta, 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 Ulta 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 Ulta 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.