I'm trying to work out a tiered pricing strategy. I'd like it to make sense in terms of volume and pricing but can't seem to work out the calculations. Here are the tiers I was originally looking at:0-30 @ $2031 - 74 @ $1575+ @ 10Obviously at the crossover it becomes far cheaper to buy more, ie 30 vs 31. I'm trying to work out a calculation that makes sense. Thanks,Craig
Optional Information: Language (or Software): Excel
Hi,Here is a sample file with the formula in the yellow cell
I'm not an excel expert. Is it possible you could lay it out with the volume points?
Did you check the formula in the file?
Yes, I was just playing with it. I think I have the same problem ie. The dollar amounts to the volume don't scale properly ie. if a client buys 30 units @ 20 it costs them $600, if they buy 31 @ 15 it costs $465. I'm trying to work out a calculation that tiers the pricing so there is incentive for the client to buy more, but the cost reduces too much at the crossover.
Can you give an example. Say they buy 45 units what should the formula return?
That's the problem. I'm thinking it probably needs a calculation for the price. For 45 units the cost would be 675 - with unit price @ 15. However at 29 the price is 580. There needs to be more incremental price points I think.
That is the calculation I'm trying to get to. Starting at 20 per unit going down to 10, what are the volume breaks.
Ok let me try to set up the formula for that and I will get back to you
Our chat has ended, but you can still continue to ask me questions here until you are satisfied with your answer. Come back to this page to view our conversation and any other new information. What happens now? If you haven’t already done so, please rate your answer above. Or, you can reply to me using the box below.
The formula that I gave does give you the RATE that you should use to multiply the quantity with. But if you want to have the AMOUNT instead of the rate here is the formula for that
=IF(A1<=30,20,IF(A1<=74,15,10))*A1
Please try this formula in B1 and let me know if you still need help
Hope this is ok. Let me know if you have any questions, and thank you for rating/accepting my answer if you are satisfied with the solution.
Craig, did you try the last formula. I am standing by to see if you need further help with that formula or if you need another formula all together
It's not quite what I'm looking for. Maybe just mocking up a structure makes sense to demonstrate:0-20 @ 2021-24 @19.5025-29 @1830 @ 15I'd like the range or unit pricing to be between $10 and $20 but at the current volume I'm giving too big of a discount at the crossover units ie. 29 units vs 30. I'd prefer it if someone didn't get such a large discount at the volume tiers. I just can't figure out the logical price points.Does that make sense?Craig
I asked you to give an example and the formula that I gave you complies 100% to your initial question AND the example that you gave.
It looks now that you need to pro-rata the unit price depending on how close it is from either end of the bracket. If so I will try to get you the formula for that, but in the meantime thank you issuing credit for the solution that I gave you per your question and example
Experience: Vast experience in Excel programming and business solutions
Thanks Craig
I am on it now...
Ok try this formula
=IF(A1<=30, ((A1-0)/(30-0))*20,IF(A1<=74, ( ((A1-30)/(70-30))*15)+15,10))
It gives you the pro-rated RATE. To get the total amount you need to multiply that rate by the quantity (I can add this to the formula if you like but I first want you to check how this new rate works)
Hi JohnSorry to be dense about this (I was an English major :-) I put this formula into B1, multiply it by the quantity in a1? If so, I get weird answers for example quantity 10 = 66.67.Thanks,Craig
Ok one moment let me send you the file
Ok I finding that there could be a misunderstannding issue here. Could you please tell me what the rate should be for each of these quantities
1
15
30
50
75
100
Thank you
Hi John1 - 2015 - 2030 - 1550 - 1575 - 10100 - 10Regards,Craig
Ok to get a 100% smooth discount transition I think you need to use this model which you can use for any desired rates and levels of discount
Attachment: 2012-06-19_160000_volume_pricing1.xlsx
Here is how it works
The formulas are in the yellow cells, and the quantity is entered in the while cells. The gray cells are for the fixed values that you want to establish
First you have to establish your base or fixed unit rate for your product. Enter that in cell A2 (gray cell)
Next you have to establish the maximum discount rate that you want to offer if the 75 quantity is ordered. You enter that maximum rate in B2 (gray)
Now you enter your actual quantities ordered in cell C2
The formulas in D2 and E2 return the applicable discount for that quantity and the total sale amount
So for 75 quantity ordered the maximum discount of 10% will be applied, and if the quantity is 25 the applicable dicount will be 3.3%, and so on
Of course you can change the base rate and the maximum discount to any desired values and the formulas will calculate the corresponding discount/sale amount
Check it out and let me know if it needs any adjustments
Hi JohnThat works. Thanks very much.Regards,Craig
Great!
You're welcome, glad to help