hellobadger
New Member
- Joined
- Jan 8, 2014
- Messages
- 6
Hello,
I've been hunting around and can't find the answer to my problem anywhere. Here is the commission spreadsheet I want to make for sales staff:
Sales staff have a software sales target (eg $500,000). Cumulative on sales through the year they receive:
- 2.5% on sales up to 25% of their target,
- 5% on sales between 25% and 75% of their target,
- 7.5% on sales between 75% and 100% of their target,
- 10% on sales over 100% of target.
Simple enough (ish) using LOOKUP right? I used the following tutorial and nearly got there Excel Magic Trick 673: Varying Commission Rate Lookup Formula SUMPRODUCT Amazing Solution!! - YouTube But it gets more complicated! Sales are made up of 'services' sold and 'licences' sold. The percentage commission stated above relate to 'licences' sold when the TOTAL sale is between the relevant percentages of target. All services are paid at 3% commission. Do you see the difficulty?
For example, if a sales member sells $50,000 in January where $10,000 is services and $40,000 is licences, they would receive 3% of the $10,000 and 2.5% of the 40,000 because the total number ($50,000) is less than 25% of their annual target of $500,000. If the same sales rep sells £100,000 later in January or later in the year where $50,000 is services and $50,000 is licences, they would receive 3% of $50,000 for the services sold but the licences is a bit more difficult. The total sales for the year are now $150,000 which is 30% of their annual target. Therefore, they receive 5% of licences on all amounts over a total of 25% of target - 5% on $25,000 and 2.5% on new licences for the remaining amount below the 25% threshold - 2.5% on $25,000.
Can anyone help me at all? I'd like to be able to simply enter the total sale amount and the split between services and licences and the commission be calculated. I could run this monthly or quarterly. Please let me know if you would like a working spreadsheet that I already have downloaded from the link above.
Many thanks,
Andy.
I've been hunting around and can't find the answer to my problem anywhere. Here is the commission spreadsheet I want to make for sales staff:
Sales staff have a software sales target (eg $500,000). Cumulative on sales through the year they receive:
- 2.5% on sales up to 25% of their target,
- 5% on sales between 25% and 75% of their target,
- 7.5% on sales between 75% and 100% of their target,
- 10% on sales over 100% of target.
Simple enough (ish) using LOOKUP right? I used the following tutorial and nearly got there Excel Magic Trick 673: Varying Commission Rate Lookup Formula SUMPRODUCT Amazing Solution!! - YouTube But it gets more complicated! Sales are made up of 'services' sold and 'licences' sold. The percentage commission stated above relate to 'licences' sold when the TOTAL sale is between the relevant percentages of target. All services are paid at 3% commission. Do you see the difficulty?
For example, if a sales member sells $50,000 in January where $10,000 is services and $40,000 is licences, they would receive 3% of the $10,000 and 2.5% of the 40,000 because the total number ($50,000) is less than 25% of their annual target of $500,000. If the same sales rep sells £100,000 later in January or later in the year where $50,000 is services and $50,000 is licences, they would receive 3% of $50,000 for the services sold but the licences is a bit more difficult. The total sales for the year are now $150,000 which is 30% of their annual target. Therefore, they receive 5% of licences on all amounts over a total of 25% of target - 5% on $25,000 and 2.5% on new licences for the remaining amount below the 25% threshold - 2.5% on $25,000.
Can anyone help me at all? I'd like to be able to simply enter the total sale amount and the split between services and licences and the commission be calculated. I could run this monthly or quarterly. Please let me know if you would like a working spreadsheet that I already have downloaded from the link above.
Many thanks,
Andy.
