Search Search for: Business ApplicationsAll Forums

# Help with variable sales commission spreadsheet

 OuttaHereBlur
Junior Member with 11 posts.

Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 02:12 AM #1
Help with variable sales commission spreadsheet
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
 Test.Commissions.zip (4.0 KB, 88 views)
Member with 8,268 posts.

Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
31-Dec-2010, 09:26 AM #2
Quote:
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.

Enough for lesson 1? (welcome to the board )
 OuttaHereBlur
Junior Member with 11 posts.

Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 10:50 AM #3
I think we've launched!
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
 Test.Commissions1.zip (4.0 KB, 35 views)
Member with 8,268 posts.

Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
31-Dec-2010, 01:37 PM #4
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.
Member with 8,268 posts.

Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
31-Dec-2010, 01:46 PM #5
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?
 OuttaHereBlur
Junior Member with 11 posts.

Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 02:47 PM #6
Quote:
 Originally Posted by bomb #21 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?
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
 Test.Commissions3.zip (5.9 KB, 34 views)
Member with 8,268 posts.

Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
31-Dec-2010, 03:04 PM #7
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? First things first though. )

Last edited by bomb #21; 31-Dec-2010 at 03:12 PM..
 OuttaHereBlur
Junior Member with 11 posts.

Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 03:10 PM #8
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.
 OuttaHereBlur
Junior Member with 11 posts.

Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 03:18 PM #9
Quote:
 Originally Posted by bomb #21 IF C7 is 450000 or more but less than 875000, some %age between 1 and 17.5
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?
 OuttaHereBlur
Junior Member with 11 posts.

Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 03:31 PM #10
Quote:
 Originally Posted by bomb #21 You have =IF(C7<\$E\$22,0.01*ROUNDDOWN(C7/\$E\$21,0),\$E\$23) in G7.
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.
Member with 8,268 posts.

Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
31-Dec-2010, 03:32 PM #11
Look, I'm just trying one step at a time, right?

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?
Member with 8,268 posts.

Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
31-Dec-2010, 03:35 PM #12
Quote:
 Originally Posted by OuttaHereBlur One more thing. The %age is actually between 1 and 17.5 MINUS the 8% Baseline ...
So if it's 1 it's actually -7?
 OuttaHereBlur
Junior Member with 11 posts.

Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 03:41 PM #13
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? ;(
Member with 8,268 posts.

Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
31-Dec-2010, 03:50 PM #14
Making matters worse? How should I know?

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
 Test.Commissions.xls (18.0 KB, 102 views)
 OuttaHereBlur
Junior Member with 11 posts.

Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 04:06 PM #15
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!
 techguy.org/971753
As Seen On

WELCOME TO TECH SUPPORT GUY!

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

 Tags commission, percentage, revenue, sales, variables

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

### Find the solution to your computer problem!

 Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)