Hi, hoping someone could assist with formula for calculating performance bonus for sales staff.
Scenario as follows:
Please see attached screenshot of sheet where I have tried to lay out options.
Explanation of Cells.
B1 – Target as agreed with sales rep.
B2 – Actual figure achieved
B3 - % achieved – Formula in place in sheet
B4 - % agreed with rep to be paid only if B1 is achieved, failure results = Zero Base commission.
B5 – Basic Salary of Rep.
Base Commission calc.
=Achieved(B2)-Base Salary(B5)* Base Commission to reach Target(b4) = Base Commission(B7)
Bonus Commission Logic (I hope makes sense) and formula required for B9
If B2 = or > C2,C3,C4,C5 of B2 – then B2 – B1 x Corresponding % shown in D column for C column achieved over B2.
For example, in B9 of my attached sheet I have manually calculated bonus commission if rep achieved 35% over their agreed target. Effectively, they would earn 2.5% of the difference between agreed target and achieved target.
Scenario as follows:
Please see attached screenshot of sheet where I have tried to lay out options.
Explanation of Cells.
B1 – Target as agreed with sales rep.
B2 – Actual figure achieved
B3 - % achieved – Formula in place in sheet
B4 - % agreed with rep to be paid only if B1 is achieved, failure results = Zero Base commission.
B5 – Basic Salary of Rep.
Base Commission calc.
=Achieved(B2)-Base Salary(B5)* Base Commission to reach Target(b4) = Base Commission(B7)
Bonus Commission Logic (I hope makes sense) and formula required for B9
If B2 = or > C2,C3,C4,C5 of B2 – then B2 – B1 x Corresponding % shown in D column for C column achieved over B2.
For example, in B9 of my attached sheet I have manually calculated bonus commission if rep achieved 35% over their agreed target. Effectively, they would earn 2.5% of the difference between agreed target and achieved target.
