By Dave Kester
Since starting my actuarial career in 1986 (yes, calculators were invented), I have created hundreds of actuarial spreadsheets. Likewise, I have seen hundreds of spreadsheets created by other actuaries. And, yes, I have “fixed” hundreds of spreadsheets. So, I thought it would be fun to pause and identify the top six
dangers of actuarial spreadsheets. I don’t claim this is the ultimate list. Just the list based on my experiences.
MY TOP SIX DANGERS LIST
6. DOCUMENTATION
Good documentation (and even bad) takes work. It is not fun work. It is not work that motivated us to pass exams. But, spreadsheets have good tools to create essential documentation if we take the time to use them. Really good documentation takes extra effort. The extra effort required is to envision the questions asked by the next person using the system. And the person after that. And the person after that.
The challenge is projecting what their mindset will be when they review the spreadsheet. What will be their actuarial or business knowledge level? How strong is their understanding of spreadsheet capabilities? What is the learning curve to correctly operate and maintain the spreadsheet? Are there any risky formulas. For example, monthly projections for 100 years produces 1200 rows of formulas. Are the formulas the same for all 1,200 rows? If not, can they be modified to be consistent? If they cannot be consistent, is the difference well documented? For starters, use different font colors and add comments.
5. POOR DESIGN
The first design question is: how clear has the input, calculation, and output been identified? The open nature of spreadsheets does not require separating these three key components. So, the designer is responsible to determine how much, or how little, these three key components are organized. Minimize, or better yet, eliminate hardcoded values embedded within formulas. It is better to create separate cells for these parameters so they can be clearly identified.
Even better, define the parameters as a named range. That way, the formula A1*(1+B1) is replaced by BeginningValue *(1+GuaranteedInterestRate).
4. CONTROLS (or lack thereof)
One foundational question to actuarial “systems” is: How do we know results are accurate? An even better question is how do we know results remain accurate after months, quarters, and years of use with new data being entered and new users using the spreadsheet? Assuming the spreadsheet was designed without error originally (a major assumption, by the way), what controls are established to ensure the spreadsheet remains bug free?
Auditors have reviewed spreadsheets. The verdict is clear. Spreadsheet design is fundamentally flawed for a controlled production environment. Any attempt to add controls to a spreadsheet is a “duct tape” fix. I’m not saying there is no value in adding controls to a spreadsheet. But, I do not think that the “controls” are a true fix.
Spreadsheets are great tools for simple calculations, prototype ideas, and testing. But, they are not designed for production environments.
3. BLINDLY ACCEPTING RESULTS
To be fair, this is not an issue specific to spreadsheets. Rather, it is a risk for actuaries overconfident with the validity of “systems.” If a system is well designed and communicated, it is easy to assume the calculations are accurate and complete. But, there are so many moving parts and opportunities for miscommunication.
For example, are the input items clearly communicated? A system could be perfectly bug free, but if the user does not understand the input and makes an input error, then we end up with “garbage in, garbage out.”
Good systems have edits to catch these issues. One example of a common input mistake is entering an interest rate as a percent when the spreadsheet expects a decimal format input. Robust systems identify these issues and notify the user.
Clear definitions are critical. Does age 35 represent the beginning of age 35 or the end of age 35? Does the first year represent the beginning of the first year or ending of the first year?
Users must develop expectations for reasonable results. Thus, if a significant input error exists, it must be identified when results are outside of expected results. Even though this danger is not specific to spreadsheets, I think spreadsheets are more “at risk.” Here’s why:
Because spreadsheets are so easy to create, any actuary can create them. But, many actuaries are not trained in system design and edits. However, more rigorous systems that require advanced skills to create are also more likely to be designed by competent developers. Competent developers are better trained to create meaningful edits that minimize data entry errors.
2. COMPLEXITY
A picture is worth a thousand words. To create a picture of a spreadsheet’s complexity try this experiment. Open the spreadsheet. Try to identify the final output or result from the spreadsheet. That is the first test. How easy is it to identify?
OK. Once the final output is identified, click on the “Trace Precedents” button in the formula ribbon:
Notice the cells dependent on the final result.
Cool. It appears only one cell depends on the final result.
OK. Click again. Clicking the button a second time identifies the precedents on the 1st level of precedents.
Keep pressing the “Precedent” button until it beeps. Once it beeps, all precedents on that sheet have been identified. Now, review the picture.
Describe the complexity as a result of the picture. Remember, this is one sheet within one spreadsheet. Your mileage may vary. But, I bet you have some spreadsheets that create a lot of pretty blue arrows!
I will not even mention formulas that link to cells in external spreadsheets. Yikes! There is no picture to describe that risk and complexity.
1. STAFF TURNOVER
Let’s face it. Our work lives for the long term. But, we often have short-term “vision.” Sometimes it is difficult to see the long-term consequence of spreadsheets created today. Sure, technology changes. Will the spreadsheet be easily upgradeable as new versions of Excel are created?
But, newer versions of Excel are a minor long-term concern. The greatest long-term concern is staffing risk. Staffing risk occurs when new staff replace the current spreadsheet author. One actuary’s spreadsheet masterpiece is another actuary’s spreadsheet nightmare.
There’s my list. How does this compare to your list? Please contact me if you have dangers not listed.
AVOIDING THE DANGERS
Textbooks, blogs, and forums could discuss avoiding spreadsheet dangers and not cover the risk adequately. But, for starters, focus on these strategies.
1. Review my dangers list (or your own) and take that natural precautionary action. If the danger is poor documentation, improve the documentation. If the danger is user input, create a process to minimize the user input risk.
2. The primary strategy is to use spreadsheets for their intended purposes. Before creating a spreadsheet, evaluate to determine if a spreadsheet is the best tool choice. If in doubt, consider other options such as a database or third party tools.
Actuarial spreadsheets: we love them, we hate them. Identifying the dangers and minimizing the risks shifts the pendulum closer to the love response.
David Kester, FSA, MAAA, is the co-founder and president of SALT Solutions, an actuarial consulting company in Des Moines, Iowa, and CoachingActuaries.com, a site that provides online practice tools for students preparing for actuarial exams. Dave can be contacted at dave@saltsolutions.com.