July 2012

ASC - Actuarial Spreadsheet Chaos

By Dave Kester

Do you have the ASC bug? You may ask, “What is ASC?” It is Actuarial Spreadsheet Chaos. Your company may never have experienced ASC. Perhaps all of your spreadsheets are perfectly aligned, completely documented, well secured, and have not exceeded the boundaries that your friendly auditors have defined for your spreadsheets. Perhaps none of your spreadsheets are performing production work. Then again, maybe your actuarial spreadsheet world isn’t this perfect. Maybe ASC has crept into your world.

I have been working with actuarial spreadsheets since 1988. Yes, way before Excel, there were darlings like Lotus 123, Quattro Pro, and Smart. I quickly fell in love with all of the capacity that spreadsheets could provide my curious actuarial mind. There didn’t seem to be a problem that couldn’t be solved with a spreadsheet. Wouldn’t you know that each year our computers would get bigger and faster and our spreadsheets would become smarter and could perform more calculations? My favorites were the macros and programming that you could perform with the spreadsheets.

I must confess that for the first part of my career, I was a major contributor to ASC. Sure, I got a lot accomplished in the short term. However, I pity the actuaries who have taken over the spreadsheets that I created after I moved on to greener pastures. That’s the challenge with our work as actuaries. The work that we perform does not reside in a vacuum. It is rarely a one-and-done event. Most of the work that we perform lives for years and gets passed on to many other actuaries.

The great thing about spreadsheets is that they are open-ended and you can create amazing solutions in a short amount of time. Unfortunately, the big downside to spreadsheets is that they are open-ended and can function as a pseudo database. So, you see that spreadsheets are double-edged swords. You love them, you hate them.

Another characteristic I have found regarding actuarial spreadsheets is that they become personal. We are very proud of the spreadsheets that we create. They are like a combination of a security blanket and a Teddy Bear. They are our security blanket because sometimes we never want to give them up. It may be time to “grow up” and put our security blanket to rest. However, we just can’t seem to do it. That is because they sometimes become a Teddy Bear to us. We have fallen in love with them. Perhaps we lay awake at night thinking about how we can add one more macro to our spreadsheet that will add another level of automation to our calculations.

So, what is wrong with our love affair with actuarial spreadsheets? The problem is that life moves on and so do we. But the business need for our spreadsheet persists, usually in a dynamic state. Thus, another actuary gets the privilege to take over your spreadsheet and even worse, modify them. One thing I have found is that no two actuaries view spreadsheets the same. What is a Teddy Bear for one is likely to be a curse for another. All the wonderful features and macros created by one become a black box and source of fear for another.

Also, our love affair with spreadsheets often interferes with logic. When it becomes logical to upgrade our spreadsheets to more robust and controlled environments, we often resist and go back to the comfort of our security blanket and Teddy Bear. When our auditors explain that our spreadsheets are a risk to the company because they are used for production work but are not compliant with modern control requirements, we will ignore and hang on for as long as we can.

As actuaries, we need to realize that times have changed. It is time for us to grow up and set aside our security blanket and Teddy Bear. The 1990s and 2000s are gone. In the 2010 decade and beyond, actuaries need to adapt to the new world. The new world consists of regulations such as Sarbanes-Oxley (SOX) and the Model Audit Rule (MAR). Actuaries no longer have the option to be able to work in a vacuum. Just getting the correct answer is not enough.

Our work now must be peer reviewed internally and externally. We need to prove that our results are reasonably accurate. Using spreadsheets for this type of production work is ignoring the reality that we live in. We need to understand that the financial crisis of 2008 could happen again if we misprice our embedded options. Financial engineering has advanced such that modern insurance products have more moving parts that, although can be modeled with a spreadsheet, would result in ASC for others to review and maintain. The principle-based world increases the number of variables and calculations we must manage.

Does this mean that we should go cold turkey and uninstall our spreadsheet application? No, certainly not. However, it is important to understand the acceptable purposes of actuarial spreadsheets in today’s environment. Spreadsheets are ideal for these types of actuarial work:

  • Review and analysis including tables, charts, and graphs;
  • Peer review – sample calculations to validate that production systems are accurate; and
  • Initial development – often used as a prototype to be given to system architects to create robust, controlled systems. Then, this prototype can be used to peer review as discussed in the previous item.

As actuaries, we focus on creating models that can solve long-term problems by making projections 10, 20, and 30+ years into the future. We need to have a similar long-term perspective on the work that we perform when we decide on when and how to use spreadsheets. Databases and other more robust tools have been created for a reason. Just because we may not be comfortable with solutions outside of spreadsheets, does not mean that we shouldn’t expand our comfort levels to provide the best professional solutions available.

David Kester, FSA, MAAA is the co-founder and president of SALT Solutions, an actuarial consulting company from Des Moines, Iowa. David’s focus is converting actuarial beasts into beauties by using technology as a friend. His latest venture is CoachingActuaries.com, a site that provides online practice tools for students preparing for actuarial exams.