Formula for tiered pricing

[freestyle]

Hi Guys,

This is my first post and I have very limited knowledge of excel so be gentle!

I am not entirely sure what terms I should be searching for so if this has been asked (and answered) in a different manner apologies!

Ok...

Basically I am trying to create a way in excel to creating a tiered pricing structure so that prices don't go down incrementally but more of a curved graph if that makes sense?

e.g Say I want to offer a product and I want to generate a pricing column in excel with 10 rows to show the prices from 1 unit to 10 units. Now for example lets say the price of 1 unit is £10 and the price of 10 units is £1. I know that a simple pricing structure would be to say each unit is simply £1 cheaper the more you buy but this is not what I need.

What I need to do is have the pricing drop more significantly then become less significant the more you buy - so by knowing the price of 5 units is £3 I am able to draw a curved graph of price VS units with 3 points plotted I just don't know how to write a formula in excel to show the prices for 1 to 10 units in a column.

Sorry I have read that back and it is not exactly clear and concise but with a limited mathematical vocab I'm struggling a bit! Hope someone can understand that and help

 

[MickG]

This may help
If you plot those 3 points over the range 1 to 10, then Add a polynomial trend line With its "Equation) Column "d",the intermediate values come out as shown.

[COLOR="RoyalBlue"][B]Row No [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(A) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(B) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(C) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(D)              [/B][/COLOR]
1.      1       1       1       =-0.1*A1^2+1.1*A1   
2.      2               1.8     =-0.1*A2^2+1.1*A2   
3.      3               2.4     =-0.1*A3^2+1.1*A3   
4.      4               2.8     =-0.1*A4^2+1.1*A4   
5.      5       3       3       =-0.1*A5^2+1.1*A5   
6.      6               3       =-0.1*A6^2+1.1*A6   
7.      7               2.8     =-0.1*A7^2+1.1*A7   
8.      8               2.4     =-0.1*A8^2+1.1*A8   
9.      9               1.8     =-0.1*A9^2+1.1*A9   
10.     10      1       1       =-0.1*A10^2+1.1*A10

Regards Mick

 

[GlennUK]

Hi freestyle,

thanks for the message. I did look at this thread, but didn't think that this:
http://www.mrexcel.com/forum/showthread.php?t=480574&highlight=increases+logarithmically
would be useful.

Is the data structure and pricing real? If 1 item costs $10 , but if buying 10 items the price drops to $1, then you're getting the same gross amount for selling 10 items as for 1 item. You might as well have a "Buy 1, get 9 free" campaign. On the other hand your example may be just that ... an example. So, are those real prices and amounts, or just made up stuff?

PS. my keyboard has decided to go US today, so it's dollars instead of pounds in my postings at the moment.

 

[freestyle]

Hi Glenn,

No the prices were examples to keep the explanation simpler the prices would actually be varied as there are numerous products - hence the need to get a formula working to create the excel doc in a more realistic fashion.

But for example 1 product I am look to create in a row in excel looks like this:

UNIT QUANTITY 1: 11.95
UNIT QUANTITY 2:
UNIT QUANTITY 3
UNIT QUANTITY 4
UNIT QUANTITY 5
..... (continues to 50 in individual numbers then goes in 1 jump to 100, then in 100's to 1000 limit)
UNIT QUANTITY 50: 0.55
UNIT QUANTITY 100: 0.48
UNIT QUANTITY 200: 0.47
UNIT QUANTITY 300: 0.45
UNIT QUANTITY 400: 0.43
UNIT QUANTITY 500: 0.40
UNIT QUANTITY 600: 0.49
UNIT QUANTITY 700: 0.38
UNIT QUANTITY 800: 0.37
UNIT QUANTITY 900: 0.35
UNIT QUANTITY 1000: 0.29

The prices from 1 to 50 are relatively arbitrary in that most people would order 50 as a sensible minimum order but in the interests of catering for customers who want less I am after creating code to provide a price from a minimum price (in this case £11.95) down to 55p for 50 units. This COLUMN is then read by VB Script that prices a QUOTE Form to produce a price online - hence the need for individual price breaks from 1-50 otherwise the auto generation of say 20 units would be 20 x11.95 which is huge compare to ordering 50 units at 55p each? Does that make sense?

As for the reason for such a steep discount from 1-50 - this is because the bulk of the costs are in the setup of manufacture not the materials or the time taken to produce once the first one is done - so in real terms 1 unit (after time spend preparing manufacture) is a very similar price to 10 units.

But the key to what I am looking to have is code that takes the price of 1 unit to 50 units and creates a tiered pricing structure that encourages clients to base 50 units as a minimum but not exclude those who want less. Hence trying to work with your code that did a similar thing just with different values.

Wow - hope that makes so kind of sense....

 

[GlennUK]

Ah, that makes sense now. Unfortunately I've run out of time ... I'll be logging off soon, and won't be back until Tuesday.

I'm sure some other curious people on here will lend a hand. I'll check back next week.

 

[freestyle]

Many thanks Mick. I can see the curve data from the data you sent but it is all a little beyond me.

 

[freestyle]

hi Glenn,

Ok well thanks for the input. I have been trying to decifier how to modify your code to suit this as it comes close in most respects but like you say hopefully someone more skilled than me will be able to offer a helping hand

 

[GlennUK]

Have a look at this:

Excel Workbook
	A	B
1	1	11.95
2	2	7.89
3	3	6.97
4	4	6.31
5	5	5.80
6	6	5.39
7	7	5.03
8	8	4.73
9	9	4.46
10	10	4.22
11	11	4.00
12	12	3.81
Sheet4

 

[freestyle]

Hi Glenn,

Thank you for this as I am still struggling to automate this.

I have input your code and am wondering how would I effect the gradient of the curve (i.e rate of unit price drop) such that the TOTAL PRICE didn't ever become cheaper to have more units. Sorry struggling to put this in words...

i.e in your example when the units reach 24 units it then becomes cheaper to have 25 units and so on all the way to 50 units.

Unit Price Total Price
1 £11.95 £11.95
2 £7.89 £15.78
3 £6.97 £20.91
4 £6.31 £25.24
5 £5.80 £29.00
6 £5.39 £32.34
7 £5.03 £35.21
8 £4.73 £37.84
9 £4.46 £40.14
10 £4.22 £42.20
11 £4.00 £44.00
12 £3.81 £45.72
13 £3.62 £47.06
14 £3.45 £48.30
15 £3.30 £49.50
16 £3.15 £50.40
17 £3.01 £51.17
18 £2.88 £51.84
19 £2.76 £52.44
20 £2.64 £52.80
21 £2.53 £53.13
22 £2.42 £53.24
23 £2.32 £53.36
24 £2.22 £53.28
25 £2.13 £53.25
26 £2.04 £53.04
27 £1.96 £52.92
28 £1.87 £52.36
29 £1.79 £51.91
30 £1.72 £51.60
31 £1.64 £50.84
32 £1.57 £50.24
33 £1.50 £49.50
34 £1.43 £48.62
35 £1.36 £47.60
36 £1.30 £46.80
37 £1.24 £45.88
38 £1.18 £44.84
39 £1.12 £43.68
40 £1.06 £42.40
41 £1.00 £41.00
42 £0.95 £39.90
43 £0.89 £38.27
44 £0.84 £36.96
45 £0.79 £35.55
46 £0.74 £34.04
47 £0.69 £32.43
48 £0.64 £30.72
49 £0.60 £29.40
50 £0.55 £27.50

 

[GlennUK]

You are the one who has decided on £0.55 per unit at a level of 50 units. That gives a total price of £27.50 for 50.

Are you saying that all total prices for units between 2 and 49 must always be less than £27.50? If so, maybe this will do:

Excel Workbook
	A	B	C
1	*	Gross	Unit
2	1	11.95	11.95
3	2	12.26	6.13
4	3	12.57	4.19
5	4	12.88	3.22
6	5	13.19	2.64
7	6	13.51	2.25
8	7	13.82	1.97
9	8	14.13	1.77
10	9	14.44	1.60
11	10	14.75	1.47
12	11	15.06	1.37
Sheet4

B51 holds £27.50