Tiered Pricing

[mohammadsameja]

Hi All,

I have a scenario where we have different discounts for customers based off the MSRP

For example 10% if total invoice value is above $1000

20% if total invoice value is $1500 and so

What I would like to do so, is that based on the total order placed by the customer, the discount is applied automatically on the MSRP and the customer gets that specific discount based on his total invoice value (either 10% or 15% or 20% or son on)

How do I do that in excel or is there any ready made template or web application where that can be done and downloaded?

Thanks a lot.

 

[63falcondude]

Cross-Posted

https://www.excelforum.com/excel-general/1191262-tiered-pricing.html

 

[Scott T]

[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]D[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]E[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1000
[/TD]
[TD="bgcolor: transparent, align: right"]10%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1500
[/TD]
[TD="bgcolor: transparent, align: right"]20%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]1501
[/TD]
[TD="bgcolor: transparent"]total
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2000
[/TD]
[TD="bgcolor: transparent, align: right"]30%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]20%
[/TD]
[TD="bgcolor: transparent"]discount
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


you can use a list of your discounts and then use a vlookup to get the percent discount
In A5

=VLOOKUP(A4,D1:E4,2)