Commissions Calculation Excel

[Treeboy]

I am trying to create a sales commissions structure using the IF formulas, perhaps someone can help. The commission structure looks like this; Salesperson monthly quote equals 50,000. Commission structure calculation looks like this. If salesperson performance equals <50% of the budget, 25,000 then commission on the gross profit equals 20%. If the salesperson's performance equals 50% - 80% of the budget then the commission equals 35% of the gross profit. If the salesperson's performance equals 80% - 99% then the commissions paid on the GP equals 40%. If the salesperson's performance equals 100% or more, commission equals 50%.
Please advise, I would greatly appreciate the help.

Thanks

 

[etaf]

you can do a number of ways, tables etc
BUT as you asked for IF
then you would use a NESTED IF

Commission structure calculation looks like this. If salesperson performance equals <50% of the budget, 25,000 then commission on the gross profit equals 20%. If the salesperson's performance equals 50% - 80% of the budget then the commission equals 35% of the gross profit. If the salesperson's performance equals 80% - 99% then the commissions paid on the GP equals 40%. If the salesperson's performance equals 100% or more, commission equals 50%.

=IF( cell with sales in >= 50,000 , GP Cell * 0.5 , IF( cell with sales in >= 40,000, GP Cell * 0.4, IF ( cell with sales in >= 25,000, GP Cell * 0.35, IF ( cell with sales in < 25,000, GP Cell * 0.2, "" ))))

But we could do a % of target and compare that as well
I just hardcoded the values , but it would be better to do the target and compare the actual sales to work out the % and use that, then the target can change
also a lookup table with the % in and values , means you can change various parameters in the future without changing the formula

But that should get you started with a NESTED hardcoded IF
post back how the data is laid out and what cells and we can help further

 

[Treeboy]

you can do a number of ways, tables etc
BUT as you asked for IF
then you would use a NESTED IF


=IF( cell with sales in >= 50,000 , GP Cell * 0.5 , IF( cell with sales in >= 40,000, GP Cell * 0.4, IF ( cell with sales in >= 25,000, GP Cell * 0.35, IF ( cell with sales in < 25,000, GP Cell * 0.2, "" ))))

But we could do a % of target and compare that as well
I just hardcoded the values , but it would be better to do the target and compare the actual sales to work out the % and use that, then the target can change
also a lookup table with the % in and values , means you can change various parameters in the future without changing the formula

But that should get you started with a NESTED hardcoded IF
post back how the data is laid out and what cells and we can help further

Thank you, this did work

 

[etaf]

you are welcome