+ Reply to Thread
Results 1 to 5 of 5

calculating commission on sliding scale

  1. #1
    Registered User
    Join Date
    04-01-2004
    Location
    California
    Posts
    2

    calculating commission on sliding scale

    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

  2. #2
    JE McGimpsey
    Guest

    Re: calculating commission on sliding scale

    Take a look here:

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

    In article <[email protected]>,
    corrado444 <[email protected]>
    wrote:

    > 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


  3. #3
    Registered User
    Join Date
    04-01-2004
    Location
    California
    Posts
    2

    Thank you

    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.

  4. #4
    JE McGimpsey
    Guest

    Re: calculating commission on sliding scale

    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

    In article <[email protected]>,
    corrado444 <[email protected]>
    wrote:

    > 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.


  5. #5
    JR
    Guest

    RE: calculating commission on sliding scale

    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,B3:D6,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

    "corrado444" wrote:

    >
    > 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
    >
    >
    > --
    > corrado444
    > ------------------------------------------------------------------------
    > corrado444's Profile: http://www.excelforum.com/member.php...fo&userid=7800
    > View this thread: http://www.excelforum.com/showthread...hreadid=491713
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1