Finding distribution voids from distributor sell-out data

A brand's biggest growth opportunity is usually not a new store. It is the store that already sells the category, already buys three of the brand's nine SKUs, and has never been offered the other six. Distributor sell-out data contains every one of those stores. This guide shows how to find them, value them and hand them to the field.

What a void is

For a store and a SKU in a period:

Void = the store sold the category (any of your SKUs, or a category flag) and did not sell this SKU

Range penetration is the same thing from the store's side:

Range = SKUs sold in the store ÷ SKUs on the list for the store's channel

The channel qualifier matters. A convenience store is not expected to stock the family multipack, and a void list that says it should is one the field team will stop reading.

The rows you need

• Sell-out by distributor: one row per store, SKU and period, with units and value. Each distributor's format, mapped once.
• Store master: store, distributor, region, channel. Usually in the distributor file; otherwise a list the brand keeps.
• SKU list by channel: which SKUs a store of each channel should carry.
• Reported sell-out: the brand's own figure for the period, as the reconciliation target.

Building the grid

1. Map each distributor file to the common columns. Keep the mapping per distributor; it changes rarely.
2. Build the store-by-SKU grid for the period: every store that sold anything, every SKU on the list for its channel.
3. Mark the cells: sold, or void.
4. Value the voids: at the store's rate for similar SKUs it does stock, or the channel average.
5. Roll up: voids and value by store, by distributor, by region. Assert that the grid's sold value equals each distributor's file total and that distributors sum to reported sell-out.

reported sell-out = Σ regions = Σ distributors = Σ stores = Σ SKUs

The last equality, by SKU, is the one that catches a SKU code changed by one distributor after a pack change.

A worked example

One distributor, one region, a convenience channel list of five SKUs, August.

Two stores at 20 percent range, each worth more than $450 a month at their own rate for the SKUs they stock. Across a region of 400 stores, the voids at that value are a field plan, and the plan is ranked before anyone opens a spreadsheet.

Where it goes wrong

One SKU list for every channel. Every small store shows voids it could never fill and the list is discredited in a week. Build the list per channel, and let the field mark a void as not applicable with a reason.

SKU codes drift. A pack change gives the same product a new code at one distributor and not another. The by-SKU assertion fails, and the mapping table gets a new row. Without the assertion the old code quietly reads as delisted.

Stores counted twice. A store served by two distributors appears twice in the grid with different partial ranges. Merge on the store identifier, not on the distributor's account number.

Sell-in confused with sell-out. Shipments to the distributor are not sales to stores. A void list built on sell-in shows what the distributor holds, not what the shelf holds.

Every month, per region

Mapped once per distributor, the monthly files produce the grid, the voids, the values and the reconciliation, and each regional manager gets the ranked list for their distributors. Covirage does this on the sell-out files as they arrive. The FMCG and CPG page describes it, and you can upload a sample sell-out file and see the grid on your own rows.