Need formula to calculate comission & bonuses

S

Steve

I need to create a formula that will figure out comissions & bonuses based on
not only amount sold, but also based on how much of each product is sold. I
have the breakdown as follows:

Percentage to Goal Total Revenue Bonus Online Only Revenue Bonus
125% $4,000.00 $2,000.00
120% $3,600.00 $1,800.00
115% $3,200.00 $1,600.00
110% $2,800.00 $1,400.00
105% $2,400.00 $1,200.00
100% $2,000.00 $1,000.00
95% $1,600.00 $800.00
90% $1,200.00 $600.00
85% $800.00 $400.00
80% $400.00 $200.00
75% $80.00 $40.00
Below 75% $0.00 $0.00


Explanation of the bonus criteria:
If a rep has a $20k goal and sells $20k (100%), they automatically earn the
$2k bonus shown for Total Revenue.
20% of their goal needs to come from online only ads to earn the online
bonus, too. So if this rep sells
$4000 in online only sales from the $20k they sold they earn $1k also for a
total of $3k. If a rep
sells only $15k of their $20k bonus, but $4k of that amount sold is from
online only then they earn
$80 from the Total Revenue bonus AND $1k from the online bonus and so on.

Now the question is, how would I enter that into the spreadsheet? I have
tried using the formula and using IF & AND but I keep getting errors. Please
help and THANK YOU!!!
 
C

cht13er

I need to create a formula that will figure out comissions & bonuses based on
not only amount sold, but also based on how much of each product is sold. I
have the breakdown as follows:

Percentage to Goal Total Revenue Bonus Online Only Revenue Bonus
125% $4,000.00 $2,000.00
120% $3,600.00 $1,800.00
115% $3,200.00 $1,600.00
110% $2,800.00 $1,400.00
105% $2,400.00 $1,200.00
100% $2,000.00 $1,000.00
95% $1,600.00 $800.00
90% $1,200.00 $600.00
85% $800.00 $400.00
80% $400.00 $200.00
75% $80.00 $40.00
Below 75% $0.00 $0.00

Explanation of the bonus criteria:
If a rep has a $20k goal and sells $20k (100%), they automatically earn the
$2k bonus shown for Total Revenue.
20% of their goal needs to come from online only ads to earn the online
bonus, too. So if this rep sells
$4000 in online only sales from the $20k they sold they earn $1k also for a
total of $3k. If a rep
sells only $15k of their $20k bonus, but $4k of that amount sold is from
online only then they earn
$80 from the Total Revenue bonus AND $1k from the online bonus and so on.

Now the question is, how would I enter that into the spreadsheet? I have
tried using the formula and using IF & AND but I keep getting errors. Please
help and THANK YOU!!!
Click to expand...

I set up this problem in a spreadsheet and found that a pair of
vlookups did the job perfectly.
I will try to send you the file - if you don't get it, could you email
me so that I can send it to you?

THanks

Chris
 
Top