Help with Commission Log

[atxmichael]

I'm developing a sales/commission tracking log for my company, the problem I have run into is this:

I'm trying to calulate the projected bonus for my sales reps based on the accessory sales. When the accessory sales are within the following ranges it needs display the corresponding bonus, also their margin needs to be 65% or higer if not the bonus will be $0.00

sales Bonus
$4000 = $400 BONUS (65% MIN MARGIN)
$3500 = $350 BONUS (65% MIN MARGIN)
$3000 = $300 BONUS (65% MIN MARGIN
$2500 = $250 BONUS (65% MIN MARGIN)
$2000 = $200 BONUS (65% MIN MARGIN)
$1500 = $150 BONUS (65% MIN MARGIN)
$1000 = $75 BONUS (65% MIN MARGIN)

I'm new to all this and I'm trying learn as I go, your help is greatly appreciated.

Thank You,
Michael Potter

If I left anything out let me know

 

[just_jon]

Hi, hello and welcome to MrExcel --

Where do we find what their margin was?

 

[atxmichael]

*Im sorry I posted this thread twice, it wasn't done a purpose.


The reps are paid a flat 15% of accessory profit(G24) as you see below in (I24)Proj Acc Comm, no matter there margin (thats easy). I call that their (COMMISSION)

Their BONUS is based on there accessory sales(F24) with a margin(H24) of 65% or higher. When it falls in the range stated in my early post they get paid the corresponding BONUS amount in (J24).

Here is snap shot of the screen:

TRACKING.xls
	B	C	D	E	F	G	H	I	J
22					ProjAcc	ProjAcc	ProjAcc	ProjAcc	ProjBonus
23	Name	AccCost	AccSales	AccProfit	Sales	Profit	Margin	Comm	Comm
24	MichaelPotter	$326.00	$2,360.00	$2,034.00	$3,180.87	$2,741.48	86%	$411.22
Detail Sales-Commission Log

Again I'm sorry for the double post, thanks for help everyone this site has been great!

 

[fairwinds]

Hi,

Try this formula:

=IF((H25<0.65),0,LOOKUP(F25,{0,1000,1500,2000,2500,3000,3500,4000},{0,75,150,200,250,300,350,400}))

in J24

 

[atxmichael]

This formula works to output the right bonus amount based on accessory sales:

=IF(F24>3999,"$400",IF(F24>3499,"$350",IF(F24>2999,"$300",IF(F24>2499,"$250",IF(F24>1999,"$200",IF(F24>1499,"$150",IF(F24>999,"$75","$0")))))))

My question now is I need it to do H24<.65 first or the bonus is $0 how do I apply that to the above formula???

 

[fairwinds]

Hi,

As you can have max 7 nested IF I suggest you use the formula I posted above instead.

If you need textformatted numbers with a $ a sin your formula, you just add that to the formula.

="$"&IF((H25<0.65),0,LOOKUP(F25,{0,1000,1500,2000,2500,3000,3500,4000},{0,75,150,200,250,300,350,400}))

 

[atxmichael]

thanx alot fairwinds, that works perfectly!