Archive for February, 2010

Calculating Weeks of Supply

February 25, 2010

A metric fundamental to managing the retail supply chain is weeks of supply (WOS). Weeks of supply tells the inventory manager how long the current on hand will last based on current sales demand.  By keeping your eye on weeks of supply you can avoid inventory stock outs and lost sales.  The basic calculation for weeks of supply is pretty simple: on hand inventory / average weekly units sold.  However our work with vendors demonstrates calculating an accurate and useful weeks of supply can be anything but simple.  Let me explain.  An EDI 852 document will provide units sold and on hand.   Very few EDI 852 documents provide data for inventory on order, inventory in transit, or inventory in the warehouse.  More sophisticated systems like Wal-Mart’s Retail Link will provide the additional inventory data.  So the first issue an analyst working only with EDI 852 must overcome is to gain a complete picture of the inventory in the supply chain – all the inventory.  If you are working with a Home Depot 852 or a Lowe’s 852 you must also gather your purchase order and shipping data so that you have the ability to understand on order and in transit inventory.  You must also decide how to apply inventory in the supply chain.  That is will you sum on hand + on order + in transit  to use as the numerator in your calculation?  Or perhaps you would prefer to ignore the on order due to long shipping lead times and use on hand + in transit. 

The next consideration is how to calculate the average weekly units sold which is the denominator in the weeks of supply calculation.    This requires some careful consideration.   If the number of weeks used to calculate the average is not selected correctly you will arrive at a misleading result.  Consider for example the sales for two vendors as seen in this chart.  One vendor has products which are non seasonal and tend to have very steady and consistent sales.  The other vendor has products which are seasonal and sell much higher in the warm spring and sum mer months.  When choosing the number of weeks for calculating the weeks of supply you want to consider the rate at which your demand changes.  If your demand is fairly steady like the non seasonal vendor a larger number of weeks can be used.  If however your demand tends to change rapidly due to seasonality or based on some event like selling licensed apparel during football season then you should choose a smaller number of weeks.  Our experience shows that a seasonal vendor should consider a four week window of sales demand and a non seasonal vendor should choose 8 to 10 weeks.

The final point to make about calculating weeks of supply is to consult with your retail buyer on the period of demand they are using.  If you are using four weeks and they are using six weeks you will arrive at different order quantities.  By discussing the calculation you may find your method is more accurate or you may find the retailer has good reasons for their method.  If you still feel your method is more accurate then calculate weeks of supply using both methods and track the accuracy over time.  This will provide you with the factual data to either change your calculation method to align with the buyer’s, or demonstrate to them why your calculation is more accurate.


Getting buyers to agree to a test

February 24, 2010

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 modular’s at Wal-Mart in the following widths: 40’, 36’, 32’ 20’ 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’ modular’s 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 a 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 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, modular’s, 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.  more information on retail analysis.

Our team built a social networking community for retail industry professionals to discuss best practices like this.  Membership is complimentary so we recommend you join in.

Retail Replenishment – How tuned in are you?

February 23, 2010

I spent about 9 hours yesterday analyzing sales, order, and forecast data for Wal-Mart, Home Depot, and Lowe’s vendors and I am somewhat surprised by my observations.  It’s pretty clear there are some min/max rules in place as I can see patters to the order quantities based on the OH inventory and the order case pack quantities.  However what surprises me is that I also see a large number of what I would guess are “manual overrides.” That is UPC/stores which clearly need inventory and fall under the minimum OH of other stores but which do not have open order, and UPC/stores that are clearly overstocked (e.g. high WOS) and yet have an open order.  It makes sense there would be automated replenishment rules in place and then some lead-way for the buyer/replenishment manager to make judgment calls so that leads me to my question….

What do you know about your key retail customers replenishment rules?

  • Simple min/max ordering?
  • Based on OTB dollars?
  • WOS trigger?
  • At what level is demand calculated?  e.g. Category/Region, Category/State, Sub-category/State
  • Under what situations will the buyer do a manual override?

Positive Signs In Retail

February 23, 2010

Nordstrom and Lowe’s both reported big increase in profits and their CEO’s believe the economy is improving and their sales will continue to improve.

Nordstrom’s Profit More Than Doubles

Upscale department-store chain Nordstrom Inc. reported a 152 percent increase in profits during the quarter ended Jan. 30, underscoring how its strategy of expanding price points and carrying more exclusive merchandise is leading to more full-priced selling. The 108-year-old Seattle retailer missed Wall Street’s profit expectations by two cents a share on higher expenses and credit card delinquencies. The company’s shares fell 3.8% in after-hours trading, to $34.75.Nordstrom maintained a cautious outlook for 2010, noting that it expects same-store sales to increase between 2% and 4%,

Lowe’s Profits Up 27%

Lowe’s Cos. reported a 27% jump in fiscal fourth-quarter earnings amid signs that U.S. consumers are at least starting to consider home-renovation projects again after more than three years of hesitation. Chief Executive Robert A. Niblock said the home-improvement retailer’s first year-over-year quarterly earnings increase in seven quarters suggests "the worst of the economic cycle is likely behind us." Lowe’s expects to see a monthly same-store sales increase during the second quarter—the retailer’s peak selling season.

Making the most of Retail Link Data

February 18, 2010
Making the most of Retail Link Data
Wal-Mart vendors have access to a wealth of sales and inventory data through Wal-Mart’s Retail Link portal. The challenge for many vendors is that Retail Link can provide access to extremely detailed data which increases the quantity and difficulty of analyzing the data and making fast business decisions. Without the right tools the process can be very time consuming and difficult.

If you are a Wal-Mart vendor invest in a set of tools that will allow you to store and quickly analyze Retail Link data. The tool should allow storage of multiple years of store and item level detail and it should provide filtering and sorting to narrow the focus of your decision making. The focus your fist analysis efforts should be sales velocity and inventory in-stock. The tool should automatically filter the top 50 and bottom 50 stores by unit sales volume. This allows the vendor to quickly identify which stores are driving the most volume, as well as the stores that need immediate attention. Next it is critical to identify on a daily or weekly basis the stores that are out of stock. Fixing the inventory out of stock in these stores will directly increase your sales.

Become proactive in dealing with out of stock at a store level by reviewing the unit sales volume for all of your out of stock stores for the 8 periods prior to the out of stock. Determine an average period units sold and then identify for each store how many periods it takes to get new inventory to the store. So for example, if a store is averaging 6 units per week and it takes 14 days to get inventory to the store then the minimum inventory level that store should maintain at all times is 12 units. This provides two weeks of inventory on-hand and will allow for proactive replenishment to avoid stock-out exposure. As a Wal-Mart vendor you may be selling into over 3,500 Wal-Mart stores but if you complete this analysis for the top 70 or 80% of your stores based on units sold velocity, you will have gone a long way toward reducing out of stock situations and increasing sales.

Store Level Merchandising Analysis Using EDI 852

February 18, 2010

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

Step 2

Calculate the average item sales velocity (Avg WS) for each item for all stores for the last ten weeks of sales.  See a sample item sales velocity table  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:

  1. Most recent two weeks of sales.
  • Stores with sales in the last two weeks for any given item will fall into this category
  1. Most recent four weeks of sales.
  • Stores with no sales in the last four weeks for any given item will fall into this category
  1. Most recent six weeks of sales.
  • Stores with no sales in the last six weeks for any given item will fall into this category
  1. Most recent eight weeks of sales.
  • Stores with no sales in the last eight  weeks for any given item will fall into this category
  1. 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

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 (LU) by unit 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 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: training on SKU Sales Analysis, Out of Stock Analysis, and SKU Forecasting are available.

Wal-Mart Tightens Delivery Deadlines

February 15, 2010

Wal-Mart’s new “must arrive by date” ratchets up supply chain pressure on vendors, shippers, and carriers.

Like most shippers, Wal-Mart Stores is looking for a delivery guarantee from its suppliers. Unlike most others, the world’s largest retailer now is demanding one. While many retailers were scrambling last week for any space they could find out of Asia, Wal-Mart implemented its strongest delivery requirements yet on suppliers in the United States, imposing new deadlines for getting goods to distribution centers as well as tough penalties on those that miss the mark. As of last week, U.S. companies shipping goods to Wal-Mart distribution centers must begin to deliver within a four-day window leading up to a “must arrive by date,” or what the company calls its MABD. The requirement will initially apply to suppliers shipping prepaid and truckload freight to Wal-Mart DCs.

What action’s can you take if you are a Wal-Mart vendor? We have started conversations with vendors about how to integrate together various Retail Link data with the vendors purchase order and shipping data to create exception based reports to show them when they are in danger. The key to not getting hammered by fines is going to be careful management and with the high volume of orders and shipments many vendors have with Wal-Mart careful exception based reporting is key.

Good Retail Blogs

February 12, 2010

Here are a few blogs for the retail industry we enjoy following and thought you might find helpful.

Poor Weather Causes Out of Stocks

February 12, 2010

According to the WSJ the snowstorms that blanketed much of the country in the past week caught apparel retailers in short-sleeves.

Most clothing chains have very little winter clothing left on their racks, the result of tightly managed inventories and better-than-expected holiday sales.

But with nearly 70% of the country covered in snow, store shelves are mismatched to the weather: filled with new spring fashions that frigid customers aren’t in the mood to buy. The lack of appropriate dress could cost retailers some momentum after improved holiday and January sales periods, said analysts.

An employee at a Gap store in downtown Washington, D.C., said the store had been sold out of cold-weather hats, scarves and gloves for over a month.

Macy’s Inc. said it’s My Macy’s merchandise localization program, which lets buyers modify merchandise assortments based on local needs, helped it avoid shortages. A spokesman said Thursday that the department store chain planned for fresh flows of coats, gloves and hats in February and March in cold-weather markets. "Macy’s continues to have ample supplies of cold-weather merchandise," the spokesman said.

This is an interesting example of how using EDI 852 and analyzing POS data may have been able to help avoid out of stocks. Although the fashion supply chain tends to have long lead times if retailers and vendors had been more closely watching the weather and local demand signals they may have been able to either reallocate inventory between warehouses and stores or perhaps place additional orders.

How much do retail out of stocks cost?

February 10, 2010

A recent RIS article  titled “How Much Are Out-of-Stocks Costing You? Much More Than You Might Think” By Greg Buzek provides  more evidence that retail out of stocks are costing vendors huge lost sales.   Buzek quantifies the scope of the loss “A retailer that invested in completely fixing its out-of-stock problem would gain a solid competitive edge. The average retailer could increase same store sales 3.7% by converting all perceived out-of-stocks into transactions. Specialty soft goods could have the biggest potential win: solving out-of-stocks would boost their same-store sales 7.1%, while department stores would see a 4.2% jump.”

The good news is we have seen dramatic improvements in in-stock performance by active store and item level analysis.  The methodology is pretty straightforward:

  1. Determine the lead time from order to product arriving at a store.  Let’s say this averages 2 weeks.  This is your minimum on hand weeks supply to avoid a stock out.
  2. Next calculate the average weekly sales velocity for each item, and each store.  Yes you must know the average sales velocity for each peg or shelf position.
  3. Calculate the weeks supply on hand for each item and store by dividing the current on hand inventory by the average sales velocity.
  4. Filter the results to show only those items with less than the 2 weeks supply on hand.  These are the stores you need to make sure place an order immediately to avoid a stock out.

This type of analysis is not hard to do, but if you don’t have the proper tools it can be very time consuming.  But it’s well worth the effort if you can improve your in stock performance by even 2% you stand to gain significant sales.