Transcript:
0:00 in this video we show how the North52
0:03 decision suite
0:04 business rules engine can be used to
0:05 manage commission calculations in
0:07 dynamics 365.
0:09 insurance companies work with brokers
0:11 and agencies to sell their products
0:13 and dynamics 365 is a great system for
0:15 managing that partner relationship
0:17 an important part of this relationship
0:19 is the management of commissions that
0:20 the agencies are entitled to
0:23 these commission agreements can be
0:24 complex and a robust system
0:26 is needed to manage the commission plans
0:28 and calculations
0:31 as an example the following rules apply
0:34 commissions are calculated quarterly
0:36 with the first quarter starting
0:38 first of january each agency is set a
0:40 quarterly target
0:42 a commission plan for each product group
0:44 or business line
0:45 the calculations for each quarter are
0:47 based on target achieved year-to-date
0:49 which is defined as follows for quarter
0:51 one we evaluate january to march
0:53 actual income versus the target at the
0:55 end of march and a percentage is applied
0:57 to
0:58 quarter one income for quarter two we
1:00 evaluate january to june
1:02 actual income versus target at the end
1:04 of june and a percentage is applied to
1:06 quarter two income
1:07 for quarter three and four the
1:09 evaluation follows the same pattern
1:11 the commission percentages will be
1:13 applied as per the following tables
1:15 depending on the relationship level of
1:16 the agency
1:18 let's take an agent whose relationship
1:19 level is expert and have exceeded their
1:21 target
1:22 for life insurance they will get a 25.7
1:25 commission
1:26 on the income they generated a standard
1:28 level agent who is below expectations
1:30 for property insurance
1:31 will receive 3.77
1:35 for this system we are assuming that the
1:36 income generated by each
1:38 agent for each business line will be
1:40 inserted into the dynamics instance
1:42 into a custom entity called commissions
1:44 every quarter from a data warehouse
1:47 for this video we'll simulate the
1:49 integration using an
1:50 excel import here is the source excel
1:53 workbook
1:54 with actual income values for an agency
1:56 by product subcategory and quarter
1:58 we'll import this data into the system
2:00 and then review the commission
2:02 calculations later
2:05 there are three entities used for the
2:07 calculation shown by the simplified
2:09 entity diagram
2:10 account commission plans and commissions
2:13 the account entity has details of the
2:15 broker agent relationship level
2:17 which is used in determining commission
2:19 percentages second
2:21 the commission plans determine the
2:22 target for each agency broker and each
2:25 business line
2:26 and third the commission records these
2:28 are generated from an import from the
2:29 data warehouse and contain details of
2:31 the
2:32 account agency or broker the product
2:35 subcategory which the business line
2:37 the period start and end date actual
2:40 income associated to the agency or
2:42 broker for the period
2:43 on save of this record the commissions
2:45 are calculated based on the rules
2:47 outlined previously
2:49 the target is looked up and copied from
2:51 the appropriate commission plan
2:53 actual year to date is calculated from
2:55 the commission records for the current
2:56 year
2:57 target year-to-date is calculated from
2:58 the commission plans
3:00 the commission percentage applied is
3:01 obtained from the rules in the decision
3:03 table
3:04 and the commission amount is then
3:05 calculated from actual revenue
3:07 times the percentage applied let's take
3:10 a look at the North52 formula for the
3:12 commission calculations
3:14 the North52 decision table formula is
3:16 triggered on create or update
3:18 of the commission record the formula has
3:20 three decision table sheets
3:22 the first has rules to determine the
3:24 commission percentage based on the
3:25 relationship level
3:27 product subcategory and the performance
3:29 against their commission plan
3:31 the second has the rules to update the
3:33 commission record with the calculated
3:34 figures
3:35 and the third has the rule to attach the
3:37 trace log order file to the commission
3:39 record
3:40 this outputs a trace file with all the
3:42 details of the calculations
3:44 which can be used if there is ever a
3:46 question about how the commission was
3:48 calculated
3:48 at that exact time for the rules in the
3:52 determined calculation percentage
3:53 and update record sheets there are
3:56 additional calculations and
3:57 configuration
3:58 on the global calculation hxml sheets
4:01 global calculations process rules and
4:04 calculations that can be used
4:05 in the decision table sheets for the
4:08 commission's calculations
4:09 we need to a calculate the year-to-date
4:12 revenue for the agency or broker
4:14 using the north52 find some formula
4:17 description function
4:18 and a parameterized fetch xml query b
4:22 calculate the commission plan
4:23 year-to-date target amount
4:25 using the North52 find some function
4:27 and a parameterized fetch xml query
4:30 c retrieve the commission plan current
4:32 period target amount
4:34 using the north52 find value formula
4:36 description function
4:37 and a parameterized fetch xml query and
4:40 d
4:41 calculate the fulfillment percentage
4:42 that the agency or broker has achieved
4:44 using
4:45 the output from the calculate
4:46 year-to-date revenue and the get
4:48 commission's plan year-to-date target
4:50 calculations
4:51 the global fetch xml sheet defines the
4:53 fetch xml queries that are used in the
4:55 global calculations
4:56 they are parameterized queries that can
4:58 change according to the information
5:00 supplied at runtime
5:02 now let's go back and review the
5:03 information we imported which has now
5:05 been transformed into commission records
5:07 with the values calculated
5:08 we can see for the first quarter the
5:10 agency was below target for life
5:12 insurance
5:12 and received a sixteen percent
5:14 commission on fifty thousand dollars
5:16 which amounted to eight thousand dollars
5:18 for the quarter thank you for watching
5:20 if you have any questions about how
5:21 North52 can help your organization with
5:23 commission
5:24 or other complex calculations in your
5:26 power platform projects
5:27 please get in touch the details are on
5:29 screen now
5:31 also please subscribe to our youtube
5:33 channel by clicking the icon on the
5:35 right hand side of the screen