1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Help with variable sales commission spreadsheet

Discussion in 'Business Applications' started by OuttaHereBlur, Dec 31, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. OuttaHereBlur

    OuttaHereBlur Thread Starter

    Joined:
    Dec 31, 2010
    Messages:
    11
    Hello. I'm new to the forum and appreciate any help you can give.

    I'm trying to create a commission sales calculator similar to this thread but with some changes: techguy.org/672521

    I've attached my spreadsheet in its current form and it is messy. Only the first few rows even get close to working. My apologies in advance! I doubt you will deem any of this as correct and I'm totally ok with that. ;)

    Here is what I'm hoping to achieve:

    1. Baseline Commission is always $2,667 per month (8% of 400k revenue generated in 2010).
    2. Performance Commission kicks in once gross revenue reaches $450,000 within the first year.
    3. Performance Commission then increases by 1% per $50,000 increase.
    4. Performance Commission caps once gross revenue reaches $875,000 (17.5%).
    5. Performance Commission caps at 17.5% (see #4 above).
    6. The percentage reached at the end of any given year carries over to the next year even though the revenue generated restarts at zero.

    I'd like each of the items in this list to be reflected somehow in the spreadsheet if possible. Several of the fields aren't calculating because I simply couldn't make them work correctly.

    Please go easy on me as this is my first spreadsheet with legitimate calculations. ;)

    Anyone up for helping me tackle this one?

    Deborah
     

    Attached Files:

  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    OK -- lesson 1. :)

    You have =B7 in C7 ("Test.Commissions.xls"). Copy that down to C18 and you get =B7, =B8, and so on. Thus the row reference increments, yes?

    Change C7 to =$B$7 and, again, copy down to C18. Now you have =$B$7 in C7:C18. Thus the row references (and indeed, the column references) have become "anchored" by the dollar "signage".

    That is "absolute" referencing, as opposed to what you first had ("relative" referencing).

    Now, you can expand on that. Use this in C7:

    =SUM($B$7:B7)

    , and then copy down to C18. Examine the results and you can see that the "sum from" reference is fixed (absolute) whereas the "sum to" reference increments (is relative).

    So: while your original (C) formulas worked, it was a lot of work to construct the sum ranges "manually". Now you have a way to have Excel do much of that work for you. (y)

    Enough for lesson 1? (welcome to the board :))
     
  3. OuttaHereBlur

    OuttaHereBlur Thread Starter

    Joined:
    Dec 31, 2010
    Messages:
    11
    Bomb #21,

    Thanks so much! So, your approach appears to be showing me how to build this layer by layer, true? But in the end, I should end up with =SUM($B$7:B7) in the C column. Correct?

    I believe that worked and I also believe that I understand what we did (more or less). Pretty scary honestly.

    That was about right for lesson #1. ;) Ready for lesson #2 if you are up for it.

    I'm very appreciate for your help.
     

    Attached Files:

  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    The formulas in H & I seem pretty straightforward, we'll leave those aside. Which leaves the G formulas.

    First thing: apply the "absolute" reference principle to those. So, make G7:

    =IF(C7<$E$22,0.01*ROUNDDOWN(C7/$E$21,0),$E$23)

    and copy down to G18.

    Beyond that, I'm off to figure the overall purpose of the G formulas. More later. :)
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    So, you have this note for the G formulas:

    "Percentage increases by 1% for every $50,000 increase in revenue starting at 450k."

    I see that G7 is zero. I also see that G8 is 1, but "Cumulative Gross Revenue" (C8 -- $69,511) is less than 450K.

    Can you explain that?
     
  6. OuttaHereBlur

    OuttaHereBlur Thread Starter

    Joined:
    Dec 31, 2010
    Messages:
    11
    Sure. The "Performance Commission Percentage" (G Column) doesn't actually kick in until the "Cumulative Gross Revenue" (C Column) reaches 450k. Once it kicks in, it increases by 1% every $50,000.

    I've included a mock column to show what the calculation should yield for column G and have tried to explain the same thing in notes in that column.

    I've also added subsequent years to show that the "Performance Commission Percentage" picks up and carries over to the next year based on where it was at years end of the previous year.

    The other thing I will need to figure out is how to decrease the "Monthly Retainer" (D Column) once it hits the increments indicated in J21:24.

    I hope this makes sense. See attached.
     

    Attached Files:

  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    I'm sticking to getting to grips with the basics. Thus I'm still referring to your first upload, K? :)

    Performance Commission Percentage

    You're after an IF formula. You have =IF(C7<$E$22,0.01*ROUNDDOWN(C7/$E$21,0),$E$23) in G7. The way I read, the syntax should be something like:

    IF C7 is less than 450000, 0.

    IF C7 is 450000 or more but less than 875000, some %age between 1 and 17.5

    IF C7 is 875000 or more, 17.5.

    Agreed?

    (another thing is the %age steps. 450000 to 875000 is only 8½ 50000 "steps", so ... 17.5? :confused: First things first though. :))
     
  8. OuttaHereBlur

    OuttaHereBlur Thread Starter

    Joined:
    Dec 31, 2010
    Messages:
    11
    Yes, you are following everything I'm trying to do! If necessary, I can cut off the top end of the commission at 17% to simplify this beast. I'm seriously impressed with your abilities and grateful for your help.
     
  9. OuttaHereBlur

    OuttaHereBlur Thread Starter

    Joined:
    Dec 31, 2010
    Messages:
    11
    One more thing. The %age is actually between 1 and 17.5 MINUS the 8% Baseline. The baseline commission (Column E) always stays the same regardless of revenue generated so I'm actually trying to calculate between 1 and 9.5% in the Performance Commission column (Column G).

    The Baseline Commission PLUS the top end (CAP) of the Performance Commission should total 17%.

    Not sure if this is critical, but does it make sense?
     
  10. OuttaHereBlur

    OuttaHereBlur Thread Starter

    Joined:
    Dec 31, 2010
    Messages:
    11
    BTW, this is what I actually have so far in G7: =IF(C7<$E$23,0.01*ROUNDDOWN(C7/$E$22,0),$E$24)

    I just noticed that the "E" fields are NOT what you reference above. Not sure if that matters, but I did notice the discrepancy. FWIW.
     
  11. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    Look, I'm just trying one step at a time, right? :D

    In G7 I now have:

    =IF(C7<$E$20,0,IF(C7<$E$22,ROUNDUP((C7-$E$20)/50000,0),2))

    If I copy down to G18 then: G17 = 1 (C17 is between 450K and 450K + (1 * 50K)) and G18 = 2 (C18 is between 450K and 450K + (2 * 50K).

    Are we (still) getting anywhere? :)
     
  12. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    So if it's 1 it's actually -7? :confused:
     
  13. OuttaHereBlur

    OuttaHereBlur Thread Starter

    Joined:
    Dec 31, 2010
    Messages:
    11
    No, sorry for the confusion.

    I just mean that we're handling the Baseline commission in another column (Column E) and it is 8%. So, we can subtract that 8% from the Performance Commission (Column G) which means we are actually only calculating %ages between 1 and 9. Because, 17% is supposed to be the total of the two "Commission" columns combined.

    There may be a better way to do this, but I just thought it best to separate the Baseline calculation from the Performance one.

    Am I making matters worse every time I respond? ;(
     
  14. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,270
    Making matters worse? How should I know? :D

    Re-uploading so you can see the G formulas so far.

    Note G18 = 2. If you tweak B18 so that C18 falls just short of the 875K cap (i.e. in B18 -- =874999-C17), then G18 = 9 -- which, with the 8% baseline = 17. That's near enough 17.5, yes?
     

    Attached Files:

  15. OuttaHereBlur

    OuttaHereBlur Thread Starter

    Joined:
    Dec 31, 2010
    Messages:
    11
    This is looking sweet! I tweaked the %age a bit so it reads:

    =IF(C18<$E$20,0,IF(C18<$E$22,0.01*ROUNDUP((C18-$E$20)/50000,0),2))

    Getting excited!
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/971753