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/