Transcript:
0:00 this video shows how North52's advanced
0:03 business rules engine for microsoft
0:04 dynamics can be used to model the
0:06 complex rules for the national flood
0:08 insurance program premiums the national
0:10 flood insurance program managed by fema
0:13 provides insurance to help reduce the
0:15 socio-economic impact of floods in the
0:16 united states
0:18 homes and businesses in high-risk flood
0:20 areas with mortgages from
0:21 government-backed lenders are required
0:23 to have flood insurance the cost of this
0:25 insurance is determined by the fema risk
0:28 rating 2.0 methodology introduced on the
0:30 1st of october 2021
0:33 it uses over 35 property attributes more
0:36 than 50 rating tables and over 1 500
0:39 calculations
0:41 in this video we will show how the
0:43 details on an application are processed
0:45 using the North52 rules engine to
0:47 determine the flood insurance premium
0:51 there are a significant number of rules
0:53 used in calculating premiums which are
0:55 defined in several technical documents
0:57 on the fema.gov website
0:59 one of the documents is the premium
1:01 calculation worksheet examples
1:03 this provides a reference that we can
1:05 check calculations against
1:08 we have analyzed and translated these
1:10 rules into decision tables and xcache
1:12 data tables to allow for automated
1:14 calculation of the appropriate premium
1:17 let's first take a look at the rating
1:19 tables
1:20 there are over 50 rating tables that are
1:22 used in calculating flood insurance
1:24 premiums and some of these have over 100
1:26 000 rows of rating data
1:28 these are added to the North52 solution
1:30 using x cache tables
1:32 the x cache tables can be easily
1:34 referenced in decision tables for
1:36 determining values that should be used
1:37 for calculating specific parts of the
1:39 premium calculation
1:41 and because they are separate from the
1:42 rule definitions they can be maintained
1:44 separately
1:46 the first category of rating tables
1:48 defined in the rating factors excel file
1:51 is the base rates for non-levied areas a
1:54 sample of this table is shown here
1:57 we use this information to build the
1:59 corresponding xcache table in the
2:00 north52 decision suite
2:02 the condition columns represent data
2:04 which is used to look up a specific
2:06 rating
2:07 for example
2:08 if the region was south carolina the
2:10 segment won
2:11 and it is for a single family home the
2:14 inland flood building rating would be
2:16 2.255
2:18 the base rates for levied properties are
2:20 defined in a separate sheet in the same
2:22 x cache record allowing the logical
2:24 grouping of related rating tables
2:27 the 1 500 plus calculations for
2:29 determining the premium are broken out
2:31 across a multi-sheet decision table
2:32 formula this reduces the complexity of
2:35 designing and maintaining such an
2:36 intricate calculation
2:39 the first sheet in the decision table
2:40 formula sets inline variables for
2:43 various segment classifications that are
2:45 used to look up rates from the x cache
2:46 tables
2:48 base rates are determined by looking up
2:49 the x cache tables for the relevant
2:51 values cell c4 is highlighted this will
2:54 return a value for a non-levied
2:56 non-barrier island property using a
2:58 global action called fema underscore
3:01 base rates and a parameter of inland
3:04 flood building
3:06 let's take a look at the global action
3:08 veeam underscore base rates
3:10 it shows how the xcache data table is
3:12 filtered to return the relevant value
3:14 the parameter inland flood building
3:17 replaces the placeholder currently
3:19 highlighted and determines the sheet in
3:21 the x-cache table that will be queried
3:24 there are three filter parameters that
3:26 are dynamically injected at execution
3:29 region
3:30 segment which is determined by the logic
3:33 in the sheet previously shown
3:35 and the single family home indicator
3:38 the xcache table that will be looked up
3:40 at runtime is the fema underscore base
3:42 rates
3:43 if the region
3:45 equals south carolina and segment equals
3:47 1 and single family home equals yes then
3:51 the value returned to the decision table
3:53 would be 2.255
3:57 this can be cross-checked with the
3:59 expected value as shown in the example 1
4:02 reference sheet
4:04 many of the rating table values do not
4:06 correspond exactly to the attributes of
4:08 a property
4:09 for example when determining the rating
4:11 based on a property's distance to river
4:13 value there are many potential values
4:15 that are not defined in the rating table
4:18 let's take the inputs for example 1
4:20 where the distance to river value is
4:22 111.2 meters
4:25 when looking up this value in the rating
4:27 table 111.2
4:29 falls between 100 and 125 on rows 14 and
4:33 15.
4:34 the guidance from fema states that in
4:36 this situation factors are linearly
4:39 interpolated between breakpoints north
4:41 52 provides an interpolation function
4:43 for this situation
4:46 so therefore when determining the rating
4:48 value for non-levied distance to river
4:50 inland flood building and contents
4:52 the following calculation is used
4:55 the calculation rounds the result of the
4:57 interpolation calculation to three
4:59 decimal places
5:01 the interpolation function takes five
5:03 parameters
5:04 x input x0 input x1 input y0 and input
5:09 y1 when the formula executes it
5:12 retrieves the input values from the
5:14 xcache data table
5:17 x is 111.2 the distance to river
5:20 property parameter value
5:22 input 0 is 125 the distance to river x
5:26 cache table upper value
5:28 input x 1 is 100 the distance to river x
5:32 cache table lower value input y 0 is
5:35 1.05
5:37 the inland flood x cache table upper
5:39 value and input y1 is 1.082
5:43 the inland flood x cache table lower
5:46 value
5:49 after execution the interpolation
5:51 function returns a value of 1.068
5:54 this can be cross-checked with the
5:56 expected value as shown in example 1
5:59 excel reference workbook
6:01 we've seen how some of the input details
6:04 are used by the rules engine to look up
6:06 rate tables and perform calculations and
6:08 are now ready to see the output
6:10 calculations
6:12 let's execute the full calculation using
6:14 our record in microsoft dynamics 365.
6:17 this record has all the parameters
6:18 required for the premium calculation
6:23 we can see the calculation result
6:25 matches the example provided by the fema
6:27 documentation
6:31 we have only covered a small fraction of
6:33 the rules and calculations used to
6:34 determine the premium calculation
6:37 if you'd like to know more please review
6:39 the associated article on our support
6:41 portal and get in touch to see how we
6:43 can help you with your complex business
6:45 rules requirements on the dynamics
6:46 platform also please don't forget to
6:49 subscribe to our youtube channel