Summary: 

Following Logan Consulting’s methodology, Microsoft Dynamics CRM was successfully implemented at an insurance agency.

The Client:

The client is a Midwestern insurance agency that provides brokers with a choice of group benefits, major medical, life, annuities, DI, LTC and linked benefits all in one place.

The Opportunity:

Processing of commissions for insurance policies is a daunting task that insurance agencies and brokers perform monthly. It consumes a great deal of time and effort to ensure that the proper policies are being paid commissions accurately, and the commissions are paid out to the producers in a timely fashion.

The client was using Dynamics 365 CRM on-premise to handle all aspects of their Insurance business. The application had been extensively customized to their business processes and had complex security roles in place. Commissions processing was being handled off-system in an Access and Crystal Reporting based custom solution that one outside contractor built over several years and operated along with internal users. The system did query the internal CRM database for information, but all processing and reporting was done through this custom system. One of the major reasons for off-system processing of commissions was security concerns.

The client currently processed several major carrier commissions statements per month, with thousands of policies each, along with 40 plus other carriers with varying number of commissionable policies.

The client wanted to investigate the possibility of re-creating the basic process flows and maintenance of the commissions process all within the current Dynamics 365 application, with well-documented processes and procedures that current users were familiar with. There was also a need for good quality variance reporting, using both custom SSRS reports and system views to create one-off ad hoc variance analysis. Finally, within CRM there was a requirement to automate external producer payment processing and reporting including monthly commission statements.

The Solution:

After a detailed analysis of the current off-system process, Logan Consulting designed a solution organized around three components to commission processing:

  1. Import of Carrier Statements directly to CRM with minimal manipulation of data. For each of the major carriers, a custom import template was developed. Logan utilized Scribe Insights integration tool to automate this import and handle data transformation and rule processing to allow matching of multiple fields required to properly match a commission to a policy. For the smaller carriers, a standard template was developed, and carrier data was manually transformed to fit the template for importing. As part of the import process, the scribe process and various workflows allowed updates of various policy information that also came with monthly statements, like number of lives covered on group policy.
  1. Variance Reporting – Based on the type of carrier and policies, Logan developed SSRS reports to handle variance reporting- the discovery of potentially missing or incorrect commissions based on the policy effective dates and last month of payment. Once the basic commission info was directly imported to CRM, standard advanced finds could be used to isolate specific data and export to excel for further analysis.
  2. Commission Payment Reporting – While each agency handles this process slightly different, Logan Consulting developed a series of custom SSRS reports for this client to provide payment info for producers, and how it was to be paid- check or EFT. Additionally, we provided a custom statement report for the Producer that summarized policies and payments. The solution also has a method to email statements to EFT paid producers directly from CRM.

The Results:

Following Logan Consulting’s methodology and the information from the analysis of the off-system commission processing, the solution was installed on their on-premise Dynamics 365 CRM platform in early September of 2018. The project results include the following:

  1. Delivery of a commissions solution that is now contained within Dynamics 365 that designated CRM users with proper security can manage the commission process from start to finish each month.
  2. Predefined Import templates that work across all carrier commission statements, minimizing manual manipulation of this data prior to import into Dynamics 365 CRM.
  3. Rules based processing of statements. If a carrier statement changes, these can be easily adjusted.
  4. Reliable standardized method for handling rejected records from the import, and easily import after correction.
  5. Use of Advanced finds and Views within CRM for Variance analysis. These are tools that CRM users are already familiar with.
  6. Custom SSRS reports that assist with uncovering variances between reported versus expected commissions.
  7. A set of processes to allow audit and payment of producers. This includes custom SSRS reports that allow selection of multiple parameters such as date ranges, agencies or brokers, and payment types.
  8. Documentation of the processes and procedures to follow for monthly processing of commissions.