+ Reply to Thread
Results 1 to 6 of 6

Tier vendor bonus

  1. #1
    Registered User
    Join Date
    02-08-2016
    Location
    Holland
    MS-Off Ver
    2010
    Posts
    22

    Tier vendor bonus

    Hello,

    I have a problem which I can't figure out. I tried to sumproduct, but I am new to it and can't get it done. So hopefully one can help.

    Problem:
    I have vendor bonusses based on a tier based on numbers sold, with different percentages. This sold units cumulate over the years so, the tier where they are in doesn't start with a recount each year.
    There are some co-vendors who both get a bonus based on the same item, yet the agreements can differ.

    In made an example for 3 articles in 10 rows. The actual would be around 350 articles with 800/900 rows. In column K I manually calculated the expected result, but for that many rows it would be very timeconsuming and I need to do this every year.
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Tier vendor bonus

    it would be helpful for you to provide the parameters for the tiers, so much of something times this amount then when they go over this amount so much times this amount etc. So we don't have to read your formulas in K to figure out your break points and percents.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    02-08-2016
    Location
    Holland
    MS-Off Ver
    2010
    Posts
    22
    Quote Originally Posted by Sam Capricci View Post
    it would be helpful for you to provide the parameters for the tiers, so much of something times this amount then when they go over this amount so much times this amount etc. So we don't have to read your formulas in K to figure out your break points and percents.
    First step is determine in which tier is the vendor currently based on last year sales. Then percentage based on the tier x sales prices x units until the max amount of the current tier is reached + amount in next tier x sales price x %.

    Is that what yoy mean?

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Tier vendor bonus

    usually people do tiered bonuses something like this...
    if they buy or sell from this amount to this amount they get this bonus, if they go over the first level highest amount they get this much additional on everything OVER that amount along with the first amount, etc.
    Example: from sales of 10,000 to 100,000 they get a 8% bonus, if they sell between 100,001 and 1,000,000 they get 6% along with their original 8%.

    Something like that (the parameters and the bonuses) are what is needed to help you - what those amounts are in order to calculate the bonuses.

  5. #5
    Registered User
    Join Date
    02-08-2016
    Location
    Holland
    MS-Off Ver
    2010
    Posts
    22
    Quote Originally Posted by Sam Capricci View Post
    usually people do tiered bonuses something like this...
    if they buy or sell from this amount to this amount they get this bonus, if they go over the first level highest amount they get this much additional on everything OVER that amount along with the first amount, etc.
    Example: from sales of 10,000 to 100,000 they get a 8% bonus, if they sell between 100,001 and 1,000,000 they get 6% along with their original 8%.

    Something like that (the parameters and the bonuses) are what is needed to help you - what those amounts are in order to calculate the bonuses.
    That info is in my example. The numbers and the tiers are in there.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Tier vendor bonus

    This proposal employs three helper columns which may be moved and/or hidden for aesthetic purposes.
    Note that based on post #1 I assumed that the amounts from column H:I are both used in the calculation.
    The first helper column is populated using: =MIN(SUM(H3:I3),IF(AND(B4=B3,D4=D3),G4-1,10 ^10))
    The second helper column is populated using: =IF(OR(B2<>B3,D2<>D3),N3,N3-N2)
    The third helper column is populated using: =O3*J3*F3%
    The total amounts per product and vendor are displayed using: =IF(OR(B2<>B3,D2<>D3),SUMIFS(P$3:P$12,B$3:B$12,B3,D$3:D$12,D3),"")
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Re: IF Function to calculate bonus based on tiered bonus rates
    By plaza2154 in forum Excel General
    Replies: 9
    Last Post: 08-05-2020, 03:56 PM
  2. Piece work incentive plan w/hourly rate of pay + bonus + bonus 2
    By Treetom in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-06-2018, 02:19 AM
  3. Replies: 4
    Last Post: 04-24-2017, 09:13 AM
  4. PLEASE HELP Calculate condition and tier bonus
    By sclasen0024 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-08-2015, 11:15 PM
  5. [SOLVED] calculate bonus at 30% of a figure, negative figures to be given 0 bonus.
    By jitterbug888 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-01-2015, 10:40 PM
  6. Hierarchy chart - Connect 1st tier to 3rd tier
    By bjcowen9000 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-22-2014, 05:24 AM
  7. Forecast Bonus Calculator - Require a formula to calculate the bonus' due
    By The_Snook in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-05-2013, 07:32 PM

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