Commission structure and calculation

Jimmya006

New Member
Joined
Feb 24, 2021
Messages
6
So I’ve browsed all the previous threads and most already have a plan and structure this is firstly where we need help but then to put it into practice please.

Our business currently pays our only sales member of staff as per the formula below;

As it stands we do circa £100k gross of sales at around 20% which equates to £17k gross a month.

Sales Person 1 gets 5% of the first £8000 and then 10% thereafter, so on currently levels around £1400 commission That’s for all sales.

Now here is the problem... We’ve employeed a 2nd sales person and dont want to disincentivse 1st sales person.

So Sales person 2 doesn’t need to be the same but we need to keep focus on margin, ensure sales person 1 doesn’t go backwards, keep them caring about every order not just their own and drive it forward. I’m struggling as to how to do it.

Please can anyone help us as best we can. Any help is greatly appreciated. Someone else must have been in the same boat as us! Driving me mad!
 
Commission2020.xlsm
ABCDE
1Gross120,000.00
220%24,000.00
3Sales12,550.00
1b
Cell Formulas
RangeFormula
D2D2=D1*C2
D3D3=SUMPRODUCT(--(D2>aB),D2-aB,aR)+LOOKUP(D1,Bonus)


For details, see previous post.
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,214,823
Messages
6,121,779
Members
449,049
Latest member
greyangel23

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