topic Price - Volume - Mix effect analysis? (PVM) in QlikView

Price - Volume - Mix effect analysis? (PVM)

Sun, 19 Feb 2012 19:40:25 GMT

Been trying to create a PVM-analysis without success.

Price Volume Mix effect

Anyone got samples on how to do it? Please help me....

Price - Volume - Mix effect analysis? (PVM)

Wed, 22 Feb 2012 08:02:48 GMT

Someone must ahve done this 🙂

There are probably some other names for it also. But the general idea is to compare sales in two time periods and see how much of the difference that comes from Price effect (same customers/products but sold at higher/lower price), Volume effect (same customers/products but different volume) and Mix effect.

Re: Price - Volume - Mix effect analysis? (PVM)

Tue, 08 Jul 2014 12:39:10 GMT

Hi Magnus,

I'm trying to do a waterfall with price, volume and mix effect but I didn't succeed in doing so;

Have you got an answer ?

Patrick

Re: Price - Volume - Mix effect analysis? (PVM)

Wed, 24 Sep 2014 15:38:29 GMT

Magnus,

My company is an OEM partner of Qlik, and our software's front-end uses QlikView to deliver the analytical output. We specialize in providing bottom-up price/volume/mix analysis. It sounds like we may be able to help you. Please feel free to write me at dtandon@thekinigroup.com.

Regards,

Dev Tandon

The Kini Group

http://www.thekinigroup.com

Re: Price - Volume - Mix effect analysis? (PVM)

Thu, 25 Sep 2014 16:23:32 GMT

http://www.volume-and-mix-analysis.com/

Hi Magnus,

The attached spreadsheet can serve as a template for solving any PVM situation that you encounter. The spreadsheet also includes "Proof" calculations which show that the sum of the micro variances equal the correct macro totals. Just put the spreadsheet on "Show Formulas" and follow the calculation logic. The main point in the analysis is the correct extraction of Mix Variance from Volume Variance. Try looking at the link for an in-depth exposition of the analysis details.

The Mix Variance amount for each individual product is determined by 3 amounts:

. The difference between the price of the individual product and the Actual Group Budget Price.

. The difference between the product's Actual Product Mix and its' Budget Product Mix.

. The actual Group Total amount.

In the case of, for example, Product 1:

. $210 - $162 = $48

. 0.173913 - 0.300000 = -0.126087

. 1150

Thus:

. Product 1 Mix Variance = $48 x -0.126087 x 1150 = $-6960

Intuitive results . . . no anomalies . . . no "gotchas"

Frank

Re: Price - Volume - Mix effect analysis? (PVM)

Tue, 07 Apr 2015 15:08:00 GMT

Are you attempting to do you PVM for two different time periods or Actual vs Budget? I have recently finished a model that does two different time periods with waterfalls, we included Currency effect within the pricing model also. Just wanted to clarify. I don't currently have an example that doesn't house actual data, but I might could mock up something if needed. But each model is usually very specific for business models also.

Re: Price - Volume - Mix effect analysis? (PVM)

prieper — Thu, 28 May 2015 21:08:47 GMT

Hi Rhonda,

am working on similar project - would it be possible to share your solution?

Rgds Peter

Re: Price - Volume - Mix effect analysis? (PVM)

Thu, 25 Jun 2015 14:11:52 GMT

Let me see how I can share a solution without data, I'm sorry to be slow responding.

Re: Price - Volume - Mix effect analysis? (PVM)

prieper — Thu, 25 Jun 2015 15:26:02 GMT

Dear Rhonda,

you may

- restrict the data (File -> Reduce Data -> Remove all values)

- or copy only the datamodel into an empty application

and send it only to me by mail - will keep it confidential, whatever is in

....

Best Regards

Peter Rieper

Edith removed a signature.

Re: Price - Volume - Mix effect analysis? (PVM)

prieper — Sun, 12 Jul 2015 20:17:37 GMT

Hi Dev,

thanks for the offer, but think that I found a solution. Just making some plausibility- and usability-tests.

Will post a solution here, once finalized.

Regards

Peter

Re: Price - Volume - Mix effect analysis? (PVM)

Anonymous — Tue, 29 Sep 2015 04:01:29 GMT

Can I also have the template that you used? Is this done in Pivot Table?

Re: Price - Volume - Mix effect analysis? (PVM)

prieper — Tue, 29 Sep 2015 06:58:06 GMT

Hi,

basically we break it into 4 parameters:

Δ PY: Amount YTD - Amount PYTD // pretty easy, it is the simple difference

Δ Curr: Amount YTD - Amount PYTD (calculated at actual rate of exchange) // currency-effect

Δ V: (Volume YTD - Volume PYTD) * Price PY (calculated at actual rate of exchange // Volume-Effect

Δ P: Δ PY - Δ Curr - Δ V // Price-Effect

The tricky part is to calculate the Price and to define the level on which to calculate. Here you will need to use the AGGR-function, below is the function used here:

....

SUM(

{$<

ResultPeriod_Mn = {"<= $(=MAX(ResultPeriod_Mn) -12)"},

ResultPeriod_Y = {"$(=MAX(ResultPeriod_Y) -1)"},

ResultPeriod_YM =,

ResultPeriod_M =>}

Amount_DOC_PY_actROE)

/

SUM(

{$<

ResultPeriod_Mn = {"<= $(=MAX(ResultPeriod_Mn) -12)"},

ResultPeriod_Y = {"$(=MAX(ResultPeriod_Y) -1)"},

ResultPeriod_YM =,

ResultPeriod_M =

>}

Quantity_CON_CY))

, ResultPeriod_M, CostGroup, SubArea)

....

In this example the cost at current exchange-rates is calculated in the script (Amount_DOC_PY_actROE).

The price is calculated on the level of the month (ResultPeriod_M), and two more groupings.

HTH

Peter

Re: Price - Volume - Mix effect analysis? (PVM)

Anonymous — Wed, 01 Nov 2017 08:31:20 GMT

Hi,

Have anyone tried to do this analysis for restaurant?

I mean, not simply analysing Price and Volume (with Mix effect, of course), but including such components as guests (not equal to volume), average bill, dishes on 1 guest e.t.c.

Re: Price - Volume - Mix effect analysis? (PVM)

gileswalker — Tue, 17 May 2022 01:28:05 GMT

This seems to be a very old thread, but even so here is my view. PVM analysis isn't really much more than a series of reasonably basic mathematical equations, but some of them aren't necessarily easy to follow because the concepts, especially "Mix" which combines some concepts together and can feel a bit sticky to walk through.

There is a lot of things online, but I have found this YT tutorial the most easy to understand of all the ones I watched recently:

https://youtu.be/boMhDeQGWts

It's clear and well paced, and the way the equations work is well explained. Others I have watched talk too fast and they over complicate their equations. I have observed that different authors construct their equations in slightly different ways, but they reach the same answer.

If you have your data already, then all you need to do it slowly build the equations using the guide. I thoroughly recommend if you employ the autocalendar within your Qlik Sense Load Script it makes the delimitation of periods much easier as you are readily able to access things like [date.autocalendar.YearsAgo] etc.

The video above focuses on Price, Volume and Mix only. But I strongly recommend also building in "new" and "discontinued" data outcomes into your data solution because trying to correctly interpret the results using just Price, Volume and Mix can be very difficult when you have "new" and "discontinued" outcomes that should be considered in isolation.

I found working the formulas using a mix of variables and master measures the easiest.

My overall advice is start in Excel, grasp the concepts, then work in Qlik Sense.