Commission Schedule Work Sheet

[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

 

[Peo Sjoblom]

Homework?

For a possible solution see:

http://www.mcgimpsey.com/excel/variablerate.html

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[la90292]

This site didn't help much because I am trying to calculate the comparable
commission rates.

 

[la90292]

Here's some addtional info:

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

 

[RagDyer]

Please don't multi-post.

Responses received in one group cannot be seen in the other groups,
therefore yielding the distinct possibility of many duplicate replies, and
the waste of time of the people who volunteer their time here.

See J.E.'s answer in the Functions group.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Here's some addtional info:

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]

See reply in microsoft.public.excel.worksheet.functions