Variable Commission Schedule

[la90292]

I need to create a commission schedule work sheet with variable commission
rates. I need to solve for (x) where (x) is the "Override Rate" comparable
to "Base Comm. Rate" paid. For example, based on $100 of sales the base
commission earned would be $11 and the override commission earned would be
$1. Consequently, when the base commision rate is adjusted according to
this schedule, what would be the rate for the override commission. Thanks
for your help.

Base Override
Comm. Comm.
Rate Rate
11% 1%
10
9
8
6
5
3
2

Cheers, victor

 

[Ron de Bruin]

Take a look here first
http://www.mcgimpsey.com/excel/variablerate.html

 

[la90292]

This really didn't help me because I am trying to compute the commission
rates.

 

[JE McGimpsey]

You haven't given enough information to know how to calculate the
Override Rate.

 

[la90292]

What additional info is required to solve for x? Using my $100 sales
example, if the base commisson rate goes to 10% (base commission rates are
based on discount off a base unit price; however, the discounts are not
needed to solve for x because we are solving for x based on the revised base
commission rates.)

The problem is to compute what would be
the override rate if the commission has been reduced to 10%. Working this
out manually, the $100 sale would earn $10 or 10% in base commissions. This
is a reduction in earned $ of $1 from the 11% base rate that
earns an additional 1% or $1 in override. So, to solve for the base
commission rate deduction, $1/$11 = .090909.

To solve for the comparable override rate/commission (note we are using the
$100 sale example which earned $1 based on 1%), we multiply .090909 * $1 =
$0.090909, then subtract $1 -$0.090909 = $0.91. The override rate would be
0.91% or $0.91 based on the $100 sale.

I would like to be able able to compute the others without having to do them
manually.

I hope you see where the problem is now. Thanks.

 

[JE McGimpsey]

Now that you've explained that the change in "override rate" (whatever
that means) is proportional (which I suppose is what you meant by
comparable) to the change in base rate, rather than being based on
something else, the solution is clear:

A2: original base rate
A3-An: new base rates
B2: original override rate

then calculate the new override rate as

B3: =ROUND(B$2*A3/A$2,4)

copied down as far as necessary, and formatted as a percentage.

 

[la90292]

Thanks for the formula. It worked perfect.

Now that you've explained that the change in "override rate" (whatever
that means) is proportional (which I suppose is what you meant by
comparable) to the change in base rate, rather than being based on
something else, the solution is clear:

A2: original base rate
A3-An: new base rates
B2: original override rate

then calculate the new override rate as

B3: =ROUND(B$2*A3/A$2,4)

copied down as far as necessary, and formatted as a percentage.