Several Reasons Excel Should Not Be Your Production-Level Actuarial System
By Stephan Mathys
Small Talk, March 2021
Microsoft Excel is widely adopted and accepted for many processes, for many reasons. It’s already on your computer when you start Windows. It seems simple and easy to use—just type a formula into a cell and you get an answer. Non-actuaries understand and build their own spreadsheets, so spreadsheets become an easy medium for transporting information into and out of the actuarial department.
And, it’s free! For smaller insurance companies (and their actuaries), which might feel resource-constrained, the lure of free can be a siren song tempting you to do everything possible within Excel. Unfortunately, that temptation may be leading you into dangerous waters where you’d better not travel if you want to accelerate your actuarial department to the next level.
This is because Excel also has many limitations making it inferior to most task-specific options. When actuaries put an Excel model into “production” (i.e., they make it the official tool for some output), they are often giving up actuarial-specific functionality they could leverage for better results. Examples might include industry-validated reserve forecasts for insurance policies, minimum contribution requirements for pension plans, or built-in optimization procedures.
As a result, using Excel for production-level work introduces risks and inefficiencies into the process. Yet precisely because Excel is so widely used, its flaws and the consequent risks sometimes become invisible.
In order to spark a debate within small insurance companies and their actuarial departments, I offer the following list of limitations of Excel in production. Perhaps this may provide the justification actuaries need to convince decision-makers on the strategic planning committee that it is time to invest in additional systems to address these problems. A subsequent article will offer spreadsheet design and maintenance suggestions for times when those investments aren’t so easy to obtain.
1. Excel is a Blank Slate
Users start with an open workbook or an existing spreadsheet that they’re adapting for a new purpose. Either way, the end-user often has to design everything, including how to get data into the model, how to store information, how to structure calculations, and how to present results for effective consumption. Are actuaries good data pipeline strategists? Are actuaries top-tier end-user experience designers? Often, no, and no. There are others who perform these tasks much better.
Yet since actuaries start with a blank workbook for just about everything, they often have to take on those tasks they're not qualified for and don’t do well at, just to get to a preliminary answer.
Doing it all yourself may seem like a standard actuarial mindset. Unfortunately, it too often results in a convoluted, cumbersome and confusing end product.
2. Excel Has Data and Size Limitations
There have been a number of costly mistakes made by users of poorly-governed or poorly-maintained Excel models.[1] In the fall of 2020, during the COVID-19 pandemic, the U.K. announced that it had missed out on tracing contacts of approximately 48,000 people due to an error in their spreadsheet which cut off a number of records.
This happened because Excel has fixed limits for the number of rows and columns that workbooks may use. Of course, newer versions of Excel allow for more, yet the limits still exist. Users must design their tools around those limits. If they don’t adequately account for them, they introduce a significant risk of model error.
3. Excel Wasn’t Designed for Effective Visual Data Displays
Excel is built for calculations that replicate row and column accounting. That structure is very appropriate for regular columns aligned according to a fixed number of decimal places. However, it is inefficient for creating compelling data-based presentation.
As the world is learning, compelling data-driven stories get results. Thus, the growing popularity of business intelligence and visualization tools like Qlikview, Tableau, Power BI, and even R.[2] It’s becoming more and more evident that Excel just is not capable of providing adequate data visualization functionality.
Should actuaries continue to use Excel as their primary tool, they will be less effective in their communications about the impacts of their analysis, less influential when it comes to strategic decisions, and less satisfied with the end results.
4. Processing Power is Limited
Since Excel is installed on your local machine, its power is limited. You may have multiple CPUs installed, yet even then you’re still limited to what you can access at the moment. Kicking off complex runs with large data sets may lock up your laptop, rendering it, and you, useless.
Even a local grid doesn’t truly allow for easy scalability due to resource inefficiency. If you know you will eventually need five times the amount of processing power you now need, what are your options? You could request new tech over and over which is a hassle and increases the likelihood of decision friction. Or you could buy much more than you need right now leaving you with unused capacity until you grow into the needs as anticipated.
In contrast to modern actuarial software, which often has a cloud backup component, your local Excel model is always going to be just that—local. If there are problems with data storage on your company’s server, you’re going to be affected until that gets sorted out. Cloud-based systems have automatic redundancies and what’s called pour-over, so that if there is some kind of problem, end-user experience isn’t affected. In fact, they practically never know it even happened.
As Milliman stated in a 2019 white paper[3] cloud computing is the way of the future. Running Excel models in production on local machines is no longer “leading-edge,” if it ever was.
5. Version Control is Nonexistent
Generally, the way actuaries manage versions of production Excel models is to simply have a different name for the file. Or to store it in a different network folder, perhaps organized by the calendar year, quarter and month.
How many times has there been an Excel file with a name like “Quarterly_Model_final_v3.xls”? Begging the question, If it’s final, why is there a version 3? Is there a version 4? What is the official version? This is cumbersome and inefficient in practice and becomes confusing at the time of review.
While there are better ways to create, track and manage the different versions of work in Excel, those better ways are not inherent in traditional Excel spreadsheet setups.
6. Formula Copy/Paste Practices Can Lead to Instabilities or Insecurities
One thing that most actuaries do (and I’m as guilty as anyone) is to create a formula in a cell and then copy that formula and paste it again multiple times, down a row or across a column.
In general, I’m trusting that I have done an adequate job of replicating my first instance over and over and over again. And that the model is adequately maintained since I first executed that copy/paste exercise. But how would I know that no disgruntled intern never went down to row 3,532 and nefariously added a “+3” into the formula? Short of clicking in to each and every cell, that is, I might never find mistakes or subversions of this type if they exist.
I am simply trusting that what I’ve seen once or twice is reliably replicated throughout the spreadsheet thousands or tens of thousands of times. Yes, I could take the time to click into each and every cell and validate that the formulas are what I suspect they are. But who has that kind of time or patience?
7. Excel Itself Has Versions! Which May Lead to Problems or Delays
About every three years there is a new version of Microsoft Excel. While this often brings new functionality, end users may get stuck with N-year old software (where N>3) because it can take a long time to upgrade corporate machines to the newest version of Windows.
In fact, the cause of the U.K.’s missing COVID-19 cases is partially due to this phenomenon. The contract tracing model used an older version of Excel (.xls format), rather than the newest version (.xlsx format). That meant the historical size limitation (#2 above) cut off data at the bottom of a table.
As a result of this version friction, actuaries are often behind the curve of where they could be, and those upgrade processes (while often promised to be as smooth as silk), can have implications in many areas. It takes time to upgrade to a new version, because there’s often vetting and back-validation to ensure results haven’t changed.
8. Undocumented or Unknown External References to Internal Components Can Lead to Data Pipeline Issues
One feature that many actuaries like is the ability to link directly from one sheet or workbook to another sheet or workbook. I do not have a problem with this in theory, as long as the link is to an appropriate location that is stable and documented.
However, what often happens is a link from a specific cell on a specific worksheet, to a specific cell on another specific worksheet.
For example, suppose the value of interest is in the [Expense] sheet, cell $D$17, because that cell is the sum of a few rows and columns above and to the left of it.
Unfortunately, cell $D$17 itself has no knowledge that there is a downstream link to it in another workbook. Should the actuary (or someone else) make a change to the [Expense] sheet, those downstream links will be broken. Perhaps he had to insert an additional column to account for a new product line, and the total is now in cell $E$17. It might look fine to the primary user in the moment, but the chain of data integrity has been broken.
This might not even show up at the time that the user is working in the file, and may only become evident when the downstream file is updated and now produces nonsense results. Or, more dangerously, produces still sensible, but wrong, results.
The fear of making changes like this can lead to paralysis on the part of users, so that inertia about the spreadsheets themselves sets in. Which can lead to additional work-arounds, sub-worksheets, and so on.
Conclusion
The concerns listed above are just a few challenges that actuaries deal with when using Excel. It’s no guarantee that all of these ideas will go away with better practices such as employing add-on spreadsheet governance tools or even commercial actuarial software. However, since commercial vendors often have these limitations in mind during the design phase, it’s much more likely that they will than that they won’t.
The next article will supply suggestions for modeling techniques and spreadsheet hygiene practices which can improve your processes and reduce risks even when still limited to using Excel.
Statements of fact and opinions expressed herein are those of the individual authors and are not necessarily those of the Society of Actuaries, the editors, or the respective authors’ employers.
Stephan Mathys, FSA, is an independent actuarial consultant who works to improve models, processes, and communications. He can be reached at mathys.stephan.j@gmail.com.
Endnotes
[1] https://slate.com/technology/2020/10/u-k-covid-19-spike-caused-by-microsoft-excel-error.html
[2] https://www.forbes.com/sites/bernardmarr/2017/07/20/the-7-best-data-visualization-tools-in-2017/?sh=39b22376c30e
[3] https://www.soa.org/globalassets/assets/files/resources/research-report/2019/cloud-computing.pdf