Overview
Insurance companies work with Brokers/Agencies to sell their products and Dynamics 365 is a great system for managing that partner relationship. An important part of this relationship is the management of commissions that the Agencies are entitled too. These commission agreements can be complex and a robust system is needed to manage the commission plans and calculations.
In this article we show how the North52 Decision Suite business rules engine can be used to manage the commission calculations in Dynamics 365.
Commission Rules
- Commissions are calculated quarterly with the first quarter starting 1 January
- Each Agency is set a quarterly target (Commission Plan) for each product group (business line)
- The calculations for each quarter are based on target achieved year to date, e.g.:
- Q1 - evaluation of January - March actual income vs target at end of March, percentage applied to Q1 income according to evaluation
Q2 - evaluation of January - June actual income vs target at end of June, percentage applied to Q2 income according to evaluation
Q3 - evaluation of January - September actual income vs target at end of September, percentage applied to Q3 income according to evaluation
Q4 - evaluation of January - December actual income vs target at end of December, percentage applied to Q4 income according to evaluation
- Q1 - evaluation of January - March actual income vs target at end of March, percentage applied to Q1 income according to evaluation
- The commission percentages will be applied as per the following tables depending the relationship level of the Agency:
| Relationship Level | Target Fulfillment | |||||
| Below < 90% |
Expected ≥90%<110% |
Exceeded ≥110% |
||||
| Expert | Property | 4.17% | Property | 5.55% | Property | 6.45% |
| Health | 4.15% | Health | 5.45% | Health | 5.65% | |
| Motor Vehicles | 3.16% | Motor Vehicles | 4.21% | Motor Vehicles | 4.78% | |
| Travel | 3.17% | Travel | 3.15% | Travel | 3.34% | |
| Liability | 2.40% | Liability | 2.90% | Liability | 3.30% | |
| Life | 16.00% | Life | 22.00% | Life | 25.70% | |
| Senior | Property | 3.97% | Property | 5.35% | Property | 6.25% |
| Health | 3.95% | Health | 5.25% | Health | 5.45% | |
| Motor Vehicles | 2.96% | Motor Vehicles | 4.01% | Motor Vehicles | 4.58% | |
| Travel | 2.97% | Travel | 2.95% | Travel | 3.14% | |
| Liability | 2.20% | Liability | 2.70% | Liability | 3.10% | |
| Life | 15.80% | Life | 21.80% | Life | 25.5% | |
| Standard | Property | 3.77% | Property | 5.15% | Property | 6.05% |
| Health | 3.75% | Health | 5.05% | Health | 5.25% | |
| Motor Vehicles | 2.76% | Motor Vehicles | 3.81% | Motor Vehicles | 4.38% | |
| Travel | 2.77% | Travel | 2.75% | Travel | 2.94% | |
| Liability | 2.00% | Liability | 2.5% | Liability | 2.90% | |
| Life | 15.60% | Life | 21.60% | Life | 25.30% | |
| Starter | Property | 3.57% | Property | 4.95% | Property | 5.85% |
| Health | 3.55% | Health | 4.85% | Health | 5.05% | |
| Motor Vehicles | 2.56% | Motor Vehicles | 3.61% | Motor Vehicles | 4.18% | |
| Travel | 2.57% | Travel | 2.55% | Travel | 2.74% | |
| Liability | 1.80% | Liability | 2.30% | Liability | 2.70% | |
| Life | 15.40% | Life | 21.40% | Life | 25.10% | |
North52 Decision Suite
For this example we are assuming that the income generated by each agent for each business line will be inserted into the Dynamics instance (into a custom entity called Commissions) every quarter from a data warehouse.
The North52 Decision Suite solution works like this:
- A Decision Table Formula is set up on the Commissions entity which:
- Evaluates the percentage commission to apply based on the actual year to date income versus the target year to date
- Updates the Commission record with:
- Target for period (obtained from the Commission Plan records)
- Calculated Target year to date
- Calculated Actual year to date
- Percentage Commission to be applied
- Calculated Commission Amount
- Attaches a trace log audit file which contains detailed breakdown of how the commission was calculated at the time of the calculation
Entity Setup
Account
The Account entity has details of the Broker/Agent relationship level which is used in determining commission percentages.
Commission Plans
Commission Plans determine the Target for each Agency/Broker and each business line.
Commissions
Commission records are initially generated from an import from the data warehouse and contain details of:- Account (Agency/Broker)
- Product Subcategory (Business Line)
- Period Start and End Date
- Actual income associated to the Agency/Broker for the Period
On save of this record the Commissions are calculated base on the rules outlined above:
- Target is looked up and copied from the appropriate Commission Plan
- Actual YTD is calculated from the current year Commission records
- Target YTD is calculated from the current year Commission Plans
- Percentage Applied is obtained from the rules in the Decision Table
Which is translated into:
- Amount is calculated from Actual * Percentage Applied
North52 Formula
The North52 Decision Table Formula is triggered on create/update of the Commissions record. The Formula has 3 Decision Table sheets:- Rules to determine the commission percentage based on the Relationship Level, Product Subcategory and the performance against their Commission Plan
- Rules to update the Commission record with the calculated figures
- Rule to attach the trace log audit file to the Commission record
Decision Table Sheet - Determine Calculation %
Decision Table Sheet - Update Commission Record
Once the Commission Percentage is determined the Commission record is updated with the calculated commission amount and the values used to calculate the amount:
Decision Table Sheet - Audit Trace log
For the rules in the Determine Calculation % and Update Record sheets there are additional calculations and configuration on the Global Calculations and Global FetchXml sheets.
Global Calculations
Global Calculations process rules and calculations that can be used as in the Decision Table sheets. For the commission calculations we needed to:- Calculate the year to date (YTD) revenue for the Agency/Broker (using the North52 FindSumFD function and a parameterized Fetch XML query)
- Calculate the Commission Plan YTD target amount (using the North52 FindSumFD function and a parameterized Fetch XML query)
- Retrieve the Commission Plan Current Period Target amount (using the North52 FindValueFD function and a parameterized Fetch XML query)
- Calculate the fulfillment percentage that that Agency/Broker achieved using the calculations above: Calculate YTD Revenue and Get Commission Plan YTD Target
Global FetchXml
The Global FetchXml sheet defines the Fetch XML queries that are used in the Global Calculations. They are parameterized queries that change according the information supplied at runtime.
