Sample Sales Commission Spreadsheet??

Posted by chris on April 05, 2000 7:40 AM

I am looking for a sample sales commission spreadsheet.

The only weird part to it, is I need to be able to track individual sales people and Team Leaders. Team leaders get a percentage of the Teams Overall and they get an individual commission also.

thanks for any advice or pointers to other web pages.

Posted by chuck on April 12, 2000 4:12 PM

#........................ START HERE
here is a simple marco to solve your problem

Sub SALES1()

'R=START ROW OF THE DATA
R = 6
'C= START COLUMN OF THE DATA
C = 3
' D= ROWS OF DATA
D = 6
' E MONTHS OF OF DATA
E = 2
' R1= SRART ROW FOR OUTPUT
R1 = 26
' C1= START COLUMN FOR OUTPUT DATA
'T1= TEAM 1 %
T1 = Cells(14, 3)
'T2 TEAM2 %
T2 = Cells(18, 3)
For I = 1 To E
C1 = 3
R = 6
For H = 1 To D

X = Cells(R, C)

P = Cells(R, (C + 1))

Y = X * (P / 100)
Cells(R1, C1) = Y
R = R + 1
C1 = C1 + 1

Next H
C = C + 2
R1 = R1 + 1

Next I
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^
'TEAM SUMS
R3 = 26
C = 3
For L = 1 To E
SUM1 = 0
SUM2 = 0
R = 6

For J = 1 To D

X = Cells(R, C)
M = Cells(R, 2)

If M = 1 Then GoTo 4
If M = 2 Then GoTo 5
GoTo 6

4 Q1 = X * (T1 / 100)

SUM1 = SUM1 + Q1

GoTo 6

5 Q2 = X * (T2 / 100)
mn = 5
SUM2 = SUM2 + Q2
6 R = R + 1
Next J
Cells(R3, 10) = SUM1
Cells(R3, 11) = SUM2
R3 = R3 + 1
C = C + 2
Next L
End Sub

HERE IS THE TABLE OF SALES

ROW1 COLUMN 2
SALES COMMISSION TABLE

NAME TEAM CODE JAN JAN/% FEB FEB/%

TOM 0 1500 10 2200 10 VARIABLE
SAM 1 2000 10 3300 10
JANE 2 1200 10 1100 10
BETTY 1 2500 10 2300 10
DICK 0 1600 10 1000 10
JERRY 2 2400 10 2400 10

TEAM 1
TOM LEADER 1 %
SAM
BETTY
TEAM2
DICK LEADER 1 %
JANE
JERRY