+ Reply to Thread
Results 1 to 13 of 13

Tiered Bonus Structure

  1. #1
    Registered User
    Join Date
    02-15-2019
    Location
    USA
    MS-Off Ver
    2012
    Posts
    8

    Tiered Bonus Structure

    Good afternoon,

    I am currently trying create an formula to calculate our new tiered bonus structure. This new structure will be based on quarterly production totals, but will bonus monthly.

    How it will work - if someone achieves $0-$50000 in production for the quarter their monthly production will bonus at 30%. If they achieve $50000.01-$75000 of total production for the quarter, their monthly production will bonus at 30% up to $50000 and 40% for any monthly production in excess of $50000. If they achieve $75000.01 or above of total production for the quarter, their monthly production will bonus at 30% up to $50000, 40% between $50000.01 - $75000, and 50% on any monthly production above $75000. This process will start over for the second quarter.

    I think that SUMPRODUCT will work, I just don't know how to manipulate it to consider all the different variables in this new bonus structure.
    Last edited by MFrancis0713; 02-18-2019 at 12:20 PM.

  2. #2
    Registered User
    Join Date
    02-15-2019
    Location
    USA
    MS-Off Ver
    2012
    Posts
    8

    Re: Tiered Bonus Structure

    Also - I have only worked with sumproduct on a few occasions so I am open to ANY other suggestions that could help me.
    Last edited by MFrancis0713; 02-15-2019 at 06:21 PM.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Tiered Bonus Structure

    Pl upload a file clearly showing manually how the calculation is done.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Tiered Bonus Structure

    Yes, a sample would help, as you mention they would get % on their monthly production amounts - none of which exceed 50000, individually.

    If you want to calculate the Qtrly Commission, relative to those tiers, then something like:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where E2 = Q1 amt of 82000 -- this would generate a commission of 28,500 which would be comprised: 50000 @ 30%, 25000 @ 40%, 7000 @ 50%

  5. #5
    Registered User
    Join Date
    02-15-2019
    Location
    USA
    MS-Off Ver
    2012
    Posts
    8

    Re: Tiered Bonus Structure

    I am trying to post an attachment now.

    Please let me know if you can see the attachment and if you have any questions.

    I tried to breakdown what I did for each bonus with a comment in the cell.
    Last edited by MFrancis0713; 02-18-2019 at 01:16 PM.

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Tiered Bonus Structure

    Hi, not sure I agree with the expected results for Ees 2 & 3, most notably upper tier calc :

    Ee 2 figures seem to be transposed in your comments - i.e. s/b 19615 @ 40% + 7385 @ 50%, no?
    Ee 3 figures, seems the 30% amount has been applied at 50% - i.e. s/b 22575.6 @ 30% (to get to 50k), rather than 50% (+75k), no?

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    above basically calculates QTD commission, for given month, and subtracts commission paid out in months prior

    apologies if I've misinterpreted the math / requirement
    Last edited by XLent; 02-18-2019 at 12:12 PM. Reason: added comment re: Ee3

  7. #7
    Registered User
    Join Date
    02-15-2019
    Location
    USA
    MS-Off Ver
    2012
    Posts
    8

    Re: Tiered Bonus Structure

    I'm sorry my calculations might have been off as well. That is why it is so important that I get a formula in place to avoid human error when trying to calculatebonus for each employee(35) each month.

    I have updated my attachment to show a breakdown for employee 2.

    Hopefully this will make it a little easier to understand the bonus structure.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-15-2019
    Location
    USA
    MS-Off Ver
    2012
    Posts
    8

    Re: Tiered Bonus Structure

    I tried the formula above and it works great for month 1 and 2,however, I can't get month 3 to give me the correct bonus amount.

  9. #9
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Tiered Bonus Structure

    Hi, I'm pretty confident the formula provided does what you want - though, I still think some of your calcs are off; re-attached.

    The way the SUMPRODUCT works is slightly different, and may be simpler to "prove" - i.e.

    every $ get 30c
    every $ above 50k get a further 10c
    every $ above 75k get a further 10c

    so, in case of Ee 2, in month 1

    QTD = 17385 * 0.3 --> 5215.5

    in month 2

    QTD = 53585 * 0.3 + 5385 * 0.1 --> 17154 less month commission 1 --> 11938.5

    in month 3

    QTD = 82385 * 0.3 + 32385 * 0.1 + 7385 * 0.1 --> 28692.50 less month1 + month2 commission --> 11538.5


    for employee 3 your monthly results should be 3127.32, 5100.00, 9342.44 -- you are showing 13857 for month 3 because, I believe, you have applied 50% {Tier 3} rather than 30% {Tier 1} to the amount required to hit Tier 1 cap.



    see attached; green section are the calcs -- I have then added a working proof for Ee3.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-15-2019
    Location
    USA
    MS-Off Ver
    2012
    Posts
    8

    Re: Tiered Bonus Structure

    You are 100% correct.

    I was working on the notes for EE2 and forgot to revise the EE3 on the new spreadsheet to reflect the corrected amount.

    Thank you SOOO much for your help and the explanation of the SUMPRODUCT by cents to dollars, that provided me with much clarity.

  11. #11
    Registered User
    Join Date
    02-15-2019
    Location
    USA
    MS-Off Ver
    2012
    Posts
    8

    Re: Tiered Bonus Structure

    I edited the formulas for EE1 and EE2 to test the SUMPRODUCT formula. For both employees Jan and Feb calculations are correct, the issue I am running into is March for both employees isn't calculating the totals that we found above.

    Am I editing the formula incorrectly somehow in cells D11 and D12?
    Attached Files Attached Files

  12. #12
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Tiered Bonus Structure

    Quote Originally Posted by MFrancis0713
    Am I editing the formula incorrectly somehow in cells D11 and D12?
    Yes; if you copy and paste the formula from post#6 into B11, copy B11 across the entirety of range B11:D16 you will get the expected results; the use of relative references in the formula (post #6) ensures that as the formula is copied across the matrix of cells it is always referencing the relevant ranges.

  13. #13
    Registered User
    Join Date
    02-15-2019
    Location
    USA
    MS-Off Ver
    2012
    Posts
    8

    Re: Tiered Bonus Structure

    Ahhh okay, I was manually trying to edit it for each cell.

    Thank you so much!

+ 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. Formula that calculates a tiered bonus structure
    By aksoozm17 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-14-2018, 05:18 PM
  2. Tiered Bonus Structure template
    By dooz83 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-01-2018, 07:08 PM
  3. tiered bonus
    By kimbdalr in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-31-2018, 04:20 PM
  4. Tiered bonus structure
    By csheils79 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-20-2014, 10:29 AM
  5. Help! Tiered sales/bonus structure...
    By cubby777 in forum Excel General
    Replies: 1
    Last Post: 03-29-2014, 06:21 AM
  6. Cumulative Tiered Bonus Structure
    By dpleventhal in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-11-2011, 11:58 AM
  7. Tiered Bonus Structure
    By fwendly in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-18-2009, 04:14 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