I am attempting to use the following "stepped" commission table to compute various income scenarios for my business and having a REALLY tough time!
I have the following...
1) A staff of (30) agents who have earned various commission amounts. Example - (names in A1 to A4, incomes in B1 to B4, need commissions payable for each agent posted to C1 to C4):
A B C
1 Joe $47,050
2 Elle $11,350
3 Jack $78,111
4 Jill $128,000
AND..
2) A commssion structure that could increment in as little as $5k increments but may not (i.e. I don't know yet exactly where the breaks are going to be - only that the will occur at no less than 5K increments)
Example of a possible rate structure (income ranges in D1 to E18 and corresponding commission rates in F1 to F18)
D E F
1 $0 $10,000 55%
2 $10,001 $15,000 60%
3 $15,001 $20,000 60%
4 $20,001 $25,000 60%
5 $25,001 $30,000 65%
6 $30,001 $35,000 65%
7 $35,001 $40,000 65%
8 $40,001 $45,000 70%
9 $45,001 $50,000 70%
10 $50,001 $55,000 70%
11 $55,001 $60,000 75%
12 $60,001 $65,000 75%
13 $65,001 $70,000 75%
14 $70,001 $75,000 80%
15 $75,001 $80,000 80%
16 $80,001 $85,000 80%
17 $85,001 $90,000 80%
18 $90,001 $90,001+ 85%
I need to do What-If scenarios where I plug in various agent earnings into B1 to B4 and varying commission amounts into F1 to F18 and the total commission amount, based on the changing/incrementing chart above, is returned for each agent in C1 to C4.
HELP! I don't know VBA. I will gladly pay for help if there is anyone out there that can provide a solution!!
Bless you.
Lee from Hawaii
I have the following...
1) A staff of (30) agents who have earned various commission amounts. Example - (names in A1 to A4, incomes in B1 to B4, need commissions payable for each agent posted to C1 to C4):
A B C
1 Joe $47,050
2 Elle $11,350
3 Jack $78,111
4 Jill $128,000
AND..
2) A commssion structure that could increment in as little as $5k increments but may not (i.e. I don't know yet exactly where the breaks are going to be - only that the will occur at no less than 5K increments)
Example of a possible rate structure (income ranges in D1 to E18 and corresponding commission rates in F1 to F18)
D E F
1 $0 $10,000 55%
2 $10,001 $15,000 60%
3 $15,001 $20,000 60%
4 $20,001 $25,000 60%
5 $25,001 $30,000 65%
6 $30,001 $35,000 65%
7 $35,001 $40,000 65%
8 $40,001 $45,000 70%
9 $45,001 $50,000 70%
10 $50,001 $55,000 70%
11 $55,001 $60,000 75%
12 $60,001 $65,000 75%
13 $65,001 $70,000 75%
14 $70,001 $75,000 80%
15 $75,001 $80,000 80%
16 $80,001 $85,000 80%
17 $85,001 $90,000 80%
18 $90,001 $90,001+ 85%
I need to do What-If scenarios where I plug in various agent earnings into B1 to B4 and varying commission amounts into F1 to F18 and the total commission amount, based on the changing/incrementing chart above, is returned for each agent in C1 to C4.
HELP! I don't know VBA. I will gladly pay for help if there is anyone out there that can provide a solution!!
Bless you.
Lee from Hawaii