Commission over 3month hurdle

[madmansgates]

Hi,
Is there a way of getting excel to work out monthly commission on the following basis:

Commission rates:
0 - 20,000 - 10%
20,001 - 40,000 - 15%
40,001 - 60,000 - 20%
60,001 - 25%

In any 3 months commission is paid monthly depending on sales per month over 3 months commision hurdle.
eg: Month 1 Sales of 15k is 15k@10%=1500 commission
Month 2 sales of 20k will have commision of 5k@10%(to reach 20k hurdle) and 15k@15% =total of 2750,
Month 3 sales of 10k will have commission of  5k @15% (40k hurdle) & 5k at 20% total of 1750

Any help would be appreciated
Thanks

 

[madmansgates]

West Man,

Thanks for your help with this query

 

[madmansgates]

Hi Dave,

Thanks for your help with this.

 

[Spark_NJ]

I have found a potential issue. If in the first month, January, I enter 45,000 or 65,000 the commission value is not correct. It is reflecting a lower average rate than what should have been earned. I have toyed around quite a bit with the formula but cannot seem to get it to reflect the correct Rate if the sales person was to have a "stellar" first month.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Any help in correcting this is appreciated.

 

[mgirvin]

Spark NJ, I may not be the most qualified to answer, but at $45,000 I would make this "by hand" calculation:

20000*0.1+(40000-20000)*0.15+(45000-40000)*0.2 = $6000

or use this formula adaptation from Dave Patton:

Excel Workbook
	A	B	C	D
1	Hurdle	Rate
2
3	0	10.0%
4	20,000	15.0%
5	40,000	20.0%
6	60,000	25.0%
7
8		Jan	Feb	Mar
9	Sales	45,000.00	20,000.00	10,000.00
10	Formula 1	6,000.00	4,250.00	2,500.00
...

Cell Formulas
Range	Formula
B10	=SUMPRODUCT(--(SUM($B9:B9)>=$A$3:$A$6),SUM($B9:B9)-$A$3:$A$6,$B$3:$B$6-$B$2:$B$5)-SUM($A10:A10)
C10	=SUMPRODUCT(--(SUM($B9:C9)>=$A$3:$A$6),SUM($B9:C9)-$A$3:$A$6,$B$3:$B$6-$B$2:$B$5)-SUM($A10:B10)
D10	=SUMPRODUCT(--(SUM($B9:D9)>=$A$3:$A$6),SUM($B9:D9)-$A$3:$A$6,$B$3:$B$6-$B$2:$B$5)-SUM($A10:C10)

at $65,000 I would make this "by hand" calculation:

20000*0.1+(40000-20000)*0.15+(60000-40000)*0.2+(65000-60000)*0.25 = $10,250

or use this formula adaptation from Dave Patton:

Excel Workbook
	A	B	C	D
1	Hurdle	Rate
2
3	0	10.0%
4	20,000	15.0%
5	40,000	20.0%
6	60,000	25.0%
7
8		Jan	Feb	Mar
9	Sales	65,000.00	20,000.00	10,000.00
10	Formula 1	10,250.00	5,000.00	2,500.00
...

Cell Formulas
Range	Formula
B10	=SUMPRODUCT(--(SUM($B9:B9)>=$A$3:$A$6),SUM($B9:B9)-$A$3:$A$6,$B$3:$B$6-$B$2:$B$5)-SUM($A10:A10)
C10	=SUMPRODUCT(--(SUM($B9:C9)>=$A$3:$A$6),SUM($B9:C9)-$A$3:$A$6,$B$3:$B$6-$B$2:$B$5)-SUM($A10:B10)
D10	=SUMPRODUCT(--(SUM($B9:D9)>=$A$3:$A$6),SUM($B9:D9)-$A$3:$A$6,$B$3:$B$6-$B$2:$B$5)-SUM($A10:C10)

Then in this second example I have shown, the amount for Feb is simply:

20000*0.25 = $5000

Does that look like the calculation methodology and expected result you are after?

 

[Spark_NJ]

Thank you for the quick reply. Yes, the manual process works well for the first month but then the question is really why doesn't the formula work for the first month as it does for all of the rest of the months?

Having to hard code in the formula for the first month, while easy, takes away from the beauty of the formula.

 

[mgirvin]

How about the examples in post #14 for $65,000 and $45,000? I got the same answer manually and with the formula.

 

[Spark_NJ]

I believe I understand what is happening. Even though the first month sales are 65K in the example, the formula still breaks down the results and pays the portions of sales at rates ramping up to the 25% rate.

The formula is taking into account the Hurdles that the commissionable sales must pass through before the sales person is rewarded at the higher percentage even though the sales were all achieved in the first month.

 

[mgirvin]

Yes, is that the calculating method you want?

 

[shg]

This is the same as suggested by Dave Patton, with a pair of blank rows:

       -A- --B--- ---C--- -------D-------
   1       Sales   Comm                  
   2                      leave row blank
   3            0     10%                
   4       20,000     15%                
   5       40,000     20%                
   6       60,000     25%                
   7                                     
   8                      leave row blank
   9   Jan 65,000 10,250                 
  10   Feb 20,000  5,000                 
  11   Mar 10,000  2,500

The formula in C9 and copied down is

=SUMPRODUCT( (SUM(B$8:B9) > $B$3:$B$6) * (SUM(B$8:B9) - $B$3:$B$6) * ($C$3:$C$6 - $C$2:$C$5)) - SUM(C$8:C8)

 

[Spark_NJ]

@ mgirvin - While I didn't think so initially but after thinking through the logic a bit more and working through the scenarios in my mind, it actually is exactly what I want. - Thanks!