Prepare Rating Information

Learning Objectives

After completing this unit, you’ll be able to:

• Describe the common rating components.
  
• Prepare data tables for import into decision matrices.
  
• Analyze pricing information for use in a rating procedure.
  
• Prepare formulas for use in expression sets.
  

Define Common Rating Components

To successfully implement the rating procedure for Cumulus’s new business insurance product, Solution Architect Justus must first gather all the existing pricing information. Cumulus stores this information in various formats—spreadsheets, computer files, product manuals–so it takes some work to assemble.

Whatever the source is, look for these two common components.

• Rating Tables: These are lookup data tables that become decision matrices.
  
• Formulas: These are calculations that go into one or more expression sets.
  

The original state of rating information varies significantly. Some require significant analysis and cleanup, while others need only slight reformatting.

To prepare your information:

• Break it into steps with rating table lookups and formulas.
  
• Prepare a list of the input variables and output variables, their data types, such as numbers, or text, and any constants used.
  
• Determine if you need more than one expression set.
  

Deconstruction

What if you have no idea where to start? Here's a suggestion—use deconstruction. Investigate how the formulas relate to product modeling and result. Start at the end with total premiums, and deconstruct that. Break it into its inputs, lookup tables, and formulas. Repeat the process and continue until you arrive at the simplest calculation.

Variables and Constants

Variable names appear in multiple places, such as matrices, expression sets, and product attribute rating inputs. Review the names of variables and constants and change them, if necessary. Plus, keep the names short and descriptive, and follow a consistent convention.

Record the source of each variable’s value. Is it a constant, user input, or output from another step? Take note if the same variable appears more than once, especially if it’s output in one step and input in another.

Now, learn about preparing tables and formulas.

Format Rating Tables

Prepare your rating tables first because outputs from the tables are often inputs to formulas. For tables, the goal is to format a CSV file for import into a decision matrix. Here's a basic process.

1. Analyze the data.
   
2. Reformat the table as needed.
   
3. Save the table as a CSV file.
   

Start by examining the values in each cell. What do they represent and how do values in a row relate to each other? Identify the purpose of each column in your own words. Explain the cause-and-effect relationship it represents. Determine which variables are inputs and which are outputs. If necessary, reorganize the table to ensure it aligns with decision table requirements. Finally, save the revised table as a CSV file, ready for import in the Digital Insurance Platform.

While humans can easily interpret pricing information in various formats, a decision matrix demands a structured approach for accurate calculations. Here's the specific format required.

• Each variable gets its own column, and the variable name is the column header.
  
• Each column has values of a single data type, such as number, number range, or text.
  
• The table requires at least one input column and one output column.
  
• Each row must have a unique combination of input values.
  

Rearrange columns and values to meet these requirements. Make sure value data types match expected user inputs and product model attribute values. For each column header, record its data type and if it’s the input or output. Have this information available for each column when you import the CSV file.

Analyze Pricing Information

Review this liability base rating table and identify the input variables and output variables and how they influence each other. How does the format of rows and columns change?

Now that you’ve figured out the answers, it’s time for a basic analysis. Click each question to learn more.

Always ensure proper formatting so the data is clear for pricing. Here’s the liability base rate table formatted with the appropriate headers and values.

With the table properly formatted, the data is now ready for import.

Prepare Formulas

After preparing the tables for the rating procedure, Justus turns his attention to the formulas. He determines the calculations involved and their relationships and renames variable names for clarity. Finally, he restructures each formula as a mathematical expression, placing the input formula on the left and the output variable on the right.

Prepare the Steps

When preparing the formula, always have:

• A list of variables with data types
  
• Each formula as a mathematical expression
  
• A sequence of table lookups and formulas
  
• Steps divided into more than one expression set, if necessary
  

Identify any dependencies and prepare the steps in their execution order. For each step, consider the source of its inputs. Is it user input or a constant value, or perhaps it requires output from another step?

To ensure a smooth flow of calculations, consider these guidelines when planning your steps.

• All inputs must exist before a step needs them. If a variable is both an output and an input, then the step that outputs the value must be specified before the step that uses that value as input.
  
• As much as possible, put steps for matrix lookups first. It’s useful when creating the expression set because matrices add their variables automatically. You must enter other variables manually before you enter the formulas.
  
• In your planned steps, include aggregations such as averages or sums of values across an array of inputs.
  
• Mark which steps output values for aggregation, and which steps output values for the results of the rating procedure.
  

Aggregation formulas are particularly important when determining how many expression sets you need and whether you need an Integration Procedure. Aggregations are calculated last. So, if an aggregation output is the input to a calculation step, then that aggregation is the end of an expression set. The calculation step using the aggregation output goes into a second expression set.

When building an expression set that aggregates an average value from various inputs, use the average to calculate another value with an Integration Procedure. Also, if there’s a need to access and transform data from third-party sources and move the workload from client to server, you can use an Integration Procedure.

In this unit, you learned about the fundamentals of preparing your existing pricing data for use in rating procedures. You now understand how to identify rating tables and formulas, analyze their structure, and reformat them for import into decision matrices. Additionally, you’ve practiced the deconstruction technique for breaking down complex pricing logic.

With your data properly formatted, you’re ready to create the foundation of your rating procedures: decision matrices. On to the next unit!

Resources

• Salesforce Help: Create Rating Procedures for Insurance Products