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