Bonus/Commission Template

[j4y4sha]

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:

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

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

 

[Dave Patton]

It is good that you received the formula that you need.
This illustrates that it is important to explain and show what one requires.
You do not calculate by tiers/brackets but build a composite rate.
SumProduct, Sum, or arithmetic will also provide your result.

Commissions 2024.xlsm
	A	B	C	D	E	F	G
8	Value	Amount		Target	Achieved	Percent	Bonus £
9	2022	1,700,000.00		-10.0%	Bonus1	0.075%	112.50
10	2023	1,850,000.00		0.0%	Bonus2	0.100%	150.00
11				2.5%	Bonus3	0.150%	225.00
12	Difference Value	150,000.00		5.0%	Bonus4	0.250%	375.00
13				7.5%	Bonus5	0.400%	600.00
14		8.82%		10.0%	Bonus6	0.650%	0.00
15				15.0%	Bonus7	1.000%	0.00
16	Bonus
17					Bonus Paid		1,462.50
18
19					Bonus5		1,462.50
20
1aaa

Cell Formulas
Range	Formula
B12	B12	=B10-B9
B14	B14	=B12/B9
G9:G15	G9	=F9*$B$12*(D9<=$B$14)
G17	G17	=SUM(G9:G13)
E19	E19	=INDEX(E9:E15,MATCH(B14,D9:D15,1))
G19	G19	=SUM((D9:D15<=B14)*F9:F15*B12)

 

[j4y4sha]

It is good that you received the formula that you need.
This illustrates that it is important to explain and show what one requires.
You do not calculate by tiers/brackets but build a composite rate.
SumProduct, Sum, or arithmetic will also provide your result.

Commissions 2024.xlsm
	A	B	C	D	E	F	G
8	Value	Amount		Target	Achieved	Percent	Bonus £
9	2022	1,700,000.00		-10.0%	Bonus1	0.075%	112.50
10	2023	1,850,000.00		0.0%	Bonus2	0.100%	150.00
11				2.5%	Bonus3	0.150%	225.00
12	Difference Value	150,000.00		5.0%	Bonus4	0.250%	375.00
13				7.5%	Bonus5	0.400%	600.00
14		8.82%		10.0%	Bonus6	0.650%	0.00
15				15.0%	Bonus7	1.000%	0.00
16	Bonus
17					Bonus Paid		1,462.50
18
19					Bonus5		1,462.50
20
1aaa

Cell Formulas
Range	Formula
B12	B12	=B10-B9
B14	B14	=B12/B9
G9:G15	G9	=F9*$B$12*(D9<=$B$14)
G17	G17	=SUM(G9:G13)
E19	E19	=INDEX(E9:E15,MATCH(B14,D9:D15,1))
G19	G19	=SUM((D9:D15<=B14)*F9:F15*B12)

Thank you and sorry I tried to explain to the best of my abilities, and it has been a long time since I have used excel.
Again, thank you to you both for the replies and solutions. it is a massive help for me.