Better Point of Sale Reports with "Variance Analysis": Velocity, Distribution and Pricing.. oh my !

Routine, weekly point-of-sale reports tend to look very similar.  For various time buckets (Last week, last 4 weeks, year to date) we total sales in both currency and units then compare to prior year.  Add in a few more measures to look at retail pricing, inventory,  or service level metrics and you may struggle to make it fit on a page.   And from a CPG standpoint, POS  reporting is only half of the story: a CPG's sales targets are not based on POS, they are based on shipments to the retailer.  How can you get a good overview of POS and reconcile that with Shipments all in one report?

Point of Sales revenue reporting

Your basic report probably looks something like this (click to view):

It's not pretty is it? It's quite hard to pull useful summary data from.and we haven't even tried to include CPG shipment data yet.

To improve on this we're going to use an accounting approach called "Variance Analysis" and tweak it to fit our needs.  Variance analysis looks at revenue or cost differences and splits it  into components driven by  volume and pricing.   Typically this is used for comparison of budget or plan to actual but we'll use it to compare year on year sales.

The calculations are fairly simple but if you would rather avoid 7th grade algebra, just trust me and skip ahead.

Volume Variance:CurrentPrice*(CurrentVolume - PreviousVolume)
Price Variance: (CurrentPice - PreviousPrice) * PreviousVolume

Add these together  to get a Total Variance.  Now let's prove this really does explain the difference between Current and Previous Sales 

Expand the terms (using introductory algebra) and we get: 

TotalVariance =
                  CurrentPrice*CurrentVolume
                - CurrentPrice*PreviousVolume
                + CurrentPrice*PreviousVolume
                - PreviousPrice*PreviousVolume 

As you can see, the 2nd and 3rd terms "cancel out" leaving us with: 

TotalVariance =
                  CurrentPrice*CurrentVolume
                - PreviousPrice*PreviousVolume 

Which is the exactly difference in Sales Revenue we wanted to explain.   Finally, express Total, Price and Volume Variances as a % of the same denominator (Last year's POS revenue) to get percentage values that are additive like this :

              7% increase due to incremental unit volume
           - 2% due to a decrease in retail pricing
              5% net change

Easy right ?

Let's make it a little more useful and split the volume variance into 2 parts:  for variance driven by changing distribution (the number of stores we sell through) and one for the rate of sale in each store.

VolumeVariance_Distribution = PreviousPrice * CurrentSalesRate    * (CurrentDistribution - PreviousDistribution)

VolumeVariance_SalesRate = PreviousPrice * PreviousDistribution    * (CurrentSalesRate - PreviousSalesRate)

Add these two variances together, multiply out the terms then simplify the result and you should get back to the formula we specified for the volume variance previously.

We can represent these results graphically as a waterfall chart.

• A waterfall chart explains the gap between 2 values, in this case, last years POS sales and this years POS sales.  
• Typically these are built as column charts, but I've laid this out as a bar chart so that its easier to read the bar labels.  
• To make it easier to see the changes, I have adjusted the horizontal scale so that it no longer starts at 0.  
• The absolute impacts are shown as labels in each bar.  
• The percentage change (relative to last year's sales) is shown in each bar label.  
• Allowing for rounding errors these % changes are additive (16.6 - 9.7 - 0.6 = 6.3)

Starting at the top, this chart shows Last years POS Sales and then each additional bar shows incremental changes that explain the gap between last year's and this year's POS Sales.  Red bars are for negative values, green bars positive.

So, this says:

• POS Sales were hit hard by a significant loss of distribution (-9.7%)
• Overall, this was more than offset by the retail price increase (16.6%)
• Despite the retail price increase, store-level velocity was effectively unchanged.  
• Action: If the decision to reduce distribution was made in anticipation of higher-price and slower sales, there is a good argument to have it restored
• Action: This product appears to be relatively insensitive to price, a more detailed pricing-elasticity study may confirm allowing a change in pricing strategy to further increase revenue.

Tying CPG Shipment revenue and POS sales together

An almost identical approach lets us calculate price and volume variances for shipment information.  In this instance the price is the cost to the retailer and volume comes from shipments rather than POS but other than that it's still just basic price and volume variances.

Now comes the challenge of connecting these two sets of variances :

To do this, we are going to split the Volume driver for shipments into 2 parts: 

• Shipment volume that is a direct result of POS unit sales (volume)
• Shipment volume that did not support consumption and just resulted in changes to the Retailer's inventory level.  (Ship too much and Retailer inventory increases: ship too little and Retailer inventory falls.)

Calculation is simple: the first term is just the POS volume, the second the difference between shipment volume and POS volume for the period.

Note: In reality, we may need to make some allowances for other sources of consumption (theft, damage, loss) or procurement (returns, diverting) but we'll ignore these for now.

Here is the result graphically.  I have laid it out in exactly the same way as the POS Sales Variance Analysis.

At the top level it does not look too bad, a year on year loss of 1.7%.  In reality this is the result of:

• a significant loss of POS volume (9.2%)
• a significant increase in retailer inventory (6.4%)
• a small retailer cost increase. (1.4%)
• Action: retailer inventory cannot continue to increase.  Unless POS volume is restored prepare for a continuing 9% loss in volume and 7.6% (9-1.4) loss in revenue

We could take this further of course and split the POS Volume component into two parts: one driven by velocity changes and one by distribution.  My preference would be to display these 2 charts side by side for each division,  category or brand using XLReportGrids.

I think this is a big improvement but it's still Analytics-Lite.  We have a much clearer idea of WHAT happened but leave it to the reader to add WHY it happened.   Predictive Analytics can help us get much further down that path.

This should make preparing for those weekly or monthly sales meetings go a little easier :-)  Of course if your numbers aren't meeting plan, you're on your own !  

If you would like the Excel file that shows these calculations you can download it here or just drop me an email