Getting the values from a curve

[nickdf]

Hi

I'd like to put together some pricing for products, with lower prices for volume.

If plotted with volume on an X axis and price on Y, this would give me a curve a bit like the bottom left quarter of a flattened oval.

I don't have the maths skills to work out a formula that gives me the curve I want, where I can specify how steep it is, how quickly it bottoms out as we get near cost price etc. So my idea was to plot a curve in Excel using certain data points, and then extract the price from the curve by seeing if Excel could give me the values along its length.

I found some VBA code to extract data from a chart, but if only gives the values I've put in to my source data - it doesn't allow me to get the values in between, i.e. along the entire length of the curve.

Any suggestions please?

Thanks in advance

 

[6StringJazzer]

You are not asking an Excel question, you are actually asking a math question. So you are going to need to do some math.

The first thing is to decide what type of curve you want. Then you can take your sample data points and use Excel to do a curve-fitting regression that would fit your points. However, be aware that you probably won't get a perfect fit. Once you have that, you can get the equation for the regression curve, then plug in volume and get price out.

I have attached a sample using your data. In your case the best fit is a logarithmic curve. I have shown your data points, the curve, and the equation for the curve, and your prices based on that equation.

Here's the other way you could do this. It would be a lot simpler, but given what your curve looks like it would be far less accurate. You would get a volume, then use linear interpolation to get the price. This assumes that the curve between each pair of your original data points is a straight line segment. This might be acceptable if you have lots of initial data points, but you don't. I have shown that too.

Last of all, I could provide a parameterized curve where you can plug in a couple of key values that will change the shape of the curve. You can see that the equation for the given curve is

=-0.117*ln(B2)+2.1876

and is of the general form

=-a*LN(x)+b

You could set up a bunch of data points where you specify a and b as parameters. I've done that in this file, too.

Dropbox

www.dropbox.com

 

[KRice]

I can offer another approach too. Your data have a characteristic exponential decay, but the decay rate is not constant. Because of that general exponential relationship, I plotted the Benchmark Unit Prices against the log of the Quantity to visually examine the decay rates, which can be described as a series of linear segments (shown below). Since there aren't very many of them, it is convenient to simply determine the slope and intercept of those linear segments and then use those slopes and intercepts in a reverse process to estimate an interpolated value for any input quantity of interest. So when a Quantity is input in the blue cells, a lookup is done to extract the slope and intercept from the appropriate section of the benchmark price-log volume curve and then apply that slope/intercept to derive an interpolated value. A plot of the benchmark prices (blue circles) against a smoothed curve consisting of 12 estimated (interpolated) values is also shown.

MrExcel_20220614.xlsx
	A	B	C	D	E
1
2	Interpolated Unit Price
3	Qty	Unit Price
4	1	2.10
5	10	1.97
6	20	1.93
7	100	1.84
8	500	1.57
9	1000	1.40
10	1500	1.31
11	3000	1.18
12	5000	1.10
13	8000	1.09
14	10000	1.09
15	17000	1.08
16
17
18	Source Data
19	Qty	ln(Qty)	Benchmark Unit Prices	slope	intercept
20	1	0	2.1	-0.05662	2.1
21	200	5.298317	1.8	-0.24853	3.116812
22	1000	6.907755	1.4	-0.2164	2.894868
23	2000	7.600902	1.25	-0.1637	2.494294
24	5000	8.517193	1.1	-0.01443	1.222877
25	10000	9.21034	1.09
Sheet1

Cell Formulas
Range	Formula
B4:B15	B4	=XLOOKUP($A4,$A$20:$A$24,D$20:D$24,,-1)*LN($A4)+XLOOKUP($A4,$A$20:$A$24,E$20:E$24,,-1)
D20:D24	D20	=SLOPE($C20:$C21,$B20:$B21)
E20:E24	E20	=INTERCEPT($C20:$C21,$B20:$B21)
B20:B25	B20	=LN(A20)

 

[6StringJazzer]

Your data have a characteristic exponential decay

That was my first impression but when I created trendlines using exponential and logarithmic, the logarithmic gave a better R².

 

[nickdf]

Can't believe how thorough and helpful these answers are guys. I haven't had a chance to digest them and try and make sense of them yet but thank you sincerely for the responses.

Nick

 

[KRice]

Your point is well taken, Jeff. I was careless with my wording and should have said "generally logarithmic decay", thinking of something like y = a log x, which is the same model you chose without any y offset. But before attempting any regression, I was curious about the relationship between log V and P, because if a single decay rate were underlying the data, then a plot of Unit Price against ln(V) should be linear (shown in my post). When I saw that the points did not lie along a single line, I abandoned the idea of regression and a whole-curve single rate model, since I wasn't confident that a good fit could be obtained. Instead, I opted to treat this curve as a piecewise curve whose individual segments can be characterized with two parameters---somewhat resembling an interpolating polynomial spline approach where a curve is treated piecewise as one moves along the curve and derives fitting parameters from more localized points---except in this case, it is a local single rate logarithmic model that is applied rather than a polynomial. And since the interpolation is between nodes, the single rate assumption is easier to justify. Unlike some approaches involving interpolation (e.g., cubic spline) where specific requirements are established for first and second derivatives at nodes and/or endpoints, I did not make any effort to consider those constraints. I went back and investigated the behavior around the nodes because I was curious whether the lack of continuity constraints would be a problem. But with Volume being in integer units and Unit Price being rounded to two decimal places, there is no discernible issue around the nodes.

Nick, please post back if you have any questions about these offerings.

 

[6StringJazzer]

I opted to treat this curve as a piecewise curve....

Quite a comprehensive analysis.

In the end, I'm wondering if we should be applying these methods to a pricing model that was developed intuitively begin with. We don't have enough information to develop an optimal pricing model based on the economics of whatever @nickdf is selling. We have answered the question as asked, but these methods are more appropriate for modeling physical processes, IMHO.

 

[KRice]

Good point. That seems to be a common issue when data are presented and there is some desire to fit it to something. I normally look into whether there is some theory or guiding principle that would inform model selection/development, but in this case, I don't know what that would be (I'm guessing some type of logarithmic model with another parameter or two to adjust the decay rate as a function of V).

 

[6StringJazzer]

Normally you would do this with a sh!tload of marketing data, and price to elasticity nearest zero. In this case we are kind of flying by the seat of our pants.

 

[Governex]

This is some great information and it is precisely the thing I am looking for!
I have a similar situation where I am trying to work out the lifting capacity of a crane based on the radius of the lift. I have a set of data from a manufacturers duty chart, but the values are not necessarily customer friendly. Any help would be greatly received...

The information I have currently is this...

Radius (m)	Lift (kg)
1	82000
2.9	22000
4.35	16400
6	11760
7.75	8900
9.65	7000
1.75	5660
13.9	4730
16.15	3970

Which produces this curve.

Ideally I would like to be able to calculate the potential lifting capacity at 5m for example...

Hope this makes sense. Thank you in advance.