Commision Calculator by jacques_appelcryn

VIEWS: 99 PAGES: 9

More Info
									                Colour Scheme
Blue              Formulaes
Green             Information
Orange            Important
Yellow            Only edit with typing
Grey              Headings and titles
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.

 3. You can add up to ten different commision structures, this can help you compare or present your commision structures. If
                 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