Prophet Modelling Technique (4): SPCODE


In several discussions with Prophet users, the topic of sub-product codes, commonly known as SPCODE, often comes up. I thought it would be helpful to share some of my views on how SPCODE should be used—especially in terms of maintaining model flexibility and supporting various actuarial analyses.


Understanding the Role of SPCODE

When Prophet performs calculations, it can produce results either on a total basis—which is labeled as SPCODE = 0—or on a grouping basis, where model points are grouped by SPCODE. Grouping by SPCODE is by far the most common approach. There is another grouping method, such as grouping by maturity year, but that’s rarely used these days, particularly in jurisdictions where regulators don’t require results to be broken down that way. (You can also use "Flexible Results" functionality for result grouping, but it is subject to additional subscription fee)

Here’s a quick illustration of how SPCODE works in practice:

  • Suppose you have three SPCODEs in your model point file: 1, 11, and 21.
  • Each SPCODE contains 1,000 model points.

When Prophet runs, it will generate four sets of results—one for each SPCODE, and one combined result where SPCODE = 0. For any variable defined as a cumulative variable, Prophet will automatically sum the values across all model points with the same SPCODE. So, when you extract results for SPCODE = 1, you’ll get the total results for those 1,000 model points.

Also, I summarize some SPCODE basics you should know:

  • SPCODE is a mandatory field in all model point files and must be placed in the first column. 
     
  • You do not need to create a variable to read SPCODE—it is automatically read by Prophet.
     
  • Any positive integer up to 9999 can be used. For model point files used for in force run (i.e., not new business processing), the SPCODE less than or equal to the “Last Sub-product Code for Existing Business” specified in the Run Structure.


My Recommendation on How to Use SPCODE

In all Prophet models I’ve developed, I emphasize one principle to the users: "SPCODE should be used purely as a grouping criterion, and NOT as a specific driver for reporting."

This is especially important when working with frameworks like IFRS17. I do not recommend using SPCODE to manage groupings such as ICG (Insurance Contract Groups). Once we assign calculation-based meanings to SPCODEs, we limit its flexibility and reduce our ability to use it for other important analysis.

Here’s why this matters:

  • Let’s say you later want to analyze profitability by distribution channel. 
     
  • If SPCODEs have already been hardcoded to represent something else, you lose the ability to use them to group policies by channel. 
     
  • Similarly, if you need to run model testing and want to trace the impact of changes by assigning different SPCODEs to test groups, you’ll face unnecessary constraints.


Don’t Lose Flexibility

It’s important to remember that our Prophet models aren’t built for just one purpose, such as IFRS17 valuation. These models support a range of actuarial analyses—from profitability studies and sensitivity tests to stress scenario reporting.

That’s why it’s essential to preserve the flexibility of SPCODE as a grouping mechanism—one that can be easily re-assigned based on the analysis you’re running, without being tied to a specific set of rules or business logic.


Related Posts:

  1. Prophet Modelling Technique (1): Enumerations
  2. Prophet Modelling Technique (2): New Business Processing
  3. Prophet Modelling Technique (3): Calculation Looping
  4. Prophet Modelling Technique (4): SPCODE

Comments

Post a Comment

Popular posts from this blog

Other Ways to Prepare Prophet Model Point Files? Try FoxPro!

Image
If you are a frequent Prophet user, you must be definitely familiar with "Model Point Files" ("MPF"), i.e. the policy data that you compile in a specific format that Prophet can recognize as inputs for Prophet runs. If my guess is right, most probably you use Data Conversion System ("DCS") to convert the source data you download from your policy administration system (may be in various format, e.g. fixed ASCII / fixed width, comma delimited, tab delimited, etc.) into the required MPF, based on the definitions you specified in your DCS programs. In case your source data are divided into various files, you may need to carry out more steps to prepare MPF, for example: Open different source files in Excel Combine required fields from different source files by using commands (such as) VLOOKUP, SUMIF & MATCH. You may have automated this process by using VBA. Convert the required files into text files Use DCS to convert those text files into MPF  How...
Read more

How do You Setup Indicators in Your Prophet Model?

Image
In my previous post, I have discussed areas that you may consider in setting up input variables . Before I proceed my discussion on core variables, I would think that it is good for use to take a look on indicators - as indicators play an important role in setting core variables with various formulas (known as "variable definitions" in a Prophet model).  The funny thing about indicators is: it DOES NOT carry any formulas that you need to use in calculating a variable! Basically, they are just some texts that you can combine using different logical operators (AND, OR, NOT), such as "ORD_PAR AND SING_PREM" for a single premium ordinary participating product. 
Read more

Master Products in Prophet Workspace - Many or Few?

When I worked on Prophet projects that involved setting up Prophet model from scratch or streamlining existing Prophet models, one of the common requests I came across was relating to no. of master products. Many Prophet users have such a perception, i.e. the lesser no. of master products in a Prophet workspace, the better it is. Let's think about it, is this perception correct? When we are talking about of having a better Prophet model, what does the word "better" mean? Define a BETTER Prophet Model It is important for us to define what we expect from a "better" Prophet model, instead of focusing on the no. of master products in the model. Let me share some views if I am a Prophet user:
Read more