We are 23 or so days into the Holiday season. Do you know where your sales are? Do you know them by item, category or client? This isn’t one of those papers on this year’s sales compared to ’08 or ’07. I want to go over some data points you or your boss may be a little concerned with. How to get the data, extract information from it and make the data useful to your efforts.
I am going to talk about building or using a Sell Through report we can create using an 852 (EDI 852 Activity Report). As we go on, we will look at some retailers that offer it – some require you to receive it (As of 2009), what data is included and what data points inside the 852 are necessary for each of the retail formulas we will cover (Primarily sell thru).
Sell-through refers to a percentage of units shipped which are actually sold (In a given period, say: one month, season or year). When a season is over, or a carrying contract is over, the goods are either marked down or sent back to the manufacturer. Sell-through is always expressed as a percentage. Let’s take a look at the formula for Sell Thru: Sell through % = units sold / (units sold + on hand inventory).
For example: During the month of December 100 shirts sell. You received 300 shirts in receipts. You end August with 900 units – shirts of stock (End of Month Stock). What were your BOM (Beginning On-Hand) units of shirts and what was your Sell-through?
• Beginning of Month stock (BOM) = EOM 900 units – Receipts 300 units + Sales 100 units = 700 units
• Sell-through = Sales 100 units / Beginning Inventory (BOM) 700 = 14.3% Sell-through in December.
If you want to look at Sell through for the month then your formula looks like: sell through% this month = units sold this month/ (Units sold this month + on hand inventory).
Understanding sell through is critical to being able to visualize and ultimately manage the velocity at which your products move from becoming ordered to being sold (To consumers or others). As your sell through increases you should/could see the following happen:
• Returns will reduce proportionally
• You will be more of a successful line/brand
• More order potential
Wow. Quite a start, Jim. Let me take a swig off my now comfortably hot venti, caramel mochaccino while I contemplate this a wee bit more. I think someone told me we were receiving 852’s and not doing anything with them as well as a few other documents.
Hold on a moment. A mochachino is a pricey, fancy drink – I only drink regular coffee, but let’s go with the $5.00 drink you just mentioned for a moment. You paid $5.00 for a beverage which will last you 45 minutes but you will go the whole week with no understanding or *visibility* of your company’s seasonal sales, delivery or other performance stats? Let’s keep that $5.00 price tag at the back of your head.
To dive right into this, let’s make some assumptions:
1. Your company sells about 7-8 items into retail
2. Your company has 3.5 retail clients (You may have)
3. Your company sends and receives EDI traffic for at least two of your retail clients
Now let’s look at some retailers you may work with. Find a couple you are familiar with below (Each of these works with the 852 to some extent. Let us know about some others (Leave a comment!):
AAFES, Academy, Associated Wholesalers, Belk, Bloomingdales, Bon Ton, Burlington Coat Factory, Costco, CVS, Dillards, Dollar General, Eckerd, eToys Direct, Famous Barr, Federated, Food Lion, Fred Meyer, Goody’s, HEB, Holt Renfrew, Home Depot, Hudson Bay, JC Penney, KB Toys, Kroger, Kroger, Lands End, Lord & Taylor, Lowes, Macy’s, Modells, Neiman Marcus, NEXCOM, Nordstrom, Office Depot, Office Depot, Office Max, Pamida, Parisian, Petsmart, Rite Aid, Safeway, Saks 5th Ave, Sears, Sephora, Sports Authority, Super Value, Target, Toys R Us, Von Maur, Walgreens
So what data is included in the 852?
This document tells the manufacturer the distributor’s (Retailer’s) inventory and level of activity per product. The standard 852 only transmits a “change” since the previous transmission. An all item refresh sends every field for every item this is often not done with the 852, but instead performed with semi-annual and annual inventory counts at the store or warehouse level. The section directly below lists out the common attributes or points carried in various 852 documents. Most retailers will not include all of them, but ask. Just because they don’t currently doesn’t mean it can’t be easily included (And may already be for some of the trading partners).
• Quantity Available (QA): Distributors Current Inventory Available
• Stock Type (QA-reference segment): “P” = Managed, “N” = Non-Managed
• Quantity on Order (QP): The Distributors Backorder with the Manufacturer
• Quantity Damaged (DG)
• Adjustment to Inventory Quantity (QT)
• Quantity Transferred (QZ)
• Calculated Reorder Point (PO)
• Quantity Out of Stock (QO): Distributors Backorder to their End Customer
• Quantity Sold (QS): Total Volume Sold since the last Transmission
• Frequency(QS): Number of Sales that makeup the Total Volume
• Quantity on Hold (HL)
• Lost Sales (LS)
• Planned Order Quantity (OQ)
• Quantity Committed (QC)
• Additional Demand Quantity (QD)
• Quantity in Transit (QI)
• Minimum Inventory Quantity (QL)
• Maximum Inventory Quantity (QM)
• Quantity Received (QR)
• Quantity Returned by Customer (QU)
Reading through the list of attributes or data carried in the 852, it is now fairly apparent which ones we need in order to create a running “Current Sell-through Percentage”: QA (current inventory) and QS (Quantity sold). I say this because while there are several ways to skin a cat. However, these two elements seem to be the most common throughout my list of retailers who send 852’s – so it makes sense to work with them. Take a look at the list of attributes. There is a huge amount of information which the 852 is built to carry.
Let’s talk about the reporting solution your company uses. Your solution at the least should track Item and Location to a depth which satisfies providing you with the insight you are looking for:
1. It has your current items included (Even if integrated to your Item Master or PLM to some extent). Some fields or attributes you should track, include:
a. Category and subcategory
b. NRF Color code
c. UPC, GTIN, EAN, ERP item #,
d. Style number
g. Case & Pack size
h. Landed cost, wholesale and retail price (MSRP)
2. You have included information on locations. These are the stores for all locations your products are carried. In addition, the more attributes which can be included for a given location, the better. What kind of attributes are we interested in? Consider the following:
a. Shipping region (FedEx or UPS)
b. Mall name
c. Store number
d. Climate (Is it cold there – Hawaii moves different stuff than Alaska)
e. Economic (Is this a depressed metro area, not depressed, etc.?)
f. Address info (City, State, ZIP)
g. Hub information (Include store number, division, flagship, outlet etc.)
h. GLN (Global location number if applicable)
There is a host of information you can track for locations as well as items. I have listed a very few to get started or get you thinking of others. Depending on the size of your company, sales, etc. you can go to specialists who can help you add to this list. Several actually sell the mall names, clustering information and very well built demographic models for each.
A solid reporting solution will allow you to differentiate and tie four areas by transaction event. In context, the transaction in this case is the 852, the four areas include: time, item, location and trading partner (Whether client or vendor).
Take another sip of that $5.00 coffee. Realize that [For far less than] your cost to drink the same drink daily could get you on your way to having a weekly report which tells you what your sales are doing with any given retail relationship (Provided they have the 852).
What additional information can the 852 provide?
• Highlight anomalies by location. For example, you are looking at a sell through by item, by store and you see one store shows a significant dip (Much lower percentage) compared to others. What could this indicate? Store personnel haven’t put out the product and it’s sitting in the back room.
• When you sold this program to the retailer you really sold them a ‘plan’ if it is coming to fruition when you engage with your buyer it becomes an amazing talking point. Conversely, if you are not hitting your ‘plan’ you need to take whatever steps possible to rectify the situation – collaborating is really facilitated when you have numbers to discuss.
That coffee of yours is cold, right about now and you only drank half of it. Throw it away.
What do you do now? You want the report so what is the best way to get it? The easiest way is to call someone else to put it altogether for you. You can go to Microsoft and get SharePoint. This was combined with Performance Point Server this year Performance Point was ProClarity). SQL Server 2008 ships with SSRS (SQL Server Reporting Services). You can go to Tableau Software. Tableau probably has the sexiest visual reporting on the planet. There are a host of solutions. If you are new to this, I would suggest one of two areas:
1. Go to your EDI provider (I don’t care if they are a VAN, or hosted EDI). Find about the reporting services they offer (No sense re-building the wheel before you know what kind of wheel you want).
2. See what the cost to take a flat file via FTP will cost. Then go to your IT person and ask if they can create the database and reports for you.
If you are a non-technical person have no resources in this area (Can’t write SQL), or if the costs come in too high, depending on the feedback I receive we can provide you with the code for a basic system. At least something to get you started or some alternatives. There are fantastic systems on the market and you can build systems for free (Minus labor) using open source.
To sum up, reporting for reporting’s sake is a waste of money. That said: reports which help you drive your business more effectively should be equated to the speedometer in your car. You won’t drive your car without a working speedometer will you? If the speedometer doesn’t work you will pay to get it fixed, or will you just ‘follow’ traffic? In this case, the idea of ‘following’ is really “hoping” because your competitor isn’t going to call you weekly and tell you how they and everyone else is doing… Buy your business a fancy cup of coffee regularly, and it will return the favor by delivering information that will drive your business more effectively!