Formula for tiered pricing (adding a quantity for tier) that includes a free tier

[dw970906]

I am trying to calculate the # of tiers required based on an inputted #. I am using a formula with MOD, but having issues getting the proper # of each tier due to the fact that I am including a "free" amount. I also need to chose the most appropriate tiers for cost effectiveness.

Tiers:
tier 1 = 500 units ($0) This tier is ALWAYS included
tier 2 = 500 units ($100 per unit)
tier 3 = 1000 units ($80 per unit)
tier 4 = 5000 units ($50 per unit)
tier 5 = 10000 units ($20 per unit)

Example 1: 1000 units
- tier 1 will have quantity of 1 (500 units @ $0/unit)
- tier 2 will have a quantity of 1 (500 units @$100/unit = $50,000)
Total cost - $50,000

Example 2: 5000 units
- tier 1 will have a quantity of 1 (500 units @ $0/unit)
- tier 2 will have quantity of 0
- tier 3 will have a quantity of 0
- tier 4 will have a quantity of 1 (5000 units @ $50/unit)
Total cost is $250,000)

In this example, it is cheaper to purchase 1 block of 5000 units (tier 4), vs purchasing 4, tier 3 blocks and 1 tier 2 block.

Any suggestions?

 

[etaf]

your 2 examples
1000 , you use the 500 threshold, 1000-500
(500 units @$100/unit = $50,000)
BUT
Example 2
you use 5000 for tier 4 - But should that be 4500 at tier 4 , as you have 500 free and the 5000 brings it to tier 4 , but its only 4500 at the 5000 tier setting
- tier 4 will have a quantity of 1 (4500 units @ $50/unit)

Also the 1000 example - should that use the 80 tier as its 1000

 

[etaf]

does this work for you??
BUT I have assumed the threshold - is based on total volume as per your 2nd example and not with the 500 deducted
But thats a simple modification

Etaf - match values.xlsx
	A	B	C	D	E	F	G	H
2	tier 1	0	0			Qty	Tier	Amount
3	tier 2	500	100			40	0	0
4	tier 3	1000	80			1000	80	40000
5	tier 4	5000	50			5000	50	225000
6	tier 5	10000	20			100000	20	1990000
7						0	0	0
Sheet2

Cell Formulas
Range	Formula
G3:G7	G3	=INDEX($C$2:$C$8,MATCH(F3,$B$2:$B$8,1))
H3:H7	H3	=INDEX($C$2:$C$8,MATCH(F3,$B$2:$B$8,1))*(F3-500)

 

[dw970906]

your 2 examples
1000 , you use the 500 threshold, 1000-500
(500 units @$100/unit = $50,000)
BUT
Example 2
you use 5000 for tier 4 - But should that be 4500 at tier 4 , as you have 500 free and the 5000 brings it to tier 4 , but its only 4500 at the 5000 tier setting
- tier 4 will have a quantity of 1 (4500 units @ $50/unit)

Also the 1000 example - should that use the 80 tier as its 1000

In the second example, you are correct, it would be 4500 units, but I will charge 1 unit of 5000. The total available units will be 5500 - I wouldn't bill again until above 5500.

 

[dw970906]

does this work for you??
BUT I have assumed the threshold - is based on total volume as per your 2nd example and not with the 500 deducted
But thats a simple modification

Etaf - match values.xlsx
	A	B	C	D	E	F	G	H
2	tier 1	0	0			Qty	Tier	Amount
3	tier 2	500	100			40	0	0
4	tier 3	1000	80			1000	80	40000
5	tier 4	5000	50			5000	50	225000
6	tier 5	10000	20			100000	20	1990000
7						0	0	0
Sheet2

Cell Formulas
Range	Formula
G3:G7	G3	=INDEX($C$2:$C$8,MATCH(F3,$B$2:$B$8,1))
H3:H7	H3	=INDEX($C$2:$C$8,MATCH(F3,$B$2:$B$8,1))*(F3-500)

In the worksheet I want to use, I will need to automatically identify the # of tiers, based on my input value. I will then use that to calculate the overall price. How can I automatically identify the # of tiers needed without manually figuring it out?

 

[dw970906]

Now that I re-read my post, to clarify, I am not charging by unit, but by the # of tiers which the input #. So, in example 2, I will charge for 1 tier at 5000 units and the output price is 5000 * $50. The important piece of this if to identify the # of tiers needed based on the input value, when considering the input value as I mentioned.

 

[etaf]

Not sure i fully understand, SORRY

to get the tier than i added the lookup , based on my current understanding
Which is the qty - decides the tier to USE - but that value is only used for the qty less the free 500 items

Not sure what you would charge if the qty was less than 500

I dont fully understand

n the second example, you are correct, it would be 4500 units, but I will charge 1 unit of 5000. The total available units will be 5500 - I wouldn't bill again until above 5500.

As the 2nd example was 5000 not 5500 and the next tier is not until Tier 5

I have also put the spreadsheet onto a share - but with XL2BB you can copy into a spreadsheet
Anyway - you can try a few different Qty and see if works

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Etaf - match values.xlsx
	A	B	C	D	E	F	G	H	I
2	tier 1	0	0			Qty	Tier Name	Tier Value	Amount
3	tier 2	500	100			40	tier 1	0	0
4	tier 3	1000	80			1000	tier 3	80	40000
5	tier 4	5000	50			5000	tier 4	50	225000
6	tier 5	10000	20			100000	tier 5	20	1990000
7						800	tier 2	100	30000
8
Sheet2

Cell Formulas
Range	Formula
G3:G7	G3	=INDEX($A$2:$A$8,MATCH(F3,$B$2:$B$8,1))
H3:H7	H3	=INDEX($C$2:$C$8,MATCH(F3,$B$2:$B$8,1))
I3:I7	I3	=INDEX($C$2:$C$8,MATCH(F3,$B$2:$B$8,1))*(F3-500)

 

[dw970906]

Thanks. I will take a look. Basically, I want to count how many of each tier needed and I need to know based on the inputted number, i.e. 2500, how many of each tier I would need (also optimizing for the best price per tier) in my example above.

I will only get the overall price by charging on the quantity of tiers (as I will have a list price per tier). Tier 1 (500) is no cost and needs to be included as a baseline. If the starting # is less than 500, then there is no cost to the tier, as this base tier is given free. Attached is a screen shot from my worksheet. So knowing the # of each tier is critical to my calculations.

 

[dw970906]

Like I said, the most important # for me is to understand how many of each tier I need and I will then use those amounts to multiple against the cost per tier to get my end price.

 

[dw970906]

Another example hopefully this clarifies. If I have 6000 units as my input, then I should have the following quantity of tiers:
tier 1 = 1 (500)
tier 2 = 2 (500)
tier 3 = 0
tier 4 = 1 (5000)
tier 5 = 0

The above is what I need to figure out for any arbitrary # I input. The I can easily figure out the cost as I have a static cost per tier. Tier 1 is always going to have a 1 as it is included for free. Hope this helps.