When looking at your revenue variance, you want to have a **complete insight** into what’s driving the changes you are seeing. You’re probably dealing with questions like “Are my margins eroding?”, “What are the most profitable products in my portfolio?”, “How is my product mix affecting my revenue?”. To find the answers, we'll explore the **Price Volume Mix analysis** and show you how to do it in Excel.

The new Zebra BI for Office lets you create an impressive Price Volume Mix Variance analysis in just one simple click. Download the free template to get inspired or try the Excel add-in for free.

The PVM analysis adds another dimension to your business reporting and is a great way to **improve your understanding of your business**. Read on to learn all about it & ace your reporting, stat!

## Overview

In this article, we'll explore how you can improve your business reports by including a **Price Volume Mix analysis. **We'll explain the three concepts and demonstrate how to **prepare your data** for the analysis in Excel. Next, we’ll go on to show how to use Zebra BI for Office to **visualize this analysis**. Finally, we’ll discuss two different approaches to it and show which of the two delivers **better results**.

## Why a typical business report is not enough

Typically, when designing a business report, we take **revenue**, **gross profits**, **income,** and possibly some other KPIs, and compare current results to the ones from the previous year, plan, or another target. Once we have these variances, we can drill down deeper and explore them by business units, geographical areas, products,and so on. Most companies don’t go much further than that.

Let's take a look at a typical report.

This is a good start but there is a better way that delivers more insight. Namely, the **Price Volume Mix analysis **which demonstrates how individual factors, such as price changes, sales volumes and product mix affect 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. An increased price directly translates into improved margins. But keep in mind that a higher price may result in lower volumes, as fewer customers decide to buy higher-priced products.

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

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

## How product mix affects revenue

Then there’s this third mysterious category called **Mix**. It’s a bit vague, but it adds a very important and interesting insight. Mix is **not **about the prices and it's **not **about the volumes. It actually explains how deep the change in the structure of your products goes.

Mix– This concept 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.

It essentially reveals **how your product mix affects the revenue**. For example, are the products you’re selling of a higher value compared to last year’s? If the answer is yes, the mix will turn out to be positive. Are you selling more of your lower-value products? If so, this might be just what’s dragging your mix down.

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

Let's review what it shows.

The chart shows that a 3.2% growth in sales can be attributed to different reasons. We can take a look at what drove the change. Did we **increase **or **decrease **the price of the products over the past year and how did that drive the sales? Did it increase the volume or decrease it? Are we selling more items of the same product than last year or fewer? What is the total effect of the volume increases?

Once you get into Price Volume Mix variance analysis, you can really get creative. Instead of just analyzing the growth from the previous year, you can analyze the change in budget. Instead of using revenue, you can use your contribution margins or your gross profit, which will make the story even more powerful. **Using the profit, in particular, makes this analysis 10 times or 20 times more insightful**.

### Adding more dimensions

And there is no rule that says you have to stick with just these three categories, is there? After all, your business does have other drivers too. Two typical ones are the **new **and **discontinued products**. Did you launch new products last year? If so, you can **see the revenue that was achieved with just them**. You could also take a look at the **impact of the discontinued products**. Once you add all this to your analysis, you can get a much clearer picture of what impacts 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 the 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. Let’s take a look at how you can prepare and organize your data and do these calculations in **Excel**.

## 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 sub-products.

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 the previous year's** 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, let's call it "**The Mix Change Method**". Despite its popularity, we do believe there is a better way to tackle this analysis. However, this method is popular enough that we want to show you how to do it in Excel using Zebra BI for Office.

Let's start by looking at the data. The first column has **product groups**, followed by two columns with sales revenues from this and the previous years. 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, the volume represents the number of products bought by your customers, while the 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 the products we sold, meaning that one out of twenty products sold in our stores was baby food. This year, this percentage grew 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 the 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 Variance analysis

The method we just described works with most data sets, but we don't think it completely okay. We 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 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 our example, we're 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 the quantity of the 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 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 remember that your mix value should be zero at the base level.

Another very significant difference is in the Total row. Take the difference in the total quantity of products sold this year minus the quantity in the previous multiplied by the average price. We're not just adding up all the volume changes from our products and 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 the 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 for Office:

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 set it 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 Office, the add-in will automatically display such data 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 explains the movement for each group very clearly. 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.

Do you want to make your life easier and get a **free template** for creating next-level price-volume-mix-variance analysis in Excel with Zebra BI for Office? Download it now and save yourself hours of work.

## Related Resources

Power BI

Guide

November 30, 2022

###### Price Volume Mix Variance Analysis in Power BI

Find out how to perform a Price Volume Mix Analysis in Power BI to see how price, volume and product mix changes affect your revenue.

Excel

Guide

November 24, 2022

###### Excel Report Templates: Build Better Reports Faster

If you take just a couple of minutes, you canrethink your approachto creating and designing your business reports. In this article, you can find key Excel report templates for some of the most commonly used business reports.

Power BI

Guide

May 16, 2022

###### Top 9 Power BI Dashboard Examples

Get inspired with our actionable Power BI dashboard examples. Try them out using interactive embeds and download the PBIX files to make them your own.

Power BI

Guide

March 29, 2021

###### Power BI for Business Analysts: The top tools you need in your toolbox

Learn all about designing sales vs. plan performance dashboards, cost vs. budget analyses, income statements and forecasts, and more.

## FAQs

### How do you calculate price volume mix analysis? ›

The basic idea here is to calculate the average revenue per unit. You take the sum of your revenue for the 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.

**How do you calculate volume vs mix? ›**

**Volume Impact = Target Price * (Actual Volume – Target Volume)** **Mix Impact = (Actual Volume – Target Volume) * (Actual Price – Target Price)**

**How do you calculate price/volume trend in Excel? ›**

**VPT = Previous VPT + Volume x (Today's Closing Price – Previous Closing Price) / Previous Closing Price**. The idea behind the indicator is to multiply the market volume of a stock by the percentage change in its price.

**How do you calculate price per volume? ›**

Cost per unit of volume (cubic unit) can be obtained by **multiplying the dimensions (to get the volume of a rectangular parallelepiped) and dividing the result by cost of strip**. Of course, in the calculator below you can use any units, not only millimeters, as long as you enter all dimensions in the same units.

**What is the best way to calculate volume? ›**

To calculate the volume of a box, you need to know its height, width, and depth. You can find the volume by **multiplying these three dimensions together**.

**How do you calculate volume price and mix variance? ›**

The mix impact for individual products is calculated by using: **(Volume in FY01 – (Total Volume in FY02 * (FY01 Volume/Total FY01 Volume))) * Average price FY01**. The overall mix impact is calculated by taking the sum total of the mix impacts for each individual product.

**What is volume price analysis examples? ›**

Volume Price Analysis (VPA) is measured vertically and over a specific period. The most well-known example of VPA would be the **regular volume bars on a Japanese candlestick chart or American bar chart**. Another type of VPA would be the popular moving average known as the Volume Weighted Average Price (VWAP) indicator.

**How do you calculate price prediction in Excel? ›**

**On the Data tab, in the Forecast group, click Forecast Sheet**. In the Create Forecast Worksheet box, pick either a line chart or a column chart for the visual representation of the forecast. In the Forecast End box, pick an end date, and then click Create.

**How do you calculate mix effect? ›**

It is calculated as **the difference between the actual unit and actual unit at budget price multiplied by the budget price**. For example, if we calculate the mix-effect for any product where the actual unit is 30 and the actual unit at a budget price is 15, then: Mix effect on quantities= 30-15= 15 units.

**How do you calculate price formula? ›**

...

**The Basic Retail Price Formula**

- Retail Price = Cost of Goods + Markup.
- Markup = Retail Price – Cost of Goods.
- Cost of Goods = Retail Price – Markup.

### What is the price value formula? ›

Determine the total cost of all units purchased. Divide the total cost by the number of units purchased to get the cost price. Use the selling price formula to calculate the final price: **Selling Price = Cost Price + Profit Margin**.

**What is a price volume analysis? ›**

Volume analysis involves **examining relative or absolute changes in an asset's trading volume to make inferences about future price movements**. Volume can be an indicator of market strength, as rising markets on increasing volume are typically viewed as strong and healthy.

**How do you calculate price analysis? ›**

Determine the total cost of all units purchased. Divide the total cost by the number of units purchased to get the cost price. Use the selling price formula to calculate the final price: **Selling Price = Cost Price + Profit Margin**.

**What is the formula for calculating price level? ›**

The first step is to **multiply the base year basket quantities by the current year prices**. This provides the numerator for the price level equation, which represents the current year's prices for the base year's basket of goods and services. The base year basket quantities are then multiplied by the base year prices.

**How do you calculate sales mix CVP? ›**

Sales mix percentage is **the number of one product's sales divided by the number of total products sold**.

**What is basic pricing analysis? ›**

Price Analysis is the process of deciding if the asking price for a product or service is fair and reasonable, without examining the specific cost and profit calculations the vendor used in arriving at the price. It is basically a process of comparing the price with known indicators of reasonableness.

**How do you calculate comparing price? ›**

The only way to tell is to look at the unit price and compare. To compare prices, **divide the cost by the weight or quantity of the item**. Then you can compare the two items.

**How do you calculate average sales price in Excel? ›**

**Use AutoSum to quickly find the average**

- Click a cell below the column or to the right of the row of the numbers for which you want to find the average.
- On the HOME tab, click the arrow next to AutoSum > Average, and then press Enter.

**How do you calculate percentage change in price level? ›**

Calculate percent change by **subtracting the original price from the new price, divide that number by the original price, and then multiply by 100**.

**What is an example of price level? ›**

What is the definition of price level? The price level has a significant impact on the purchase of goods and services but also on the purchasing power of money. For instance, **if P is the amount of money required to buy a specified quantity of goods and services, then one dollar can buy 1/P**.

### How do you measure change in price? ›

**The Consumer Price Index (CPI)** measures the rate of price change of goods and services purchased by households. CPI is made up of a basket of goods and services that are chosen to be representative of the actual purchases made by households.

**Is CVP easy to calculate? ›**

**A CVP analysis keeps calculations simple** – but that means it has to make some assumptions upfront. For example, a CVP analysis assumes that all the units you produce will be sold and also assumes that your fixed and variable costs are constant.

**How do you calculate target profit in CVP analysis? ›**

**Cost-Volume-Profit Analysis**

- Multiply the expected number of units to be sold by their expected contribution margin to arrive at the total contribution margin for the period.
- Subtract the total amount of expected fixed cost for the period.
- The result is the target profit.

**What are the general rules in volume analysis? ›**

**Traditional rules for volume analysis:**

- If prices are up and volume and open interest are rising, the market is strong.
- If prices are up and volume and open interest are declining, the market is weak.
- If prices are down and volume and open interest are rising, the market is weak.

**What is price volume mix? ›**

Price effect refers to what happens when you apply higher- or lower-selling prices per unit; volume effect refers to the variation in the number of units sold; and the mix effect refers to the change in the mix of quantities sold — that is, the percent of units sold per reference over the total. Dayton Kellenberger.