Revenue Variance Analysis: Price, Volume, and Mix

1. The "Why": Understanding the Story Behind the Revenue Number

Imagine you report to your CFO that revenue missed the budget. The immediate follow-up question will be, "Why? Did we not sell enough units? Did we have to give too many discounts? Or are we selling more of our cheaper products?" PVM analysis is the framework that answers these questions precisely.

Let's break down the three core drivers:

• Price: This measures the impact of selling your products at a higher or lower price than planned. Did you raise prices successfully, or were you forced to discount?
• Volume: This measures the impact of selling more or fewer total units than planned. Are customers buying more or less of your "stuff" overall?
• Mix: This measures the impact of a change in the composition of what you sell. Are you selling a higher proportion of premium, high-priced products (favorable mix) or a higher proportion of entry-level, low-priced products (unfavorable mix)?

To build your intuition, let's start with the core concepts.

As the article illustrates, these three effects combined explain the total change in revenue. Your job as an analyst is to isolate and quantify each one.

2. The "What": Data Requirements for PVM Analysis

Before we can build a PVM model, we need the right ingredients. A common mistake is not having data at the right level of detail. For this analysis to work, you cannot use high-level, aggregated numbers.

You need data broken down for each period you are comparing (e.g., Actuals vs. Budget, or This Year vs. Prior Year). The required granularity is:

• By Product/SKU: You need sales data for each individual product or service.
• Revenue/Sales Value: The total sales value for each product.
• Quantity/Volume: The total number of units sold for each product.

The following video segment clearly outlines these requirements.

With this data structure, you can derive the average selling price (Revenue / Quantity) for each product in each period, which is a critical input for our calculations.

3. The "How": Calculating Price, Volume, and Mix Variances

There are several methods to calculate PVM variance. We will use a robust approach that is common in professional FP&A teams. This method not only decomposes the variance for existing products but also explicitly isolates the impact of new and discontinued products, providing a more complete picture.

The logic is to calculate each component's effect, ensuring they all sum up perfectly to the total revenue variance.

Here is the step-by-step process to build this in Excel. Let's assume you have a table with the following columns for your products: PY_Qty, PY_Revenue, AC_Qty, AC_Revenue.

Step 1: Calculate Price and Identify Product Status

First, we need to calculate the average selling price for both periods and determine if a product is Active, New, or Discontinued.

1. Calculate PY Price: = IFERROR(PY_Revenue / PY_Qty, 0)
2. Calculate AC Price: = IFERROR(AC_Revenue / AC_Qty, 0)
3. Determine Status: Use a nested IF statement to categorize each product.
   • =IF(PY_Qty=0, "New", IF(AC_Qty=0, "Discontinued", "Active"))

Step 2: Calculate Variances for New and Discontinued Products

This is the most straightforward part.

• New Product Variance: For products with a "New" status, the variance is simply their entire Actual Revenue (AC_Revenue).
• Discontinued Product Variance: For products with a "Discontinued" status, the variance is the negative of their Prior Year Revenue (-PY_Revenue).

Step 3: Calculate Price and Volume Variance for Active Products

For "Active" products, we isolate the pure price and pure volume effects.

• Price Variance: This is the change in price multiplied by the actual quantity sold this year. This tells us the impact of price changes on the sales we actually achieved.

  • Formula: (AC_Price - PY_Price) * AC_Qty

• Volume Variance: This is the change in quantity multiplied by the prior year's price. Using the old price isolates the pure volume effect, preventing the impact of the price change from being counted in both places.

  • Formula: (AC_Qty - PY_Qty) * PY_Price

Step 4: Calculate the Mix Variance

The mix variance is the most complex component conceptually. It represents the change in revenue that comes from a shift in the portfolio of products sold. We calculate this as the "plug" or residual amount after accounting for all other effects. This ensures a perfect reconciliation.

• Mix Variance Formula: $$\text{Mix}=\text{Total Revenue Variance}-\sum (\text{Price Variances})-\sum (\text{Volume Variances})-\sum (\text{New Variances})-\sum (\text{Discontinued Variances})$$

Where:

• Total Revenue Variance is the sum of all AC_Revenue minus the sum of all PY_Revenue.
• The SUM functions are the totals of the variance components you calculated in the previous steps for all products.

This method correctly isolates mix as the final piece of the puzzle. A positive mix variance means you shifted sales towards your more expensive products (relative to the prior year's prices), while a negative mix variance means you shifted towards cheaper ones.

This entire calculation logic is demonstrated in the following video.

4. Going Further: PVM on Gross Profit

While analyzing revenue is powerful, analyzing Gross Profit or Contribution Margin is even more insightful. A favorable revenue mix (shifting to higher-priced products) might be unfavorable for profit if those products have very low margins.

The good news is that the calculation is nearly identical. Instead of using Revenue and Price, you use Gross Profit and Gross Profit per Unit.

Conclusion

You have now learned how to perform a comprehensive Price-Volume-Mix analysis, a cornerstone of a senior FP&A analyst's toolkit. You can now move beyond saying "what" happened with revenue and explain the "why" with confidence and precision.

Key Takeaways:

• Revenue variance can be decomposed into Price, Volume, and Mix effects.
• Price variance shows the impact of charging more or less than planned.
• Volume variance shows the impact of selling more or fewer units.
• Mix variance shows the impact of shifting the sales portfolio towards higher or lower-priced products.
• A robust model also isolates the impact of New and Discontinued products.
• The calculation method involves breaking down the variance for each product and calculating Mix as the final reconciling item.
• This entire framework can be applied to Gross Profit for even deeper insights into profitability drivers.

In our next lesson, we will continue to explore variance analysis by focusing on costs. We will learn how to decompose cost variances into fixed, variable, rate, and efficiency components, giving you a complete framework for analyzing the full Profit & Loss statement.