calculating commission on sliding scale

[corrado444]

Hi, I am a fairly new Excell user and I hope someone here can help me or
point me in the right direction.

I am trying to figure out a fair way to charge commission to a client.
Ideally, my commission should be tied to the price of the item or
service I sell and the quantity of items I sell in a given period.

Originally I wanted to charge commission as follows:

X= sale price
y= quantity sold
z= commission charged in %

The problem I am trying to solve is that I want to charge a lower
commission the more items I sell. In addition I charge the customer a
lower commission as the items increase in price.

I tried to calculate it by setting limits:

sale price quantity sold commission
$0 to $499 0 to 10 15%
$0 to $499 10 to 20 10%
$0 to $499 20 to 40 7%

$500 to $1000 0 to 10 10%
$500 to $1000 10 to 20 7%
$500 to $1000 20 to 30 5%

$1000 to $5000 0 to 10 7%
and so on.

The reason for this sliding scale is that the set up involved in any
given sale is fixed: if I sell one item or a 100 the amount of work is
very nearly identical.
However if I use the model above and sell 9 items I am going to make
more money than if i sold 10 items.

The ideal formula would work by incrementing the commission for every
unit sold, not just in block of 10 or so.

Anyone has encountered this type of formula before?

TIA for your help

 

[JE McGimpsey]

Take a look here:

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

 

[corrado444]

Thank you for your replay.
The formula you pointed me to will work for my initial set up, and I
swear I would have never found it by myself.

The formula I am looking for is one where the commission is
incrementally adjusted according to the sale price and quantity sold.
Conceptually is very similar to this one, but it doesn't relay on
blocks of values, but each value affects the commission incrementally.

In essence, if I sell 7 items under $100 I should get a x commission,
if I sell 8 the commission percentage should go up in increments for
each product sold under $100. Maybe the formula you pointed me to does
that, I'll plug in some real numbers to try, but it looks like it still
needs me to classify sales with a price range and a quantity range to
arrive at a a given percentage.

The goal would be to have each single result affect the commission
without having any ranges at all, either in price nor quantity.

Maybe there is another way to arrive at this, and my logic is faulty.

I am selling expensive restaurant supply on eBay for a dealer. Most of
them will be in the $1000 to $15000 range. In addition I'll be selling
less expensive items to attract more clientele.

The amount of work to list an item is the same whether I sell 1 or a
100 items and whether they cost $1 or $1,000, the only extra work will
be in dealing with closing the sale and the shipping, but the listing
work represents about 70% of the total effort.

Ideally, I would like my commission to go down as sales increase in a
smooth way, without abrupt jumps, say form $199 to $200. In the latter
example, if I sold 10 items at $199, my commission would be X, if I
sold 10 $201 items I would actually make considerably less in
commission if my threshold was $199.

By the same token, if my commission for under $199 is x up to 9 items
sold, and x*0.7 for 10 or more, I would be better off not selling that
last 10th item because I would make less total commission.

The reverse is true for my client.

I hope I am doing a decent job explaining this. It's really confusing.

Thank you again for your help.

 

[JE McGimpsey]

In that case, I would use the table technique that I outline at the site
I cited, but make it two dimensional, e.g.,:

A B C D E
1 1 2 3 4 ...
2 0 x1% x2% x3% x4%
3 100 y1% y2% y3% y4%
4 1000 z1% z2% z3% z4%

where row 1 is the number of units sold in each bracket, and y and z
are incremental percentages, as described.

Then the calculation would be, assuming J1 is the dollar value of the
item and J2 is the number sold:

=SUMPRODUCT(--(J1>$A$2:$A$4),(J1-$A$2:$A$4),
OFFSET($B$2,0,MATCH(J2,$B$1:$E$1,TRUE)-1, 3, 1))

ensuring that the number of rows in OFFSET() equals the number of rows
in the lookup range

 

[JR]

Try this approach. It involves two tables, one for quantity and one for
dollar amount...
B C D E F G H
2 Items Sold Item Price
3 1 5 5% $1.00 $100.00 5%
4 6 10 4% $101.00 $200.00 4%
5 11 15 3% $201.00 $300.00 3%
6 16 20 2% $301.00 $400.00 2%
7
8
9 Quantity Amount Total
10 7 $76.00 $532.00
11 9%
12 $47.88 Comm
The formula in D11 determines the blended commission based on quantity sold
and dollar amount sold. It is...
=VLOOKUP(B10,B36,3,TRUE)+VLOOKUP(C10,F3:H6,3,TRUE) Using TRUE in the
vlookups will return the appropriate % if your criteria falls within a range
in each of the tables (i.e. it is not looking for your EXACT criteria) In
the example above, you sold 7 items (4%) at a cost of $76.00 each (5%) for a
total commission on the $532.00 of 9% (or $47.88). I don't know exactly how
you track your sales but, this should give you a base to mold to your needs.

Regards,
JR