Author: Helen Thomas

We have been contacted by many vendors to major retailers in the past two weeks, looking for a solution to EDI 852 reporting.  It’s not surprising since many major retailers send the EDI data out and simply hope vendors are able to use the data in some beneficial manor.  The fact is, most vendors are not in a good position to make this happen.  Especially since most vendors have many retail customers, all of which have different EDI templates and reporting requirements.  What a mess.

What is the point of making sales and inventory data available to suppliers if the data is unusuable, or inaccurate?

To make sense out of EDI 852 data requires a reporting tool capable of presenting summarized views (e.g. sales by month for each SKU and store) and then drill down capability to investigate problems.  In addition, a solid reporting tool needs to provide exception based management of the data.  This provides the vendor the ability to ingrain business logic like min and mix inventory turns and then be notified when something is out of whack. 

If you are struggling with EDI 852 data reporting, take solice.  You are not alone. 

Our President and CEO will be a featured speaker at the upcoming Trade Promotion Association event in Chicago.  If your organization is concerned about trade promotion, this is a must attend event.

The Trade Promotion Management Association (TPMA), is a non-profit trade association for professionals and organizations involved with trade promotion. TPMA provides members with information, education and research on the dynamic world of trade promotion, including co-op advertising, market development funds, slotting fees, off-invoice deductions, channel promotions and more.

Mark your calendars today for TPMA’s Annual Conference, September 24th – 27th, 2006 in Chicago, IL. This will prove to be a watershed event, raising the bar for all trade promotion programs and their measurable results. New strategies, identifiable metrics, new media, and the supporting analytics to continuously improve results will be presented.

Our Accelerated Analytics product was recently highlighted in the retail blog ShiSh List.  We recommend taking a look at their posting as well as looking around the blog.  Lots of good content on retail collaboration.

Accelerated Analytics was designed and built using Microsoft SQL 2005 and ProClarity (for the non-geek that means latest great technology) for business users to perform category management analysis, POS data analysis, and to support collaboration between retailers and their vendors.

A surprisingly large number of organizations are still using spreadsheets as the backbone of core business processes like POS data analysis, category management, and vendor collaboration.  There are many reasons for this, and even for a business intelligence professional like myself, I cannot simply dismiss their use altogether.  Although, it would be good for business 😉  Spreadsheets are an incredibly useful tool in many situations.  However, they are also terribly overextended and misapplied.

I cannot tell you how many times in the past month I’ve heard some form of this statement; “Our spreadsheets work just fine, I don’t see any reason for us to change.”

I am going to take a few deep breaths and be as diplomatic as possible in answering that statement.  So here are a few reasons not to use spreadsheets for POS data analysis, category management, or vendor collaboration. 

Spreadsheets are inefficient for most complex data analysis.  Spreadsheets were designed to be a presentation layer for data and allow a user to perform some limited high level math.  Unfortunately, in many offices, spreadsheets have become complex programming environments where power users spend hour after hour manipulating data.   Because they are on the very edge of what a spreadsheet was designed to do, they spend 80% of their time fetching and manipulating data and only 20% performing analysis.

No single version of the truth.  Each spreadsheet has its own business logic, calculations, and definitions, so each user must spend time simply familiarizing themselves with the information.  In some cases they will have a different definition, so then the spreadsheet must be recreated to suit their needs.  One client of mine comes to each meeting, distributes his spreadsheet, and then leaves to refill his coffee cup and use the restroom while all the other attendees just figure out his math. 15 minutes on the front of every meeting multiplied times a dozen senior managers!

Lack of data quality and consistency.  Any time data is manually inserted into a spreadsheet, an opportunity exists to make a mistake.  The mistake could be inserting the wrong set of data or not applying the correct unit of measure.

Spreadsheets create a single point of failure.  Almost every office I have ever visited has a power user with an Access database and an Excel spreadsheet.  The rest of the office lives in fear of the day this power user might choke on a chicken bone at lunch, or decide they would rather live in Tahiti.

No auditability or verification.  In the post-SOX business world, this is a key concern for any public company or large private company with ambitions of an IPO.

Stock outs are a serious problem.  Research has shown stock outs average 8%, but can be as high as 40% on promoted items.  So, retailers and vendors are losing up to 40% of their potential sales on some items.  The financial impact is often exasperated by an overly large order to compensate for the back-order of demand and, the retailer hopes, add some safety stock.  This of course just compounds the problem by increasing inventory costs and reducing GMROI.  Maybe worst of all, research shows when consumers are faced with an out of stock situation they will continue purchasing the items on their list but go elsewhere to buy that lost item.  If the out of stock happens a second time, they are likely to change retailers.  If it happens one more time, they are likely to change brands all together.

Here are some steps that can be taken to reduce stock outs:

1. Use a data analysis tool to proactively monitor POS data on fast moving items.
2. Use a data analysis tool to calculate min/max on the longest time series of data possible, and be sure to account for geographic variances.
3. Coordinate your promotions internally, and among your supply chain.
4. Assume new product introductions will create an inventory problem and plan accordingly.
5. Leverage the experience of your vendors by empowering them to analyze sales and inventory data.

Show of hands, how many of you know how to calculate price elasticity?  Well, if you are like me, you know the general concept but the math is a bit rusty.  So here is a quick and dirty refresher.

Price elasticity is a measure of how demand for a product is influenced by price changes.  This measure can help determine whether to change the price of products by calculating what effect price changes have on the quantities customers purchase.  Price elasticity can help to answer questions like:

If I increase my unit price by 20%, how much unit sales volume will I lose?

If I lower my unit price by 10%, how much unit sales volume will I gain?

To calculate the price elasticity (PE)

PE = [(Q2-Q1) / ((Q1+Q2) / 2 )] / [(P2-P1) / ((P1+P2) / 2]

Where Q1 = initial quantity; Q2 = final quantity; P1 = initial price; P2 = final price

Understanding the calculation results

If the PE > 1 the product is relatively elastic.  An increase in price would result in a decrease in revenue, and a decrease in price would result in an increase in revenue.

If the PE < 1 the product is relatively inelastic.  An increase in price would result in an increase in revenue, and a decrease in price would result in a decrease in revenue.

A picture truly is worth a thousand words…especially when you are analyzing data.  Which would you rather have? A spreadsheet with a thousand SKU’s showing sales and gross margins or a picture that instantly tells the story?  If you have spent hours analyzing data in Excel, you probably have a strong preference.  When we work with business users, they are amazed at the way our Accelerated Analytics software can show them a picture of what is happening in their business.  We are always amazed they don’t already have this technology.  The next time you have to conduct a complex analysis, consider how a picture like this would make your life a hundred times easier.

The perfect order is a very useful measuring stick for the retailer and manufacturer to measure performance.  According to AMR Research, “the perfect order is the ability to produce orders that are complete, accurate, and on time.”  Research estimates a 3% improvement in perfect order performance will increase profits by 1%. 

Elements of the perfect order

• On time
• Complete
• Damage free
• Accurate documentation

Calculating the perfect order

Many organizations multiply together the scores for each element to calculate a composite score.  Therefore, if pick accuracy is 99.2%, on time delivery is 97.2%, shipped without damage is 98.7%, and 99.8% are invoiced correctly, the perfect order measure would be 94.9%. 

The perfect order provides a single value key performance indicator (KPI) which can be easily measured.  It’s like a stop light – red, yellow, or green.  You immediately know how your organization is performing.  Managers should be careful, however, to review each individual element to have a complete and accurate view of performance.

The ability to sense and react to demand is a key driver of perfect order performance.  Which is why more and more vendors are asking for POS and inventory data from their retail customers, so they can better understand demand with as little latency as possible.

Many times, when I talk to retailers about sharing data with vendors, I hear some version of “Well they are not asking for it,” or “only if they can show me how they will use it.”

It reminds me of the classic quandary…Which came first, the chicken or the egg?

Or in this context… which comes first… the retailer offering to share data, or the vendor requesting data?

Innovative leaders in any industry are always optimists.  They are the ones that don’t wonder if a vendor will use the data or how, they wonder how big of an improvement in sales can happen by even one good decision.  They wonder if they can get another couple percentage points closer to the perfect order by sharing data.  What would happen if vendors were empowered to be category captains and proactively helped to manage in-stock performance?  What if they really analyzed POS data in the way your internal team does?  They don’t know the answers.  But they are optimistic about the opportunity.  They do some research, learn others have had success, determine the risk is low, and take a step forward.

Notice I said leaders are optimists, not fools.  I own a business.  I understand the realities of limited resources and prioritizing a portfolio of possible projects.  But given the abundance of research on the benefits of sharing data with vendors, this project falls solidly in that coveted upper right quadrant.  If you don’t belive me, google ECR, DDSN, or CPFR and read the case studies.

So how does one insipre a VP of Merchandising, or Supply Chain, or Purchasing to take that first step?  How do you position the WIFM?

The benefits of optimizing the retail supply chain using better demand planning and collaboration with suppliers are well documented. Studies of retailers by the Harvard Business, Grocery Manufacturers Association, National Retail Federation (NRF), and AMR Research show results of 15% less inventory, 17% better perfect order performance and 35% shorter cash-to-cash cycles.  

Documented benefits include: 

•  Relationships with trading partners: 57% improved 
•  Stock outages: 38% reduced 
•  Sales: 38% increased 
•  Inventory: 29% decreased 
•  Forecast accuracy: 38% Improved 
•  Internal communications: 24% improved 
•  Asset utilization: 14% better