28 July 2010

Published in: Risk governance

How to bring control and flexibility to actuarial processes, part two

Last week Mark Schneider and Darren Robinson examined the data warehouse approach to reconciling these two goals. This week they conclude their article with a detailed look at the spreadsheet-based automation approach and the potential of combining the two approaches.

Why spreadsheets are used by actuaries

While spreadsheets have their weaknesses, as we described earlier, they also have a number of strengths. The main strengths of spreadsheets in an actuarial context are:

Control is located as close as possible to the subject-matter experts

While often overlooked as a benefit, this is possibly the primary case for spreadsheets and the underlying reason that they are prevalent throughout the industry today. The proximity of expertise and control allows processes to be developed more quickly and iteratively with a lower up-front specification cost compared with more traditional forms of systems development.

Flexibility

It is clear that spreadsheets are very flexible and can easily be modified. They can readily be adapted to accommodate changing circumstances such as when new reports are required or when new data sources need to be incorporated. The latest versions of spreadsheets such as Microsoft Excel, when used with newer computers, can handle large volumes of data.

Highly visual interface

Users of spreadsheets can always see the data they are working with. This makes it easier to find errors and correct them.

Ease of producing reports and graphs

It is easy to move data into spreadsheets from a wide variety of sources. Once the data is in a spreadsheet, it is usually easy to combine it in various ways to produce end-user reports and graphs.

Users do not need IT training

Most staff in actuarial departments are capable of using spreadsheets without substantial training. They are able to manipulate data, create and modify reports, incorporate new sources of data and perform checks without the need to communicate the requirements to programmers.

Cost

Spreadsheets are highly cost-effective with a price tag of a few hundred dollars per user. While they do not offer all the functions of large, centralized systems, their very low cost brings organizations to the realization that they will achieve "80% of the functionality for 10% of the price." As organizations continue to look for ways to constrain costs, this increased focus on value in conjunction with capability (as opposed to 100% capability first - then cost) is expected to become more common.

Facilitate communication

Because of their ubiquity, spreadsheets provide a useful common medium for the communication of data and information within an organization

Support rapid "what if?" thinking

With spreadsheet-based models and analysis in the hands of the actuary, high-skilled resources within an organization are able to conduct small, low cost "experiments" on the way that models are organized and executed. This results in incremental improvements, albeit often "under the radar," and consequently undervalued.

Tools to support the use of spreadsheets

Spreadsheets have therefore been used from their very first incarnations for actuarial work. Much more recently, we have seen a number of tools emerging to address the weaknesses of spreadsheets. These tools include:

Version control tools

These deal with issues such as proliferation and helping to provide audit trails. An example of this type of tool is Microsoft SharePoint. These tools reduce the need to rely on the organization of files via share-server directories, although care is still required to organize the source-control tool appropriately.

Audit tools

These list or highlight the differences between versions of spreadsheets, identify inconsistencies and highlight links and other "at risk" elements of spreadsheets.

Automation tools

These tools provide the ability to automate the steps that users follow when performing various functions. Some of these operate on spreadsheets. Traditionally, macros and Visual Basic for Applications (VBA) code has been used for this purpose, particularly with spreadsheets, but newer tools are available that address a number of deficiencies of macros and Visual Basic code.

The deficiencies that are addressed include the need for programming skills, the lack of audit and version controls and the fact that macros are limited to use with certain Microsoft Office products. In addition, some automation products only operate on a single PC at a time and are not multi-user enabled or web-based.

More recently, tools have started to become available that create fully documented and auditable, automated processes and work with a wide range of products such as spreadsheets, databases and actuarial systems. They provide a capability to automate the preparation, checking and manipulation of data whether in spreadsheets or other formats while also addressing most of the deficiencies of spreadsheets that are listed above.

Spreadsheet development standards

The adoption of formalized standards for spreadsheets also helps to address their deficiencies.
These standards may include:

• Maintenance of version information and change control lists
• Colour coding and formatting of input, calculation and output cells
• Restrictions on linking spreadsheets
• Protection of calculation sheets and/or cells.

Benefits of the spreadsheet automation approach

In addition to the strengths and benefits of spreadsheets, by using spreadsheet automation systems in an actuarial context, the following benefits become available:

Processes are documented

By definition, a process that has been automated has its steps recorded in some manner. Some process automation systems can produce a report that documents the steps in the process as well as listing all the files used in the process and all the dependencies that exist between the steps. As the process is updated or modified, so documentation of the process is modified.

Automation keeps actuaries in touch with the steps involved in the actuarial processes for which they have responsibility and satisfies the requirements of auditors and regulators. The use of spreadsheets or other desktop technologies means that the reverse is also the case and process documentation that has been automatically created and kept up-to-date can be used to reproduce manually the steps in a process, thus providing components of a business continuity plan.

Audit trails of changes to processes are available

Some systems provide reports of the changes made to processes. They identify the individual responsible for the change, the time the change was effected and the nature of the change. Such reports are particularly helpful for supervisors or auditors.

Audit trails of changes made to files are available

Similarly, some systems can provide a log of changes made to the underlying data files; either manual changes that are made by a user or automated changes that are programmed by a user but effected by the system itself. This addresses one of the major deficiencies of spreadsheet use where it is common for a significant number of changes to be made without any clear evidence of the route taken to achieve the end result.

An important differentiation between systems with this capability is the extent to which a human (a) can modify the audit trail, and (b) is required to do something to produce a valid and useful audit trial. There is an argument that the ideal audit trail requires no intervention and indeed does not allow human intervention. Not all solutions deliver these audit trail requirements.

Changes to spreadsheets are automated

Automation substantially reduces the risk of manual errors made by users simply because most of the work is no longer performed by the user. This time saving provides the opportunity for actuarial resources to concentrate on reviewing results instead of running processes and mechanically performing valuations and other repetitive actuarial tasks.

Location and review of data problems is simplified

Data used in the actuarial process becomes readily accessible and can easily be viewed at various stages through the process. Therefore, identification of errors, and of the stage at which errors were introduced into the process, is straightforward compared with locating such data problems in a large enterprise database using specialist tools.

Flexibility is introduced and rapid changes to processes are made possible

Automated processes can be managed and maintained by the actuarial department rather than the IT department. As a consequence, involvement of the IT department is substantially reduced and changes to a process can be made by the actuarial department without needing to be prioritized by the IT department.

It is also much easier for the actuarial department to make changes to spreadsheets that make allowance for new or changed product features, product lines or data requirements. This does, however, pass the onus of checking for the downstream impact of the changes to the actuarial department rather than the IT department.

Management of the actuarial department will therefore need to ensure that robust change-control processes are in place and monitored. These change-control processes can be much like those used in IT departments and the IT department should be consulted when setting these up. Controls may include the use of development, test and live versions of spreadsheets and automated processes, and documenting the appropriate approach to migration from one version to another.

Staged development and deployment is enabled

Process automation systems can usually be implemented in stages. A large-scale development project is normally not required as many existing systems continue to be used with the automation system linking these together. Much of the work can be done by the users of the software and those who are familiar with the processes.

It is also feasible to replace portions of processes with new or improved software or tools while-still preserving the overall process. This approach fits with a software development approach known as Agile (and its philosophy of evolution with small iterations) which is usually more appropriate for knowledge based environments than methods that require the specification of the system in full before development begins.

Integration of actuarial systems is facilitated

Some automation systems facilitate the integration of a range of third-party systems such as actuarial projection systems, reporting systems, scenario generators and risk aggregators. This obviates the need for substantial development projects to integrate these tools with data sources and other tools required as part of the actuarial process.

Reliability and robustness are increased

Naturally, automation of processes should make the processes more reliable. However, this does depend on how the automation has been done. Spreadsheets and data files may need to be reorganized to better suit automation. For example, it is much more difficult to automate spreadsheets with fragmented data that have subtotals inserted between records or multiple tables of data on a single worksheet. Many of the necessary changes will make the spreadsheets easier to maintain and check.

Another benefit of reorganizing spreadsheets and files for automation is that this activity serves as a catalyst for what is often a long-delayed clean-up and simplification, such as the removal of information no longer required. It may no longer be necessary to carry forward historic results in each version of the spreadsheet as the automation system will hold this information. Such cleansing of spreadsheets will naturally reduce the risk of error in the processes that use them.

In addition, once a process has been automated, it is easy to add multiple-data validity checks, cross-check against separately loaded totals, compare to previous months' calculations, parallel-run under different methods and provide the opportunity to configure the process to stop at pre-designated points and await human review and sign-off before continuing with the next stage of the process. These checks are expensive to add to data warehouse systems on an incremental basis but easy to add to spreadsheet automation systems.

Cost is substantially reduced

One of the biggest benefits of spreadsheet automation is the low cost of implementation, software and maintenance. When compared with the size of development projects for a data warehouse, process-automation systems typically require much smaller development projects and require fewer new software tools (typically only the automation system itself) as existing tools continue to be used. In addition, modifications to processes or data corrections can be made by the user rather than the IT department, thereby decreasing communication costs and delays.

Key-person dependency is reduced

Many processes in actuarial departments are well understood by only a few people, normally those who carry them out each month or quarter. There are often 100 or more steps involved in these processes and any documentation is often out of date. Companies are therefore very dependent on a few key people for producing their actuarial calculations. By automating these processes, along with the documentation produced, key-person dependency can be substantially reduced.

Simplification and alternative calculations are facilitated

Automation frees up actuarial resources to investigate alternative or simplified approaches to detailed calculations and these alternatives can readily be tested against the full set of automated calculations. A simplified alternative "top-down" calculation process can be implemented for use when quick approximations are required, as a fallback when the full data set is not available in time for a complete "bottom-up" calculation, or as a check on the results of the full calculation.

Significant resources are accessible

Some automation systems provide easy access to substantial computer resources through mechanisms such as "cloud computing". These resources can include large numbers of computers to perform calculations on multiple CPUs, in parallel, and without the need for the company to purchase the hardware. "Cloud" providers also typically provide considerable data storage capacity at cost effective rates.

The reason that such resources can be cost effective for actuarial purposes is because the cost is solely determined by the time the resources are used. Therefore if a valuation is run for, say, only two days a month, it may be cheaper to rent the computer CPUs only for those two days rather than own them, house them, maintain them and have them sit idle for most of the month. With cloud computers, it costs much the same to use 200 CPUs for one hour as it does to use 20 computers for 10 hours.

This means that actuarial calculations, which are usually well suited to running in parallel, can be completed much more quickly using a larger number of rented cloud computers compared with using, perhaps, a smaller number of dedicated in-house computers for the same cost.

It is, however, necessary for the automation software provider to have suitable security structures and load-sharing functionality in place to take advantage of cloud computing resources. The cloud computers may also not have the same instant availability that dedicated in-house computers would normally provide.

Browser-based

If the automation software can be hosted on the organization's intranet or is internet-based, the processes set up by the actuarial team can span several locations. Data can be collected from multiple sources and can be used and reviewed in other locations. This facilitates the operation of decentralized actuarial functions but also helps with the collection and dissemination of information to and from centralized or regional actuarial departments.

For example, data can be reviewed in the local insurance operation and errors corrected, then automatically fed into a centralized actuarial projection process; at the end of the process, the results which are pertinent to the local operation can be sent back to it and made available to the local personnel. In addition, a web-based system will facilitate any requirement for local support.

Regulatory requirements are addressed

Some process automation systems allow for the separation of roles, in particular those of development and maintenance of processes vs. the role of running processes. Sophisticated systems also allow for controlled access to information and remove the possibility that production runs can be deleted or modified without an audit trail providing evidence of the deletion or modification. Versions of data and past runs can be maintained or rerun if required. This ensures that processes making use of automation readily comply with regulations such as Solvency II and Sarbanes-Oxley.

Weaknesses of the spreadsheet automation approach

Actuarial processes that have been automated via process automation systems, like other powerful tools, do not mitigate the need for sound management and adherence to established protocols. Problems can occur if:

Insufficient time is dedicated to checking spreadsheets

Spreadsheets are often set up and amended by users unfamiliar with automation. It is therefore vital to ensure that the spreadsheets to be applied to the process are correct and appropriate to the task before the process goes "live". Without adequate checking at the outset, an incorrect spreadsheet could be introduced to the process and re-used each time a process is run.

Management systems are not in place

Appropriate management structures must be put in place to review and sign off changes made to the company's automated processes, as well as the spreadsheets and data structures used with these. While there are tools available to help with managing versions and indentifying changes to the processes and the underlying files used by the processes, these are of little value if managers do not mandate the use of these tools and apply sound version-control procedures.

Adequate data checks are not established

If processes are not set up with adequate data checks, cross-checking of totals and other verification steps throughout each stage of the process, the results produced by the automated system could be erroneous. One of the key advantages of performing actuarial tasks manually is that users are continuously scanning the numbers and, with experience, can often spot inconsistencies and problems.

Manual checks must be replaced with automated checks and the opportunity must be provided for suitably experienced users to check interim and final calculations before they are published. However, if properly done, automation can enable actuaries to spend a much larger proportion of their time on applying their skills to reviewing results rather than "cranking the handle" to produce them.

A hybrid approach?

It is possible to combine process automation systems with the use of a data warehouse. In this scenario the process automation system is used to combine, convert, check and feed the required data into the data warehouse. Data is then drawn from the data warehouse and run through the actuarial systems by the process automation system and the results passed back into the data warehouse.

This approach may reduce the cost of developing systems to ensure that clean data is fed into and out of the data warehouse and, at the same time, the data becomes more flexible. To overcome the very significant cost of developing a data warehouse and the inflexibility associated with data warehouses, the design should be kept simple and development should be in stages and be supplemented by spreadsheets controlled through a process automation system.

This approach should make it possible to deploy a hybrid approach at a reasonable cost and with sufficient flexibility and control. Hybrid solutions will become increasingly attractive as process automation system providers continue to add tools which enable users to create and modify data bases in a similar controlled manner to other file types such as spreadsheets.

Evaluating the options

When evaluating the options available, an organization should consider the following questions:

• How quickly do we need to respond to changes in our business, i.e. how important is flexibility? How acquisitive has the organization been? How quickly can we change our systems?
• Where in the organization do we want control to reside for this subject matter? Where does the intellectual capacity for driving our process really reside?
• To what extent do we want to rely on human vs. machine effort to create compliance and audit documentation?
• Are we using the time of our subject-matter experts and highly skilled staff in the best way? Do we have them doing low-value activities at the cost of higher-value activities and analysis?
• Looking ahead over the next several years, do we expect compliance and audit requirements to become more or less stringent? How will we respond to this? How quickly will we need to respond?
• Are resources likely to be available on an ongoing basis to maintain and modify a data warehouse and its associated technologies?
• How effective has the organization been in delivering large-scale IT development projects on time and on budget?
• What is the impact on the competitiveness of my organization and my perceived value as an actuarial professional within this organization if competitors answer these questions more aggressively and more rapidly than we do?

All of the above forms a significant agenda for actuarial teams as they seek to satisfy chief financial officers demanding more efficient processes and chief risk officers requiring tighter controls.

Almost as soon as the spreadsheet was invented, the debate about its advantages and disadvantages began. Whatever the weaknesses of spreadsheets, they play a central role in the financial services industry and seem likely to continue to do so.

Firms should therefore ensure that an appropriate risk governance structure, supported by suitable tools, is put in place irrespective of the approach that is adopted by the actuaries, accountants and others within the organization. This structure should take into account the considerations that we have highlighted earlier - such as robustness of data, reasonableness checks, key person dependency, auditability, transparency, documentation, cost and repeatability.

The biggest challenge each firm faces is finding exactly the right dividing line between flexibility and control that suits its own business mix and culture.

Mark Schneider is a founder and managing director of Solvexia. He has worked for almost 30 years in roles that have combined actuarial science and computer science. Mark first worked for Liberty Life in South Africa, then as a consultant for Tillinghast after moving to Sydney. In 1993, he co-founded Classic Solutions, creator of the MoSes actuarial projection system. After Towers Perrin, now Towers Watson, purchased his firm in 2002, Mark became managing principal of Tillinghast's global software business before leaving the company and starting Solvexia in early 2008. mark.schneider@solvexia.com

Darren Robinson is the valuation and capital actuary at BT Financial Group. He has worked for 20 years on valuation systems and processes, with a focus on removing complexity and improving transparency. Prior to working at BT, Darren worked at CommInsure as the portfolio actuary for unit-linked business where he worked on process integration following the merger of Commonwealth Bank, Prudential, CMLA and Legal and General, on unit pricing issues and on the pricing and hedging of a variable annuity.  

 

 

Back to top