Price Volume Mix (PVM) is a powerful analytics tool that can distinguish sales and cost performance factors. Properly performing this analysis in Microsoft Excel allows you to dissect changes and improve effectiveness.
To do a PVM Analysis in Excel:
- Download Your Data: Download your sales data into a spreadsheet. Organize the data in a master sales database containing variables such as period (e.g., monthly or yearly) and transaction type types (e.g., sales, returns, or discounts).
- Compute Values per Transaction: Calculate key figures like net revenue, volume changes, and mix effects.
- Create Price Volume Mix Analysis Reports: Create and update a price volume mix summary report that displays the results of the PVM analysis.
In this article, we’ll guide you through performing a price volume mix analysis for your sales data in Excel, allowing you to realize better profit margins.
Let’s dive in!
Why Price Volume Mix Analysis in Excel is important
Price volume mix (PVM) analysis is a critical tool for businesses to make meaningful decisions and understand the factors driving or impeding performance.
Learning how to conduct PVM in Excel allows organizations to accurately interpret sales data and break down changes in financial performance.
A proper PVM Analysis in Excel helps businesses to:
Identify Trend and Changes: Understand the reasons behind changing revenue, spot profitable customer groups, and products for improvements.
Set Price and Sales Strategy: Align their pricing and sales strategies with customer demands and market trends.
Improve Cost Management: Pinpoint areas where costs can be reduced or where higher pricing can be achieved.
Improve Profitability: Gain insights into the factors driving profitability and identify opportunities for improved margins, sales, and market positioning.
Align and Support Decision Making: Ensure alignment across the organization by providing a unified, factual basis for key business decisions.
Now, let’s see how you can perform an effective Price Volume Mix Analysis by following these proven steps.
4 Steps to Price Volume Mix Analysis
The approach to performing a price volume mix (PVM) analysis follows 4 fundamental steps:
Data Preparation
Data Transformation
Compute PVM Values
PVM Report Creation
Let’s dive in and review how you can perform each step.
1. Data Preparation
Before you start analyzing your data, ensure it’s accurate, relevant, and organized.
Here’s how to properly prepare:
- Download your data (sales and cost data)
- Organise data in a master sales database. The master sales database should include variables such as period (e.g., monthly or yearly), customer type (new or existing), product segment, and transaction types (e.g., sales, returns, or discounts).
- Keep separate lines for each product, customer, and transaction type.
- Include a column for the period under review (e.g., quarter, half-year, full year).
- Add columns for the value of sales transactions under original, promotional, and markdown prices, as well as the costs of those transactions if you’re analyzing gross profit.
- Regularly reconcile data downloaded with source system data (like ERP).
Organizing your data correctly is crucial, as the success of your analysis depends on accurate and comprehensive information.
2. Data Transformation
Once your data source is well-organized, you can begin the transformation process. In Excel, use the data pane to create a pivot table with relevant rows, columns, and values.
We recommend creating a pivot table with columns such as period, customer type, and product type. Then, use sales, returns, discounts, and other value metrics in these variables.
3. Compute PVM Values
To further enhance your analysis, consider performing additional computations.
Start by calculating your Net Revenue by making sales revenue more returns or discounts.
Also, identify the volume change by comparing current and previous period volumes. Remember to account for any returns or discounts in the current period when calculating volume.
Finally, compute your Mix Effect by evaluating the change in the proportion of sales from each product/customer type. This is helpful in understanding how changes in product/customer mix affect overall sales performance.
Now, let’s see how you can put this data into easy-to-understand reports.
4. PVM Report Creation
After creating the necessary computations, use them to generate a Price Volume Mix analysis report.
This report should highlight the performance metrics you’ve computed, such as Price, Volume, Net Revenue, and Mix Effect.
By visualizing these metrics in charts or tables, you can gain a deeper understanding of the factors influencing your sales and associated costs.
Here’s how you can create charts in Excel:
1. Creating a Chart for Current and Previous Period Volume
Arrange your data in a table.
Under the “Insert” tab, click on “PivotChart” and choose a chart type (e.g., Line, Bar, or Column).
Customize your chart by adding labels, legends, and data points.
2. Creating a Chart for New and Existing Customer Groups
Create a pivot table with customer type and period as columns.
Select the data and click on “Pivot Chart” under the “Chart Design” tab.
Choose a suitable chart type, such as a Stacked Bar Chart.
Customize the chart by adding data labels, color-coding, or a legend.
By using these visual cues, you can easily identify which factors are contributing predominantly to your current and past revenue, and you will be able to understand why your net revenue is going up or down.
Now that you’re familiar with the Price Volume Mix analysis, let’s review the best practices of this powerful business intelligence technique.
3 Price Volume Mix Analysis Best Practices
In this section, we’re going to review 3 best practices of PVM:
- Analyze Data at Different Levels
- Perform Sensitivity Analysis
- Utilize Financial Impact Analysis
Let’s explore them on more detail.
1. Analyze Data at Different Levels
Employ various perspectives to gain a comprehensive understanding of your performance data:
Harsh Granularity: Analyze data at its most detailed level, such as by customer, product, or transaction, to uncover specific reasons behind changes in sales and cost figures.
Aggregates Analysis: Summarize your findings by computing the price, volume, and mix at various levels (e.g., product category, customer segment, or the entire business).
This approach helps you to identify the most significant factors affecting your financial performance.
2. Perform Sensitivity Analysis
Sensitivity analysis helps you understand how different factors affect your overall performance.
In your Excel data analysis:
Scenario Modelling: Create various scenarios to see how your sales and cost numbers will change based on different price and volume assumptions.
Formula Analysis: Calculate how your key metrics, such as how sales revenue and profit, will change if inputs like prices and sales volumes shift.
This approach helps businesses address uncertainties by identifying the most influential factors and adjusting their strategies accordingly.
3. Utilize Financial Impact Analysis
While working on the Excel spreadsheet, it’s important to not only identify causes of change but also measure their financial impact accurately.
Quantify and Attribute: Use your PVM calculator to precisely quantify the financial impact of price, volume, and mix changes.
Adjust Strategy and Resource Allocation: With a clear understanding of the financial impact, you can better guide your future strategy and allocate resources for maximum effectiveness.
You can also use what-if analysis to brainstorm potential activities, such as increasing sales volumes, introducing new products, or altering pricing strategies, and project the likely financial outcomes.
By following these best practices, you can ensure that your Price Volume Mix analysis provides you with valuable insights to drive business success.
Price Volume Mix Analysis Indicators
Frequently, businesses can experience changes in their revenue, which can be perplexing without a precise understanding of the levers that drove these changes.
A comprehensive breakdown, such as the mix analysis dashboard shown here, can help you identify these levers more effectively, allowing you to conceptualize how product mix affects revenue.
By categorizing your data into Price and Volume and running a variance analysis on them, you’ll be able to determine whether changes in your key revenue stream are attributed to different dynamics or variations.
For example:
Volume Analysis is particularly informative in understanding customer purchase volume trends and their sales effect.
Price Analysis measures changes between the price of goods or services in your company’s industry. This is important when establishing benchmarks for competitors and determining how competitive your pricing is.
Mix Variances measures the revenue variance in the composite product portfolio. If the volume of certain products increases or decreases, it could have a significant impact on the overall gross profit of your business, for example.
This will inform the root cause of the mix change and, ultimately, guide the business strategy.
The following 3 steps are crucial when conducting a mix analysis using these results:
Identifying the cause of changes
Positioning your product strategy
Knowing where to invest in good placement strategy
By understanding the key influences on your revenue stream, you can make more informed and targeted decisions.
The best part about using Excel for this type of thing is that you can combine data sets, calculate variances and projections, and create detailed visualizations clearly showing how your business is performing.
Let’s check out an example.
Interactive Price Volume Mix Analysis Example
Now we’ll take a look at how to conduct a Price Volume Mix (PVM) Analysis with a simple example. Let’s get right into it.
Context
We’re using Excel to analyze sales data from a company in the bakery supply industry. The data includes sales for two years, broken down by product category and SKU.
By doing this meaningful analysis, you can segment 2 categories: Gross Revenue and Mix Change.
We’ll Be Exploring The Following:
Price Variance Analysis: Combining the two years of sales, investigating how revenue changes within the same period are tied to price adjustments.
Volume Variance Analysis: Exploring how changes in sales volume contribute to revenue changes, even if prices remain constant.
Mix Variances Analysis — Combining the Price and Volume: Identifying the mix of sales across product categories and how it tilts revenue over time.
The Mix Effect: Introducing 3rd category of SKU for specific categories only, both in terms of prices and volumes, and calculating the net impact on revenue.
Final Boot Outcomes
Price Difference between 2019 and 2021 — $5,676
Volume Difference between 2019 and 2021 — $7,394
While the process can be detailed and intricate, Excel allows for both total and granular insights.
By applying the quadrant method to break down the margin effect, as shown below, you can further delineate the impact of SKU mix changes on revenue. Further, you can check out how some price differences affected revenue in this video:
Let’s reflect on some key take-aways.
Final Thoughts
Performing a Price Volume Mix (PVM) analysis in Excel enables businesses to understand the actual cause of revenue changes by dividing them into pricing, volume, and mix effects.
When grouped together in this way, sales and cost numbers become more accurate, insightful, and useful for decision-making.
By doing the PVM analysis, businesses can:
Spot important trends: Identify trends quickly, like sales growth or profitability improvements.
Better understand market conditions: Understand how volume changes are due to the market conditions and not just price cuts and how global factors affect sales and profits.
Validate business decisions: Business leaders can track the performance of new products or markets and validate that these are having a positive impact on sales and profits.
Remember, a well-executed PVM analysis in Excel allows businesses to take a closer look at their revenue changes and know why they’re happening, and where they need to focus for improvement.
We actually had a great post about this on our Forum; you can check it out here.
For more advanced formulas in Microsoft Excel, check out the video below:
Frequently Asked Questions
Based on the keywords and topics you’ve provided, here are ten frequently asked questions (FAQs) related to price volume mix analysis and variance calculation in Excel:
How is Price Volume Mix Variance Calculated for Multiple Products in Excel?
Understand the steps to calculate price volume mix variance in Excel for multiple products, including data preparation, variance calculation, and result summarization.
What is the Formula for Calculating Mix Variance in Excel?
Learn the formula used in Excel to calculate mix variance, essential for analyzing the impact of product mix on revenue.
Can You Provide an Example of a Price Volume Mix Analysis Report in Excel?
Discover how to create a comprehensive price volume mix analysis report in Excel, including data organization, variance calculations, and visual representation.
How Does Product Mix Affect Revenue and Profit Margins?
Explore the relationship between product mix and its impact on a company’s revenue and profit margins, with a focus on variance analysis.
What Are Key Factors Affecting Price Volume Mix Data?
Identify the key factors that influence price volume mix data, such as market trends, customer preferences, and product changes.
How Can Excel Be Used to Analyze the Impact of New and Discontinued Products on Revenue?
Learn techniques for using Excel to analyze how introducing new products or discontinuing existing ones affects a company’s revenue.
How to Calculate Price Volume Mix Variance with Excel for Quarterly or Annual Reports?
Understand the process of calculating price volume mix variance in Excel specifically for quarterly or annual business reports.
What is the Significance of Analyzing Price Changes and Volume Effects Separately in Excel?
Learn the importance of separately analyzing price changes and volume effects in Excel to gain a clearer understanding of their individual impacts on revenue.
What formula should I use to calculate the price volume mix variance in Excel?
To calculate the price volume mix variance in Excel, you can use the following:
((New Price – Old Price) x New Volume) – ((New Volume – Old Volume) x Old Price) + (Old Volume x (New Price – Old Price)).
