Spreadsheet Tips (1): More Flexible MOP Template

In case you are looking for some Excel / Spreadsheet tips on how to make your templates more efficient, perhaps you can get some ideas from the tips I share below. Afraid of bombastic command or VB coding? Don't worry, the tips I share below are very simple and you don't need to be an expert to use the tips.

The following tips use examples from the template I used to compile movement of policy (MOP) statistics (i.e. new business, in force business & termination/alteration) . However, you may apply the same ideas for other types of templates as well.

Tips (1): Produce results by plan code level, not product group
Normally, the MOP statistics are reported in specific product groups, such as "Whole Life", "Endowment", "Temporary" etc. in BNM Form L6-L8 (Conventional Insurance) / FT5-FT7 (Takaful). It is common for us to group our data by the required product groups using data processing applications (e.g. FoxPro, Data Conversion System (DCS)) before we transfer the results to the spreadsheet template.

Well, try to think a few conditions you may come across - Your superior finds out there is a mistake in classifying few product codes into incorrect product codes, and he needs you to update the last year production and current year production based on the correct product group. OMG, you need to update the FoxPro / DCS tables and rerun the programs to product revised new business summaries - to make matter worse, the last year new business data may be already gone due to hard disk crash (because you forgot to backup the data)...

Figure 1: Data Sheet
You will not face such problem if you produce the new business results by product code / plan code. Instead of doing grouping at FoxPro / DCS level,  you only do the product grouping at spreadsheet template level. As shown in Figure 1, I use column G (labeled as "Index") to do product grouping. For example, for "Ind_Trad_EndEdu_Reg" :
  • Refers to "Individual Products + Traditional Business + Endowment + For Education + Regular Premium".
      
  • I use SUMIF to sum up the no. of policies sum assured and annualized premiums for the product group, i.e. "=SUMIF('data_NewBiz (Ind)'!$G:$G,"Ind_Trad_EndEdu_Reg",'data_NewBiz (Ind)'!$C:$C)".


Figure 2: Reference Table
It is not difficult to set up the product group "code". In Figure 2, you can easily find out that the code is basically just the combination of several fields, and teh underscore ("_") is used separate the fields (you can omit "_" if you want your "code" to be shorter. You can use "&" to combine the values from different fields, instead of CONCATENATE, such as "=IF(ISBLANK(B6),"",B6&"_"&C6&"_"&D6&"_"&E6)" used in my template.

Now you can produce the revised report easily - change the product group in the current year and last year working file, and your revised report is done!

Tips (2): Too many worksheets in your spreadsheets? How to display the selected group of worksheets?
It is common for actuarial people to have spreadsheet template with a lot of worksheets. For example, the MOP template for Figure 1 & 2 contains 25 worksheets. Sometimes it may be a bit troublesome to go to the specific worksheets I would like to check.

Figure 3: Buttons used to hide unwanted worksheets
To overcome this problem, I use VBA to hide the unwanted worksheets. As shown in Figure 3, I used 4 buttons to hide the worksheets that I do not want to see. As the 9 data sheets are only referred when needed, I create an additional control to display/hide the data sheets. When I click "Display Monthly Sheets" button (without data sheets), only 6 worksheets are displayed and it makes my checking easier! 

Below is an example of the VBA coding I used in my template:

Sub DisplayMthlySheets()
    Application.ScreenUpdating = False
    
    For i = 1 To Sheets.Count
        If Sheets(i).Name = "input" Or _
           Sheets(i).Name = "FT5_mth" Or _
           Sheets(i).Name = "FT6_mth (ProdClass)" Or _
           Sheets(i).Name = "FT6_mth (Cause)" Or _
           Sheets(i).Name = "FT7_Close" Or _
           Sheets(i).Name = "Recon (Mth)" Then
            Sheets(i).Visible = True
        Else
            Sheets(i).Visible = False
        End If
   Next i
    
    Call DisplayDataSheets
    
    Application.ScreenUpdating = True    
End Sub

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 Should I Do My UAT? (1): Program / Spreadsheet / Model

Image
If you are working in the actuarial department of an insurance company, it may not be uncommon to you involving in various testing exercises, which are known as User Acceptance Testing ("UAT") in general. Some of us may think that UAT is only applicable to the policy administration system implementation project (Oops... Do I remind on the non-stop follow-ups from your Project Manager?), but in fact UAT is also needed for the programs, spreadsheet templates or actuarial model we use to perform various actuarial studies - whether they are developed internally or by external consultant. It is particularly important for those tools which we are going to use to perform regular studies. In my view, there is no one right answer for the approach to be used in performing UAT on program / spreadsheet / model - most importantly, the approach that you adopt should allow the required testing to be carried out in a structured manner, and it should not create too much "processes"
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