A reader recently asked if we could recommend a reference document resource that contains a comprehensive list of calculations or formulas used in CPG. While our Glossary currently contains over 75 terms and abbreviations (many with calculations), we are considering expanding our glossary scope beyond syndicated data to cover other types of CPG metrics. We would like to produce a stand-alone file that could be downloaded for offline referencing.
Please share in the comments below (or you can reply to our subscription email if that’s where you are reading this):
- Your favorite calculations
- Anything that you are often asked to explain
- Anything that you never remember and always have to look up
Thanks in advance, dear readers!
CAGR
Formula for previous year when you have current year and % growth
Kelly, I always have the look up that second one myself! Thanks for the suggestions!!
Hi – Gross sales, Net sales, Trade funds, promotion ROI for CPG.
i agree cagr is often used at my company
Most definitely anything related to ACV
Thanks Scott! What aspects of ACV are most confusing?
Nearly all my clients prefer to talk about doors / stores selling because they feel so much more concrete than the ACV. I’m always fighting the good fight for ACV, but it remains hard to move non-data nerds to the measure.
Fair point! Maybe we should use both. Within a retailer, using units/store/week might be most helpful, since it’s more intuitive. But if we are comparing one retailer to another, looking at a measure like $/million can reveal strengths of a smaller retailer versus a larger one.
I used price per oz in beverage and meat snacks quite a bit. It allows you to know where your brands rank in terms of value.
Thanks Michael! Price per volume is an interesting topic because, in some databases, there is a not a good volume metric. I have a client where the volume is just units again which is not helpful. Do you get price per ounce from Nielsen/IRI or do you have to calculate it yourself?
Common Excel formulas, such as Index/Match, Sumifs, and Vlookup.
Good idea! I hadn’t thought about including Excel formulas but that makes perfect sense.
TechontheNet is an amazing source for that too! It doesn’t just provide definitions of how to use them, but also pictures with examples.
Thanks for the tip!
Great idea! Kelly already suggested the first two that came to mind. Another — outside syndicated — would be calculating Retail $ Sales when you’re given Cost $ and know (or estimate) Margin %.
Thanks! Formulas for integrating multiple data sources are always helpful. Appreciate the suggestion!
Hi, I’ve been working with IRI for a few moments, and now we’ve changed to Nielsen. Would like to know how are the facts/measures called in each platform. For instance, is the same USW than SSPD? Cheers
That’s a good idea!
Formula for margin and markup
Also promotional formulas like lift and subsidized sales
Love this site btw!
Thanks for the suggestions!
Base support and incremental lift
Thanks Manish!
Yes! Thank you for the suggestions.
Share/Market Impact and Price Mix were always confusing for me!
Thanks for the suggestions!
I second this!
The difference between increasing/decreasing % ACV and increasing/decreasing store doors (PODs). Just because a product lost store doors does not necessarily mean it also lost % ACV.
Explaining this concept (a product losing store doors but gaining in % ACV) has been a successful way for me to help people understand what % ACV actually means and how it can be applied.
If I am off-track in my explanation, I welcome any feedback!
Hmmm…I feel like the case of a product losing store doors but increasing its %ACV distribution would be very rare. Maybe if the stores themselves that kept their distribution were bigger over time (i.e. as measured by total ACV $). I’d love to see other examples of that happening – feel free to send a message via Contact and we can discuss further offline.
Love this idea! I often validate many of my formulas using this site.
Can you point me to any resources to understand how mix impacts the average price per category. For example, say I sell 3 items (equal vol of units) and the avg price is $10. But next quarter my mix changes to a 40/40/20 split and my avg price drops to $9.00. Trying to understand the contribution of each item to the $1.00 delta ($10-9). Thanks in advance.
Take a look at this post, where I say “At the category level, it never makes sense to look at price per unit. (Yes, I did say never! This is one of the few times I use that word with respect to data analysis.)” You need to know the before and after prices of all 3 items to do the calculation you’re asking about. Feel free to click here and send me actual data and I can show you how to do it.