Spreadsheet Tips (3): How to Create a Spreadsheet Template?

"Do you agree that spreadsheets cannot be separated from actuarial life?" Yes, I totally agree without any doubt - almost all actuarial exercises that I know involve spreadsheets, especially MS Excel. I even heard a actuarial joke before teasing actuaries: "An actuary is a person who creates complicated spreadsheet".

Tips (1): Simple Rules for Good Spreadsheet Templates
Well, actually I don't quite agree with this actuarial joke. I admit that I have few spreadsheet templates that are complicated, but many spreadsheet templates I created before are actually pretty simple (despite the number sheets). Why should we create a complicated spreadsheet template if we can use a simpler way to do our work? I used to advise my ex-team members previously to follow some simple rules when creating their spreadsheet templates:
  • Avoid advanced Excel formulas if possible - you can do most of your jobs using common Excel formulas (such as SUM, SUMIF, SUMPRODUCT, COUNTIF, VLOOKUP, HLOOKUP, ...). Please do not use arrays if possible as they will make checking work very difficult (please don't expect anyone to go through all formulas one-by-one...).
     
  • Minimize VBA - if a calculation can be done easily by just using common Excel formulas, NO macros/VBA please.... As macros/VBA involves coding, it takes time for the users to understandand it may not be easy to modify, especially for an entry level actuarial executive. In my view, you should try to avoid macros/VBA unless your calculation require repetitive computation jobs (such as extracting Prophet results for 25 runs), or you need to automate some non-calculation tasks (such as creating various "paste value version" of reports for finance).
     
  • Use standard formatting - As I share in my previous article on spreadsheet tips ("Spreadsheet Tips (2): How to Structure Your Spreadsheet?"), by having standard formatting rules, you don't need to spend time to figure out which colors / border style / font face & size will make your spreadsheet nicer. You can save your efforts for other more "productive" work.
     
  • Organize tables systematically - normally, a spreadsheet template is formed by many tables - your spreadsheet templates will be organized if you organize the tables systematically. One of the simple ways to try to organize tables is to place your tables "from up to down" (vertically) if possible, instead of "from left to right" (horizontally) or both (i.e. "vertically" and "horizontally" at the same time). However, tables with many rows (such as mortality tables) are more suitable to be placed side by side as they are too long.
Figure 1: Organize Tables "Vertically"

Figure 2: Bad Example of Organizing Tables


Tips (2): Simple Steps in Creating Spreadsheet Templates
Creating spreadsheet templates is usually part of the exercise to setup an actuarial processing. After getting your approach paper (i.e. document that outlines the methodology of your actuarial study) ready, you can start to create your spreadsheet template:

Figure 3: Suggested Steps for Creating a Spreadsheet Template

  • Step 1: Plan Your Spreadsheet
    Think how you want your spreadsheet template to be, based on the methodology outlined in the approach paper. For example, you may need to decide whether you want to have monthly report and YTD (year-to-date) report for you new business analysis.
     
  • Step 2: Create "Report" Sheets
    After having an idea how your spreadsheet template will look like, you can start with creating the "report" sheets - i.e. those sheets you would like to print out in hardcopies or send to the target readers. At the same time, you may want to create the "input" sheet as well (which you want to place your general parameters such as reporting date) as your "report" sheets may need to use those general parameters.
     
  • Step 3: Create Intermediate Sheets
    Based on the "report" sheets you have created, you can now continue to create the other sheets which helps to translate the data ("input") to the "report" sheets ("results"). You can use some dummy data with specific trends (such as in multiple of 100) to help you to detect errors immediately along setting up those sheets.
     
  • Step 4: Test Your Spreadsheet
    Test your spreadsheet template using dummy data first, followed by the actual data - in case there is any error which you didn't detect previously.
     
  • Step 5: Finalize Your Spreadsheet
    Protect your spreadsheet template if needed. Promote your spreadsheet template to "production" (basically save your spreadsheet template in a designated folder in the actuarial server) and deploy any existing template (move the existing template to another folder which you keep all old templates; please do not delete your old template!) which is no longer needed. Lastly, finalize all necessary documentation. 
If you would like to have an additional reading, perhaps you can to take a look "Spreadsheet Issues: Pitfalls, Best Practices, and Practical Tips", by Mary Pat Campbell (an article from The Actuarial Practice Forum, February 2010)

You may refer to the following articles for more spreadsheet tips:

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

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