Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Help with variable sales commission spreadsheet


(!)

OuttaHereBlur's Avatar
OuttaHereBlur OuttaHereBlur is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 03: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
File Type: zip Test.Commissions.zip (4.0 KB, 76 views)
bomb #21's Avatar
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, 10:26 AM #2
Quote:
Please go easy on me
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's Avatar
OuttaHereBlur OuttaHereBlur is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 11: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
File Type: zip Test.Commissions1.zip (4.0 KB, 23 views)
bomb #21's Avatar
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, 02: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.
bomb #21's Avatar
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, 02: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's Avatar
OuttaHereBlur OuttaHereBlur is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 03:47 PM #6
Quote:
Originally Posted by bomb #21 View Post
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
File Type: zip Test.Commissions3.zip (5.9 KB, 22 views)
bomb #21's Avatar
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, 04: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 04:12 PM..
OuttaHereBlur's Avatar
OuttaHereBlur OuttaHereBlur is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 04: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's Avatar
OuttaHereBlur OuttaHereBlur is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 04:18 PM #9
Quote:
Originally Posted by bomb #21 View Post
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's Avatar
OuttaHereBlur OuttaHereBlur is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 04:31 PM #10
Quote:
Originally Posted by bomb #21 View Post
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.
bomb #21's Avatar
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, 04: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?
bomb #21's Avatar
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, 04: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's Avatar
OuttaHereBlur OuttaHereBlur is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 04: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? ;(
bomb #21's Avatar
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, 04:50 PM #14
Making matters worse? How should I know?

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
File Type: xls Test.Commissions.xls (18.0 KB, 84 views)
OuttaHereBlur's Avatar
OuttaHereBlur OuttaHereBlur is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Dec 2010
Experience: Beginner
31-Dec-2010, 05: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!
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
commission, percentage, revenue, sales, variables

(clock)
THIS THREAD HAS EXPIRED.
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)
 
Thread Tools


Similar Threads
Title Thread Starter Forum Replies Last Post
Solved: Need help with variable Ziggy1 Software Development 9 15-Sep-2010 01:47 PM
Need some help with Excel bfee317 Business Applications 6 29-Oct-2009 10:16 PM
Solved: Help with Wireless modem mycomputre Networking 4 16-Mar-2009 10:22 AM
need help with a graphics card Panzerwolf Games 0 19-Dec-2008 07:17 PM
Help with merging a excell spreadsheet to a word document escapexls2001 Business Applications 7 31-Jul-2007 04:50 PM

WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑