Commissions Code Maintenance

You set the Commission Code Maintenance for a user in the User ID Maintenance form on the Commissions tab by selecting the Settings icon beside the Commission Code list.

Commission_Code_Maintenance

A "commission code" does not define the actual rate of a commission. Codes determine whether the base rate for the user should be either increased or decreased based on either or both of these two factors:

(1) the margin of the sale, and
(2) the payment status of the invoice (applies to open item accounts, C.O.D. invoices, and Installed Sales contracts only).

The commission rate will be based on the percentage you assign on the User Maintenance form for each user type (Assigned, Order, and Cashier).

Two separate tables are provided: Gross Margin and Not Paid (Days). There is no requirement that a code have both tables populated. A code may only include "not paid" reductions and no gross margin ranges or percentages, or a code may only use gross margin ranges and percentages, but not list any "not paid" levels.

Margins, for commission reasons, are calculated by the Commissions Inquiry using an adjusted subtotal and an adjusted total cost. Sales tax is not included in the subtotal, so no commission is ever calculated for the tax amount. The extended totals (amount and cost) of "adjustment" type items is backed out of the original subtotal and total cost. "Adjustment" items which are excluded are the following types: gift cards, rental, deposit, freight, stored value, labor, and delivery. In addition, any positive adjustments (adders, etc.) are also removed from the totals (including the total cost if the adjustments are assigned a cost value).

Any use of commissions codes or settings outside the software (custom reports, etc.) will not necessarily match with what is described here. Commission amounts are not calculated and saved with invoices or any other data; therefore, the commission reported for a particular user or transaction is subject to change if the code or user settings related to commissions are modified. Other factors can also affect the transactions which commissions are calculated for such as later cost corrections, merges, and charge backs. The use of commissions codes as explained in this help topic pertains specifically to how the Commissions Inquiry form applies this information. The inquiry calculates commissions based on the current settings for the user, not the settings in place at the time an invoice (sale) was processed.

Gross Margin

The Gross Margin (upper) table allows you to define ranges of margin from low to high sequentially in ascending order (from top to bottom, where the top level is the lowest margin range and the bottom-most level is the highest range). Each level has an lower (left) and upper (right) range value; however, the first level assumes a starting (lowest) range of zero (0.00) and the last level you define assumes an upper margin value of 100%. A level (margin range) assigned a commission adjustment rate of 100% would provide the user with the full amount of the original commission, an adjustment rate of 50% would provide them with 1/2 (half) the original commission amount, and an adjustment rate of zero (0.00) would provide them with no commission. Negative margins (where total cost is higher than the subtotal) produce no (zero) commission on sales or returns.

Ten possible levels are provided; however, you don't need to use all the levels provided. Define only as many levels as you need, but you should not skip or leave blank levels in between other valid ranges (you can leave blank levels at the end or bottom). The very last level you define should have an empty (blank) ending (upper) margin percentage.

The commission percentages are entered in the far right-hand column across from each gross margin range defined. These percentages define how much of the original commission should be given based on the margin (resulting in either a reduction, no reduction, or an increase in the commission). The percentages entered do not define the commission rate itself!

Not Paid (Days)

This table may be used to adjust the original commission amount based on the payment status of the invoice and a percentage you specify. Past due payment status is only available for customer accounts that are charge and open item. Cash-only and balance forward types only consider the first level (if defined).

As with the Gross Margin table, a number of levels are provided. Each level consists of a lower and upper range of days (age) as well as the percentage you wish to adjust the previously calculated commission by. The first (top) level assumes a lower value of zero days and the last (bottom) level assumes any number of days greater than the last upper level you defined.

The age of an invoice is determined by comparing the Entry Date of the document to the Paid Date of the invoice. Paid date is only set for open-item type accounts.

The age of the invoice is not based on the billing cycle of the invoice or any other Receivables settings (terms, past due period, etc.), so the periods you define may not necessarily match the aging which appears on a customer's statement or any terms that might be associated with that customer. For example, a customer might not be charged finance charges until their balance is past 60-days or is a term's customer with 60-day allowance before finance charges. If you choose to reduce commissions for open-items past 30-days, the commission is still reduced regardless of the customer's payment expectation. For this reason, you should consider factors such as what your company's payment expectations are and how they should be applied to the payment of commission.

Age ranges are intended to be entered from lowest (top) to highest (bottom). Typically, if a company wanted to adjust commissions for late payment, the adjustment would reduce the commission more for older unpaid or paid late items. If a zero rate (commission adjustment percentage) is specified, no commission will be given (blank entries are not considered a zero).

Commission Code Reporting Tips

Consider these tables and field values when developing your own Commission Reports

The USERS (dbo.Users) table contains base commission rates along with other user information. The Commissions Percentage field names are:

CommPctAsgSales, CommPctOrdSales, and CommPctCashier.

The CommissionsBasis field contains a numeric setting for either gross sales (0) or gross profit (1).

The field named Commissions is a Boolean value (either True or False) and indicates the value of the Paid Commissions check box on the User Maintenance form.

Commission Code information is stored in three tables:

CommissionCodes, CommissionGMRanges, and CommissionNPRanges.

The CommissionCodes table contains just the code and description (not including some additional fields used for auditing, etc.).

The CommissionGMRanges table includes records for each Gross Margin level assigned to a code. Relevant columns in this table include the Code, Sequence (level index), LowerRange (starting margin), UpperRange (ending margin), and CommissionAdjustment (the decimal value of the percentage entered.

For example, 50.0% would also be 50.0000 in the table). CommisionNPRanges includes records fro each Not Paid (Days) level. Relevant columns in this table are the same as those in the CommissionGMRanges table. The only difference is that the UpperRange and LowerRange columns in the Not Paid (Days) table maintain integer values (tinyint), not decimal values.

The basic calculation used for commissions by the Commissions Inquiry is below:

Commission Amount * (GM Adustment %/100) * (Not Paid Adjustment %/100)

The Commission Amount (above) is calculated based on the user's percentage based on their user type (assigned, order entry, or cashier) and whether or not the user's commission is configured to be calculated based on the "gross sale" amount (the adjusted subtotal) or just the "gross profit" (the difference between the adjusted subtotal and the adjusted total cost).*

The Commissions Inquiry references data in the tables mentioned previously as well as data regarding transactions from the Documents,InvoicesHdr, and InvoicesDtl tables.

*What do we mean by "adjusted" subtotal and total cost?

The original totals are adjusted by the total of any "adjustment" type items as well as any positive adjustments (adders, etc.) applied to the transaction. Adjustments may or may not have a cost assigned to them; however, if they do, that cost is adjusted out of the total cost before any margin calculations are done.

When writing reports, it's important to understand that a number of different types of records are maintained in the InvoicesHdr table. These include invoices, open tickets (advice notes), charge returns, canceled invoices, direct ship invoices, and installed sales invoices. Each is assigned a different type which is stored as an integer (enumeration) in the InvoiceType column. These types are defined below:

Enumeration (Code)	Invoice Type
0	Ticket (Advice Note)*
1	Invoice
2	Charge Return**
3	Canceled Invoice***
4	Direct Ship Invoice
5	Installed Sale Invoice

*Open Tickets (known as Advice Notes) deduct on-hand quantities when processed; however, payment is not immediately processed. Since Tickets don't have payments, they should probably be excluded from Commissions reports. The "invoice type" on Tickets is changed to the "Invoice" type after the payment is processed.

**Charge Return invoices are created from the Charge Returns transaction in Point of Sale. This does not include returns which are "invoices" that were processed from the Sales transaction. One major difference between an "invoice" and a "charge return" record is that only the "invoice" records reflect return amounts as negative figures. The Charge Return maintains totals as positive amounts, even though it represents a credit (return). Any reporting that includes "charge returns" must therefore either change or consider these amounts as negative (or you may wind up paying commission on returns).

***Canceled invoices are maintained for security and reference reasons. These should be excluded from most reports.

Other possible things to consider regarding payment might be whether an invoice is awaiting COD payment or involved coupons or gift cards, etc.
For example, COD sales have not yet been paid but would have an associated invoice, coupons reduce the amount paid but not the gross sales total or profit, and gift cards are sold and used as payment so they can be reflected twice (once when sold initially and again when used as payment).

How the fields found in the tables we mentioned here are used for commissions purposes is flexible.
For example, the commissions percentage for assigned accounts can be used for any calculation whether it is assigned sales or not. The use of commissions related fields is entirely up to the user, and commissions reporting can be done even without the use of any of these fields.

Check our Customer Portal for ActiveReports® samples that you can use for commission reporting. If you need a special report, you can either write your own or request that Data Support staff create a custom report (there is a fee for this service).