How to Do Price Volume Variance Analysis in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

You can download the practice workbook here.


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

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 C11 cell and write down the following formula,
=SUM(C5:C10)
  • Then, hit Enter.

summing quantities to show how to do price volume variance in excel

  • As a result, we will have the sum of the goods sold for the previous year.
  • Then, select the D11 cell and write the formula below,
=SUM(D5:D10)
  • Hit Enter.

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

calculating quantities sold annually to show how to do price volume variance in excel

Read More: How to Find Population Variance in Excel (2 Easy Ways)


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 E11 cell and write the following,
=SUM(E5:E10)
  • Then, press Enter.

summing revenues to show how to do price volume variance in excel

  • Consequently, we will get the total revenue for the past year.
  • Again, select the F11 cell and write,
=SUM(F5:F10)
  • Finally, hit Enter to get the total revenue for this year.

determining total revenue to show how to do price volume variance in excel

Read More: How to Do Variance Analysis in Excel (With Quick Steps)


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 G5 cell and enter the following formula,
=E5/C5
  • Hit Enter.

evaluating revenue per unit to show how to do price volume variance in excel

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

  • After that, click on the H5 cell and type,
=F5/D5
  • Then, hit the Enter button.
  • 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 Sample Variance in Excel (2 Effective Approaches)


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 C13 cell and enter the following,
=E11/C11
  • Then, hit the Enter button.

calculating average price to show how to do price volume variance in excel

  • Consequently, we will get the average price for the last year.
  • Again, select the C14 cell and type,
=F11/D11
  • Then, hit Enter to get the average price for the current year.

Read More: Budget vs Actual Variance Formula in Excel (with Example)


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 I5 cell and write the following,
=C5/$C$11
  • Press Enter.

determining mix to show how to do price volume variance in excel

  • 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 J5 cell being,
=D5/$D$11

Read More: How to Calculate Variance Percentage in Excel (3 Easy Methods)


Similar Readings


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 K5 cell and type,
=J5-I5
  • Then, Hit Enter.

calculating mix change to show how to do price volume variance in excel

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

Read More: How to Calculate Variance Using Pivot Table in Excel (with Easy Steps)


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 L5 cell and type the following,
=(H5-G5)*D5
  • Then, hit Enter.

calculating price variance to show how to do price volume variance in excel

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

  • After that, select the L11 cell and enter the following,
=SUM(L5:L10)
  • Press the Enter button.

summing up price variances to show how to do price volume variance in excel

  • 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 N5 cell and write the following formula,
=$E$11*(G5-$C$13)*K5/100
  • Then, hit Enter.

determining mix variance to show how to do price volume variance in excel

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

  • After that, select the N11 cell and type the following,
=SUM(N5:N10)
  • Then, hit the Enter button.

summing up mix variances to show how to do price volume variance in excel

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

Read More: Mean Variance Optimization in Excel (With Easy Steps)


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 M5 cell and write,
=(D5-C5)*G5-N5
  • Then, press the Enter button.

determining volume variance to show how to do price volume variance in excel

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

  • After that select the M11 cell and enter,
=SUM(M5:M10)
  • Then, hit Enter.

summing up volume variances to show how to do price volume variance in excel

  • Consequently, we will have our volume variance.

Read More: How to Calculate Semi Variance in Excel (3 Simple Methods)


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.


Related Articles

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

8 Comments
  1. 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.

  2. 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.

        • Avatar photo
          Meraz Al Nahian May 24, 2023 at 1:55 PM

          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.

  3. 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo