The first post in this series “Human-Understandable Stats” showed how a business decision maker’s “I wish” for by-SKU sales lift, produced a simple model that delivered the wish. This post demonstrates how the delivered wish can drive Returns On Sales up.  

The data in the following analysis is obfuscated real. That is, the patterns are the same, but SKUs are SKU001 through SKU100, not identified products. And the data has been shifted by multiple factors.

First, a waterfall diagram of model results: Figure 1 shows SKUs on the x-axis (across the page) and category sales lift per SKU on the left y-axis (up and down).

Figure 1: By-SKU Category Sales Lift

• SKUs are sorted from highest category sales lift at left, to lowest at right. Green boxes show for each SKU that increases sales, by how much sales increased per week.
• Red boxes show for each SKU that decreased sales, how much sales decreased per week.
• The white box is the model intercept. 

These results all fall straight out of the “I wish I knew by-SKU sale lift” model. 

Figure 2 groups the SKU sales behavior into four groups: 1. Super Stars, 2. Franchise Builders, 3. Junk (SKUs adding < breakeven sales), and 4. cannibal SKUs (SKUs decreasing category sales).

Figure 2: Four Species of Sales Lift by-SKU

To turn raw model results like these, into practical tools for management decisions, requires only that we use the #1 data science tool in the world (like it or not) … Microsoft Excel.

Figure 3 is the same data, sorted from high sales lift, to low sales lift, for a single territory, and adds:

• 95% confidence intervals around sales lift estimates
• a 2nd y-axis to show how many store-week data points are in each SKU’s estimate (orange bars at the bottom)
• Simple average sales in-store in white
• A break-even sales indicator (3 cases per week per store) in yellow

Figure 3: Visual Model Summary

This graph is set up to power common sense SKU management. Study the graph, and it becomes clear that:

1. The highest sales lift SKUs in this territory have too-little distribution:
   • At about 20 cases per week per-store (left y-axis), SKU001 is high-category-impact as are SKU002 through SKU014
   • However, with only 130,000 store weeks of data (right y-axis) SKU001 is less well distributed than cannibal SKUs (SKU090-SKU100). SKU002 through SKU014 are also less well distributed than cannibals.   
2. From the break even line, it is clear that:
   1. About 40 SKUs are below break even sales lift.
   2. The distance between average sales and break even sales, seems very small.
      • Cannibal SKUs are drag down average sales. So, it should be possible to increase the distance between average sales and break even sales. 
3. As a practical way to proceed, perhaps it might be wise, to:
   • Drop cannibal SKUs one by one, and push high-performing-SKUs further into distribution.
   • Say, by picking SKU100 (-16.2 cases per store per week) to die first, and replacing SKU100 with SKU013 which has about the same number of store weeks and a +7.6 sales lift.  
   • Experiments like this with a high change in sales 23.8 cases per store week (7.6+16.2) are a great place to start in teasing out what works in assortment planning

Excel gets better though, as we can use model output to compute quick-and-dirty predictions about what would happen to category sales, and then pop them into a pivot table that gives weekly sales change estimates. 

Wait, wut? Where are the 23.8 cases per store week coming from? From two quick and dirty assumptions: 

1. Dropping cannibal SKUs will remove in-store cannibalization, immediately increasing sales
2. Pushing high performing SKUs further into distribution (2x their current store coverage), will increase sales by current estimated sales lift

First, let’s see the implied sales change from dropping cannibals = +214,000 cases: 

Next, the implied sales change from pushing top 10 sales impact SKUs 2x further into distribution = +116,000 cases. 

*Brain Pain Note*
The top 10 sales-impact SKUs are not the top 10 sales-lift SKUs. Because existing distribution varies widely across SKUs, the top SKUs from doubling the existing distribution favors higher-distributed SKUs. 

Study of these two pivot tables implies:

1. The place to start in SKU optimization may be to drop cannibal SKUs
   1. Dropping cannibal SKUs should net about 214,000 incremental cases
   2. Pushing the top 10 SKUs 2x further into distribution should net about 116,000 incremental cases
   3. So the gain from dropping SKUs is 214,000/116,000 = 184% more sales per SKU change
2. The number of shelf facings that need to be dropped, is terrifying!
   1. The greatest sin of retailing is being out-of-stock
   2. Pulling the SKUs that hurt category sales, is the highest priority, and there are so many cannibal SKUs that empty shelf facings would happen
   3. Empty shelf facings imply out-of-stock, and therefore are terrifying
3. Business decision makers have a lot of options to improve return on sales by dropping or pushing SKUs

Bringing pivot table results back into the Visual Model Summary provides what I call a “Type 1 SKU map.” This map provides business decision makers with cues about which SKUs to change, and most importantly, why.

Figure 5: SKU Map Type 1 – Common Sense SKU Swapping

If we compute the average distribution penetration of our 100 SKU portfolio (18% look for the vertical orange line in Figure 6), we can then plot what I call a “Type 2 SKU map” which cues decision makers to look at SKUs as a “portfolio” where they can increase margins by dropping low performers, and then to power up return on sales by moving High Potential and Potential Franchise builders, to the right. 

Figure 6: SKU Map Type 2 – Portfolio Potential 

Conclusion

Business decision maker “I wishes” produce simple models that can dramatically improve SKU selection and Returns On Sales. These simple models can be extended with Excel to be natural and expressively powerful tools for assortment decisions.