Tiered Pricing Question

Hello,

Looking for some assistance with tiered pricing. Arg - this is driving me nuts!

We rev share with a vendor based on a certain volume of units processed. Judging from some other threads I've seen nested IF statements, SUMPRODUCT, and others - what is the right tool/formula?

Scenario:

Units processed: 6,000,000

1.5% of units = the amount of rev = 90,000

The following tiers key off the units processed but corresponding % applies to rev share total.

Tiers for fees paid by us to vendor
0.00 - 1,000,000.00 = 50%
1,000,000.01 - 3,000,000.00 = 25%
3,000,000.01 - 5,000,000.00 = 15%
5,000,000.01 - 8,000,000.00 = 10%
8,000,000.01 - all others = 7%

In "long hand" it works like this:
1,000,000 = 15,000 * 0.50 = 7,500
2,000,000 = 30,000 * 0.25 = 7,500
2,000,000 = 30,000 * 0.15 = 4,500
1,000,000 = 15,000 * 0.10 = 1,500

6,000,000 (total units) = 90,000 (total rev) = 21,000 (rev share amount)

The goal is to create a formula that produces the 21,000 number.

Any help here is appreciated.

Thanks

You'll find a solution to a similar problem here.

http://www.excelforum.com/excel-2007...rcentages.html

Here's a sample file that shows you an example of TIERED discounting structure:

Complex vs Simple discount techniques

=1.5% * SUMPRODUCT((A1>{0,1,3,5,8}*1000000) * (A1-{0,1,3,5,8}*1000000) * {50,-25,-10,-5,-3}%)

The values {50,-25,-10,-5,-3}% are the differences in commissions between adjacent tiers.