SOLVED

Commission calculation based on variable targets

Occasional Contributor

Hello Excel Masters and Experts,

I am stuck in a formula, i need your help to solve it.

I'm attaching an excel sheet and all the information has been written on it. (cOMMISSION CAL.xlsx)

Looking forward to the solution.

Thanks in advance

9 Replies

@fzn365 

When exactly does the sales rep get 2% commission? When they sell up to 250 units above target, or when they sell at least 250 units above target?

And what (which column) is the commission percentage applied to?

@Hans Vogelaar,
Let say, The target of person A is 500 units, and he sold 650 units, the commission will be in excess (150 units). and commission will be calculated in the 3rd yellow highlighted column (column name in the sheet is (Comm %).

Person B, has a target of 833 units to sell, he sold 751, so no commission
another example of person B, target of person B is 833 units, he sold 945, so his commission will be on (945-833) 112 units

Target Brackets are mentioned in the sheet are below.


Commission Benchmark Commission %
Target unit + 250 surplus 2
Target unit + 500 surplus 3
Target unit + 750 surplus 4
Target unit + 1000 surplus 5
Target unit + 1500 surplus 5.5
Commission Benchmark
Target unit + 250 surplus - Commission %2
Target unit + 500 surplus - Commission %3
Target unit + 750 surplus - Commission %4
Target unit + 1000 surplus - Commission %5
Target unit + 1500 surplus - Commission %5.5

@fzn365 

Could you be so kind to answer the question that I asked?

The answer of Q1: When they sell up to 250 units above target,
The answer of Q2: Under the Commission Calculations section there is a column (sold amount), the percentage will be applied to that.

@fzn365 

 

Thank you. So the sales rep gets 5.5% commission if they sell between 1000 and 1500 units above target.

But what if they sell more than 1500 units above target? Does it remain 5.5%?

That's correct:
if they sell more than 1500 units above target, it will remain 5.5%
best response confirmed by fzn365 (Occasional Contributor)
Solution

@fzn365 

OK, so the 1500 doesn't play any role in this. See the attached version. I created a small lookup table and used a VLOOKUP formula.

@Hans Vogelaar Thank you. Got the solution