Price Volume Mix Analysis: how to do it in Power BI and Excel

When looking at your revenue variance, you want to have complete insight into what drove the changes you are seeing. You have questions, such as: Are my margins eroding? What are the most profitable products in my portfolio? How is my product mix affecting my revenue? To find answers, we’ll explore Price Volume Mix analysis and show how you can do it in either Excel or Power BI.

PVM analysis is a great way to improve your understanding of your business. It adds another dimension to your business reporting.

However, it is a topic that many users struggle with.

Overview

In this article we’ll explore how you can improve your business dashboards by including a Price Volume Mix analysis.

We will explain the three concepts and show how to prepare your data for analysis in Excel and Power BI.

Next, we will show you how to use Zebra BI visuals to visualize this analysis.

We will also discuss two different approaches to analysis and show you which will deliver better results.

Why a typical business dashboard is not enough

Typically, when designing a business dashboard, we take revenue, gross profits, income or another KPI and compare current results to previous year, plan or another target. Once we have that variance we can drill down deeper: explore variances by business units, geography, products and so on. Most companies do not go much further than that.

Let’s take a look at a typical dashboard.

This is a good start but there is a better way that delivers more insight. That is the Price Volume Mix analysis, where we can see how individual factors, such as price changes, sales volumes and product mix affected your revenue.

Price — This is the simplest concept to understand. Price simply reflects the price of your product as you sell it. It is the main contributor to the growth of margins in your business. Increased price directly translates to improved margins. But keep in mind that higher price may result in lower volumes as fewer customers decide to buy.

This is an important way of looking at your business. Let’s take price — it is one of the key factors affecting your growth and performance as a company. The relation is pretty straightforward — increased price usually translates to improved performance. Volume is another factor driving your company’s growth. Growth in volume normally correlates to better performance unless offset by something else.

Volume — This is the number of products you sell. Selling more products at the same price means more revenue. However, volume has little effect on your profit margins. Selling more products at lower prices reduces your profitability if the cost of goods remains unchanged.

Take a look at how Zebra BI shows you this data:

Let’s review what it shows.

The chart shows that 5.5% growth in sales has different sources. We can look at what drove the change. Did we increase or decrease the price of products in the past year and how did that drive the sales? Did it increase the volume or depress it? Are we selling more items of the same product than last year or less? What is the total effect of the volume increases?

How product mix affects revenue

Then there’s this third mysterious category called “ the Mix”. It’s a bit vague but it adds a very important and interesting insight. The mix is not about the prices and it’s not about the volumes. It does actually explain how deep the change in the structure of your products is. It essentially explains how your product mix affects the revenue. For example, are you selling higher value products this year than the last year? Then the mix will be positive. Are you selling more of your less valuable products? That might drag your mix down.

Mix — This reflects the fact that not all products are created equal. Some products have better profit margins than others, which means that changes in your product mix will affect your revenue. Selling more products with better profit margins drives up the revenue and vice versa.

Once you get into Price Volume Mix variance analysis, you can get really creative with many options. Instead of just analyzing the growth from previous year, you can analyze the change from the budget. Instead of using revenue, you can use your contribution margins or your gross profit which will make the story even more powerful. Particularly using the profit makes this analysis 10 times or 20 times more valuable to your insight.

Adding more dimensions

And there is no rule that says you have to stick with just these three categories because your business has other drivers, is there? Two typical ones are new and discontinued products. Have you launched new products which didn’t sell last year? Of course, this will grow your business. This means that you can identify the revenue that was achieved with new products. You could also look at the impact of discontinued products. Once you add all this to your analysis you are starting to get a clearer picture what impact your sales.

This is a great example of how powerful this type of analysis can be. At a glance you can see whether new products are offsetting the revenue lost from discontinued products. You can see the movements in your prices and product volume while also keeping your finger on the pulse of the performance of your product mix.

It is clear that Price Volume Mix variance analysis should become an essential tool in your reporting belt. You can use it for ad-hoc analyses or make it a regular part of your quarterly or annual reports. This post will look at how you can do these calculations in Excel or Power BI, how to prepare your data and how to organize it.

Price Volume Mix analysis in Excel

Let’s start by explaining what you actually need to create your first Price Volume Mix variance analysis. The bare minimum you need is data by products — this can be products at the most basic levels like SKUs for each and every product, product groups or even more sophisticated hierarchies with subproducts. You also need revenue for your current and previous year as well as quantities, which is simply the number of items sold in the current and previous year. You could also replace previous year data with your plans, even though many people do not plan product quantities.

Your data may vary, depending on where you exported it from.

The popular approach — The Mix Change Method

First, we’ll look at a method for doing Price Volume Mix variance analysis that is very popular online and that I call “ The Mix Change Method”. Despite its popularity, I do believe there is a better way to tackle this analysis. However, this method is popular enough that I want to show you how to do it in Excel using Zebra BI Excel Add-in.

Let’s start by looking at the data. The first column has product groups, followed by two columns with sales revenues from this and previous year. The next two columns show quantities for this and previous years, followed by price. In this case, price is a bit of a misnomer, because it is actually just revenue per unit. Here are the calculations for the Price PY and Price AC columns:

Price PY = Revenue PY / Quantity PY
Price AC = Revenue AC / Quantity AC

You also need to make sure you don’t calculate the total for prices as the average of all totals in the column. Instead, just divide your total revenue with your total quantity. You now have everything necessary to calculate the price change, which is simply the difference between the two prices (AC and PY), multiplied by the number of units sold in this year.

Looking at the example above, we can see the baby food prices went up while we are taking a large hit on the baked goods category because of declining prices. To calculate the total, you just need to add up all the values in the entire Price column and you get the overall impact of prices on your revenues. In our example, reduced prices in several categories resulted in a severe drop in revenues because of pricing.

Next up is the volume change and the trick here is to separate the volume effect from the mix effect. Put very simply, volume represents the number of products bought by your customers, while mix is that volume expressed in percentage.

Here are the formulas for calculating the Mix:

Mix PY = Quantity PY / SUM(Quantity PY)
Mix AC = Quantity AC / SUM(Quantity AC)

Our table shows that baby food represented 5.1% of the entire volume of products we sold, meaning that one out of twenty products sold in our stores was baby food. This year, this percentage gre to 5.4%, meaning we are selling more baby food. This can be either good or bad. If baby food is a profitable product, that is good news. However, if this is a less profitable product, this could have a negative impact. Imagine, you are selling more of your loss leader. That would have a serious impact on your bottom line.

Here is the formula to calculate the impact of the product mix on total revenue. First, calculate the change in the mix share ( Mix AC — Mix PY). For baby food in our example, the change in portfolio share is 0.26%. The final calculation seems fairly complicated and reads as follows:

Mix = SUM(Quantity AC) / (Price PY - Price PY / SUM(Price PY)) * Mix Change

The basic idea here is to calculate the average revenue per unit. You take the sum of your revenue for previous year. And then you take the quantity of products sold this year and divide it by the difference in the price of each product minus this average price. So basically, just think of it as taking the average price and then looking at whether the price of the baby food is higher than this average price?

In our example, baby food is priced higher than the average and increasing the volume will grow our revenue. On the other hand, the baked goods category is priced below our average, which means increasing the volume will get us a negative effect.

A better method for Price Volume Mix analysis

The method we just described works with most data sets, but I think it’s not completely okay. I would like to talk about another way, which uses a different method for calculating the mix variance as simply a subtraction at the end of the process. This is a method recommended by Controller Akademie.

Here is the dataset we will be using.

As you can see, we introduced two new categories reflecting the product status:

New products are products that are only sold in this year and can’t be compared between years.

Discontinued products have not contributed to this year’s results because they stopped being sold before or during this year.

In my example, I’m simply checking whether revenue was first generated this year (for new products) or last generated last year (discontinued products).

The first change in how we calculate everything comes with volume change. First, we check a product is active so we can exclude the new and discontinued products, which are not included under volume. We take the quantity this year minus quantity previous year and multiply the result by the average revenue per unit from the previous year. Here’s the formula:

Vol = IF(Status = "Active"; (Quantity AC - Quantity PY) * Price PY)

The thing that is the most obviously different from the previous example is that the mix value is zero and will always be zero at the individual product or SKU level. Product mix means the change in the products within a certain group or in your total product portfolio. This is something most people get wrong and is slightly confusing? You should keep in mind that at the base level, your mix value should be zero.

Another very significant difference is in the Total row. Take the difference in the total quantity of products sold this year minus quantity in the previous multiplied with the average price. We’re not just adding up all the volume changes from our products and are are instead doing the calculation on the level of the group. This is what actually ensures that you are excluding the effect of the prices in the volume of variance.

This approach results in a slightly different volume change on the level of group and that is why this then appears in your mix. The mix is then simply: the total variance, minus price variance, minus volume variance, minus new products, minus discontinued products.

Visualizing Price Volume Mix data in Excel

Here is our data shown in Excel with Zebra BI:

When creating this type of chart, make sure you turn on the difference highlight, showing the actual AC and PY revenue difference. This is a very meaningful visual emphasis that will definitely help you understand your data.

Once you have this set up, you can also use this data on each product level.

You take the basic data for the previous year, price change, volume and mix and do an analysis for all product groups at once. If you’re using Zebra BI for Excel, you should bring everything to a single table with small multiples. There is simply no better way to understand what’s going on in your business at a single glance.

This Price Volume Mix analysis gives you an insightful overview. It shows you what is going with the fruits and vegetables, for example. Are the prices going down or are we selling more? What is going on with our product mix and are we pushing more profitable products? What is pushing a certain product category forward? These are the types of questions you can now answer.

Price Volume Mix analysis in Power BI

Again, we first need to prepare data. Here is the table, imported from Excel, although you can import it from other sources.

An important thing to do here is to separate your products from this table, especially if you have a sales table with all columns in a single table. An easy way to do this in Power Query is to create a reference to your sales table, rename it and remove all columns but the ones containing products and product groups to create the dimension of products.

When you return to your sales model, you will have your sales table along with a separate product table. Then you need to make sure that the link relationship is one to many. This is how it should look in the end.

Setting up Price, Volume and Mix variance measures

Once we have our separate products table, we have something to work with. In the Fields pane, you have Quantity and Revenue for the current and previous year. Now you can start calculating your Price field, meaning your average revenue per unit. That is very easy to do. Just divide revenue by quantity and add “zero” at the end. Or — even better — use the blank function here for handling the division by zero.

Price AC = DIVIDE([Revenue AC], [Quantity AC], BLANK())

Next step is again calculating the Price change field, which is just the difference between actual and previous year’s price.

The Price variance gets a bit trickier. Let’s take a look at the formula and then go through it step by step.

∆Price = SUMX(Products, IF([Price PY] * [Price AC] <> 0,[Price change] * [Quantity AC],0))

This formula takes the price change for every product and multiplies it by the actual quantity. The IF statement is used to check for active products only and exclude any new or discontinued products. Then we use the SUMX to aggregate everything from the product level to all of the hierarchies above that. That is why SUMX is used here with the products table and why it is so important to have a completely separate table for products.

After this, the Volume variance is very straightforward.

∆Volume = IF([Quantity PY] * [Quantity AC] <> 0, ([Quantity AC] - [Quantity PY]) * [Price PY])

For the volume variance, calculate the change in the quantity and multiply it with the price for the previous year.

To calculate the Mix variance, you need to essentially replicate what you did in Excel.

∆Mix = ([∆PY]) - ([∆Volume] + [∆Price] + [New] + [Discontinued])

After calculating the total variance by subtracting previous year’s revenue from this year’s revenue, you simply subtract everything. Subtract the volume change, price change and new and discontinued products. This provides you with your Mix variance.

Creating the Price Volume Mix analysis visualization

Now is a good time to use Zebra BI visuals for Power BI to visualize your data. However, before you start that, here is a simple trick to make it all easier. As you work with your data, you end up with many separate measures. However, for charts and tables and so on, you can make it simpler by collecting the measures in a single category.

You do this by creating a separate disconnected table, which I named PVM. This simple table is not connected to any data and is just a list of values.

Table contains short and long names of measures and a SWITCH statement is used to transfer the value from those measures into this table.

Revenue by PVM = SWITCH(MAX(PVM[ID]), 1, [Revenue PY], 2, [∆Price], 3, [∆Volume], 4, [∆Mix], 5, [New], 6, [Discontinued], 7, [Revenue AC])

In this case, the values are transferred from the actual measures. This is just the way to unpivot all the measures and put them into one dimension for easier handling.

This allows you to take a Zebra BI chart and simply take the Category measure from the PVM table and drop it onto the Category field in your Visualization pane.

This puts all categories on the axis. The only way for this visualization to work, you need to use a waterfall chart. Which is very easy to do — simply add a Zebra BI Charts visual, select Revenue by PVM measure and add the Category measure to the Category field. All that’s left is right-clicking on Revenue AC and Revenue PY column headings to mark these values as Results.

If you are using subtotals, you can easily just mark any additional columns as subtotals. This will help you in your analysis.

You could also easily create Small multiples by dropping the Product group measure into the group field.

This delivers a great-looking view with small multiples by product groups.

Start using Price Volume Mix analysis

Price Volume Mix analysis is a powerful tool that you can expand with various additional data points to look at the factors that affect your revenues.

Instead of going blind into your planning and analysis, you can use this tool to pinpoint your key issues and your key opportunities for improving revenues and profit margins.

By understanding what drives your revenues and profit margins, you can quickly address deteriorating margins and focus your efforts on the products that matter.

Originally published at https://zebrabi.com on December 3, 2020.