# Commision Calculator by jacques_appelcryn

VIEWS: 99 PAGES: 9

• pg 1
```									                Colour Scheme
Blue              Formulaes
Green             Information
Orange            Important
Yellow            Only edit with typing
White             User can edit

Introduction
1. This spreadsheet will show you the results of commision structures when a target has been reached. There are default
structures that will give you an indication of what types of commision structures you can use. I have set it up so that you can
test different types of commision structures and also determine which one suits you the best.

2. I have made two different aproaches at calculating commision. You ca select the option next to the commision selection.
The "Max" option lets you set a maximum commision at target 10 and then it will take the basic into account to make sure that
the maximum commision isn't exceeded. The normal type starts with all the figures at target one and it can go up or down
from there.

you have a competitors commision structure you can also add it to the list for comparison.

Working with the "Build" tab
1. The "build" tab works out the commision by using the target value, calculating a commision percentage and a basic where
neccesary. It can also adjust the commision percentages and the basic earnings as higher targets are reached. There is also a
maximum and minimum earnings setting.
2. Select the target values. There are 10 different target levels to choose from. If you don't want to use all ten, just make the
last ones you don't need zero, the graphs will unfortunately end a bit short.
3. "Commision Structures" In the yellow section, you can now give the commision structures new names. As long as you type
in the amount the number at the start will fill in automatically.
4. "Basic" You can now enter the basic that they will earn, unlike minimum payment, they need to reach the target amount
and the basic stays in effect when higher amounts are reached.
5. "The basic will go up with this amount everytime a higher target is reached, this value can be negative, but the total basic
may go below zero and effect the earnings negatively. If you want the basic to decrease to zero, you might be better of using
minimum payment.
6. "Commision Start" is what the earnings will be at target 1. If you select the max option, it will become "Commision Max" and
will be the total earnings at target 10.
7. "Commision Change" The commision will go up with this amount everytime a new target is reached. If you selected the max
option, this will be the amount the commision decreases with from target 10.
8. "Minimum Earnings" This is the minimum payout the person will receive, any amount lower will be increased automatically
to this amount.
9. "Maximum Earnings" The payout amount will not go above this amount, any amount higher than this will be decreased to
this amount.
10. "Notes" Here you can add notes for yourself.
11. "Select Commision Structure" Here the data for the specific commision structure will be displayed, just type in the number
of the commision structure.
12. "Comparison" Here you can enter another commision structure that you can compare with the main one.
13. "Calculate" This will calulate the earnings on a certain amount exactly based on the selected commision structure, just
enter the amount into the block to have it calculated.

Overwriting formulas
1. You can edit the basic values and commision percentages manually but this cause the formulaes that were there before to
stop working. I have a different spreadsheet that you can easily mix formulas with manual adjustments.
Commision as per Straight Commision With Basic                                                           Rising Commision
Commision                                          Total                                     Total
Target          Basic            Rate       Commision             Total       Commision                     Total      Commision     Difference
Target 1           10,000         1,000          20.00%       2,000.00           3,000.00       30.00%                     1,500.00        15.00%      -1,500.00
Target 2           20,000         1,000          20.00%       4,000.00           5,000.00       25.00%                     3,200.00        16.00%      -1,800.00
Target 3           30,000         1,000          20.00%       6,000.00           7,000.00       23.33%                     5,100.00        17.00%      -1,900.00
Target 4           40,000         1,000          20.00%       8,000.00           9,000.00       22.50%                     7,200.00        18.00%      -1,800.00
Target 5           50,000         1,000          20.00%      10,000.00          11,000.00       22.00%                     9,500.00        19.00%      -1,500.00
Target 6           60,000         1,000          20.00%      12,000.00          13,000.00       21.67%                    12,000.00        20.00%      -1,000.00
Target 7           70,000         1,000          20.00%      14,000.00          15,000.00       21.43%                    14,700.00        21.00%        -300.00
Target 8           80,000         1,000          20.00%      16,000.00          17,000.00       21.25%                    17,600.00        22.00%         600.00
Target 9           90,000         1,000          20.00%      18,000.00          19,000.00       21.11%                    20,700.00        23.00%       1,700.00
Target 10          100,000         1,000          20.00%      20,000.00          21,000.00       21.00%                    24,000.00        24.00%       3,000.00

Select Commision Structure            3          Normal                            Calculate          5,000      2,000.00                 Comparison        2

Commision        Commision     Minimum       Maximum
Commision Structures                   Basic        Basic Change      Start            Change      Earnings      Earnings                      Notes
(1) Straight Commision                                        0               0        20.00%            0.00%            0       100,000
(2) Rising Commision                                          0               0        15.00%            1.00%            0       100,000
(3) Straight Commision With Basic                         1,000               0        20.00%            0.00%            0       100,000
(4) Rising Commision With Basic                           1,000               0        15.00%            1.00%            0       100,000
(5) Basic and Commision                                   1,000           1,000        10.00%            0.00%            0       100,000
(6) Item 1 With Minimum Payment                               0               0        20.00%            0.00%        3,000       100,000
(7) Item 2 With Minimum Payment                               0               0        15.00%            1.00%        3,000       100,000
(8) Item 3 With Minimum Payment                           1,000               0        20.00%            0.00%        3,000       100,000
(9) Item 4 With Minimum Payment                           1,000               0        15.00%            1.00%        3,000       100,000
(10) Item 5 With Minimum Payment                          1,000           1,000        10.00%            0.00%        3,000       100,000

Graphs are available for each of these
10,000   20,000   30,000     40,000    50,000   60,000   70,000   80,000   90,000   100,000
Straight Commision               2,000    4,000    6,000      8,000    10,000   12,000   14,000   16,000   18,000    20,000
Rising Commision                 1,500    3,200    5,100      7,200     9,500   12,000   14,700   17,600   20,700    24,000
Straight Commision With Basic    3,000    5,000    7,000      9,000    11,000   13,000   15,000   17,000   19,000    21,000
Rising Commision With Basic      2,500    4,200    6,100      8,200    10,500   13,000   15,700   18,600   21,700    25,000
Basic and Commision              2,000    4,000    6,000      8,000    10,000   12,000   14,000   16,000   18,000    20,000
Item 1 With Minimum Payment      3,000    4,000    6,000      8,000    10,000   12,000   14,000   16,000   18,000    20,000
Item 2 With Minimum Payment      3,000    3,200    5,100      7,200     9,500   12,000   14,700   17,600   20,700    24,000
Item 3 With Minimum Payment      3,000    5,000    7,000      9,000    11,000   13,000   15,000   17,000   19,000    21,000
Item 4 With Minimum Payment      3,000    4,200    6,100      8,200    10,500   13,000   15,700   18,600   21,700    25,000
Item 5 With Minimum Payment      3,000    4,000    6,000      8,000    10,000   12,000   14,000   16,000   18,000    20,000

Calculations
10,000   20,000   30,000     40,000    50,000   60,000   70,000   80,000   90,000   100,000
Item 1 - Basic                       0        0         0          0        0        0        0        0        0         0
Item 2 - Basic                       0        0         0          0        0        0        0        0        0         0
Item 3 - Basic                   1,000    1,000    1,000       1,000    1,000    1,000    1,000    1,000    1,000     1,000
Item 4 - Basic                   1,000    1,000    1,000       1,000    1,000    1,000    1,000    1,000    1,000     1,000
Item 5 - Basic                   1,000    2,000    3,000       4,000    5,000    6,000    7,000    8,000    9,000    10,000
Item 6 - Basic                       0        0         0          0        0        0        0        0        0         0
Item 7 - Basic                       0        0         0          0        0        0        0        0        0         0
Item 8 - Basic                   1,000    1,000    1,000       1,000    1,000    1,000    1,000    1,000    1,000     1,000
Item 9 - Basic                   1,000    1,000    1,000       1,000    1,000    1,000    1,000    1,000    1,000     1,000
Item 10 - Basic                  1,000    2,000    3,000       4,000    5,000    6,000    7,000    8,000    9,000    10,000
Item 1 - Commision               2,000    4,000    6,000       8,000   10,000   12,000   14,000   16,000   18,000    20,000
Item 2 - Commision               1,500    3,200    5,100       7,200    9,500   12,000   14,700   17,600   20,700    24,000
Item 3 - Commision               2,000    4,000    6,000       8,000   10,000   12,000   14,000   16,000   18,000    20,000
Item 4 - Commision               1,500    3,200    5,100       7,200    9,500   12,000   14,700   17,600   20,700    24,000
Item 5 - Commision               1,000    2,000    3,000       4,000    5,000    6,000    7,000    8,000    9,000    10,000
Item 6 - Commision               2,000    4,000    6,000       8,000   10,000   12,000   14,000   16,000   18,000    20,000
Item 7 - Commision               1,500    3,200    5,100       7,200    9,500   12,000   14,700   17,600   20,700    24,000
Item 8 - Commision               2,000    4,000    6,000       8,000   10,000   12,000   14,000   16,000   18,000    20,000
Item 9 - Commision               1,500    3,200    5,100       7,200    9,500   12,000   14,700   17,600   20,700    24,000
Item 10 - Commision              1,000    2,000    3,000       4,000    5,000    6,000    7,000    8,000    9,000    10,000
Item 1 - Total                   2,000    4,000    6,000       8,000   10,000   12,000   14,000   16,000   18,000    20,000
Item 2 - Total                   1,500    3,200    5,100       7,200    9,500   12,000   14,700   17,600   20,700    24,000
Item 3 - Total                   3,000    5,000    7,000       9,000   11,000   13,000   15,000   17,000   19,000    21,000
Item 4 - Total                   2,500    4,200    6,100       8,200   10,500   13,000   15,700   18,600   21,700    25,000
Item 5 - Total                   2,000    4,000    6,000       8,000   10,000   12,000   14,000   16,000   18,000    20,000
Item 6 - Total                   3,000    4,000    6,000       8,000   10,000   12,000   14,000   16,000   18,000    20,000
Item 7 - Total                   3,000    3,200    5,100       7,200    9,500   12,000   14,700   17,600   20,700    24,000
Item 8 - Total                   3,000    5,000    7,000       9,000   11,000   13,000   15,000   17,000   19,000    21,000
Item 9 - Total                   3,000    4,200    6,100       8,200   10,500   13,000   15,700   18,600   21,700    25,000
Item 10 - Total                  3,000    4,000    6,000       8,000   10,000   12,000   14,000   16,000   18,000    20,000
9
0
10,000   20,000   30,000     40,000    50,000   60,000   70,000   80,000   90,000   100,000
Straight Commision               2,000    4,000    6,000      8,000    10,000   12,000   14,000   16,000   18,000    20,000
Rising Commision                   600    1,400    2,400      3,600     5,000    6,600    8,400   10,400   12,600    15,000
Straight Commision With Basic    2,900    4,800    6,700      8,600    10,500   12,400   14,300   16,200   18,100    20,000
Rising Commision With Basic      1,500    2,200    3,100      4,200     5,500    7,000    8,700   10,600   12,700    15,000
Basic and Commision              1,000    2,000    3,000      4,000     5,000    6,000    7,000    8,000    9,000    10,000
Item 1 With Minimum Payment      3,000    4,000    6,000      8,000    10,000   12,000   14,000   16,000   18,000    20,000
Item 2 With Minimum Payment      3,000    3,000    3,000      3,600     5,000    6,600    8,400   10,400   12,600    15,000
Item 3 With Minimum Payment      3,000    4,800    6,700      8,600    10,500   12,400   14,300   16,200   18,100    20,000
Item 4 With Minimum Payment      3,000    3,000    3,100      4,200     5,500    7,000    8,700   10,600   12,700    15,000
Item 5 With Minimum Payment      3,000    3,000    3,000      4,000     5,000    6,000    7,000    8,000    9,000    10,000

Calculations
10,000   20,000   30,000     40,000    50,000   60,000   70,000   80,000   90,000   100,000
Item 1 - Basic                       0        0         0          0        0        0        0        0        0         0
Item 2 - Basic                       0        0         0          0        0        0        0        0        0         0
Item 3 - Basic                   1,000    1,000    1,000       1,000    1,000    1,000    1,000    1,000    1,000     1,000
Item 4 - Basic                   1,000    1,000    1,000       1,000    1,000    1,000    1,000    1,000    1,000     1,000
Item 5 - Basic                   1,000    2,000    3,000       4,000    5,000    6,000    7,000    8,000    9,000    10,000
Item 6 - Basic                       0        0         0          0        0        0        0        0        0         0
Item 7 - Basic                       0        0         0          0        0        0        0        0        0         0
Item 8 - Basic                   1,000    1,000    1,000       1,000    1,000    1,000    1,000    1,000    1,000     1,000
Item 9 - Basic                   1,000    1,000    1,000       1,000    1,000    1,000    1,000    1,000    1,000     1,000
Item 10 - Basic                  1,000    2,000    3,000       4,000    5,000    6,000    7,000    8,000    9,000    10,000
Item 1 - Commision               2,000    4,000    6,000       8,000   10,000   12,000   14,000   16,000   18,000    20,000
Item 2 - Commision                 600    1,400    2,400       3,600    5,000    6,600    8,400   10,400   12,600    15,000
Item 3 - Commision               1,900    3,800    5,700       7,600    9,500   11,400   13,300   15,200   17,100    19,000
Item 4 - Commision                 500    1,200    2,100       3,200    4,500    6,000    7,700    9,600   11,700    14,000
Item 5 - Commision                   0        0         0          0        0        0        0        0        0         0
Item 6 - Commision               2,000    4,000    6,000       8,000   10,000   12,000   14,000   16,000   18,000    20,000
Item 7 - Commision                 600    1,400    2,400       3,600    5,000    6,600    8,400   10,400   12,600    15,000
Item 8 - Commision               1,900    3,800    5,700       7,600    9,500   11,400   13,300   15,200   17,100    19,000
Item 9 - Commision                 500    1,200    2,100       3,200    4,500    6,000    7,700    9,600   11,700    14,000
Item 10 - Commision                  0        0         0          0        0        0        0        0        0         0
Item 1 - Total                   2,000    4,000    6,000       8,000   10,000   12,000   14,000   16,000   18,000    20,000
Item 2 - Total                     600    1,400    2,400       3,600    5,000    6,600    8,400   10,400   12,600    15,000
Item 3 - Total                   2,900    4,800    6,700       8,600   10,500   12,400   14,300   16,200   18,100    20,000
Item 4 - Total                   1,500    2,200    3,100       4,200    5,500    7,000    8,700   10,600   12,700    15,000
Item 5 - Total                   1,000    2,000    3,000       4,000    5,000    6,000    7,000    8,000    9,000    10,000
Item 6 - Total                   3,000    4,000    6,000       8,000   10,000   12,000   14,000   16,000   18,000    20,000
Item 7 - Total                   3,000    3,000    3,000       3,600    5,000    6,600    8,400   10,400   12,600    15,000
Item 8 - Total                   3,000    4,800    6,700       8,600   10,500   12,400   14,300   16,200   18,100    20,000
Item 9 - Total                   3,000    3,000    3,100       4,200    5,500    7,000    8,700   10,600   12,700    15,000
Item 10 - Total                  3,000    3,000    3,000       4,000    5,000    6,000    7,000    8,000    9,000    10,000
30,000

25,000

Straight Commision

20,000                                                                                              Rising Commision
Straight Commision With Basic
Rising Commision With Basic
15,000
Basic and Commision
Item 1 With Minimum Payment
10,000                                                                                              Item 2 With Minimum Payment
Item 3 With Minimum Payment

5,000                                                                                              Item 4 With Minimum Payment
Item 5 With Minimum Payment

0
10,000

20,000

30,000

40,000

50,000

60,000

70,000

80,000

90,000

100,000
10,000   20,000   30,000   40,000   50,000   60,000
Straight Commision               2,000    4,000    6,000    8,000   10,000   12,000
Rising Commision                 1,500    3,200    5,100    7,200    9,500   12,000
Straight Commision With Basic    3,000    5,000    7,000    9,000   11,000   13,000
Rising Commision With Basic      2,500    4,200    6,100    8,200   10,500   13,000
Basic and Commision              2,000    4,000    6,000    8,000   10,000   12,000
Item 1 With Minimum Payment      3,000    4,000    6,000    8,000   10,000   12,000
Item 2 With Minimum Payment      3,000    3,200    5,100    7,200    9,500   12,000
Item 3 With Minimum Payment      3,000    5,000    7,000    9,000   11,000   13,000
Item 4 With Minimum Payment      3,000    4,200    6,100    8,200   10,500   13,000
Item 5 With Minimum Payment      3,000    4,000    6,000    8,000   10,000   12,000
70,000   80,000   90,000   100,000
14,000   16,000   18,000    20,000
14,700   17,600   20,700    24,000
15,000   17,000   19,000    21,000
15,700   18,600   21,700    25,000
14,000   16,000   18,000    20,000
14,000   16,000   18,000    20,000
14,700   17,600   20,700    24,000
15,000   17,000   19,000    21,000
15,700   18,600   21,700    25,000
14,000   16,000   18,000    20,000

```
To top