How to Perform Price‑Volume‑Mix (PVM) Analysis in Excel — Free Template

Revenue went up 15% last quarter. Great news, right?

Not necessarily. What if that growth came entirely from price increases while unit sales plummeted? Or maybe you sold more units, but customers shifted toward your lowest-margin products?

Traditional revenue reports hide these critical details. Price‑Volume‑Mix analysis reveals the truth behind your numbers.

Download the Price‑Volume‑Mix Analysis Template before you follow along.

What Is Price‑Volume‑Mix Analysis?

PVM breaks revenue change into three drivers—price effect, volume effect, and mix effect—so finance leaders can explain growth sources to investors or the board.

Instead of seeing just “Revenue increased $500K,” you’ll know exactly how much came from:

• Price changes: Higher or lower selling prices per unit
• Volume shifts: More or fewer units sold
• Mix effects: Customers buying different product combinations

This granular view transforms vague revenue discussions into strategic decisions about pricing, product focus, and sales initiatives.

Gather Current & Prior‑Period Sales Data

Traditional analysis starts with the CSV export nightmare. Log into QuickBooks. Download transaction details. Switch to Excel. Copy and paste. Repeat for last year’s data. Then do it again for each client if you’re managing multiple companies.

Coefficient connectors eliminate this manual process entirely. Pull current and prior‑period SKU‑level sales directly into Excel from QuickBooks, Xero, or NetSuite—no downloads required. Connect unlimited client instances and manage all your PVM analyses from a single workbook.

The template expects this data structure:

• SKU or Product ID
• Product Name
• Current Period Units Sold
• Current Period Revenue
• Prior Period Units Sold
• Prior Period Revenue

Map SKUs to Product Groups

The template’s “SKU Map” tab groups individual SKUs into meaningful categories for analysis. Think “Premium Widgets,” “Standard Widgets,” and “Budget Widgets” instead of analyzing 47 individual SKU codes.

This grouping step is crucial. Executives don’t want to see variance explanations for SKU #WDG-4472-BLU. They want to understand how Premium vs. Budget product performance drove overall results.

In Google Sheets, Coefficient AI can auto‑classify new SKUs based on naming patterns, dramatically speeding up this mapping process for firms managing multiple product catalogs.

Calculate Price, Volume & Mix Effects

The mathematical formulas behind PVM analysis look intimidating, but the template handles all calculations automatically:

Price Effect Formula:

(Current Price – Prior Price) × Current Volume

Volume Effect Formula:

Prior Price × (Current Volume – Prior Volume)

Mix Effect Formula:

(Current Mix % – Prior Mix %) × Total Current Revenue

The template’s “PVM Calc” sheet contains these formulas pre-built. Simply paste your sales data, and watch the variance drivers populate automatically. No need to wrestle with complex Excel formulas or worry about calculation errors during month-end crunch time.

Build the Core PVM Dashboard

The template’s “Dashboard” tab includes a waterfall chart that visually splits total revenue change into price, volume, and mix components. You’ll also find zebra-style small multiples showing variance drivers for each product group.

This dashboard design follows financial reporting best practices:

• Waterfall charts show cumulative impact of each driver
• Small multiples allow quick comparison across product lines
• Color coding immediately highlights positive vs. negative variances

Visualization Best Practices

Choose visualization types based on your audience and story:

• Waterfall charts work best for explaining total variance to executives
• Small multiples help product managers spot trends across categories
• Lollipop charts emphasize magnitude differences between drivers

Coefficient keeps these visuals live as source data refreshes. No more rebuilding charts every month or worrying about broken chart references when SKUs change.

Interpret the Drivers

Raw variance numbers tell only part of the story. Here’s how to read the signals:

Price up, volume down: Potential price elasticity issue. You raised prices but lost customers. Calculate whether the revenue gain from higher prices exceeds the loss from reduced volume.

Volume up, mix toward premium: Ideal scenario. You’re growing sales and customers are choosing higher-margin products. This suggests strong brand positioning and effective upselling.

Volume up, mix toward budget: Growth through commoditization. You’re gaining market share but potentially eroding margins. Consider whether this builds sustainable competitive advantage.

Mix shifts without volume changes: Customer behavior is changing within your existing base. Premium shifts suggest successful value communication; budget shifts may indicate competitive pressure.

Drill Down by Customer or Region

Add Excel slicers to isolate specific customer tiers, sales channels, or geographic regions. This granular analysis reveals which customer segments drive your variance patterns.

Coefficient automatically pulls customer class, location, and channel dimensions from your accounting system. No manual field mapping or data cleanup required. Filter your PVM analysis by enterprise vs. SMB customers, or compare East Coast vs. West Coast performance with a single click.

Add Prior‑Year & Forecast Comparisons

Layer additional time periods to see Actual vs Budget vs Last Year patterns. Did this quarter’s price effect exceed your forecasted pricing impact? How does current mix shift compare to seasonal patterns from prior years?

Coefficient imports multiple time periods in one refresh. Build three-way variance analysis (Actual vs Budget vs Prior Year) without managing separate data imports or version control headaches.

Automate Refresh & Alerts with Coefficient

Schedule monthly sales data pulls from your accounting systems. Set Slack or email alerts when price effects turn negative or mix effects erode below acceptable thresholds.

For fractional CFOs managing multiple clients, this automation transforms PVM from a quarterly exercise into an ongoing monitoring system. Your clients receive fresh variance insights every month without any manual work on your end.

Example alert conditions:

• Price effect below -5% for two consecutive months
• Mix effect shifts toward budget products beyond 10%
• Volume variances exceed 20% threshold
• New product revenue falls below forecast

Transform variance into insight

Price volume mix analysis transforms basic “revenue up/down” reports into actionable strategic intelligence. Understanding whether growth comes from pricing power, volume expansion, or portfolio optimization enables targeted business decisions.

Our PVM template eliminates hours of manual variance calculations while providing professional-grade analysis capabilities. Whether you’re a fractional CFO managing multiple clients or an internal finance team preparing board presentations, automated PVM analysis delivers the insights executives need to drive performance.

Ready to understand what’s really driving your revenue changes? Download our free template and connect your accounting data for automated price volume mix analysis that refreshes on your schedule.

FAQs

How to calculate price volume mix analysis?

PVM analysis uses three formulas: Price Effect = (Current Price – Prior Price) × Current Volume; Volume Effect = Prior Price × (Current Volume – Prior Volume); Mix Effect = (Current Mix % – Prior Mix %) × Total Current Revenue. Our template automates these calculations—just input your sales data.

What is the PVM model in Excel?

The PVM model breaks down revenue variance between periods into price, volume, and product mix components. It uses variance analysis formulas within Excel to show exactly what drove revenue changes. Coefficient’s template includes pre-built formulas and dashboard visualizations for immediate analysis.

How to analyze price and volume?

Compare current period prices and volumes against prior periods at the SKU level. Calculate separate price effects (revenue change from pricing decisions) and volume effects (revenue change from unit sales). Use waterfall charts to visualize how each driver contributed to total revenue variance.

What is the formula for the price volume trend in Excel?

Price Volume Trend combines price and volume effects: ((Current Price × Current Volume) – (Prior Price × Prior Volume)). This shows total revenue change, which PVM analysis then decomposes into separate price, volume, and mix drivers for deeper insights into business performance.