Author: Clare Stone
Spreadsheets are a much-loved tool within the actuarial community. A recent survey conducted by the Institute and Faculty of Actuaries found that 98% of respondents use Microsoft Excel to perform various day-to-day tasks. But how useful are they?
In this post, we’ll consider the advantages and disadvantages of spreadsheets within the actuarial community, taking a closer look at the reasons why actuaries love them, as well as why they could be concerning. Once we’ve considered the benefits and challenges associated with spreadsheet use, we’ll consider some solutions to improve spreadsheet efficiency and minimise the risks associated with them. Let’s get started.
How can spreadsheets benefit the actuarial community?
Let’s start with the positive. It’s clear that spreadsheets provide many benefits to the actuarial community — that’s why almost 100% of actuaries use them! Here are some of the key advantages associated with their use:
They’re flexible
Spreadsheets are highly customisable as they can be programmed to complete many of the daily activities performed by actuaries. This versatility means spreadsheets can be used for modelling, performing stress tests, storing data, and deriving pricing, as well as many other activities. In particular, actuaries report spreadsheets as the preferred tool for prototyping new calculations and analysis.
They’re easy to use
Although programming spreadsheets correctly requires skill, these skills are easy to learn compared to the programming knowledge needed to customise alternative software packages.
This ease of use is a significant advantage for actuarial communities, who work in a fast-paced environment and need to respond to change quickly. It means the spreadsheets can be customised in-house, without the need to consult programmers in an iterative process that would hamper operation speed. Instead, actuaries can customise their software in response to a dynamic environment, making them the top choice for many tasks.
They’re cost-effective
Thanks, in part, to their flexibility and ease of use, spreadsheets are a cost-effective solution for actuarial companies. Using spreadsheets to perform several functions means fewer software packages are required, reducing expensive software costs for the organisation.
Additionally, the ease of customisation further reduces overheads, as the actuarial community doesn’t need to hire programmers to customise their solutions. Given software packages like Excel are widely used, they’re well supported, meaning minor technical issues can often be resolved quickly and efficiently.
But what happens when spreadsheets go wrong?
When spreadsheets work well, they’re a valuable asset to an actuarial organisation. But they’re not risk-free, and a quick Google search will reveal an abundance of spreadsheet-related horror stories.
In one spreadsheet nightmare-come-true, an actuary in South Africa was found responsible for a 40 million rand loss to a major pension fund — equivalent to around £1.9 million. The reason for this loss? A coding error in the spreadsheet they had been using.
Although this is an extreme example of spreadsheets gone wrong, it’s essential to consider the risks and challenges involved with spreadsheet use before using them. Here are some of the critical pitfalls to think about:
They lack governance
Although flexibility is one of the main benefits of using spreadsheets, this comes at a cost — a lack of governance.
Many actuarial organisations operate using multiple versions of the same spreadsheet. In some instances, actuaries may even customise their own versions of a spreadsheet for their use. How do we ensure the calculations remain accurate with many different versions in use? How do we ensure actuaries use the most up-to-date versions and not legacy versions?
These challenges have led to actuarial communities attempting to implement more controls and audits to govern spreadsheet use in their organisations. However, these attempts are often futile, given how easily spreadsheets can be customised by those using them.
They’re poorly documented and not standardised
Along with the lack of governance, spreadsheets within the actuarial industry are often poorly documented. This lack of documentation becomes a problem when the individual who developed the spreadsheet leaves the organisation, taking their expertise with them.
Additionally, spreadsheets are not standardised. There are many ways to achieve the same result, most of which involve complex formulae and coding. This lack of standardisation, especially when coupled with insufficient documentation, means spreadsheets can be expensive and time-consuming to manage.
They can introduce model risk
Model risk management (MRM) is a vital consideration for the actuarial community. Given spreadsheets are often used as the primary modelling platform by actuaries, it’s crucial to understand how they can prevent or contribute to risk. Although spreadsheets can minimise model risk when used correctly, there are several concerns from a model risk perspective.
Much of this concern stems from the lack of governance previously discussed. Because changes can be implemented quickly by the actuaries, errors can be introduced into the model without robust development and testing — compromising the outputs and increasing risk. Besides, having several versions of the same spreadsheet in use within an organisation can lead to outdated versions being used, performing calculations using old models.
They’re messy
What happens when the actuary who customised the spreadsheet leaves the organisation and passes it onto their replacement? While the spreadsheet may feel intuitive for one person, it could be nonsensical to somebody else — especially in the absence of documentation or centralised spreadsheets.
The same applies to interpreting data from spreadsheets. If you’re not the person who’s developed it, the outputs can be hard to understand. Again, this all comes back to the same thing — while flexibility can be a huge advantage, it can also be a major disadvantage.
They’re prone to errors
The fact that spreadsheets are prone to errors is well-established. Research conducted by Ray Panko found that…
“on average, 86% of spreadsheets contain significant errors”.
This is hugely problematic for the actuarial community, which depends on the accuracy of data used to perform calculations and underpin modelling processes. These errors can be costly, as evidenced by the pension fund losses discussed earlier in the article. But what’s the solution?
The Solution: How can actuarial organisations use spreadsheets efficiently?
Despite these challenges, when used correctly, spreadsheets are an invaluable tool for the actuarial community. Here are some best practices to consider for your organisation:
Consider whether spreadsheets are the best fit for the task at hand
While spreadsheets are an effective tool for many actuarial functions, they’re not the one-size-fits-all solution many believe them to be. For example, specialised systems are often best placed for producing and running financial modelling, reducing the challenges associated with model risk highlighted above.
Carefully consider the tools you’re using to perform tasks within your actuarial organisation, and migrate to more effective tools when spreadsheets aren’t the optimal choice. Many modelling platforms enable the conversion of Excel spreadsheets, facilitating a seamless transition.
Prioritise user-friendly design
Actuarial spreadsheets are often complex, making them challenging for new users to get used to. As well as providing sufficient documentation, new spreadsheet design should focus on creating user-friendly solutions. The more intuitive the system is to use, the less likely problems will arise due to misuse and human error.
Expect errors
Finally, when using spreadsheets, expect errors! The high error rate highlighted in research indicates that all actuaries need to be aware of the potential for errors and bugs in their systems. Being aware of this can help reduce expensive mistakes by ensuring actuaries are critical of their outputs — on high alert for mistakes.
In summary, spreadsheets can be a valuable tool for actuaries — but they come with many risks. Following industry best practices and migrating to more appropriate tools when necessary can help reduce the high error rates encountered with spreadsheet use.
Need help unravelling your end-user computing ecosystem? The Mo.net Excel Model Adapter (“EMA”) may be the answer.
Comments are closed.