Range gap per store: turning the SKU list into a Monday brief for distributor reps

A distributor's weekly sales sheet says exactly which SKUs each store bought. Compared with the SKU list for the store's channel, it says which SKUs the store could stock and does not. Valued and ranked, that is the rep's week. This guide sets out the method from sheet to brief.

The measure

Per store, per period:

Range = SKUs bought ÷ SKUs on the channel list Gap value = Σ (missing SKUs × the store's rate for similar SKUs it buys)

Per route:

The stores ranked by gap value, and the SKUs most often missing

The rows you need

• Weekly sales sheet: store, SKU, cases or units, value, week, route, rep.
• SKU list by channel: which SKUs a store of each channel should carry, and the SKU type for valuation.
• Store master: store, channel, route. Often in the sales sheet; otherwise a list the sales manager keeps.

Building it

1. Store-by-SKU grid for the period, with the channel list as the columns for each store.
2. Mark bought and missing.
3. Value each missing SKU at the store's average weekly value for SKUs of the same type it does buy; otherwise the channel average.
4. Rank stores by gap value within each route.
5. Assert that cases by store sum to the route and routes to the depot's sales sheet total.

depot sales = Σ routes = Σ stores = Σ SKUs

The by-SKU equality catches a SKU code that changed after a pack change.

A worked brief

Route 4, convenience channel list of nine SKUs, week 37. The three stores with the largest gap value.

The brief for the rep:

1. Store 1102 stocks cola and water only. Energy 4pk sells at every other store on the route; at its own rate that is $310 a week.
2. Store 1107 does not carry Water 24pk, which is the route's biggest line. $260 a week.
3. Store 1111 has no energy. $190 a week.

Three lines, three stores, three SKUs, three numbers. It took the sales sheet and the SKU list.

Where it goes wrong

One list for every channel. The most common failure and the one that kills the brief fastest.

Value at list price. A store's willingness to stock a SKU is better predicted by what it already spends on similar ones. Use the store's own rate.

Gaps that are not gaps. A store with no chiller cannot stock the cold line. Let the rep mark it and keep the store off that SKU's list.

Seasonal SKUs. A summer line missing in November is not a gap. Carry seasonality on the SKU list.

Every Monday, three lines

Mapped once, the weekly sheet produces the grid, the values and the brief per rep every Monday, reconciled to the depot's total. Covirage does this from the sheet as it is. The distributors page describes it, and you can upload a sample sales sheet and see the grid on your own rows.