The Cost to Brands of Incorrectly Calculating Store Distribution for Replenishment Analysis

Understanding store distribution is a seemingly innocuous component of any retail replenishment analysis for consumer brands. Often overlooked, or worse yet, poorly understood, brands blindly build weekly reports based on data models embedded within each retailer’s portal. These data models must work at scale for the retailer, which equates to hidden filters on the data output to optimize processing time. Imagine how many reports Walmart’s Retail Link needs to generate each day. It’s hard to fault retailers for trying to streamline the data supply chain. But, in the end, a shrinking distribution equates to missed replenishment orders for brands. 

In this article, we’ll explore the following: 

1. Walk through an example of how a store distribution calculation, as a component of a replenishment analysis, can go wrong.  
2. Model out the potential missed sales opportunity for a brand when incorrectly calculating store distribution.
3. Review correctly calculating store distribution based on a standard P.O.S. data format via Power BI. 
4. Rationalize the investment in P.O.S. data analytics vendors to handle this step automatically. 

A – Demand shocks influence replenishment systems

Replenishment systems have faults. The retailer’s replenishment logic bases the recommended PO quantity for each product on various factors, including predicted demand, lead time, and safety stock requirements. The critical variable to unpack is predicted demand. The scenario below frequently happens, leading to a missed chunk of replenishment business. 

1. An item sells out at retail from a demand shock, producing a series of zero sales days.
2. The string of zero sales days causes the retailer’s auto-generated predicted demand curve to dampen, potentially down to zero.
3. The replenishment system no longer kicks out a PO since the updated predicted models are showing no demand on the horizon. 
4. When the analyst runs a replenishment report, a store/item filter of one unit on hand or one unit sold over the last N periods is selected to keep the data small enough for excel. This filter inadvertently removes the item/store combinations that experienced the OOS event, leading to an under-reported replenishment recommendation back to the retailer. 

B – The cost of (unintentionally) excluding stores from a replenishment recommendation analysis

Using the formula below, the missed retail sales opportunity exponentially grows as each variable increases. 

Item Count * Store Count * Avg Weekly Quantity Sold *ASP *# of Retailers

As commercial distribution increases so do the risk of missed sales due to idiosyncrasies in the data pulled from a retailer reporting portal. 

C – Step-by-step guide on how to correctly create a store list given a typical P.O.S. data layout using Power BI

When pulling P.O.S. data from a vendor portal, it’s possible that item/store combinations that have zero on-hand units and zero sales activity from the most recent week ending date could fall off the report. In turn, the analyst omits a cluster of stores from the recommended replenishment analysis. 

Let’s play this scenario out with sample data that drives our point home. 

This analysis will cover the following steps – 

1. Import the dataset.
2. Profile the top ten records to get a lay of the land.
3. Illustrate how store counts significantly vary for a given SKU weekly.
4. Create a store distribution list in power query.

1. Import the dataset

This analysis will use an Excel workbook data source. Click on “Get data” > “Excel workbook,” then find the location of the workbook. 

After successfully loading the workbook, the power query editor will look like the below image. 

2. Profile the data.

The dataset contains six standard fields one could download from a retailer reporting portal. Within the power query editor, all data types are visible. 

• The date range for this sample dataset is from 2020-02-08 to 2022-11-26. We will assume that 2022-11-26 is the current week we have data for. 
• The unique SKU count is one, labeled “abc.” 
• This data is representative of one retailer. 

3. Illustrate how store counts vary greatly by different filter criteria.

After building a clustered column chart displaying record count by week ending date, this item clearly experienced numerous zero on hand and zero sales dates throughout this data pull, none more critical than on 2022-11-26. 

Through DAX measures, store distribution calculations illuminate how much variability exists over time. The entire dataset shows 772 unique store numbers. Filtered down to records associated with the last date, 2022-11-26, the store count precipitously drops to 96. Imagine how different a replenishment recommendation would appear if the analyst were only concerned with recent records.

4. Create a store distribution list using the power query editor. 

The critical feature of generating a store distribution list is to extend your filter parameters beyond the most recent week-ending date. The file used for this example contains a wide range of dates to leverage. Let’s assume all POG set orders landed in stores nine months ago. 

This will be our first filter in power query. Here is a breakdown of the logic in the custom column.

• Find today’s date (DateTime.LocalNow())
• Convert today’s date to date only (DateTime.Date())
• Find the date nine months ago based on today’s date (Date.AddMonths)
• Compare each week_ending_date record against nine months ago and output a TRUE/FALSE value. 

Next, determine a “units_sold” threshold based on the nine-month trimmed dataset to bring the store count down to a reasonable figure (“reasonable” relative to each retailer). Graph a cumulative total measure to help understand how total store count relates to historical volume bands. 

Here is the DAX measure. 

Here is the graphed-out cumulative store count running total measure. Depending on how aggressive the analyst would like to get on their replenishment recommendation, a nine-month trailing history of total units sold, around ~12 units per store, equates to a distribution list of ~418 stores. 

D – Rationalize the investment in P.O.S. analytics vendors

Every component of this article, from the retail expertise through the technical Power BI demo, can be easily handled by vendors such as Krunchbox. Spending internal resources to acquire P.O.S. data from vendor portals is no longer a competitive advantage. However, what you do with the data can be a differentiating factor from the competition.