0 Replies Latest reply: Apr 4, 2017 10:42 AM by Stijn Cottenie

Define client position to calculated quartiles

For a simulation analysis, I am trying to calculate the effect of a product price change on the total net turnover.

In the graph below you find the net price level (y-axis) per unit and the sales volume (x-axis). Net price is calculated as the ratio of net turnover ([NTO]) to volume ([VOL]). Each dot represents a separate customer ([CUST]) For the dataset, the first and third quartile net price are calculated and also stored in a variable - see the red and green horizonal line on the chart. The price increase a customer gets depends on its current position to Q1 and Q3. Customers that are below Q1 will get the biggest price increase, while those already above Q3 will get a much smaller increase. The user of the sheet can set this percentage price increase. These inputs are stored in a variable.

I guess that the expression to calculate the effect on net turnover would look like (in the example of customers already above Q3):

sum({\$<SETANALYSIS>)}sum([NTO]))*vQ3incr

With 'vQ3incr' the percentage price increase for customers already above Q3, as indicated by the end user.

I am not sure however what SETANALYSIS expression to use. Online I found the following syntax, but this does not work in the sheet:

[CUST]={"=(Sum([NTO])/sum(VOL))>= \$(vUpper)"

Here 'vUpper' is the variable storing the Q3 net price.

Any help on this would be greatly appreciated!