Base Weighted Weeks (Part 2 of 2): What Will Happen to Sales If…

We’re delighted to once again have Mark Laceky, President of mLogic Consulting as guest blogger.  mLogic’s previous post explained one of their favorite measures, Base Weighted Weeks (BWW).  This follow-up post gives a concrete example of how to use BWW and includes an Excel-based tool that you can download and use yourself.

Keep in mind that this post falls into the “Advanced” category!  If you are fairly new to the CPG industry and/or to syndicated POS data, then you may want to check out some of our other posts.  If you have any questions on this specific post or the Excel file, PLEASE contact mLogic directly via email at info@mlogicconsulting.com.  And now, on with the show!

The Only Constant Is Change

As any Marketer and Analyst can tell you, plans change.  They change all the time.  And the people who change them want to know (yesterday) what the sales impacts are likely to be based on those changes.  Or worse – they have 18 ideas about changes they might want to make and they want someone (you) to estimate the impacts of each.  What if we raise price and spend back?  What if our trade budgets get cut 20%?  What if we add a feature week?  What if we promote with deeper discounts?  How much would it drive sales if we got more displays?

If you’re the person responsible for providing answers/estimates on the fly you’ve probably devised some sort of spreadsheet to help you do the calculations, right?  It has some syndicated POS data in it (baselines and base prices? incremental volumes and promoted prices? current distribution?  category trend?).  And then there are all those other business drivers that you can’t get from your syndicated POS Nielsen or IRI database – consumer promotion, advertising, package changes, etc.  Of course, there are also lots of assumptions that make the whole thing work, right?

We can’t help you estimate the impact of changes to all of the drivers, but we can help you with the price and promotion end of things – using data that is available in your Nielsen or IRI database.  (Note:  If the mention of business drivers sounds familiar, you may have read about them in the series of posts on Volume Decomposition in the CPG Data Tip Sheet.  This first of the 6 posts gives an overview of how to attribute past sales to the various business drivers and Part 6 talks about the “other drivers.”  While a volume decomp analysis looks backwards to explain what happened, the methodology and tool in this post looks into the future to estimate what is likely to happen using similar concepts.)

Bringing It All Together – A Unified Approach To Estimating Sales Impacts

Our previous article reviewed Base Weighted Weeks (BWW) as a better measure of promotion frequency with the added benefit that all the math works out to the last decimal point.  This article (and the attached “prize inside”) will show you how to use BWW and some other concepts together to create a simple but powerful sales estimation tool to make your life easier (part of it anyway).  CAVEAT: this approach is recommended only when you don’t have more powerful tools on hand (such as price/promotion models, marketing mix models, etc).

CLICK HERE to download the pre-built Excel tool that we call Impact EstimatorIt gets you from data pull to simulator and does all the math for you.  Of course, if you’re curious about the formulas you can explore the tool.  But for lighter reading, here are the key concepts:

  • There is a standard POS data pull template you should start using (1. Data Pull tab)
    1. It has everything in it so you don’t have to keep going back to the well – pull data just once!
    2. Link all calculations in the other 2 tabs to this raw data pull to build your Impact Estimator
  • Break all business driver inputs into individual pieces that are multiplicative! That is, turn each individual component into an impact index, then multiply all the indices together to get the net impact.
  • Start with baseline volume drivers then move to incremental volume drivers.
  • Baseline volume drivers are anything not related to in-store trade promotions.  Base drivers include base price changes, distribution changes, changes to advertising, changes to consumer promotion, category trends, changes in competitive impacts, etc.
    1. You’re on your own estimating some of these impacts – the secret is to estimate them one by one. You can go back later and change your assumptions if you like.
    2. Example: category is growing at 4% so our brand will likely grow at 4% or an index of 1.04.
  • There are three incremental volume drivers – promotion frequencies, mix by promotion type and discount levels.  The tool automatically fills in the current BWWs and discounts for each of the mutually exclusive and additive promotion types and the user then inputs changes:
    1. TPR (a temporary price reduction with no ad and no display present)
    2. Feature Ad w/o Display (a feature ad with no display present)
    3. Display w/o Feature Ad (a display with no feature ad present)
    4. Feature & Display (a feature ad with a display present)
  • The Impact Estimator tool automatically calculates a Promoted Price Elasticity (PPE) based on the % lift and % discount on TPRs and then calculates multipliers for ads and displays. The user then can input new promoted discounts and watch volumes change accordingly

Example: Geography A, Brand X, Category Z, 1-Year

Note that the tables shown below are all in the Simulator tab in the Impact Estimator Excel tool.  Starting with Baseline Volume Drivers, the user enters inputs into yellow-shaded cells (rows 9-14):

In this example, we are assuming the category volume trend will be -1.3%, Brand X base price/EQ will go up +5.0%, % ACV Distribution will drop 5% (not points), average items carried will be up +10% and competitors will be doing something (trade promotion? distribution gain? advertising?) that will cause Brand X to lose 1% of its volume.

As noted in the tool, there are some assumptions being made:

  1. Base Price Elasticity (BPE) is a simple estimate of ¾ of the Promoted Price Elasticity (PPE) – this is a general rule of thumb.  If you have a BPE from previous research use that instead.
  2. For % ACV Distribution we are assuming an 85% incrementality rule – that is, if we get 5% less distribution we’ll only see a 4.25% loss in volume (due to reduced cross-store cannibalization and assuming that stores that drop distribution don’t sell as fast anyway).
  3. For average items carried we’re assuming a 20% incrementality rule.  That is, getting 10% more items on shelf (e.g. going from 10 to 11 where carried) will only increase volume by 2% due to the new item being a slower mover and cannibalizing from existing items on shelf.

The net result is a Baseline Volume % Change estimate of -9.8% (in cell I15).

Then we move on to the Incremental Volume (Rows 23-26).  The starting rule is that (with no changes to the trade promotion plan) incremental volume would be down the same % as base volume (in this case -9.8%).  Then we move to changing the trade promotion plan:

In this example, we’ve increased total promotion frequency from 17.0 BWW to 19.0 BWW by increasing the frequency of each of the four promotion types.  We also increased the discount levels slightly for each promotion type.  The result is that our incremental volume should go up by +14.1% (cell I30) instead of being down -9.8% due to the baseline dropping.

The net result (rows 32-39) of all the changes is that Total EQ Volume should drop -5.6%.  And since the Average Price/EQ will only be up 2.2%, we would expect our dollar sales to be down -3.5%.

The Upshot?

This article and tool are meant to be useful by highlighting some key concepts and then bringing them all to bear inside a unified approach.  Key ideas are:

  • The right data pull means you only have to do it once
  • Always handle base drivers separately from incremental drivers
  • Break all of your assumptions into manageable, multiplicative pieces
  • Promotion changes are very manageable using BWW, PPE and multipliers – which in turn are easy to glean from the data

We hope some or all of these concepts make it into your own sales estimation tool!

Please direct comments and questions about this article directly to Mark.  His contact details are below.

Mark Laceky is President and founder of mLogic Consulting, Inc. which specializes in consumer packaged goods (CPG) pricing and trade promotion strategy. He previously managed the Nielsen North America Price & Promotion analytics practice and spent years in analytical leadership roles at Kraft Foods and The Quaker Oats Company. He may be reached via LinkedIn or by email at info@mlogicconsuting.com.

Print Friendly, PDF & Email

CPG Data Tip Sheet’s Favorite Things

Tip Sheet readers frequently write us and ask for suggestions on how to find training classes, what reading we recommend, and what other resources are out there to help them on their path to CPG Data Guru status. Here are our recommendations. We … [Continue reading]

Base Weighted Weeks (Part 1 of 2): How Much Trade Promotion Support Are You Really Getting?

We’re delighted to have guest contributors Mark Laceky (President) and Mike Fridholm (SVP, Client Service) from mLogic Consulting introducing one of their favorite measures.  Keep in mind that this article is most relevant for use in more strategic … [Continue reading]

How To Get More From TDP, Part 3: Sales Productivity

Hello, fellow analysts! Ready to add another tool to your kit? This is the third and final part of my series on how to use TDP to enhance your work. If you are new here, and not yet familiar with TDP, start with this basic introduction or my first … [Continue reading]

Enhancing Your Analysis with TDP, Part 2: Explaining Sales Trends

In a recent post, I illustrated the most common usage of Total Points of Distribution (TDP) - as a substitute for % ACV in velocity calculations. In this post, I’ll share another one of my favorite ways to leverage the TDP data metric, which is … [Continue reading]