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:

• Spreadsheet Tips (1): More Flexible MOP Template
• Spreadsheet Tips (2): How to Structure Your Spreadsheet?
• Spreadsheet Tips (3): How to Setup a Spreadsheet Template? (This article)