The analysis of the price and volume changes is a critical component in business. It allows businessmen to analyze the current year’s sales based on the previous one and also predict future sales. In this article, we will show how to do price volume variance analysis in *Excel*.

## How to Do Price Volume Variance Analysis in Excel: Step-by-Step Procedures

In this article, we will discuss price volume variance analysis in * Excel* in an exhaustive way. The variance in price and volume can be analyzed for the previous year and current year and also for the current year and the next year. In this article, we will discuss the current year’s price volume analysis based on the previous year.

### Step 1: Calculating Total Numbers of Quantity Sold

In this step, we will calculate the number of goods sold for the current year and the previous year. We will use *the SUM function* to do so.

- To begin with, select the
cell and write down the following formula,**C11**

`=SUM(C5:C10)`

- Then, hit
**Enter**.

- As a result, we will have the sum of the goods sold for the previous year.
- Then, select the
cell and write the formula below,**D11**

`=SUM(D5:D10)`

- Hit
.**Enter**

- As a result, we will get the quantity sold for the current year.

**Read More: **How to Calculate Variance of Stock Returns in Excel

### Step 2: Determining Total Revenue

In price volume variance analysis, it is important to know the total revenue. Here, we will calculate the total revenue of the last year and the current year. We will use * the SUM function* to do it.

- Firstly, choose the
cell and write the following,**E11**

`=SUM(E5:E10)`

- Then, press
.**Enter**

- Consequently, we will get the total revenue for the past year.
- Again, select the
cell and write,**F11**

`=SUM(F5:F10)`

- Finally, hit
to get the total revenue for this year.**Enter**

**Read More: **How to Create Minimum Variance Portfolio in Excel

### Step 3: Evaluating Revenue Per Unit

In this step, we will calculate the revenue per unit sold for a particular year. It is an important parameter to analyze the price volume variance in * Excel*. This is also called price.

**Revenue Per Unit = Revenue of the product/ Unit sold of that product**

- Choose the
cell and enter the following formula,**G5**

`=E5/C5`

- Hit
.**Enter**

- Consequently, we will have the price for the first product.
- Lower the cursor down to
**autofill**the answers.

- After that, click on the
cell and type,**H5**

`=F5/D5`

- Then, hit the
button.**Enter** - As a result, we will get the price for the current year.
- Finally, move the cursor down to the last cell to autofill the cells.

**Read More: **How to Calculate Schedule Variance Using Excel Formula

### Step 4: Calculating Average Price

The average price of products is a crucial parameter for understanding price volume variance analysis in * Excel*. This is the total revenue per year divided by the total quantities sold per year.

**Average Price = Total revenue/Total quantities sold**

We will calculate the average price for both years.

- Firstly, select the
cell and enter the following,**C13**

`=E11/C11`

- Then, hit the
button.**Enter**

- Consequently, we will get the average price for the last year.
- Again, select the
cell and type,**C14**

`=F11/D11`

- Then, hit
to get the average price for the current year.**Enter**

**Read More: **Budget vs Actual Variance Formula in Excel

### Step 5: Determining Mix

Mix is the percentage of a particular good sold in total goods sold. This is formulated as quantities sold for a particular product divided by the total goods sold in that year.

**Mix = (Quantities of a particular good sold/Total goods sold)*100**

- To start with, choose the
cell and write the following,**I5**

`=C5/$C$11`

- Press
.**Enter**

- Consequently, we will have the mix for that year.
- Move the cursor down to the last cell to autofill.

- Repeat the same process for the current year with the formula in the
cell being,**J5**

`=D5/$D$11`

**Read More:** How to Calculate Portfolio Variance in Excel

### Step 6: Calculating Mix Change

The mix change is the difference between the mix of the previous year and the current year. This is also an important parameter.

**Mix Change = Mix of current year- Mix of the previous year**

- Select the
cell and type,**K5**

`=J5-I5`

- Then, Hit
.**Enter**

- Consequently, we will get the differences in mix.
- Lower the cursor to autofill the cells.

**Read More: **How to Calculate Budget Variance in Excel

### Step 7: Evaluating Price Variance

The price variance shows how the price of the product has changed from the previous year to the current year. If the price change is positive then the product is bringing in cash for the company otherwise the product is losing money.

**Price Variance = ( Price CY – Price PY)*Quantity CY**

- To start with, select the
cell and type the following,**L5**

`=(H5-G5)*D5`

- Then,
*hit*.**Enter**

- As a result, we will get the price variance for that product.
- Move the cursor down to the
cell to autofill.**L10**

- After that, select the
cell and enter the following,**L11**

`=SUM(L5:L10)`

- Press the
button.**Enter**

- Consequently, we will get the sum of the products’ price variance. It is positive that means overall the price is changed in favor of the company.

**Read More: **How to Calculate Variance Inflation Factor in Excel

### Step 8: Determining Mix Variance

The mix variance is a bit complicated term. It shows the variance of mix of the products. Here, we need to see if the current year’s price of a particular product is greater or less than the average product of the products in two years. We also look at the mix change of that product in those two year. These two product determines if the product will have a positive mix variance , in other words favorable for the business.

**Mix Variance = Revenue PY*(Price PY – Average Price PY)*Mix Change/100**

- Firstly, select the
cell and write the following formula,**N5**

`=$E$11*(G5-$C$13)*K5/100`

- Then, hit
**Enter.**

- As a result, we will get the mic variance for that product.
- Move the cursor to the
cell to autofill the rest of the cells.**N10**

- After that, select the
cell and type the following,**N11**

`=SUM(N5:N10)`

- Then, hit the
button.**Enter**

- As a result, we will have the sum of all mix variances. It is too a positive number which means a favorable one.

### Step 9: Calculating Volume Variance

The volume variance is the difference between the quantities sold multiplied by the price of the quantity from the previous year. Since we will calculate the mix variance, this will be deducted from the volume variance. The volume variance is positive means the company has sold the amount of goods this they planned last year.

**Volume Variance = ( Quantity CY-Qunatity PY)*Price PY – Mix Variance**

- Select the
cell and write,**M5**

`=(D5-C5)*G5-N5`

- Then, press the
button.**Enter**

- As a result, we will get the volume variance for that product.
- Then, lower the cursor down to the
cell to autofill the values.**M10**

- After that select the
cell and enter,**M11**

`=SUM(M5:M10)`

- Then, hit
.**Enter**

- Consequently, we will have our volume variance.

**Result Interpretation:**

( Before reading the portion below, don’t get confused with the values in bracket in the dataset above. The values with bracket represent negative values. )

Here’s how to understand what they mean:

**1. Price Variance: **This shows how much the change in prices affected the total revenue. If it’s positive, it means the price increase helped make more money. If it’s negative, the price decrease hurts the revenue. In the dataset above, the **Total Price Variance** is** $96.7**. It depicts that the owner is capable of making more money.

**2. Volume Variance:** This tells us how much the change in the number of items sold affected the total revenue. If it’s positive, it means selling more items boosted the revenue. If it’s negative, selling fewer items lowered the revenue. In this case, the **Total Volume Variance** is also positive with a value of **$17,794.20. **This means the seller was selling more items than previous year.

**3. Mixed Variance: **Mix variance, also known as sales mix variance, reveals how the proportion of different products or services sold affected the revenue change. If it’s positive, it means the sales mix of products contributed favorably to the revenue change. If it’s negative, the sales mix had a less favorable impact on revenue. Here, the **Total Mixed Variance** seems also positive with a value of **$5764.14. **This defines that total mixed sales are responsible for overall revenue growth.

By doing this analysis, you can figure out what caused the revenue to change, whether it was because of price changes, selling more or fewer items, or both. It helps businesses understand how their sales are performing and make decisions on pricing and sales strategies accordingly.

**Read More: **How to Calculate Semi Variance in Excel

**Download Practice Workbook**

You can download the practice workbook here.

## Conclusion

In this article, we have talked about how to do price volume variance analysis in * Excel*. We did this for the previous year and the current year. It can also be done for budgeting for the next year. The procedure will be the same. In the case of previous year, we will have to consider the current year and the next year will replace the current year.

**<< Go Back to Calculate Variance in Excel | Excel for Statistics**** | Learn Excel**

Are there any detailed explanation on the mix variance formula?

Mix Variance = Revenue PY*(Price PY – Average Price PY)*Mix Change/100

I cannot figure out why we need to divide the formula by 100.

Thanks

Hello JC, thanks for reaching out. Here, the main formula for Mix Variance is:

Mix Variance = Revenue PY*(Price PY – Average Price PY)*(% of Mix CY – % of Mix PY). Although the Mix PY and CY were shown in percentage format in the article, we didn’t calculate these values as percentage. So in the formula, the difference of Mix CY and PY is divided by 100.

Can you explain in detail for the mix variance? I cannot figure out why we have to start with budgeted sales revenue and divide the whole formula by 100.

Mix Variance = Revenue PY*(Price PY – Average Price PY)*Mix Change/100

Hello Justin, thanks for reaching out. Here, the main formula for Mix Variance is:

Mix Variance = Revenue PY*(Price PY – Average Price PY)*(% of Mix CY – % of Mix PY). Although the Mix PY and CY were shown in percentage format in the article, we didn’t calculate these values as percentage. So in the formula, the difference of Mix CY and PY is divided by 100.

Thanks for the info. It’s confusing to include the division by 100 when most readers will not understand why it’s there. I would suggest removing it when you come to review the article. Thanks again.

Thanks David for your feedback. The formatting of Mix PY, Mix CY and Mix Change being in percentage made it a bit confusing. We’ll update it soon.

Hi, thanks for this. However in the formula for the mix variance, you used QTY CY rather than Revenue PY. Is this correct or an error? Please check again and confirm. Thank you

Hello Ayodeji Aboderin, thanks for reaching out. You are right, there was an error. Now, the article is updated accordingly. Please let us know if you have any other queries.

Regards

Sajid Ahmed

Exceldemy

Hello!

can you please update the conclusion and explain how to conclude the result, what it depicts and how to interpret the data? if possible?

thank you!

Also, the explanation for the steps is great! Helped a lot!

Hey there, Mr. Chhavi.

Thanks a lot for your feedback.

“Here’s how to understand what they mean:

1. Price Variance:This shows how much the change in prices affected the total revenue. If it’s positive, it means the price increase helped make more money. If it’s negative, the price decrease hurts the revenue.2. Volume Variance:This tells us how much the change in the number of items sold affected the total revenue. If it’s positive, it means selling more items boosted the revenue. If it’s negative, selling fewer items lowered the revenue.3. Mixed Variance:Mix variance, also known as sales mix variance, reveals how the proportion of different products or services sold affected the revenue change. If it’s positive, it means the sales mix of products contributed favorably to the revenue change. If it’s negative, the sales mix had a less favorable impact on revenue.By doing this analysis, you can figure out what caused the revenue to change, whether it was because of price changes, selling more or fewer items, or both. It helps businesses understand how their sales are performing and make decisions on pricing and sales strategies accordingly.”

I hope you have your answer here. And sorry for the inconvenience. We would love to hear from you again.

Regards

ExcelDemy Team

The formulas in the Excel download file for Mix is different than the screenshots in the article. Can you explain the difference? One uses current year quantity and the other uses prior year revenue.

Excel File=$D$11*(G5-$C$13)*K5/100

Article above=$E$11*(G5-$C$13)*K5/100

Hello

JOHN C,Yes, you are right, the formula for calculating the

Mixvalue is different in the given Excel file and the above article.The formula mentioned in our article is correct. There must have been an error while selecting cell

E11, hence the adjacent cellD11is present in the Excel file formula.We have updated the Excel file with the correct formula. Thanks for your feedback.

Regards,

Seemanto Saha

Exceldemy

Hello,

Thank you for this guide.

I was wondering how you would change these calculations for products that are new or are discontinued.

Thanks!

Hello Michiel,

Thanks for your question. If you want to add new products to the dataset and include them in your calculation, you will have to convert the range into a table. Then you will be able to add as many new products as you required by inserting new rows in the dataset.

On the other hand, if the products in the dataset are discontinued, you don’t need to modify any cells that contain the “Quantity” or “Revenue” values since we are not doing any arithmetic operation on them. For safety reasons, you can apply the

IFERROR()function in all the cells where you are applying formula. For example, you would change the formula inColumn Ginto. The exception is the cells where we will calculate the sum of different values. For those cells, we will modify the formula ofIFERROR(E5/C5)M11into.SUM(IF(ISNUMBER(M5:M10), M5:M10))Hopefully, you have got the desired answer.

Why is the Mix variance based on REVENUE PY* and not QUANTITY PY?

Mix Variance old = Revenue PY*(Price PY – Average Price PY)*Mix Change/100

Mix Variance new = Quantity PY*(Price PY – Average Price PY)*Mix Change/100

Hi Strike,

Thanks for your comment. The Mix Variance formula provided in the article is based on Revenue rather than Quantity because it is intended to capture the impact of changes in the sales mix of different products on revenue, which includes both price and quantity components. Here’s the formula:

Mix Variance = Revenue PY * (Price PY – Average Price PY) * Mix Change / 100By using Revenue PY in the formula, you are considering

both the price and quantity component. Thus it becomes a comprehensive way to evaluate how the mix of products contributed to changes in revenue. It provides a more complete picture of why the revenue changed. Using Quantity alone would only consider the quantity component and not account for price changes, which are also crucial in understanding revenue variations.If you have any further query, then please inform us in the reply section.

Regards,

ExcelDemy Team

Why is the Volume Variance based on the prior year price, but the Price Variance is based on the current year volume?

Hi KIEL,Thanks for your question. Actually, you may seem that the choice of using prior year prices for Volume Variance and current year volumes for Price Variance is wrong, but it aligns with certain assumptions and conventions often used in variance analysis. Let me explain the reasoning behind this choice:

1. Volume Variance based on Prior Year Price:The assumption here is that changes in quantities sold are evaluated against the prices of the prior year. Volume Variance assesses the

impact of selling more or fewer units than planned at the prior year’s prices. Using the prior year’s price isolates the impact of changes in quantity sold, assuming that the pricing structure from the prior year remains relevant.2. Price Variance based on Current Year Volume:Here, we’re checking

how price changes between this year and last year affect earnings, considering the amount sold this year. We assume that this year’s sales volume reflects the current business situation, and any price changes are looked at based on this year’s sales.I think you have understood the matter. If you have any further queries please inform us in the reply.

Warm Regards,

ExcelDemy Team