Has this happened to you? Sales of your brand are down vs. the same period last year and management wants to know why. Sales is saying it’s because there is less advertising this year but Marketing is saying it’s because you’ve lost distribution and the retail price and merchandising are not as competitive. Another, more pleasant, scenario is that sales are up and Marketing says it’s because of the addition of more advertising this year but Sales says it’s coming from distribution gains and lower pricing. *How do you know what the real answer is?*

Since there are many different business drivers that impact sales, the challenge is to estimate how much of the volume change was due to any number of things that were taking place at the same time. In fact, it is often things that are happening in the store at the point of purchase that have the biggest and most immediate impact on sales. Fortunately, your IRI/Nielsen database has the data to enable you to look at those retail drivers. By accounting for as many business drivers as possible, you can get to a decent answer to the question of why sales are up or down. This post is the first in a series on decomposing a change in volume into its component business drivers. Future posts will address specific drivers and how to quantify the impact of them on the business.

**Due-To Analysis, aka Volume Decomp**

An analysis like this is often called a “due-to,” because it tells you how much of the sales change is ** due to** each of the drivers. It is also known as a

**(short for volume decomposition) or**

*volume decomp**(since it bridges the volume from one period to another). Many of the largest CPG companies have tools from IRI/Nielsen that do this analysis automatically, but smaller companies may not. Find out if your IRI or Nielsen contract includes a volume decomp tool. If it doesn’t, it’s usually because of budget constraints but the good news is that you can conduct the analyses described in this and future posts to get to a good approximation of what is driving your business.*

**volume bridge**First up, what do you want to explain? See this post about the 3 ways to measure sales – dollars, units and equivalized volume (EQ). I find it best to use a due-to explain the change in physical volume and *not* dollar sales. That way you can show how much a change in pricing affected the physical volume. You may want to look at the overall dollar impact as well but, for most manufacturers, there are serious operational and cost implications to changes in volume so it’s important to understand and anticipate changes in volume. If your brand is comprised of multiple sizes, then EQ volume is the best measure to use for this. And you can also do this analysis for the category or your competition.

__Business Drivers and Elasticity__

You can think of the business drivers in a due-to as falling into a few different buckets and you have the data needed to address the first 4 buckets right in your IRI/Nielsen database:

- Distribution
- Pricing
- Merchandising
- Competition
- All Other

Everything else *not* available in your IRI/Nielsen database that drives the business falls into All Other. This bucket can have some combination of advertising, consumer promotion, shopper marketing, overall economic conditions, weather and anything else not already mentioned. You may be able to include some of the All Other drivers in your analysis, if the data is easily available at the right levels of geography and time.

In addition to having data for the drivers, you need an **elasticity** for each driver. Think of an elasticity as how much a change in the driver results in a change in volume. If the elasticity is 1.0, then a 5% increase in the driver results in a 5% increase in volume. If the elasticity is 0.8, then a 5% increase in the driver results in a 4% increase in volume (0.8 * 5%). If the elasticity is 1.2, then a 5% increase in the driver results in a 6% increase in volume (1.2 * 5%). The elasticity is positive if the driver and volume move together or negative if they move in opposite directions and the sign should make sense in real life. For example, if distribution goes up, volume also goes up so the distribution elasticity will be positive. Price elasticity, on the other hand, is negative because if price goes *up* we expect volume to go *down*. Elasticities can be determined in different ways and I won’t go into all the analytics behind calculating an elasticity here. Future posts will talk about where to get or how to estimate the elasticity for each driver.

__Due-To Analysis__

A due-to starts by looking at changes in volume from one period to another, listing factors that might have driven those changes, and gathering data about how those factors have changed. It can be presented as a table and/or in graphical form.

The table below shows that Magnificent Muffin volume is up +2.5% vs. year ago in the Total US Food channel and how much each of the drivers themselves have changed over that same period. With this information compiled, you are ready to work on that last column, the Due-To % chg.

*Due-To % chg = the % change in volume due to that driver. The sum of the Due-To % chg across all drivers = the % chg vs. year ago for volume. The Due-To % chg for All Other Drivers is the difference between the % chg vs. year ago for volume and the sum of all the other due-to % chg values.

This type of analysis is often presented in a “waterfall” chart that starts with the year ago volume on the far left, then shows the amount of volume change due to each driver and ends with the volume in the current period. Negative volume drivers are in red and positive volume drivers are in green. You can see in this chart that the increase in price and an increase in competitive volume resulted in volume *declines,* while an increase in distribution and net increase in merchandising across tactics resulted in volume *gains* for Magnificent Muffins. All Other drivers also contributed to volume gains – this is where more/better advertising, consumer promotion and/or shopper marketing shows up in the due-to.

In these other posts I talk about how to change the ? in the table into due-to % chg numbers and how to quantify the volume impact of each driver, as depicted in the waterfall chart.

__Part 2__ – Impact of Distribution

__Part 3__ – Impact of Pricing

__Part 4__ – Impact of Trade

Part 5 – Impact of Competition

And then the final post in the series, Part 6 – Impact of Everything Else

Did you find this article useful? How do you conduct or present a volume decomp and how is it different than this? Please share in the comments below. Subscribe to CPG Data Tip Sheet to get future posts delivered to your email in-box. We publish articles about once a month. We will not share your email address with anyone.

What does CWW stand for? I’ve been using IRI and Nielsen data for over 20 years and have never seen this abbreviation. I’m used to seeing it written as Average Weekly % ACV Feature & Display (or just %ACV F&D implying average weekly).

CWW stands for “cumulative weighted weeks” and is a measure of the amount of merchandising received. It takes into account both reach and frequency of support. The %ACV measures you mention do not take into account the frequency. See this entry in the Glossary on the blog. I’m not sure if it is available on syndicated databases from IRI/Nielsen but I have seen it on custom DBs at many different companies.

Hope that helps!

Hi

I need to create a water fall chart which moves from budget to actual and the movement is because of 5 products of the company. Within the five products there is price and volume variance. Sample data is as below

Budget 1000 Actual 2000

Variance due to Product A (+1800 ) = Price variance (+1500)and Volume variance ( +300)

Variance due to Product B (+200 ) = Price variance (-500)and Volume variance ( +300)

Can i please get the template for the waterfall chart

Pls read the actual as 3000 and not 2000

As always, great and always timely post. Would you happen to have an Excel download of the cool table and/or the waterfall chart that you can share?

Here is a link to the instructions I used to create the waterfall chart:

https://www.ablebits.com/office-addins-blog/2014/07/25/waterfall-chart-in-excel/

The hardest part (which is not hard, just a little tedious) is changing the color of the individual bars and moving the labels with the values above or below each bar.

Hope this helps!

Thanks for this great article. How do you calculate the due-to % chg numbers and how to quantify the volume impact of each driver?

Thanks for asking! The next few posts will be about how to calculate those things. The first one will be posted in the next day or two and is about Distribution. Stay tuned…

Excellent article and I can’t wait for the whole series to be released. Could you elaborate on why you are using Volume and not $ value? Thanks in advance!

Other people have asked the same question about why I used volume and not dollars on the volume decomp, so it’s a popular topic. In order to keep the whole thing somewhat simple, I focused on volume.

A similar analysis can also be done on dollar sales but then you need to explicitly account for 2 different impacts of a price change. Assuming that price has increased, then you need to calculate:

1. how much volume declined because price went up

and

2. how much dollar sales increased on the volume that did sell at the higher price.

When price elasticity is -1.0 or less (which is virtually always!), the net of the lower volume but higher revenue is almost always lower total revenue.

Hope that helps!

Hi, I use a database that has some limitations. The data is bi-monthly (one data point for two months combined i.e. Jun/Jul 16 etc.,). I don’t have a measure for “Any Promo Distribution”. There is also no baseline / incremental information. Would I still be able to do a rough version of this analysis? what are the compromises I can make?

What you can include in this type of analysis does depend on what facts you have available on your database. Even with bi-monthly data you could probably include price and distribution (and also competition – post on that coming soon), plus All Other. If you want to discuss further, please use the Contact Us form and we can set up a phone call.

Thanks for the great article. I can`t find the article about impact of competition. Did you post it or it`s in future plans?

The post on including competition in the volume decomp should be posted sometime this month. Thanks for asking!

Hi Robin, I’m curious if you have any materials/links to explain how to calculate the elasticities for each driver in the analysis? Is it as simple as the delta in demand over delta in driver?

If you take a look at each of the 5 posts in this series you’ll see that I explain where the elasticity comes from for each driver. Unfortunately the calculations are not as simple as you proposed. If you just calculated change in sales divided by change in each driver, there would be double-counting. The main point of doing a volume decomposition is to control for all the drivers happening together and not attribute the entire volume change to any one driver. Hope this helps!

Thank you for the reply! That makes sense since your using the change in volume in more than one elasticity calculation. Thank you for these amazing posts!

Hi Robin,

Is this effective looking at a single retailer? I would like to do a Due-to looking at only one major retailer, this won’t throw off the CWW will it?

Thanks!

TJ

Yes! You can do this for any level of geography. In fact there is usually less “Unexplained” at the lowest level of one retailer in one market. CWW and all other measures should be fine.

Your blog is awesome. Is there a reason you don’t try to include velocity as a driver in the decomp? Is it because it is so highly correlated with other drivers?

Thanks for the compliment! We’re glad you’re finding the blog useful. I don’t include velocity in the decomp because it would be double counting. All the other drivers

exceptdistribution are what drive velocity so you can include either velocity or the other drivers but not both. If you want to know the impact of the change in velocity, it’s just the sum of the impacts of all drivers excluding distribution. Hope that helps!