We use cookies and other similar technologies (Cookies) to enhance your experience and to provide you with relevant content and ads. By using our website, you are agreeing to the use of Cookies. You can change your settings at any time. Cookie Policy.

Managing the Supply Chain with a Retail Data Warehouse

Originally published May 15, 2007

In a previous article, we discussed the need for building a customer-centric data warehouse. In this article, we focus on an equally important area: creating a data warehouse that captures the data and provides the information needed to manage the inventory and supply chain to maximum advantage.

Excellent supply chain and inventory management is a complex and vital part of retail strategy. Getting this right can make the difference between success and failure, profit and loss, growth and loss of market share. Nothing will send customers across the street to the competition faster than a poor supply strategy that leaves shelves bare of the products they want. Allowing products that do not sell to sit on shelves is also obviously counterproductive.

However, getting it right is not easy in this just-in-time world where supply chains typically reach halfway around the world. The products you need may start with components and parts manufactured in China, with precision part finishing and sub-assembly in the United States and final assembly in Mexico before being shipped to the retail outlet, ideally just in time to go on the shelves.

Managing such a complex supply chain requires sophisticated data analysis that can allow the retailer to anticipate demand, and particularly shifts in that demand, ahead of time. As with customer-centric data, capturing all the relevant data in a single, central location – a data warehouse – where it can be mined for vital information is a key to gaining competitive advantage. As with the customer-centric data warehouse, this takes effort, and not all retailers have invested the resources to capture all the data and ensure that the data they do capture is high quality. This, however, creates opportunity for those retailers who are willing to invest that effort.

Analytic Subject Areas

The first question, then, is what main areas and sub-areas should the data warehouse cover? We believe that the retailer needs to focus on five main areas:

Inventory Management

Inventory management breaks down the basic information on inventory into segments by several metrics to track performance of the supply chain from purchase order to store, allowing analysis and optimization of inventory to meet customer demand. An overall score of the supply chain's efficiency can be determined by assessing metrics such as:

  • The rate of stock turnover for the best and worst performing products

  • Total investment in inventory over time

  • Amount of inventory in each stage of the supply chain (order, shipping, en route to store, on shelves) at a given time

  • Products that are getting returned often (and why)

  • In-stock position

There are a number of different ways to view inventory management depending on the class of retailer, the kinds of merchandise they sell and their brand image with customers. For example, a grocer with a mid-priced brand image selling staples such as milk and bread may offer a limited selection of SKUs and put them on full replenishment, needing only a rate of sale and quantity on hand to trigger automated re-ordering systems. This is inventory management at its simplest. It is also very important to price staple items competitively; thus, an understanding of the competitor's pricing is a vital data point.

On the other hand, a fashion retailer selling seasonal apparel items has a much more difficult set of decisions to make, although the profit on each item may be higher than in our previous example. Analysis of SKUs across a silhouette or class may be applicable in determining whether to mark down or promote the product. Often, a season's worth of merchandise may be purchased in only a few "buys" due to the shortness of the season and the need to clear the merchandise by season's end to avoid massive markdowns.

Vendor Management

Vendor management looks at the state and value of the retailer's relationship with each supplier. A quantitative measure of each vendor's value to the retailer can be obtained by analyzing areas such as:

  • Actual sales and returns of each vendor's products compared to predictions

  • The vendor's ability to deliver products on time

  • The flexibility of each vendor in handling changes

  • Frequency of product returns for each vendor

Additionally, measuring how long the retailer takes to pay the vendor's invoices and whether delays in payments are causing delays in order fulfillment can uncover problems in the retailer's systems.

Sharing up-to-date sales and stock information with vendors is becoming more common, yet this remains an underleveraged method for improving the relationship with each vendor. In previous articles, the concept of a vendor business intelligence (BI) extranet has been discussed, along with the value that can be gained from such a BI-based application. Allowing vendors to see the retailer's view of their performance creates shared visibility into problems in the relationship and minimizes the communication gap that can be created in supplier/retailer collaboration. It can be helpful to have high-level metrics available in a vendor scorecard, along with the ability to drill into order and shipment level detail to look for anomalies.

Product Cost Analysis

Product cost analysis creates a more granular insight into costs by analyzing markups, discounts and other costs such as shipping, storage and stocking. It should answer key business questions such as:

  • How can product costs be reduced while enhancing product and customer profitability?

  • What is the impact on profit and revenue of changes in sales prices and product mixes?

Cost of goods sold may be the vaguest metric in the entire retail business. While the definition is universally understood from an accounting perspective, most retailers have many different definitions of "cost" that are not always well understood. Most frequently, accounting and merchandising view cost differently, and the store operations function may tack on additional "costs" to incent particular store manager behavior. In this environment, creating an enterprise repository of the components of cost and using that repository as the basis for calculating one or many derived costs achieves the benefit of removing variations from different databases.

Merchandise and Assortment Planning

Merchandise and assortment planning valuates performance of assortments, stores and departments. It can support optimization of store clusters and assortment plans based on actual results by analyzing information such as:

  • The open-to-buy position compared to the previous year

  • Profiles of store clusters

  • Performance of clusters in various locations

While not typically thought of as part of the "supply chain," the process of merchandise and assortment planning initiates actions in the supply chain, namely the writing of orders to suppliers for goods to be manufactured and delivered to distribution centers and stores. These two processes are evolving retail disciplines. They are heavily data driven and require reliable information stores and analytical tools to be effective. Many retailers purchase specialized tools for these functions, but the inputs to those tools (SKU sales history) and the outputs from them (assortments, financial targets) become key data warehouse elements to be analyzed historically.

Distribution Center Operations

Distribution center operations monitors performance of distribution centers. By aggregating employee costs, shipments and receipts, shrinkage and weeks of supply, it can quantify the fixed costs of managing distribution centers, how shipping costs have changed over time and the relative level of efficiency of the distribution centers. That information can support investigation of the potential for reducing costs through employee retraining or reassignment, the use of alternative shipping methods and changes in handling methods to reduce breakage.

Data Subject Areas

Breaking down the data needed by a retailer to track the subject areas described would result in a plan to source the following types of data into the retail data warehouse:

Merchandise and Assortment Plans (objectives for the business related to inventory and sales) – Information on financial plans and targets, open-to-buy data and assortment plans by store should be sourced to compare with actual results.

Vendors, Deals, Purchase Orders (vendor-centric data) – Contract and deal details, incentives for bulk purchases and baseline inventory costs are vital for creating better strategies for minimizing direct costs of re-supply.

Products (specific and general product information) – Segment-specific information can provide the basis for reallocating shelf space to product segments (fashion, food, etc.) according to the volume of sales of each segment in each store.

Inventory Movement and On-Hand – Physical and calculated inventories, inventory receipts and adjusts, supplier shipments and intra-enterprise item movements are obviously vital to ensuring optimal inventory levels. Data on transfers between distribution centers and stores can identify excessive handling issues.

The Complete Picture

Many retailers have data warehouses, but relatively few use the retail data warehouse to analyze their entire supply chain. Combining detailed supply chain data and analysis with sales and customer data gives a complete picture of both demand and supply side factors in a retail business.

  • Dan RossDan Ross
    Dan is the Managing Partner of the Retail Practice at Claraview, a strategy and technology consultancy that helps leading companies and government agencies use business intelligence to achieve competitive advantage and operational excellence. Claraview clients realize measurable results: faster time to decision, improved information quality and greater strategic insight. Dan is a frequent contributor to business intelligence literature, writing on topics spanning technical approaches and business impact, and the Claraview Retail Practice serves some of the world's most advanced users of retail data warehouses.

    Editor's note: More retail articles, resources, news and events are available in the BeyeNETWORK's Retail Channel. Be sure to visit today!

Recent articles by Dan Ross



Want to post a comment? Login or become a member today!

Be the first to comment!