This template enables users to calculate sales commission for up to 5 levels of sales agents. Simply create a unique agent code for each sales agent, specify the appropriate commission percentages, copy & paste the appropriate sales data into the template, link each sales transaction to an agent code and all the commission amounts are automatically calculated. The template also includes a unique commission report which can be compiled for any user defined date range.
The following sheets are included in the template:
Setup - enter a business name and specify the appropriate sales tax percentages on this sheet.
Agents - create a unique agent code for each sales agent, specify the appropriate commission percentage for each of the 5 commission levels and link the agent code to the code of the agent which is directly above the agent in the commission structure. The agent commission codes from level 3 to 5 are automatically determined based on the level 2 agent commission code.
Sales - copy & paste all the relevant sales data onto this sheet. Only the columns with yellow column headings require user input and you can add additional columns if required.
Commission - this sheet includes a commission report which can be compiled for any user defined date range. All the calculations on this sheet are automated and the only user input which is required is to specify the appropriate date range for which the commissions need to be calculated.
Setup
The business name and sales tax percentages can be entered on the Setup sheet. The business name is used as a heading on all the other sheets and the sales tax percentages are used to calculate the inclusive, exclusive and sales tax amounts.
The sales tax codes in the sales tax section on the Setup sheet are automatically included in the tax code list boxes on the Sales sheet. There are two sales tax code types - one for national or federal sales tax and one for state sales tax. The codes created on the Setup sheet are included in both sales tax code list boxes and the sales tax percentages are automatically applied based on the sales tax codes selected for each sales transaction.
Note: You can add an unlimited number of additional sales tax codes by inserting a new row above the end of list entry, entering the new sales tax code and specifying the appropriate sales tax percentage. If sales tax percentages change, you can therefore simply create a new sales tax code with the new sales tax percentage and use the new code for all transactions after the effective date of the rate change.
Agents
A unique agent code needs to be created for each sales agent and the appropriate commission percentages need to be entered on the Agents sheet. A sales tax code also needs to be selected for each agent and the agent needs to be linked to the agent code of the sales agent who is directly above the agent in the commission structure.
Note: We recommend that you use an abbreviated version of the name of the agent as the agent code because it will make it easier to identify the correct agent when selecting the appropriate agent code from the list box in column H on the Sales sheet. For example, an agent code of ABC01 can be created for the agent "ABC Limited".
The following user input fields are included on the Agents sheet:
Code - enter a unique agent code in this column.
Agent Name - enter the name of the agent in this column.
Tax 1 Code - select the appropriate national or federal sales tax code for the agent from the list box in this column. All the sales tax codes which have been created on the Setup sheet will be available for selection and the sales tax amounts are calculated based on the appropriate sales tax percentage of the selected tax code.
Tax 2 Code - select the appropriate state sales tax code for the agent from the list box in this column. All the sales tax codes which have been created on the Setup sheet will be available for selection and the sales tax amounts are calculated based on the appropriate sales tax percentage of the selected tax code. If you do not require two sales tax types, this column can be deleted.
Com2Code - select the agent code of the agent that is directly above the agent in the commission structure. If there is no agent above the agent in the commission structure, leave this column blank.
Com1% - enter the sales commission percentage that is applicable to all the products sold by the agent.
Com2% - enter the sales commission percentage which needs to be used in order to calculate the commission earned by the agent on sales of agents one level below the agent in the sales commission structure.
Com3% - enter the sales commission percentage which needs to be used in order to calculate the commission earned by the agent on sales of agents two levels below the agent in the sales commission structure.
Com4% - enter the sales commission percentage which needs to be used in order to calculate the commission earned by the agent on sales of agents three levels below the agent in the sales commission structure.
Com5% - enter the sales commission percentage which needs to be used in order to calculate the commission earned by the agent on sales of agents four levels below the agent in the sales commission structure.
Note: Only the columns with a yellow column heading require user input - the columns with a light blue column heading contain formulas which are automatically copied for all the new agent codes added to the sheet. The calculated columns contain the following calculations:
Com3Code - the formula in this column determines the agent code of the agent who is two levels above the agent in the commission structure. The Com2Code that is selected in column D is used to determine this agent code and the commission percentage specified in the Com3% column will be used to calculate the sales commission paid to this agent.
Com4Code - the formula in this column determines the agent code of the agent who is three levels above the agent in the commission structure. The Com3Code that is determined in column J is used to determine this agent code and the commission percentage specified in the Com4% column will be used to calculate the sales commission paid to this agent.
Com5Code - the formula in this column determines the agent code of the agent who is four levels above the agent in the commission structure. The Com4Code that is determined in column K is used to determine this agent code and the commission percentage specified in the Com5% column will be used to calculate the sales commission paid to this agent.
EC - an error code of E1 will be displayed in this column if any of the agent codes created in column A are duplicated. The error code can be removed by simply deleting one of the rows which contain the duplicated sales agent code.
Note: All the columns on the Agents sheet have been included in an Excel table. This feature is extremely useful when entering data in a table format because the formulas which are included in calculated columns (the columns with a light blue column heading) are automatically copied when new rows are inserted into the table or when data is entered into the first blank row below the table. You can therefore add an agent to the sheet by simply entering an agent code in column A - the table will then automatically extend to include the new agent.
It is important to note that only the commission percentages specified for an agent are used to determine the commission earned by the particular agent. If an agent therefore earns commission on the sales of an agent who is below the agent in the commission structure, it is the commission percentage specified next to the higher level agent which is used for calculation purposes.
Example: Agent XXX01 is one level above agent YYY01 in the commission structure, the Com1% for both agents is 5%, the Com2% for agent XXX01 is 1% and the Com2% for agent YYY01 is 3%. Agent YYY01 will earn 5% commission on all his sales and agent XXX01 will earn 1% on the sales of agent YYY01. As you can see from this example, the commission percentage that agent XXX01 will earn is determined by the Com2% this is specified for agent XXX01 and not the Com2% specified for the agent who sells the products (agent YYY01). Also, if an agent that is directly below agent YYY01 in the commission structure sells products, agent YYY01 will earn 3% commission on all of that agent's sales.
Note: We recommend that you also create a generic agent commission code which can be used for sales transactions where no commission is payable to an agent. This agent code can then be selected from the list box in column H on the Sales sheet when no commission should be calculated. Column H on the Sales sheet will then always contain an agent code even though some transactions would not attract commission which makes it easier to determine whether an agent code has been assigned to all sales transactions.
Note: All sales commission percentages should be entered exclusive of sales tax. The inclusive sales amounts included on the Sales sheet are amended based on the tax codes specified on the Sales sheet in order to calculate the exclusive sales amounts. The commission percentages (which exclude sales tax) are then multiplied by the exclusive sales amounts in order to calculate the appropriate commission totals for each agent. Sales tax is then added to the commission totals on the Commission sheet based on the sales tax codes specified for the appropriate agent on the Agents sheet.
Sales Transactions
All sales transactions should be entered or copied onto the Sales sheet. The appropriate sales agent code then needs to be selected from the list boxes in column H in order to allocate each sales transaction to a sales agent. All the sales commission calculations on this sheet and the Commission sheet are automated.
Note: Only the columns with a yellow column heading require user input - the columns with light blue column headings contain formulas which are automatically copied for all new sales transactions added to the Sales sheet (as a result of using the Excel table feature).
Note: All the columns on the Sales sheet have been included in an Excel table. This feature is extremely useful when entering data in a table format because the formulas which are included in calculated columns (the columns with light blue column headings) are automatically copied when new rows are inserted into the table or when data is entered into the first blank row below the table. You can therefore enter a new transaction by simply entering an invoice number in column A - the table will then automatically extend to include the new transaction.
All the column headings contain a filter selection arrow - this feature indicates that a Filter has been applied to the table data. This Excel feature is very useful when you need to filter the data which forms part of a table based on one of the filter criteria available after clicking the selection arrow. The Filter feature can also be used to sort data based on a single column.
Example: If you only want to display the sales transactions for a particular month, you can filter the data based on the Invoice Date column by selecting the appropriate date filter from the filter menu that is displayed after clicking the selection arrow. After reviewing the filtered data, simply select the "Clear filter" option in the filtered column in order to remove the filter and to display all the sales transactions on the sheet.
You'll also notice that there are totals included in the cells above all the amount columns (formatted in italic). These cells contain a Subtotal function which results in all filtered records being included in the calculated total. This means that after filtering data, only the transactions which are displayed on the sheet are included in the calculation (column total). If you therefore print the sheet while a filter is in effect, the totals above the column headings will be calculated accurately and will only include the filtered transactions which have been printed.
The following user input fields are included on the Sales sheet:
Invoice Number - invoice numbers can be in any format and have no effect on commission calculations.
Invoice Date - the tax invoice dates should be included in column B - all commission calculations are based on the dates which are included in this column.
Customer - the customer name or customer code can be included in this column but has no effect on commission calculations.
Description - a description of the products or services to which the tax invoice relates can be included in this column but has no effect on commission calculations.
Tax Inclusive Amount - the total invoice value inclusive of sales tax should be included in this column.
Tax 1 Code - select the appropriate national or federal sales tax code for the agent from the list box in this column. All the sales tax codes which have been created on the Setup sheet will be available for selection and the sales tax amounts are calculated based on the appropriate sales tax percentage of the selected tax code.
Tax 2 Code - select the appropriate state sales tax code for the agent from the list box in this column. All the sales tax codes which have been created on the Setup sheet will be available for selection and the sales tax amounts are calculated based on the appropriate sales tax percentage of the selected tax code. If you do not require two sales tax types, this column can be deleted.
Agent Code - select the appropriate agent code for each sales transaction in this column. A generic "no commission" agent code should be selected for all transactions where commission is not applicable. Note that only agent codes which have been created on the Agents sheet will be included in the list boxes in this column.
Note: Only the columns with a yellow column heading require user input - the columns with a light blue column heading contain formulas which are automatically copied for all the new sales transactions added to the sheet. The calculated columns contain the following calculations:
Sales Tax 1 Amount - the sales tax amounts in this column are calculated based on the tax 1 code selected in column F and the sales tax percentages specified on the Setup sheet. The tax 1 calculations are applicable for national or federal sales tax and should therefore apply to most countries. If no sales tax should be calculated, the E tax code can be selected in column F.
Sales Tax 2 Amount - the sales tax amounts in this column are calculated based on the tax 2 code selected in column G and the sales tax percentages specified on the Setup sheet. The tax 2 calculations are applicable for state sales tax and may therefore not apply in all countries. If you do not need state sales tax calculations, you can delete this column.
Exclusive Sales Amount - the amounts in this column are calculated based on the tax codes selected in columns F and G and the appropriate sales tax percentages which have been specified for the selected tax codes on the Setup sheet.
Com1% - this commission percentage is looked up on the Agents sheet based on the agent code selected in column H.
Com1 Amount - this amount is calculated by multiplying the exclusive sales amount in column K by the Com1%.
Com2 Code - this agent code is lookup up on the Agents sheet based on the agent code in column H. This is the code of the agent who is one level above the agent whose code is reflected in column H.
Com2% - this commission percentage is looked up on the Agents sheet based on the agent code in column N.
Com2 Amount - this amount is calculated by multiplying the exclusive sales amount in column K by the Com2%.
Com3 Code - this agent code is lookup up on the Agents sheet based on the agent code in column H. This is the code of the agent who is two levels above the agent whose code is reflected in column H.
Com3% - this commission percentage is looked up on the Agents sheet based on the agent code in column Q.
Com3 Amount - this amount is calculated by multiplying the exclusive sales amount in column K by the Com3%.
Com4 Code - this agent code is lookup up on the Agents sheet based on the agent code in column H. This is the code of the agent who is three levels above the agent whose code is reflected in column H.
Com4% - this commission percentage is looked up on the Agents sheet based on the agent code in column T.
Com4 Amount - this amount is calculated by multiplying the exclusive sales amount in column K by the Com4%.
Com5 Code - this agent code is lookup up on the Agents sheet based on the agent code in column H. This is the code of the agent who is four levels above the agent whose code is reflected in column H.
Com5% - this commission percentage is looked up on the Agents sheet based on the agent code in column W.
Com5 Amount - this amount is calculated by multiplying the exclusive sales amount in column K by the Com5%.
Total Commission - this column displays the sum of the all the commission amount columns.
Total Com % - the total commission percentage is calculated by dividing the total commission amounts in the previous column by the exclusive sales amounts in column K.
Sales Commission Report
The sales commission report on the Commission sheet is automatically calculated based on the sales transactions included on the Sales sheet and the commission percentages included on the Agents sheet. The sales commission report can be compiled for any date range by simply entering the appropriate "From" and "To" dates in cell E2 and G2 respectively.
The Com1 to Com5 columns include the totals per agent calculated on the Sales sheet (Com1Amount to Com5Amount columns). The sales tax codes in column C and D are looked up on the Agents sheet and determine whether sales tax is added to the total commission amounts in order to arrive at an inclusive total. The sales tax amounts are based on the sales tax percentages specified on the Setup sheet.
Note: If you do not need both sales tax code and amount columns, you can delete the second sales tax code and amount columns without affecting the sales tax calculations in the first code and amount columns. You should not however delete both sales tax code and amount columns as the template requires the inclusion of the first columns.
Note: All the columns on the Commission sheet have been included in an Excel table. This feature is extremely useful when entering data in a table format because the formulas which are included in calculated columns are automatically copied when new rows are added into the table.
Note: By default, only the first 30 agent codes are included on the Commission sheet. If you therefore create more than 30 agent codes on the Agents sheet, some of the codes may not be included on the Commission sheet. This can be rectified by simply dragging the selection handle in the bottom right corner of the table downwards in order to copy all the formulas into the required number of additional rows. All sales agent codes will then be included in the report.
Note: We have added conditional formatting to the Code heading cell which will result in the heading being highlighted in red if the number of agent codes on the Agents sheet is greater than the number of agent codes on the Commission sheet. If this cell is therefore highlighted in red, it means that you need to add additional rows to the Commission sheet by copying the formulas in the last row that is included on the report.
