3 tier incentive matrix w/ infinite possibilities

[jjones]

I am trying to set up an incentive matrix for sales reps. If there were only
3 clearly defined tiers or payout %, then I could wrap this up with an IF
formula. What I actually need, however, is some way of calculating infinite
ranges in between my established tiers. So let's say my sales reps have a
goal of selling 100 widgets per month at a cost of $1000 each. My tiers are
constructed like this:

% of Goal Achieved % of Revenue Share
Tier 1 90.00% 0.75%
Tier 2 125.00% 3.00%
Tier 3 150.00% 4.50%

If, for example, the rep sales only 89 widgets, he gets no revenue share.
If he sales 90 he gets 0.75% of the revenue (in this example that would be
0.75% of $90,000 or $675). But if he hits something like 107.5% of his
widget goal, then I want it to calculate a percentage of revenue share in
between the first tier (0.75%) and the second tier (3.00%). And then I need
the same sort of calculation to take place if the numbers lie in between the
2nd and 3rd tiers. And of course I need it capped at 150% of goal...so the
most the rep could ever make is 4.50% of the revenue.

 

[FrankWood]

Assuming your revenue table is begins at cell A1 you would have to add a row
for Teir 0 such as:
% of Goal Achieved % of Revenue Share
Teir 0 89% 0
Tier 1 90.00% 0.75%
Tier 2 125.00% 3.00%
Tier 3 150.00% 4.50%


Then assuming the percentage of sales that you are looking up is in cell C19
you could use this formula.

=VLOOKUP(C18,$B$2:$C$5,2,TRUE)

Since the “range lookup†part of the function is set to “True†anything 89%
or less will return Zero. Any value 90% to 125% will return 75%. Anything
150% or above will return 4.5%


Hope that helps.

Frank

 

[FrankWood]

My bad... Just re-read my post. Set Cell b2 to 0. Then it will return any
percentage from 0 - 89% with % of revenue set to 0.

 

[jjones]

Thanks but actually that's not what I need. I know how to make it do that.
I don't want values between 90% and 125% to round down to 0.75 or up to 3.00;
I want it to give me a number in between 0.75 and 3.00. For example, a % of
goal halfway between tiers 1 and 2, like 107.5% of goal, should equal a
payout % that's roughly halfway between 0.75 and 3.00, like 1.88% of revenue
share.

 

[Pete_UK]

Assuming your table occupies A1:C4 like this:

%_of_Goal_Achieved %_of_Revenue_Share
Tier_1 90.00% 0.75%
Tier_2 125.00% 3.00%
Tier_3 150.00% 4.50%

and that the percentage widgets is in B10, then you can put this
formula in C10:

=IF(B10<B$2,0,IF(B10>=B$4,C$4,INDEX(C$2:C$4,MATCH(B10,B$2:B$4))+(B10-
INDEX(B$2:B$4,MATCH(B10,B$2:B$4)))*(INDEX(C$2:C$4,MATCH(B10,B$2:B
$4)+1)-INDEX(C$2:C$4,MATCH(B10,B$2:B$4)))/(INDEX(B$2:B$4,MATCH(B10,B
$2:B$4)+1)-INDEX(B$2:B$4,MATCH(B10,B$2:B$4)))))

to give you what you require. You can copy the formula down if you
wish - here's some sample results:

80% 0.00%
85% 0.00%
90% 0.75%
95% 1.07%
100% 1.39%
105% 1.71%
110% 2.04%
115% 2.36%
120% 2.68%
125% 3.00%
130% 3.30%
135% 3.60%
140% 3.90%
145% 4.20%
150% 4.50%
155% 4.50%
175% 4.50%
200% 4.50%

Hope this helps.

Pete

 

[Pete_UK]

Here's a shorter version of the formula, again looking at the
percentage of widgets in B10:

=IF(B10<B$2,0,IF(B10>=B$4,C$4,IF(B10<B$3,C$2+(B10-B$2)*(C$3-C$2)/(B$3-B
$2),C$3+(B10-B$3)*(C$4-C$3)/(B$4-B$3))))

Hope this helps.

Pete

 

[jjones]

Thanks, Pete! ;-)

Here's a shorter version of the formula, again looking at the
percentage of widgets in B10:

=IF(B10<B$2,0,IF(B10>=B$4,C$4,IF(B10<B$3,C$2+(B10-B$2)*(C$3-C$2)/(B$3-B
$2),C$3+(B10-B$3)*(C$4-C$3)/(B$4-B$3))))

Hope this helps.

Pete




.

 

[Pete_UK]

You're welcome.

Pete

Thanks, Pete! ;-)






- Show quoted text -