By Dave Kester
I usually find the best time to plan my next article when I am away from my desk. Today, I was planning the next article during lunch. I was trying to articulate the dangers and risk of using spreadsheets for actuarial solutions that have better options. It is a difficult concept to explain because we love our spreadsheets.
As I was thinking over past issues that I have witnessed with actuarial spreadsheets, I was mindlessly preparing my lunch. That often means heating up something good my wife prepared. Today was meatloaf. To keep everything preserved, the meatloaf was frozen. There were several days of meals in the container so I needed to cut a few (I won’t tell you how many!) slices for lunch. My mind was racing about the possibilities of the new article as I lazily used my fork to separate the frozen meatloaf slices. I must admit, it took quite a bit of work. Eventually, I got the first piece cut. My mind magically returned to the task at hand and I remembered I had a nice sharp knife in the drawer right in front of me. For my second piece, I grabbed the knife and easily sliced my second slice of meatloaf.
After heating the meatloaf, I found a spot in my office where I could enjoy lunch and think about the article. As I took my first bite, I realized that I had bent the fork while separating the meatloaf. Not only did it take a lot longer to cut the meatloaf by using the fork, but I ruined it in the process. A fork is a great tool but only when used correctly, like eating. Likewise, a knife is a great tool used for cutting. I suppose I could say it is not the best tool for eating. The unfortunate part is that forks can be used for cutting if you work with them hard and long enough. Just like spreadsheets can manipulate lots of data and perform wonderful calculations. But, in the end, you get a bent fork.
Of course, when I saw the bent fork, a smile came to my face as I realized I had found the perfect analogy to explain the dangers of using spreadsheets for projects that should be performed by other tools. These tools are designed for more difficult jobs such as cutting, while spreadsheets are designed for comfortable projects such as eating.
This analogy begs to ask the question: What type of actuarial projects are “eating” projects that can safely be created with a spreadsheet and which projects are “cutting” that should be performed with a more focused tool?
Start with some of the easier answers. What items are production processes? What calculations are reviewed by auditors and examiners? Are there key SOX or Model Audit controls that are part of the process?
An answer of yes to any of these questions means that a spreadsheet is not the tool for these processes. How about situations that may not be as obvious? One question that I like to ask is what are the risks if the creator of the spreadsheet leaves the company? Could another person safely assume the responsibility of managing the spreadsheet?
One option is to rate spreadsheets on two criteria. The first is: How important is the spreadsheet to the financial well-being of a company? The higher the importance, the more likely a different tool should be chosen.
The second criterion is: How complicated is the spreadsheet? Again, the greater the sophistication, the more risk it is not the correct tool. The real critical concerns are if the result is high importance and high complication.
The obvious question is: What other tools can be used an alternative? Unfortunately, the answer isn’t always so obvious. However, there are other options. The best option depends upon the specifics of the need. Very few generalizations can be made. However, the solution usually involves a clean separation of the data from the calculations. The data is normalized into an industry-wide database designed for security and control. The calculations are performed through queries or code or a combination of the two. Clear audit trails are created. Following these guidelines should meet the demands of auditors, examiners and internal control requirements.
What about managers? More important, what guidelines can be provided to make a smooth transition for the next staff member who inherits the responsibility after the original designer leaves? The key to meeting these objectives is to systemize the process into organized steps. It is a good idea to create a flow chart to show the beginning, the end, and the steps in between. The idea that a picture is worth a thousand words often escapes actuaries. The magic is that if the flow charts and pictures are created during the design stage, they can be a great tool to organize and plan the system. Have you ever tried to flow chart a spreadsheet? That is a challenge. It is very difficult to determine the process flow. What is the beginning and what is the end? The system is designed for everything to happen at once. Unless the author creates an organized flow by the design, the process will look like a spaghetti system.
As much as actuaries pursue objectivity, we often show our emotional side when making decisions regarding tool selection. It has been my experience that making an objective decision regarding whether a spreadsheet is the appropriate tool is complicated, because actuaries form an emotional attachment to them. It almost becomes a teddy bear we like to cuddle. However, this love relationship actuaries have with spreadsheets becomes a love/hate relationship once the creator transitions out and others transition in and become the new managers of the process.
If you are a spreadsheet fan, I can relate. I’m a recovered spreadsheet fanatic. I have made my mistakes by using them inappropriately. I think that was part of the times in the 1990s when Lotus 123, Microsoft Excel, Borland Quattro Pro and others became popular for actuaries. However, we need to learn from the downside of the long-term implications of using tools that don’t meet modern control and auditing requirements.
I recently read an interesting article from Forbes written by a non-actuary named Tim Worstall. The article is titled “Microsoft’s Excel Might Be the Most Dangerous Software on The Planet.” Tim explains the dangers and risks of spreadsheets when used for modeling financial concepts.
Change is never easy. However, as actuaries we exist to reduce risk and solve financial problems. In order to accomplish that goal, it is imperative that the tools we use to accomplish these goals are the right tools for the job.
David Kester, FSA, MAAA, is the co-founder and president of SALT Solutions, an actuarial consulting company in 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.