Sliding commission scale - help!

[kr4]

Hi everyone,

I am a recruiter and have a tiered commission system. We have 3 tiers, 10%, 20% and 30%. We have "deals" which is where you add something to your billings.


What tier your commission for each "deal" comes to, is based on what your total is for the quarter so far. For example:

If your quarterly total is below £30,000, any "deal" you put on is 10% commission (For example at the start of the quarter putting on £28,000 would give you £2800)

If your quarterly total lands between £30,000 and £50,000, the proportion of any "deal" you put on that sits within this £30k to £50k bracket is 20% commission. (For example, if I was on £28,000 and then added an extra £10,000 "deal", £2,000 of this would be 10% commission and then the remaining £8000 would be 20% commission (giving me a total of £1800 commission for that "deal")

If your quarterly total is anything above £50,000 for the quarter, then the proportion of any "deal" you put on that is above the £50k bracket is 30% commission. (For example, if I was on £38,000 and then added an extra £20,000 "deal", £12,000 of this would be 20% commission and then the remaining £8000 would be 30% commission (giving me a total of £4,800 for that "deal")

My aim is to have a system where I can note each "deal" one by one, in a column that adds up my total billings to show my total for the quarter, but then for it to work out how much commission I will get in a separate column from each "deal" based on the information above!

Any help at all would be amazing!

Thanks,
Kiran

 

[Dave Patton]

Commissions 2023.xlsm
	A	B	C	D
1		Commission
2	Total Deals	58,000
3	Total	9,400	9,400	9,400
4
5	Deal	1	2	3
6	Amount	28,000.00	10,000.00	20,000.00
7	Commission	2,800.00	1,800.00	4,800.00
8	cumulative	2,800.00	4,600.00	9,400.00
9
10	Bracket	Rate
11
12	0	10%
13	30,000	20%
14	50,000	30%
2b

Cell Formulas
Range	Formula
B3	B3	=SUM((B2>A12:A14)*(B2-A12:A14)*(B12:B14-B11:B13))
C3	C3	=SUM((B2>{0;30000;50000})*(B2-{0;30000;50000})*({0.1;0.1;0.1}))
D3	D3	=Rev_r(B2)
B7:D7	B7	=Rev_r(SUM($B$6:B6))-SUM($A$7:A7)
B8:D8	B8	=Rev_r(SUM($B$6:B6))

I show 3 versions/alternatives of the same formula; you can try each and use the one that you prefer.
Rev_r is the Lambda version. It shows the parameter CumulDeals that you enter.

With Name Manager
new Rev_r
value =LAMBDA(CumulDeals,LET(u,CumulDeals,b, {0;30000;50000},r, {0.1;0.1;0.1}, SUM((u> b) * (u- b) * r)))

 

[Dave Patton]

Commissions 2023.xlsm
	A	B	C	D	E	F	G
15					or
16	Deal	Amount	Commission	Cumulative	Cumulative	Cumulative
17	1	28,000.00	2,800.00	2,800.00	2,800.00	28,000.00	2,800.00
18	2	10,000.00	1,800.00	4,600.00	4,600.00	38,000.00	4,600.00
19	3	20,000.00	4,800.00	9,400.00	9,400.00	58,000.00	9,400.00
20	4	5,000.00	1,500.00	10,900.00	10,900.00	63,000.00	10,900.00
2b

Cell Formulas
Range	Formula
C17:C20	C17	=Rev_r(SUM($B$17:B17))-SUM($C$16:C16)
D17:D20	D17	=C17+N(D16)
E17:E20	E17	=Rev_r(SUM($B$17:B17))
F17:F20	F17	=SCAN(0,B17:B20,LAMBDA(s,c,s+c))
G17:G20	G17	=Rev_r(F17)
Dynamic array formulas.

 

[ErwinHenninger]

Hi everyone,

I am a recruiter and have a tiered commission system. We have 3 tiers, 10%, 20% and 30%. We have "deals" which is where you add something to your billings.


What tier your commission for each "deal" comes to, is based on what your total is for the quarter so far. For example:

If your quarterly total is below £30,000, any "deal" you put on is 10% commission (For example at the start of the quarter putting on £28,000 would give you £2800)

If your quarterly total lands between £30,000 and £50,000, the proportion of any "deal" you put on that sits within this £30k to £50k bracket is 20% commission. (For example, if I was on £28,000 and then added an extra £10,000 "deal", £2,000 of this would be 10% commission and then the remaining £8000 would be 20% commission (giving me a total of £1800 commission for that "deal")

If your quarterly total is anything above £50,000 for the quarter, then the proportion of any "deal" you put on that is above the £50k bracket is 30% commission. (For example, if I was on £38,000 and then added an extra £20,000 "deal", £12,000 of this would be 20% commission and then the remaining £8000 would be 30% commission (giving me a total of £4,800 for that "deal")

My aim is to have a system where I can note each "deal" one by one, in a column that adds up my total billings to show my total for the quarter, but then for it to work out how much commission I will get in a separate column from each "deal" based on the information above!

Any help at all would be amazing!

Thanks,
Kiran


Have you resolved your problem? I'm a student at university with an academic assignment to finish, but I don't have a lot of time. I searched online for help, and I came across Navigating the World of MBA Essay Help as a Busy Student site, which instructs me on how to write an essay and enables me to finish it quickly with the aid of the examples I can see on the website.

Have you resolved your problem?

 

[Dave Patton]

Welcome to the forum.

You can try the suggested solutions.
Click on the icon below the f(x) in the post's heading, then move to a clean sheet and paste.
or
You can post a clear explanation of your question in a new thread.

or you can post your suggestion

​

 

[Dave Patton]

To ErwinHenninger
Since you have an older version of Excel, you would use SumProduct or array enter the sum formula.

Commissions 2023.xlsm
	A	B			E
1	Total Deals	58,000
2	Total Commission	9,400			9,400
3
2b

Cell Formulas
Range	Formula
B2	B2	=SUM((B1>A11:A13)*(B1-A11:A13)*(B11:B13-B10:B12))
E2	E2	=SUMPRODUCT(--(B1>A11:A13),(B1-A11:A13)*(B11:B13-B10:B12))