Need more info or you couldn't find what you were looking for, let us know by sending an email to: support@dancik.com.
Sales Commission Report (X by Y)
Sales Commission Report by Order Number (X by Y)
Sales Commissions by Price Class
The X by Y Sales Commission Report supports all of the sales commissions strategies available within the system. This report lists, in detail or summary format, the sales, gross profit, and commission calculations, sorted using any X by Y combination.
When running this report, the system prompts you for the basis of the commission: invoices created or invoices paid. The system also prompts you to include commission exceptions for aged invoices, and many other options.
X by Y Commissions Report - By Payment Dates - Header Page
Detailed X by Y Commissions Report - Primary Salespeople
Recap and Summary for Primary Salespeople
X by Y Commissions - Header Page - Secondary Salespeople
Support for Negative Commission Write-offs
Assigning Salespeople _to Orders
Assigning Salespeople to Invoices
1. Decide if you want the commission report to be for invoices or payments.
Note: The decision to run the report for invoices or payments affects the information contained in the X by Y Sales Commission Report Options Screen.
2. Next, select an option for salesperson assignments.
Option 1, the default option, uses the salespersons assigned on each invoice.
Option 2 uses the salespersons currently assigned to each customer account, if any. This option may be effective when you are reassigning salespeople and territories. It provides a way to show what a newly assigned salesperson or territory would have earned in any historical period on the system.
Option 3 is the same as option 1, except that whenever a secondary salesperson is found on an invoice, that invoice will be split between the two salespeople using the percentage entered in the Commission% for Secondary Salesperson setting. For example, a $100 invoice with a 25% split will appear as $75 for the primary salesperson, and $25 for the secondary salesperson.
3. Enter an option and press Next. The next screen lets you pick a date range and other parameters which can limit the amount of data retrieved for the report.
Use the drop down options to enter the report parameters such as Branch, Salesperson, and/or Manufacturer.
The Aggregate Salesperson functionality, introduced in 2014, allows commissions to be shared across salesperson codes assigned to the customer. For more information on this feature, refer to Commission Sharing.
The settings at the bottom of the screen (described in the table below) control how gross profit percentages will be calculated. The default values are the recommended values for each field.
Field |
Description |
Include Trim Class |
Enter N to omit sales of items that have a trim class in the Item File. |
Cost to Use |
GP% is calculated using the cost selected here.
|
Sales Allowances |
Enter Y to include the sales allowance (promo) portion of the price. Enter N to remove the sales allowance from the price. Y is the default. |
Funds |
Enter Y to include the fund/overbill portion of the price. Normally you should not pay commissions on funds. N is the default. |
Cost Allowances |
Enter Y to include the cost allowance (rebate) portion of the cost. Y is the default. |
Include Only Sales Within GP% Span |
You can limit the report to invoice/lines within a specified gross profit percentage range. |
4. The next series of screens let you pick the X and Y parameters. They are common to all X by Y reports. Normally for sales commissions your X parameter is Salesperson# and your Y parameter is Cust Account#.
5. The next screen includes options that are specific to the X by Y commissions. This information varies depending on whether you are running the report for invoices or payments. The figure and text below describe the screen that appears if the invoice option is taken.
Field |
Description |
Include Invoices |
Choose if you want to run the report for paid invoices (P), unpaid invoices (U), or both (B). When running by invoice dates, the default option is B, to include all invoices regardless of payment. |
Unpaid Invoice Days Past Due |
Enter the days past due date you want to consider for the report, if you select U for the previous option. This enables you to run the report as a collection tool, listing only past due invoices for the salesperson, to help with collections. |
Include Commission Exceptions |
Entering a Y directs the system to use the settings made in the Commission Exception File to calculate and report commissions. |
Increase Cost of Sales% |
This field allows you to add an overhead cost which reduces gross profit and commissions if commissions are based on gross profits. For example, if you enter 3.75%, then the costs on all invoices will be increased by 3.75% on the X by Y Commissions Report. |
Access Secondary Salesperson |
Activate this setting if you want to consider a secondary salesperson involved in the sale. Note: Secondary salespeople are added on the second line in the Salesperson field during order entry or through the Maintain Salesperson Assignments program. |
If you are running the report for payments, the following setting display.
Field |
Description |
Include Commission Exceptions |
Entering a Y directs the system to use the settings made in the Commission Exception File to calculate and report commissions. |
Do you want to use the Commission Table based on Aging |
Enter a Y to take advantage of the settings made in the Commission Aging Parameters screen of the Base Commissions File. Note: For more information of the steps required to set up commission rates, refer to Commission Rate Exceptions File - COM 2. |
Do you want to include Commissions on Partial Payments |
If an invoice is partially paid within the specified payment date range, and you enter N, the system does not include the invoice when calculating commission. The program only pays commission on the full invoice amount when it is paid in full, or when a final partial payment is made which closes out the invoice. If you enter Y the system divides the partial payment amount by the invoice amount to get a percentage of invoice dollars paid. This percentage is used to adjust the commission dollars. The salesperson is only paid commission on the percentage of the invoice that was paid. The system handles partial payments as follows. Let’s start with an invoice for $100.00 where the salesperson gets a 5% commission on a gross profit amount of $20.00. Let’s assume that the aging parameters cause the commission to be reduced by 2% if the invoice is paid between 31 and 45 days from the due date, and reduced by 3% if paid between 46 and 60 days from the due date. For this example, let’s assume the payment was made in 35 days, leaving a 3.00% commission. A partial payment of $75.00 is made on this invoice. The X by Y Commission report would see the partial pay invoice and calculate the partial payment factor as 75/100, which equals 0.75. The system, then multiplies the 3.00% by the 0.75 partial payment factor, which equals 2.25%. The 2.25% is used in the commissions report, as the applicable commission rate. The report shows the partial payment factor. This is the equivalent of paying commission only on the portion of the invoice that was paid. If an invoice is partially paid in one month and the remaining balance is paid in full the following month or later, then the X by Y Commission Report only picks up the amounts of each partial payment and calculates the commission in the month when each payment is made. Using the example above, if the remaining balance on the invoice of $25.00 was paid in 45 - 60 days, and the 5.00% commission is reduced by 3.00%, then the commission report uses the following formula: The partial payment is .25 of the original invoice or 25/100. The remaining 2.00% commission is multiplied by the partial payment factor of .25, which equals a 0.50% commission rate. If the invoice is partially paid one month, and the next month it is partially paid again, then the X by Y Commission report uses each partial payment. The calculation is similar to the one in the previous paragraph. It uses the amount paid in the payment date range specified. Regardless of the number of payments made, the sum of the partial payments can never exceed the total invoice amount. The complete formula is as follows: Commission % = (Paid Amount/Invoice Amount) x (Original Base and Exceptions Commission % minus Aging Adjustment %). With either partial payment option, N or Y, the same amount of commission is paid on any invoices that are paid in full. If you enter N, commission is only paid once, when the final payment is received. If you enter Y, commissions are paid on each partial payment. If you enter N, invoices that are never paid in full earn no commissions. Note: The following payment codes, used in accounts receivable to clear an invoice, are NOT included in the X by Y Commission reports on paid invoices: WW, OA, AD, DM, WC, WN, and WP. These codes are used for write-offs and adjustments, which are not considered to be payments. Discounts allowed at the time of payment are considered to be part of the payment. Therefore, salespeople are paid for the full invoice amount even when an early pay discount is allowed. |
Increase Cost of Sales% |
This field allows you to add an overhead cost which reduces gross profit and commissions if commissions are based on gross profits. For example, if you enter 3.75%, then the costs on all invoices will be increased by 3.75% on the X by Y Commissions Report. |
Access Secondary Salesperson |
Activate this setting if you want to consider a secondary salesperson involved in the sale. Note: Secondary salespeople are added on the second line in the Salesperson field during order entry or through the Maintain Salesperson Assignments program. |
6. If you activate the setting Access Secondary Salesperson, you are presented with the options shown below.
Field |
Description |
Run report for |
If you choose to run both reports, one for the primary and one for the secondary, the reports will run one after another. To avoid confusion, the primary and secondary information is not included on the same report. This allows for different formulas to apply to primary versus secondary reports. |
Calculate secondary salesperson commission option |
Select one of the secondary salesperson commission calculation options.
A primary salesperson’s commission is based on the Base Commission and Exceptions tables.
|
If calculate secondary salesperson commission option selected is 1,2 or 3 enter commission% |
Enter a commission percentage to be paid to the secondary salesperson. You can specify whether you want the commission percentage to come from the sale amount or the amount of gross profit generated from the sale, by entering an S or P. |
Reduce Primary Salesperson Commission By Secondary Salesperson Commission |
Causes whatever the commission is for the secondary salesperson to be subtracted from the primary salespersons’ commission dollars. Note: A primary salesperson’s commission is always based on the Base Commission and Exceptions tables. |
Use Commission Override% If Found On Specific Orders/Invoices |
Enter a Y if you want to access the commissions for secondary salespeople that are overridden using the Maintain Salesperson Assignment program. Whenever an override is found it will be used. |
7. The next window gives you some options pertaining to the type of report you want to generate.
This screen confirms your X by Y parameters. You can enter X to start a new page for each salesperson (assuming your X parameter is salesperson).
The Summary Report only prints the X by Y subtotals. Enter N for the regular detailed version, showing each line item sold.
The customer version drops out cost and profit information. The default is M for management version, unless the user is coded to not see costs.
This header page for an X By Y Commissions Report was run by a payment date range, for both Primary and Secondary salespeople. The reports are both requested when the user enters “B” on the “options for secondary salespeople” screen. The reports are then run separately, and this header page goes with the first report that is generated, which is for the primary salespeople.
The header page displays the parameters that were selected. In this case, the user selected to give the Secondary salespeople 25% of the respective Primary salesperson's commissions, and to reduce the Primary salesperson's commissions by that 25%.
Each line item is listed, sorted by salesperson and then by customer account (the X and Y parameters).
The “Excpt” column on the far right of the report has several codes indicating exceptions including the following:
S- means that the commission rate to the left is based on sales dollars.
P - means that the commission rate to the left is based on gross profit dollars.
D - means that an exception was made due to the order being a free delivery.
C - means that an exception was made for the line item being a “cut” (as for a cut of carpet, vinyl, or laminate sheet goods).
75 - is a commission exception number, meaning that the exception shown is Record 75 in the Commission Exceptions File. A salesperson could refer to that exception record to see what the exception was based upon. You should consider supplying your salespeople with a list of your exceptions.
“age” - means that the exception is based on an aging parameter exception.
“spl” - means that the commission was split and this is the amount that the commission was altered by. In this case, 25% of the commission is taken away to give to the secondary salesperson. The report shows which salesperson the commission is split with, and what the net payable commission amount is for each of the two salespeople on each split line item.
“pp” - means partial payment. The commission is adjusted so that only the percentage of the invoice that was paid, is commissioned. The report shows what percentage of the invoice was paid, and what percentage of the commission is taken away due to the partial payment.
Note: There is a complete legend of all the exception codes at the bottom of each X by Y Commissions Report.
This is the summary report for the primary salespeople, it goes with the prior reports. A detail and summary report is printed for the primary and secondary salespeople. This report lists each salesperson and the total commission amount payable.
Note: The summary report always summarizes based upon the “X” parameter of your X by Y Report. If your “X” parameter is not salesperson, then whatever your “X” parameter is will be summarized in this report.
The header page displays the parameters that were selected. In this case the user selected to give the Secondary salespeople 25% of the respective Primary salesperson's commissions, and to reduce the Primary salesperson's commissions by that 25%.
The Detail Report list each line item, sorted by secondary salesperson and then by customer account (the X and Y parameters).
This report (like the primary salesperson report) is limited to the salespeople that were selected on the parameters screen, and show on the header page.
This report shows an exception in the EXCPT column labeled ovr. This represents a commission override that was entered using the “Maintain Salesperson Commissions” program. The override was for 1.00% of sales. If you cross-reference this line item to the primary salesperson report, you will see that the primary salesperson receives a negative commission due to the selected option to “reduce the primary salesperson’s commission by the amount give to the secondary salesperson”. For this invoice/line, the secondary salesperson’s commission was greater than the primary salesperson commission.
The commission reports can produce an additional report showing negative commission write-offs based AR write-off code WZ. This report can be used to impact salesperson commission amounts which are generated by write-offs in AR for amounts that were never paid.
Note: This functionality has to be activated via the System Wide Setting - Options for Salesperson Commissions.
Associated Files
Use the AR Ledger to find each account's current balances as well as aging and all open transactions, or those paid or closed in the current month.
An Example
Let’s look at the reports for just one invoice that had write-offs on a couple of lines.
The commission report, which shows how much commission was paid per invoice, shows that $52.11 was paid in commissions for invoice 411691 (the report was run just for that invoice).
When we look at the commission write-off report, we see that $14.21 in commissions has been written off and should be taken off the $52.11 commission that the salesperson was originally slated to receive.
Here is how the commission write-offs were calculated.
By looking at the AR Ledger for Invoice 411691 we can see a $250 write-off.
To find the percentage of the invoice that was not paid divide 250/579. The $579 is the total Extended Price of the invoice. It is not the invoice total since it does not include any fees, charges or taxes.
250/579 = 43.17%
So now we know that 43.17 percent of the invoice was not paid for. The system uses that percentage to calculate how much should be deducted from the original commissions. The first and third lines had negative commissions (-17.00 and - 3.00)
Original Reduction
Commission Amount
39.37 x .4317 17.00
6.95 x .4317 3.00
Add all the commission payments, both the negative and the positive, and the total is 14.21.
52.11 - 14.21 = 37.90 (new commission payment)
The Dancik system supports several strategies for assigning salespeople to orders. These strategies can be used concurrently, and in some cases used in combination on the same order. Salesperson Codes can be assigned to orders in the following places:
Header of an order - Via the Salesperson field
Billto File - Account “profile” screen. This is the default unless overridden.
F14 “Codes” screen of the Billto File
Salesperson Codes can be assigned to invoices in the following places:
The salesperson assignments on an order automatically flow through to all invoices for that order, unless overridden on the invoice.
The salesperson assignment for an invoice may be overridden by keying a salesperson code on the header of the invoice when the order is invoiced.
The salesperson assignment may be overridden even after invoicing, by using the Maintain Salesperson Assignments by Order or Invoice option on the Commissions menu.
Sales Commissions by Price Class
This is a self-contained alternate commission system with its own rate table and report. The Sales Commission by Price Class Table, accessed from the File Management application, allows you to structure a commission structure for each price class.
Enter either an invoice range or an invoice date range.
The customer version drops out cost and profit information. The default is M for management version, unless the user is coded to not see costs.
The X by Y Sales Commission Report by Order Number includes all of the same parameters, features, and formulas as the regular X by Y Sales Commission report. It also includes a third level of sorting - by order number, within the X and Y parameters.
This option enables you to print all line item details, or a summary by order within the X and Y parameters. You can apply commissions at the order level, instead of at the line item level. This is an extremely important option. If you apply commissions at order level instead of line level, consider the following:
Commissions based on gross profit % use the gross profit % of the entire order, not each line. This can cause different commission percentages to be applied, as line item GP% may vary on a line by line basis, and may differ from the blended order-level GP%.
Commission exceptions based upon item-level information (such as manufacturer or item) are ignored when you choose to calculate commissions at order level. This is because an order can consist of multiple products. Product information only exists at the line item level and line item calculations are not performed.
Commissions _by Order - Header_Page
Commissions - Calculated at Order Level not line item level
Sales and Commissions Split 50 50
Sales Commission Summary Report
This header page is for an X By Y Commissions Report by Order that was run by a payment date range, for both Primary and Secondary salespeople. The reports are both requested at the same time, but are run separately, and this header page goes with the first report that is generated, which is for the Primary salespeople.
The header page displays the parameters that were selected. In this case the user selected to give the Secondary salespeople 25% of the respective Primary salesperson's commissions, and to reduce the Primary salesperson's commissions by that 25%. The header page also notes that any commission exceptions that are based on products or other line item data will not be used. This is because of the option to calculate commissions at order level (not at item level).
Each line item is listed, sorted by salesperson and then by customer account (the X and Y parameters). The report is then sorted by order# and then invoice number within the X and Y parameters.
The Excpt column on the far right of the report has several codes indicating the exceptions. However, all commission calculations, including the commission exceptions are calculated at the order level only, based on the user's choice to calculate at order level. The line items are all listed, but the commission only shows next to the order-level sub-totals.
The order sub-total lines list the order#, followed by the ship via code, the customer PO#, and the order totals.
Even though the report is sorted by order number, the calculations are done at line-item level, similar to the regular X by Y Commissions Report.
The header page displays the parameters that were selected. The header page also notes that all commission exceptions will be used, because the calculations are performed at line item level.
On the actual report, each line item is listed, sorted by salesperson and then by customer account (the X and Y parameters). The report is then sorted by order number and then invoice number within the X and Y parameters.
The Excpt column on the far right of the report lists the codes indicating exceptions, including “spl” for split commissions and “pp” for partial payments. All commission are calculated at the line item level, based on the user's choice to calculate at line level.
This is the header page for an X By Y Commissions Report by Order that was run by a payment date range, with the Split Sales & Commission Option. The split sales & commission option produces a single report, which splits the sales among the primary and secondary salespeople. For example, a single invoice may be listed twice - once under the primary salesperson and again under the secondary salesperson. The quantity and extended vales of the invoice are split by the percentage that the user requests, which in this example is a 50/50 split.
The header page displays the parameters that were selected. The header page also notes that all commission exceptions will be used, because the user selected to perform the calculations line item level.
Each line item is listed, sorted by salesperson and then by customer account (the X and Y parameters). The report is then sorted by order number and then invoice number within the X and Y parameters.
The Excpt column on the far right of the report lists the codes indicating exceptions, including “spl” for split commissions and “pp” for partial payments. All commission are calculated at the line item level, based on the user's choice to calculate at line level.
There can be several exceptions based upon age of the invoice when paid. For example:
an order which has its commission eliminated (E) due to the invoice being > 120 days old when paid.
aging exceptions for orders which add to the commission because the invoice was paid in less than 180 days.
orders with multiple lines can be split between salespersons. Each line can be split - with half the quantity and half the extended price and gross profit dollars allocated to each salesperson (due to the 50/50 split requested by the user). However, although the sales and GP dollars are split evenly, each salesperson has their own commission rates and their own exceptions. Therefore, the commission dollars for each salesperson is different.
All X by Y Commission Reports include a summary sheet that lists each “X” parameter (usually salesperson) and the total sales and commissions payable. This report may be used for payroll.
This is a self-contained alternate commission system with its own rate table and report. The Sales Commission by Price Class Table, accessed from the File Management application, allows you to structure a commission structure for each price class.
Enter either an invoice range or an invoice date range.
The customer version drops out cost and profit information. The default is M for management version, unless the user is coded to not see costs.