12.9. Lookup Tables
SICS makes it possible to work with lookup tables that define premium, limits, deductions or any financial measure that can be defined in a table. The tables can be defined outside of SICS and imported into SICS, or they can be defined and edited within SICS.
SICS imports lookup tables using a CSV (comma separated variables) format with column headings in the first row. This format of file can easily be created from most popular PC packages such as Microsoft Excel, Lotus 123 or SAS.
In SICS Life the last column of the table must be the numeric measure that is being defined in the table. In P&C the last two columns are the numeric measures that are big defined in the table.
Lookup Table Concepts in SICS Life #
Lookup tables in SICS are numbers held in a multidimensional non-numeric structure. For example, let’s take a simple case of a rate table where the premium rate varies by Gender and Smoker Status. This is a simple 2 dimensional structure and may be represented easily in a table like this:
| Smoker | Non-Smoker | |
|---|---|---|
| Male | 30 | 25 |
| Female | 27 | 22 |
If we add a dimension to our structure by additionally varying the rate by a limited Age band (20-24 in 1 year steps), we find it is much harder to represent that data. One way would be to have 5 tables like the one above for each age in the structure, or we could shift the perspective and have one table for males and one table for females thus:
| MALE | Smoker | Non-Smoker |
|---|---|---|
| 20 | 30 | 25 |
| 21 | 30 | 25 |
| 22 | 31 | 26 |
| 23 | 31 | 26 |
| 24 | 32 | 27 |
| FEMALE | Smoker | Non-Smoker |
|---|---|---|
| 20 | 27 | 22 |
| 21 | 27 | 22 |
| 22 | 28 | 23 |
| 23 | 28 | 23 |
| 24 | 29 | 24 |
As you add dimensions you can see that representing this data becomes more and more difficult and unwieldy. SICS allows you to hold this kind of data structure and view it from any perspective, but in order to import such a table, SICS requires that the data be presented to it in a linear fashion; a CSV file.
Within Excel, a table structure such as the male/female example shown earlier would be held thus:
| Age | Gender | Smoker Status | Premium Rate |
|---|---|---|---|
| 20 | Male | Non-Smoker | 25 |
| 20 | Male | Smoker | 30 |
| 20 | Female | Non-Smoker | 22 |
| 20 | Female | Smoker | 27 |
| 21 | Male | Non-Smoker | 25 |
| 21 | Male | Smoker | 30 |
| 21 | Female | Non-Smoker | 22 |
| 21 | Female | Smoker | 27 |
| 22 | Male | Non-Smoker | 26 |
| 22 | Male | Smoker | 31 |
| 22 | Female | Non-Smoker | 23 |
| 22 | Female | Smoker | 28 |
| etc… |
As you can see, you will have a row for each non-numeric column value (age, gender, smoker status) with a corresponding rate value. The order of the columns is not important, except for the last column which must be the numeric measure, in this case the rate. You may order the other columns whichever way make it easier for you to look at the data.
It is important for SICS that there are no “holes” in your data set. A data set that only has entries for Female Smokers and for Male Non-Smokers, would be invalid, as this would have “holes” within the multidimensional structure. Or, put more simply, you have to have a rate value for every combination of qualifier, even if that value is zero.
You should now save your data in the CSV format with row headings in the first row. In Excel, you can do this using the Save As option and selecting CSV (comma delimited) for the save as type.
Most other packages are capable of saving or generating a CSV format file.
Once you have your data saved in a CSV format in this layout, it is ready for SICS to import.
Lookup Table Concepts in P&C #
There are three Table Types available in P&C:
- EPID (Estimated Premium Income Distrubution)
- ESPID (Estimated Subject Premium Income Distribution)
- SLD (Stop Loss Distribution)
These tables can be attached to the Limit and Premium Condition of an OCC.
The EPID and SLD tables are informational only, and can be attached a Proportional OCC or Non-Proportional OCC of Type Stop Loss, respectively.
The ESPID Table can be attached to a Non-Proportional OCC of type other than Stop Loss, and is the basis for the automatic generation of Accounting Classifications.
See Handle Limit and Premium Conditions in Handle Agreement Terms and Conditions chapter for more details regarding the attachment of Lookup Tables.
All tables have a single dimensional structure, where the last two columns are the numeric measure being defined in the table. These numeric measures, being referred to as the Ratio Basis, can have the following values:
- Fixed Monetary Amount (FA)
- Percent (PC)
This is how a typical ESPID Table can look like:
| Country | MCOB | COB | SCOB | SUPI1 | SUPI2 |
|---|---|---|---|---|---|
| NOR | PROPERTY | ENG | MACHINERY | 10000 | 40000 |
| NOR | PROPERTY | FIRE | FIRECOM | 20000 | 30000 |
| NOR | MARINE | MARINEC | MCGOODS | 30000 | 20000 |
| NOR | MARINE | MARINL | MLHULL | 40000 | 10000 |
| SWE | PROPERTY | ENG | MACHINERY | 10000 | 40000 |
| SWE | PROPERTY | FIRE | FIRECOM | 20000 | 30000 |
| SWE | MARINE | MARINEC | MCGOODS | 30000 | 20000 |
| SWE | MARINE | MARINEL | MLHULL | 40000 | 10000 |
You can now save the data in the CSV format with row headings in the first row. Once you have your data saved in a CSV format in this layout, it is ready for SICS to import.
Lookup Table Concept in Cede #
There is an option to create a lookup table in Cede:
- Premium Cede Lookup Table
This table can be attached to the Limit and Premium Condition of a proportional OCC, and is the basis for calculation of Assistance Coverage (f.ex. car assistance or travel assistance) premium.
See Handle Limit and Premium Conditions in Handle Agreement Terms and Conditions chapter for more details regarding the attachment of Lookup Tables.
The table has a single dimensional structure, with two columns to be defined: Assistance Key and Premium amount. Assistance Key is a reference field and Premium amount is a fixed monetary amount.
You can now save the data in the CSV format with row headings in the first row. Once you have your data saved in a CSV format in this layout, it is ready for SICS to import.