Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!