Module Description

This module is integrated with the standard sales and purchase cycle functionalities of Dynamics 365 Business Central. Salesperson can be linked to a commission contract, where commission percentages or amounts have been set as rules. The commission is calculated in the sales order and posted to the commission ledger entries together with the posting of the sales invoice or credit note. The commission cost can be posted on a periodic basis in the general ledger entries. The final step is the settlement of the commission, and the generation of a draft purchase invoice.

Setup

Commission Setup

Complete the “Commission Setup” which contains the basic settings of the module.

Please fill in the page considering that:

• Purchase Settlement Invoice Nos. : Serial number used for the provisional purchase invoice generated by the settlement.
• Default Base incl. Line Discount : If this field is checked, the commission contracts will automatically consider the line discount entered in the order as part of the commission base. However, it will be possible to change this setting in the individual contract.
• Default Base incl. Invoice Discount: If the field is checked, the commission contracts will automatically consider the invoice discount as part of the commission base. However, it will be possible to change the setting in the individual contract.
• Default Base VAT incl.: If this field is checked, the commission contracts will automatically consider the VAT amount as part of the commission base. However, it will be possible to change the setting in the individual contract.
• Salespersons Commissions Settlement Terms: indicate the terms for the settlement of commissions, and can be set as follows:

1. Invoiced : All entries posted in the period are settled. Salespersons are paid a commission on everything that has been invoiced.
2. Paid (Fully): All entries posted in the period are settled, if the invoice has been paid by the customer. Commissions are only accrued when the invoice is fully paid.
3. Paid (Partial): the entries posted in the period are settled proportionally with the amount paid by the customer.

• Get Agent From: Specifies whether the documents inherit the agent from “Bill to Customer” or “Sell to Customer”.
• Journal Template Name : indicates the Template for manual registrations of commissions ledger entries
• Journal Batch Name : indicates the Batch for registrations of commissions ledger entries
• Get price from : Indicates which price should be used as the taxable base for the commissions calculation. It can be set as follows:

1. Order lines: the taxable base that is used to calculate the commission is that of the original order that generated the entry.
2. Invoice/credit memo lines: the taxable base used to calculate the commission is that of the invoice/credit memo.

If you have relationships with salespersons with currencies other than Euro, you must complete the exchange rate difference tab in case of exchange rate difference.

Please fill in the page considering that:

• Management of Foreign Exchange Differences: this should be checked only if you want to activate the detection of exchange differences during the registration phase of the commission settlement invoice.
• Gen. Journal Foreign Exchange Differences: indicates the Template for recording exchange rate difference entries.
• Foreign Exchange Differences Batch: indicates the batch for recording exchange rate difference entries.
• Active Foreign Exchange Differences Account: account used for profits from exchange differences.
• Passive Foreign Exchange Differences Account: account used for losses from exchange differences.

Salespersons Card

The salesperson list contains various information and settings to manage the posting and settlement of commissions.

The minimum fields to be filled in are the following:

• Code: Salesperson ID
• Name: Name or business name of the salesperson/purchaser
• Salesperson Type: Two values can be indicated:

1. External: salesperson to whom the commission is recognized
2. Internal: salesperson who is not recognized the commission

• Salesperson Commissions Settlement C/G account: indicates the account that will be used on the pro-forma commission invoice lines
• Salesperson Commissions cost G/L account: indicates the cost account that will be used by the procedure for calculating invoices to be received.
• Simulated C/G account for invoices to be received : indicates the “invoices to be received” account that will be used with the simulated cost allocation procedure.
• Commission Vendor No.: vendor code linked to the salesperson to which the commissions will be paid and the pro-forma invoice will be created

Salesperson Setup

In order for the system to calculate commissions, each agent must be added to the setup page; if not entered in this setup, commissions are not calculated.

• Salesperson Code : salesperson code.
• Area Manager: Indicates the person responsible for the area to which the salesperson is connected.
• Salesperson Group: Specifies the salesperson group that the salesperson is a member of.
• Settlement Type: Indicates how the individual salesperson is settled:
  1. From setup: takes into account the value set on the Commissions Setup;
  2. From group: takes into account the value set on the Salesperson Group;
  3. Invoiced: the salesperson is paid based on Invoiced;
  4. Paid (partially): the entries posted in the period are settled proportionally to the amount collected from the customer;
  5. Paid (Completely): the entries posted in the period are settled only when completely collected by the customer.

Commission contract

The rules for calculating the sales commission must be specified in one or more commission contracts, specifying that commission settlements are made by contract.

It is recommended to create a contract for each salesperson.

The main information must be included in the commission contracts.

• Code: code of the commission contract, it must coincide with the code of the salesperson for whom you want to make the contract
• Description: short description (optional)
• Status: The contract is generated with Status “New”. No calculations can be performed until the contract is changed to “Certified”. A certified contract cannot be modified, to do so you must change the status to “Under development”. After making changes you must change the status back to “Certified” to make it usable.
• Contract: Reference to the contract number. (optional)
• Commission settlement in currency: activate if you want to calculate commissions in the currency of the sales document.

The terms of the contract are defined in the lines.

• Priority: When calculating the commission for a document line, the system processes the contract lines in priority order (from lowest to highest priority); this way, if multiple contract lines apply to a document line, only the one with the lowest priority is used.
• Commission Type : Specifies whether the contract applies to a salesperson or a group of salespersons
• Code : code of the saleperson or group of salespersons
• Document Salespersons:
  • Document Salespersons : the commission is calculated for the salesperson defined in the contract row both in the case of Agent1 and in the case of Agent 2, it is not applied for Area Manager
  • Area Manager : the commission is calculated for the salesperson defined in the contract row when he is set as Area Manager
  • Full Document - Always applies to all documents. If you select this option, you will not be able to enter the agent code.
    (if you enter the agent, the system will return the following error :
• Salesperson 1: The commission is calculated for the salesperson who is specified in the document as Salesperson 1
• Salesperson 2: The commission is calculated for the salesperson who is specified in the document as Salesperson 2
• Reference date: can be set:
  • Registration date: the registration date present on the order is considered to check the validity of the contract line
  • Order date: the order date present on the order is considered to check the validity of the contract line
• Start Date: Starting date of the contract (optional)
• End Date : Ending date of the contract (optional)
• Additional commission : additional commission line
• Value type:can be set:
  • Percentage: the commission is calculated based on a percentage of the taxable amount indicated in the Value field
  • Fixed amount: the commission is a fixed amount regardless of the taxable amount that must be indicated in the Value field
  • Amount per Qty: to calculate the commission, the quantity in the order lines is considered and multiplied by the Value field
  • Net weight amount: to calculate the commission, the net weight in the rows is considered by multiplying it by the Value field
• Value : Contains the value that refers to the type in the previous field.
• Base Incl. Invoice Discount: if flagged, the calculation will automatically consider the discount lines in the commission base
• Base incl. Line discount: if flagged, the calculation will automatically consider the invoice discount in the commission base
• Base incl. VAT: if flagged, the calculation will automatically consider VAT in the commission base
• Commissions on non-item lines: it has to be flagged if commissions must also be calculated on non-item type lines. (e.g.: C/G account)

Customer and item filter

In the commission contract it is possible to set different filters:

• Customer No .: You can choose a specific code from the customer list. The commission contract line will be filtered on the entered code and the commission will be calculated only on the indicated customers.
• Customer commission group : it is possible to set up commission groups that can be assigned in the customer card. The commission contract line will be filtered on the code entered and the commission will be calculated only on the customers with such commission groups indicated in the customers cards.
• Customer Field Filters: By clicking on the assist edit you can specify that a certain commission rule applies to a certain customer or group of customers (all fields in the customer table can be used) based on the filters that are applied to the customer cards fields.
• Item No.: You can choose a specific code from the item list. The commission contract line will be filtered on the entered code and the commission will be calculated only on the indicated items.
• Item commission group: it is possible to set up commission groups that can be assigned in the item card. The commission contract line will be filtered on the code entered and the commission will be calculated only on the items having such commission groups indicated in the items cards.
• Item field filters: by clicking on the assist edit you can specify that a certain commission rule applies to an item or groups of items (all fields in the item table can be used)) based on the filters that are applied to the item cards fields.

Calculation step

This type of calculation is used when the percentage or amount of the commission can change depending on the value of a field included in the sales document lines. The fields that can be managed as a threshold are:

1. Line discount
2. Profit

In the “calculation threshold type” field you can select the discount or profit method, while the “calculation threshold” field is used to set the rows for the calculation threshold.

The example shows a decreasing scale of commission percentage in relation to the discount percentage specified for each row.

• Threshold value (starting from): represents the percentage of discount or profit intended as "starting from" (based on what has been set in the "calculation threshold type" field) (e.g. from the figure: applies a 5% commission starting from a 4% discount, a 3% commission from an 8% discount and applies a 2% commission from a 10% discount)
• Value (%): fill in this field if the commissions are set as a percentage value.
• Value (VL): fill in this field if the commissions are based on fixed amounts.

These last two fields are alternatives to each other.

Functionality

Customer

In the customer card you can indicate the salesperson code, salesperson code 2 and the internal salesperson.

Commissions in the sales order

When creating the sales order, the “Salesperson Code” and “Salesperson Code 2” fields are automatically filled in with the information in the customer's card. However, it is possible to change the field values.

After releasing the order, both from the lines (select: “Related information” – “Commissions”) and from the header (select: “Actions” – “Functions” – “Commissions”) it is possible to view the calculation that has been carried out for the commissions present in the order.

The “Document Commission Line” page shows a preview of the commissions that will be posted definitively when the invoice/credit memo is posted. This function allows users to check the percentage or amount of commissions applied to the selected line or the entire document, and the taxable base on which they are calculated.

In this screen you can also manually change the commissions, if you need to specify a value different from the one entered in the contract. Click on “Actions” and then on “Change commission value”.

• Old value: indicates the old value of the commissions
• New value: enter the new commission value

By closing the page the system will ask for confirmation.

Rows on which the commission has been manually modified will be identified with the “Manually modified” flag active.

It is possible to modify multiple lines through the “Change Commission Value Multi-Line” function. It follows the same logic as the previous function.

Commissions on sales invoices and credit memos

Creating the invoice

In the example below, the invoice was created using the “Get Shipment Lines” function to get the lines posted in the order.

The same commission used and calculated in the sales order will be reported in the sales invoices. You can check the commission that is applied from the lines (line - related information - commissions) or from the invoice header (actions - functions - commissions).

If there are two different salespersons on the sales order and the invoice, the user will have to go to the order even if it is already shipped, reopen it, change the agent and release it. In this way the commissions will be recalculated on the order with the updated agent and correctly reported on the invoice.

It's possible also disable commissions: in this way the “commission ledger entry” line will not be created when posting the invoice, for those lines that have been disabled.

Invoice posting

Posting sales invoice / credit memos according to standard process:

When you do this, the system simultaneously post commission ledger entries. From the posted sales document, you can use the “Find entries” function to check the posted entries.

The key fields in this table are:

• Entry type: specifies whether the entry is of the normal or settlement type
• Commission Factor : Specifies the percentage or fixed amount used to calculate the commission.
• Taxable Amount (VL): amount on which the commission is calculated considering the setup settings
• Amount (VL): amount of the commission
• Remaining Amount (VL): The remaining amount will show, at any time, the amount of commissions that is still awaiting settlement for the saleperson.
• Paid Commission (VL): is populated with the amount of commissions that have been paid. Initially, when no settlement entry has been made, this field will be empty.
• Settled Amount (VL): is populated with the amount of the invoices addressed to the supplier linked to the saleperson, not yet paid

The commission ledger entries of a specific salesperson can also be visible from the Commission Contract page:

Posting the commission cost

At the end of each accounting period, the cost of commissions that have not yet been paid to the salesperson will be accounted for in the balance sheet. Users can do this through the “Post Commission Cost” report.

The report will allow you to allocate the cost with two distinct logics:

Commission cost in G/L

The first logic involves posting the commission cost in the G/L entries, generating Cost entries for Invoices to be received .

As a result, the settlement invoice will not be posted on cost, but will bring the Invoices to be received account back into line, to reverse the amount allocated with the report.

Below are the fields to fill in:

• Posting date : indicates the registration date that will be proposed in the batch
• General Journal template: specifies the model in which the registration will be proposed. Indicate a General Journal template.
• General Journal Batch: Specifies the batch in which the posting will be proposed

The report can be launched for all salepersons or for a single saleperson. The commission ledger entries posted up to the date entered in the report, which have not yet been settled, will be considered.

The General journal posting batch will be populated with the cost to be posted: commission ledger entries are grouped by salepersons and size.

Commission cost in simulated accounting

The second logic involves posting the commission cost in simulated accounting, generating self-reversing Cost to Invoices to be received entries .

In this case, the settlement invoice will report the cost G/L account in the lines as it happens in the ordinary settlement.

Below are the fields to fill in:

• Posting date : indicates the registration date that will be proposed in the batch
• General Journal Template: specifies the model in which the posting will be proposed. Indicate a Journale Simulation Template
• General  Journal Batch: specifies the batch in which the posting will be proposed
• Reverse Commision Calculation Date: indicate the calculation to define the registration date for reversal entries (1G). The field becomes editable only if a Simulated type journal is entered in the Gen. Journal template field.

The report can be launched for all or for a single salesperson. The commission ledger entries posted up to the date entered in the report, which have not yet been settled, will be considered.

The simulated journal will be populated with the cost to be posted: commission ledger entries are grouped by salesperson and size.

Commission Settlement

Commission Summary

The report to be delivered to the salesperson links all the commissions settled and invoiced for a single salesperson in each period. This report that lists the commissions to be settled can be launched both before the final settlement, to carry out any checks, and after having settled the period.

The report is as follows:

• Show Item Detail : if checked, shows the detail of the items inserted in the document
• Hide base amount : if checked, hides the base amount on which the commission is calculated

You can filter by:

• Salesperson/purchaser: it is possible to filter by the fields in the saleperson card
• Commission Ledget Entry: You can filter by the fields of the commission ledger entries

The print that is proposed is the following:

The print fields are:

• Document No .: Specify the invoice number
• Posting Date : Specifies the date on which the entry was posted
• Order No.: Specify the order number
• Customer No.: Specify the customer number
• Qty : Specify the quantity in the order
• Provv (%): Specifies the salesperson percentage for each document
• Taxable Amount (VL): amount on which the commission is calculated
• Amount (VL ): amount of commissions
• Paid Commission (VL): This field is filled in if the commissions have been fully paid. It corresponds to the amount already paid and is therefore filled in when the purchase invoice is posted.
• Settlement Amount (VL): This field specifies the amount that is yet to be paid. It is populated when the settlement report is launched.

Final settlement and pro-forma creation

The “Commission Settlement” report is used to create a pro-forma of the purchase invoice and the simultaneous definitive settlement of salesperson commissions. The settlement can be based on sales invoices posted in the period or on invoices paid to customers, depending on the agreements with the salesperson and the setup defined for the salesperson.

The report can be launched for all or for a single salesperson:

• Ending date (posting): Specifies the date to be considered as the limit for the posting date of the documents to be settled
• Ending Date (payment): Specifies the date to be considered as the cut-off date for the payment date of the documents to be settled.
• Posting Date: Specifies the date that will be shown as “Posting Date” on the purchase invoice.
• Salesp. Comm. Settl. Term: Can be set to:

1. Paid (completely): Only fully paid documents are included
2. Paid (partially): Documents are included in proportion to the amount paid
3. Invoiced: documents posted up to that date are included

If a commission contract is selected, the one set on the contract itself is proposed.

This report creates a draft purchase invoice, which can be found in the list of purchase invoices:

In addition to creating the purchase invoice, the report creates “Settlement” type entries in the commission ledger entries.

You can also launch the Settlement Commision report from the commission contract. In this case, the report will already be automatically filtered for the commission contract number you are positioned on:

Sales Commission Journal 

It's possible also manage commissions from the commission journal. This function allows to:

• Manually enter additional commission entries
• Manually settle commission entries already posted in the system
• Importing commission entries from other systems

Manual insert can be made from the dedicated Journal commission entries page, which can be called up from the menu, or from the “Insert commission journal lines” action in the commission contract:

The journal to be filled out is the following:

The main fields are:

• Commission contract: code of the commission contract on which you want to write the commission line
• Commission contract line: number of the commission contract line on which you want to write the commission line
• Entry type : normal or settlement
• Posting Date : the posting date of the commission line
• Document No.: document number. If you want to connect the line to an existing invoice, enter the invoice identification number.
• Document type: specifies the type of document that you want to "simulate" for the creation of the commission ledger entry; it can be:
  • Sales Invoice
  • Sales Credit Memo
  • Purchase Invoice
  • Purchase Credit Memo
• Invoice-to no.: number of the customer/vendor to whom you want to invoice
• Salesperson Code: Salesperson Code
• Type: identifies the sales/purchase object on which to calculate the commission. The options are the same as for the invoice lines, i.e.:
  • C/G Account
  • Item
  • Resource
  • Fixed Asset
  • Charge (Item)
• Nr : Number of the item being sold/purchased, e.g. the article number.
• Description : explanatory field
• Value type: indicates the type of value you want to insert as commission, it can be:
  • Percentage %
  • Fixed amount (VL)
  • (VL) for quantity (base)
  • (VL) for net weight
• Value (% or VL): percentage value or amount of commission based on “value type”
• Taxable amount: taxable amount on which to calculate the commission
• Taxable amount (VL): taxable amount in local currency (€) on which to calculate the commission
• Amount: commission amount
• Amount (VL): amount in local currency (€) of the commission
• Link to document type : the type of document you want to link to
• Link-to document no.: number of the document you want to link

From the journal, you can use the “suggest settlement lines” action to view the open “normal” type commission ledger entries (filtered by salesperson and by commission contract) and select the commission lines you want to settle. Upon confirmation, pre-filled settlement lines are created in the journal.

When you post the commission lines entered in the journal, commission ledger entries are created simultaneously.

Commission Recalculation

Using the Re-calculate Document Commissions feature, you can recalculate commissions.

The report can be run by enabling the following flags:

• Manually modified: if active, allows to ignore manual changes and recalculate commissions on all documents, even those manually modified
• Update orders and temporary documents: if active, allows to update the commissions of sales orders, sales return orders and all invoices and credit memos linked to the orders.
• Update posted invoices: if active, allows to update the commissions of posted invoices.
• Update posted credit notes: if active, allows to update the posted credit memos commissions.

It is not possible to activate the flag for temporary documents and those for posted documents at the same time.

In the request page it's also possible insert filters to select specific documents. The filter must be set in the section consistent with the activated flag:

The recalculation of commissions is performed only if the commission has not yet been paid and/or if the cost of the commission has not been posted in the general accounting.