Maximum sales commission calculation

Sounds like a simple MIN would do.

MIN("commission $", "max commission $")

"Commission $" = formula to calculate commission $ with no limits
"Max Commission $" = formula to calculate max commission $ based on the 25% limit

Bobsterslc wrote:
Here is a screenshot of a piece of what I'm working on.

Hi Bob,

Here's what your screenshot looks like when embedded in a post, using the HTML code that Photobucket provides (third item in the list of codes provided).



In your original post you said:

I have a table that calculates sales commissions for a number of people and includes an overriding commission for the sales managers. I need to cap the sales commission at a fixed percentage, say 25%, for the sales people.

I'm not sure what you mean by "I need to cap the sales commission at a fixed percentage."

The first question that comes to mind is "25% of what?"

Do you mean that the commission is not to exceed 25% of Total earnings?

If so, your formula for B5 (the first cell where commission is calculated) would be:

=MIN( formula1,B2/3)

Where formula1 is whatever formula is used to calculate the commission when it is less than the cap, and B2 contains the salary/wages which must constitute at least 75% of the total compensation under this interpretation.

Do you mean the commission rate is on a sliding scale that rises with increased sales, but does not exceed 25%?

If so, you need to specify the rules that control the rate. Does it rise in steps? Does the 'new' rate at each step apply to total sales, or only to sales above that step? Are the steps the same size for everyone, directly related to the individual goals, or set by some other criteria?

There's not enough information available fom your table to determine a pattern. For the first three columns, person B's commission is 12% of sales, person C's is under 4% and person D's is 30% of sales.

For a more detailed response, we'll ned a more detailed specification of the question.

Regards,
Barry

There are no formulas shown so I will assume the limit on the commission is 25% of the person's salary (row 1) and that your row 5 (commission) is the commision before applying any limits. I will assume that row 5 is calculated with a formula, that you didn't simply type numbers into that row. Rewrite it so it is =MIN(25%*B1, whatever the formula was).

OK, that makes the problem much clearer.

This should calculate the correct commission rate. Multiply by the Sales amount to get the commission amount.

=IF(B3/B4<0.5,0,IF(B3/B4>2.5,0.25,B3/(10*B4)))

Note that there's no rounding in the formula. The rates calculated for the first three months are

0.1207565 0 0.25

or, expressed as percent,

12.07565% 0% 25%

and those actual rates would be used in the calculation.

Regards,
Barry

The formula seems to be correct and so does your table. Or at least it matches your description of what you wanted. Column B shows % of goal met as 288% which would mean a 25% commission (due to the cap). 25% * $11,535 = $2883.75.

I highly recommend keeping a cap. The person will receive 25% of all sales they make. Therefore, there is still a great incentive to keep on selling. Without a cap, a salesperson may have a great (fluke) month that would destroy your margins, maybe even causing a loss. For instance let's say in Month 1 the person sold $40,000 worth of product. Sounds great until you realize that without a cap, the commission would be a whopping 100% of sales. You would be much better off financially if the guy was fired before he did it again.

Also, salespeople are pretty crafty. They can (and will) play games here. With the current structure (with or without a cap), there is an incentive to push sales into next month. For example, lets say I have a goal of $4000 and the opportunity to make $4000 sales this month and $4000 sales next month. If I do that, I will get 10% commission both months, for a total of $800. If I can push some of this month's sales into next month (i.e., delay them a little) so I sell $2000 this month and $6000 next month I will get a commission of 5%*2000 + 15%*6000 for a total of $1,000. Which do you think I will do?

HI Bob,

When plugging in numbers, it's often useful to use numbers which make the result easy to interpret visually. Try 12000 instead of 11535 to make 25% immediately recognizeable. You might also round the salary/wage number for these tests for the same reason.

Here's a graph showing only the commission portion of the earlnings. You can see the curve changes to a straight (25%) line at 10 000 for the blue line ($4000 goal) and at 20 000 for the green line ($8000 goal).



Badunit's comments regarding keeping the cap are well taken.

Regards,
Barry