Bonus/Commission Template

J

j4y4sha

New Member
Joined
Jan 16, 2024
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi all, first time posing here (please be kind) i saw a thread as below for a sliding bonus scheme that looks close to what i require:

www.mrexcel.com

Sliding incremental bonus calculation

Hi there, I haven't used this forum for a while but I'm desperately in need of some help! I've been asked to put together a bonus calculator for a new bonus scheme at work. The scheme works like this; Assuming John hits his target i.e. D6 is greater than 0 then; - He gets 10% (D11) of the...
www.mrexcel.com www.mrexcel.com

I was hoping someone had something similar they could send me that I could adapt to suit my requirements.
What I am looking for is a template where I can put in say 2022 total annual sales figure as a base and then under this enter 2023 total annual sales figure & the work out the difference and percentage increase/decrease. Below this then a scale of:
-10%-0% - xx bonus,
0%-2.5% - xx bonus
2.6% 5%- xx bonus

etc up to 10% and then 10-15% & 15% plus

From there I can edit the percentage increases and the percentage value received if that makes sense.

Any help would be greatly apprecaited.

Thanks

James
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
you want the % worked out from the base
so in example
2022 = base
2023
1 difference
1/2022 =
postive difference
0.049455984%

so as between 0 and 2.5% - then apply that bonus -

i have laid out an idea here

you have 2.5 on 1 scale and 2.6 on another
what about 2.55 ????
need to adjust table to correct range

Book5
ABCDEFG
1difference %bonus
22022-0.1Bonus1
3202310.00049456Bonus20Bonus2
40.025Bonus3
50.05Bonus4
Sheet1
Cell Formulas
RangeFormula
B3B3=A3-A2
C3C3=B3/A2
D3D3=INDEX(G2:G5,MATCH(C3,F2:F5,1))
 
Upvote 0
Solution
Your first post was on a thread with tiered formulas. " Hi, sorry to jump on this post, but this is exactly the kind of template I am looking for. Any chance someone can forward me a copy of this please. Thank you".
You requested a template. I can post an updated version of the thread that you referred to.
Please provide a concise example of your challenge. Ensure that you provide information on brackets, rates and expected calculated amount.
Your information on the brackets would be clearer with an example.
N.B. My post is relevant for tiered formulas with brackets calculated by formula.
Part of the post is included so we can see the results and calculate it with arithmetic.
You can copy the example below to a clean sheet. Click on the icon below the f(x) in the header, move to your sheet's cell A1 and paste.
Commissions 2024.xlsm
ABCDEF
1Rate
2ArithmeticDifferential
300.0010%4,250.0010%
40.0542,500.0015%6,375.005%
50.185,000.0020%8,500.005%
60.15127,500.0030%12,750.0010%
70.2170,000.0040%32,000.0010%
863,875.00
9
10
11Target850,000.00
12Achieved1,100,000.00
13> Target250,000.00
14Percentage29.411765%
15Bonus63,875.0063,875.00
16
1a
Cell Formulas
RangeFormula
B3:B7B3=A3*$B$11
F3F3=C3-N(C2)
F4:F7F4=C4-C3
D3:D7D3=MAX(0,(MIN($B$13,B4)-B3))*C3
D8D8=SUM(D3:D7)
B13B13=B12-B11
B14B14=B13/B11
C15C15=LET(b,{0;0.05;0.1;0.15;0.2}*B11,SUMPRODUCT(--(B13>b),B13-b,{0.1;0.05;0.05;0.1;0.1}))
D15D15=LET(b,{0;0.05;0.1;0.15;0.2}*B11,SUM((B13>b)*(B13-b)*{0.1;0.05;0.05;0.1;0.1}))
Named Ranges
NameRefers ToCells
aB='1a'!$B$3:$B$7D3

or
Commissions 2024.xlsm
ABCD
10
11Target850,000.00
12Achieved1,100,000.00
13> Target250,000.00
14
1563,875.0063,875.00
16
1a
Cell Formulas
RangeFormula
B13B13=B12-B11
C15C15=LET(b,{0;0.05;0.1;0.15;0.2}*B11,SUMPRODUCT(--(B13>b),B13-b,{0.1;0.05;0.05;0.1;0.1}))
D15D15=LET(b,{0;0.05;0.1;0.15;0.2}*B11,SUM((B13>b)*(B13-b)*{0.1;0.05;0.05;0.1;0.1}))
 
Upvote 0
Thank you for the responses, will give both a try and let you know how I get on with them. A bit of a noob (or not used spreadsheets in a while so I may ask some stupid questions) :)
 
Upvote 0
etaf said:
you want the % worked out from the base
so in example
2022 = base
2023
1 difference
1/2022 =
postive difference
0.049455984%

so as between 0 and 2.5% - then apply that bonus -

i have laid out an idea here

you have 2.5 on 1 scale and 2.6 on another
what about 2.55 ????
need to adjust table to correct range

Book5
ABCDEFG
1difference %bonus
22022-0.1Bonus1
3202310.00049456Bonus20Bonus2
40.025Bonus3
50.05Bonus4
Sheet1
Cell Formulas
RangeFormula
B3B3=A3-A2
C3C3=B3/A2
D3D3=INDEX(G2:G5,MATCH(C3,F2:F5,1))
Click to expand...
Hi etaf,

Thank you for the reply to my post, I think your sheet is closer to my requirements. I have been using this and adding in percentages etc (these may change going forward but easy enough to sort out)

Is there a way that the Bonus Paid amount can display either of the 2 below options:

Opt.1 - if Bonus 5 is reached, then it displays this in the Bonus paid box
Opt.2 - if Bonus 5 is reached, then it displays the total sum of Bonus1-Bonus5 added together

The above is just a reference, obviously I require whichever bonus is reached to display as above.

Bonus-Commission.xlsx
ABCDEFG
8ValueAmountTargetAchievedPercentBonus £
92022£1,700,000.00-10%Bonus10.0008£112.50
102023£1,850,000.000%Bonus20.0010£150.00
113%Bonus30.0015£225.00
12Difference %5%Bonus40.0025£375.00
138%Bonus50.0040£600.00
14£150,000.009%10%Bonus60.0065£975.00
1515%Bonus70.0100£1,500.00
16Bonus
17Bonus Paid£1,462.50
18Bonus5
TEST 2
Cell Formulas
RangeFormula
A14A14=B10-B9
B14B14=A14/B9
G9:G15G9=F9*$A$14
G17G17=SUM(G9:G13)
A18A18=INDEX(E9:E15,MATCH(B14,D9:D15,1))


Thanks in advance

James
 
Upvote 0
how about
=SUMIF(D9:D15,"<="&B14,G9:G15)

H17
if i understand correctly

Book9
ABCDEFGH
8ValueAmountTargetAchievedPercentBonus £
920221700000-0.1Bonus10.00075112.5
10202318500000Bonus20.001150
110.025Bonus30.0015225
12Difference %0.05Bonus40.0025375
130.075Bonus50.004600
141500000.0882352940.1Bonus60.0065975
150.15Bonus70.011500
16Bonus
17Bonus Paid1462.51462.5
18Bonus5
Sheet1
Cell Formulas
RangeFormula
A14A14=B10-B9
B14B14=A14/B9
G9:G15G9=F9*$A$14
G17G17=SUM(G9:G13)
H17H17=SUMIF(D9:D15,"<="&B14,G9:G15)
A18A18=INDEX(E9:E15,MATCH(B14,D9:D15,1))
 
Upvote 0
etaf said:
how about
=SUMIF(D9:D15,"<="&B14,G9:G15)

H17
if i understand correctly

Book9
ABCDEFGH
8ValueAmountTargetAchievedPercentBonus £
920221700000-0.1Bonus10.00075112.5
10202318500000Bonus20.001150
110.025Bonus30.0015225
12Difference %0.05Bonus40.0025375
130.075Bonus50.004600
141500000.0882352940.1Bonus60.0065975
150.15Bonus70.011500
16Bonus
17Bonus Paid1462.51462.5
18Bonus5
Sheet1
Cell Formulas
RangeFormula
A14A14=B10-B9
B14B14=A14/B9
G9:G15G9=F9*$A$14
G17G17=SUM(G9:G13)
H17H17=SUMIF(D9:D15,"<="&B14,G9:G15)
A18A18=INDEX(E9:E15,MATCH(B14,D9:D15,1))
Click to expand...
Thank you for the quick response.
Yes this works correctly for Opt.2.

For "Opt.1 - if Bonus 5 is reached, then it displays this in the Bonus paid box."

So for this, I just want it to display the £ value that is associated with the bonus number itself, not combined bonus1-bonus5, just bonus5... If that makes sense.

Thank you :)
 
Upvote 0
is this what you mean ?
=INDEX(G9:G15,MATCH(B14,D9:D15,1))

Book9
ABCDEFGH
8ValueAmountTargetAchievedPercentBonus £
920221700000-0.1Bonus10.00075112.5
10202318500000Bonus20.001150
110.025Bonus30.0015225
12Difference %0.05Bonus40.0025375
130.075Bonus50.004600
141500000.0882352940.1Bonus60.0065975
150.15Bonus70.011500
16Bonus
17Bonus Paid1462.51462.5
18600
Sheet1
Cell Formulas
RangeFormula
A14A14=B10-B9
B14B14=A14/B9
G9:G15G9=F9*$A$14
G17G17=SUM(G9:G13)
H17H17=SUMIF(D9:D15,"<="&B14,G9:G15)
A18A18=INDEX(G9:G15,MATCH(B14,D9:D15,1))
 
Upvote 0
etaf said:
is this what you mean ?
=INDEX(G9:G15,MATCH(B14,D9:D15,1))

Book9
ABCDEFGH
8ValueAmountTargetAchievedPercentBonus £
920221700000-0.1Bonus10.00075112.5
10202318500000Bonus20.001150
110.025Bonus30.0015225
12Difference %0.05Bonus40.0025375
130.075Bonus50.004600
141500000.0882352940.1Bonus60.0065975
150.15Bonus70.011500
16Bonus
17Bonus Paid1462.51462.5
18600
Sheet1
Cell Formulas
RangeFormula
A14A14=B10-B9
B14B14=A14/B9
G9:G15G9=F9*$A$14
G17G17=SUM(G9:G13)
H17H17=SUMIF(D9:D15,"<="&B14,G9:G15)
A18A18=INDEX(G9:G15,MATCH(B14,D9:D15,1))
Click to expand...
Yes that works perfectly.... Thank you so much (y)
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question Question
Multi tiered commission structure formula help
Replies
2
Views
128
Dave Patton
D
A
  • Question Question
Annual Bonus Prorated
Replies
1
Views
1K
t0ny84
T
J
  • Question Question
Bonus Calculation Spreadsheet
2
Replies
10
Views
4K
Dave Patton
D
T
  • Question Question
Catch up commission plan - need help with the formula
Replies
1
Views
451
StephenCrump
S
J
  • Solved
Excel formula help!!!
Replies
4
Views
504
felixstraube
F

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top