22 July 2010

Published in: Risk governance

How to bring control and flexibility to actuarial processes

Actuarial teams are increasingly using technology to try to balance these potentially conflicting goals. Mark Schneider and Darren Robinson examine two approaches to dealing with this issue. The first part looks at the data warehouse approach. Part two next week.

In order to perform regular valuations (including margin on services and capital and embedded-value calculations), actuarial teams are increasingly required to run complex processes, usually on a monthly basis and in ever-shortening timeframes. There is a growing range of requirements placed on these regular processes, which typically fall into the following categories:

• Balancing flexibility with the necessary control in the use of spreadsheets
• Robustness of data
• Reasonableness checking
• Removal of key person risk
• Automation: all too often, too much of the team's time is spent producing results rather than reviewing and communicating them
• Reliability of results: to the extent that processes are automated, the removal of human error can mean that results become more reliable, particularly if reasonableness checks are also automated
• Auditability, transparency and documentation
• Repeatability: today's practices rarely support the reliable and efficient "re-running" of processes to confirm previous results
• Reduction in operational risk and capital charges

Figure 1 below illustrates the relationship between some of the potentially conflicting demands faced by actuaries in financial services.

Figure 1: Trade-off between available resources and the level of control. The impact on the curve of added complexity and appropriate technological improvements is also illustrated.

There are two broad technological approaches being taken to address these demands: the IT data warehouse approach and the spreadsheet-based automation approach.

The approaches are not mutually exclusive and we examine later how a combination of the two addresses a number of the issues.

Benefits of the IT data warehouse approach

There are a number of benefits to be obtained from setting up a single data warehouse for the storage of all the reporting information required by the company. These benefits include:

Consistency

Clearly, the most attractive feature of the data warehouse is that it can become the consistent "single source of truth" in the company. All users would access the same data and therefore avoid the costly, all too prevalent, issue of different systems (such as administration and accounting systems) providing inconsistent information. Naturally, the organization would need to ensure that the information being fed into the data warehouse is correct in the first place.

Efficiency

A single data warehouse, if properly designed, should provide an efficient mechanism for storing the required information as it eliminates both data duplication and the need for multiple exercises to reconcile the data back to source systems.

Control

Data warehouses are normally developed, maintained and supported by a company's IT department. Hence the rigour and control that an IT department normally brings to the development, maintenance and support of core company systems can be relied upon to provide the same level of control over the structure and changes made to the data warehouse and to the use of its supporting tools and processes.

Ease of access to information

With suitable reporting tools used in conjunction with modern database systems, it should be easy to obtain and combine information across one or more of the dimensions in the data warehouse as long as the information has been stored at a suitably granular level and provided the supporting databases are efficient enough to rapidly supply the information.

Reduce spreadsheet proliferation

A data warehouse should make it possible to replace a multitude of spreadsheets and therefore reduce the risks associated with the use of spreadsheets in actuarial functions. These risks are well known but worth repeating. They include:

• Proliferation. It is very common for multiple copies of spreadsheets to be created for each valuation and for each run within a valuation. It becomes a logistical challenge for users to organize these spreadsheets and to ensure the appropriate workbook is used each time. Typically these are organized using the file directory structure on a company file server.
• Invalid links between spreadsheets. The plethora of copies of spreadsheets and the inevitable approach taken to link spreadsheets within Microsoft's Excel leads to a problem when incorrect data is updated in a target workbook or the update fails to occur. The error or omission may be recognized late in the process and, sometimes, not picked up at all. This represents a substantial operational risk.
• Lack of version control. The issue of proliferation can make it difficult for users to be sure that they are using the correct version of a spreadsheet. This can be partly addressed through the use of version control tools such as Microsoft SharePoint for the storage of the spreadsheets.
• Errors in copying formulae. Another very common error that occurs when using spreadsheets involves users copying cells where they should not be copied or failing to copy cells to all intended targets. This is a consequence of the inherent nature of spreadsheets where formulae are repeated for each cell in a selected range in contrast to tools that are designed to use a single copy of a formula with a processing loop to repeat the calculation. This is another substantial source of operational risk for companies.
• Lack of documentation. Spreadsheet-based and desktop-software-based processes are often not documented, or the documentation that does exist is often not maintained by the users, because of the ease with which spreadsheets can be changed and the time pressures under which users tend to operate. This leads to key person dependency and errors, especially when new staff are required to take over a task.
• Other user errors. Many companies use people to perform repeated processes each month or quarter to combine data and results for actuarial reporting. If the spreadsheets and processes are not well organized and clearly defined and monitored, it is easy for even highly skilled people to introduce errors through this manual manipulation of the spreadsheets.

Replace Multiple Systems

In addition to replacing spreadsheets, the data warehouse may replace a number of other custom systems used in conjunction with the actuarial projection systems. These may have been used to manipulate or aggregate data, correct errors or other tasks. These would typically have been written using Visual Basic, Visual FoxPro, SAS or the Prophet DCS tool.

Weaknesses of the IT data warehouse approach

There are a number of challenges to adopting and implementing a single data warehouse and, to date, few companies have successfully achieved the goal. The reasons for this are as follows:

Design challenges and data complexity

The approach requires companies to carefully design the data store that gathers information from multiple sources such as accounting systems and legacy administration systems with widely differing formats (e.g. gender stored as M or F in one system vs. 1 or 2 in another), using widely differing technologies (e.g. SQL Server databases in one system vs. old proprietary COBOL data formats in another) and widely differing tools available to access the data.

The problem is exacerbated in organizations that have been highly acquisitive and the existence of five or more administration systems is not uncommon. It is very difficult to design these databases in a robust manner and with sufficient flexibility to meet the diverse reporting needs of the firm. The speed at which data can be accessed from the databases can also be substantially impacted by the design and it is essential to employ suitably experienced designers for this task.

Concentration of specification risk

When organizations enter into large IT projects that take six or more months to complete with dozens of participants, they are effectively placing a very large bet that they can specify and communicate precisely what they will need at a future point in time. Mistakes in either the specification or communication of requirements severely impact the cost estimates when a correction is needed six, nine or 12 months later.

Scale and cost of development projects

Large development projects are usually required to design, build, test and deploy these data warehouses. Often external resources such as actuarial consultants, project managers and IT developers are engaged to provide the specialist knowledge or "manpower" required to undertake such large, one-off development projects.

We understand that it took 20 people approximately two years to build such a data warehouse in a large Australian insurer. This equates to a cost in excess of A$10m (US$8.66m), excluding the cost of the hardware and software required to support the development and host the resultant databases. Naturally, with projects of this size, there is a substantial risk of cost and time overruns and history shows that it is more common than not that substantial overruns will occur.

An actuarial data warehouse (which may be part of an enterprise data warehouse) is typically one of the more complex data warehouses in the organization and a number of specialists are usually required to develop it. These include actuarial, product, IT and database specialists.

Cost of ancillary software and integration

Companies may also purchase "wrappers" for their existing actuarial projection systems. These provide an ability to schedule runs and store versions of the models, the input data and the calculated results in a manner that means they can be retrieved and re-run if required. Implementation projects will need to include the ability to connect these wrappers to the information in the data warehouse, to read assumptions from it in order to perform the projections and to load the calculated results back into the data warehouse.

Cost of ongoing maintenance and support

The development, support, maintenance and provision of infrastructure for such an approach will move from the actuarial department to the IT department of the organization. It is likely that a number of technologies such as query tools (e.g. Toad or Visual Studio), development tools (e.g. C# or Visual Basic), databases (e.g. SQL Server or Oracle), ETL (Extract/Transform/Load e.g. Microsoft SSIS or Oracle Data Integrator) middleware tools and reporting tools (e.g. Cognos or Business Objects) will be required for the development and use of the data warehouse. These will need to be supported so it will be imperative that suitably skilled people are retained for this purpose. It is estimated that a large data warehouse will require five or more people with an annual cost of approximately A$1m+ (US$866,000+) to support the technology alone.

Volume of data

The data warehouse can become very large as the information stored typically has multiple dimensions or combinations. These may include the following (along with estimates of the size of each dimension):

• Purpose of the run (e.g. statutory reserve, EV, capital calculations, etc, say 1, if calculations for all purposes are done in the same run. In some companies, separate runs may be required for each purpose).
• Number of projected cashflows (e.g. premiums, sums insured, number of policies, etc, say 200).
• Number of periods in projection (while projections for 600 months are common, this storage dimension may be reduced if fewer months are required in the database and the full set of results is kept elsewhere -- so assume 24 months of projected numbers are stored in the database).
• Valuation month (e.g. March 2010, say, the current set plus 24 prior monthly sets and another 10 half-yearly sets are retained for historic or taxation-related purposes, equating to 35 sets in total).
• Interim runs (assume interim runs are deleted, so say only the final run, i.e. 1, is retained).
• Number of product lines (e.g. term business, say 6).
• Grouped data level (i.e. the lowest level at which projected results are stored. This must be granular enough to be able to report on all dimensions required for all reporting purposes (such as region and distribution channel). The ultimate level of granularity is at policy or coverage level, but for this purpose we assume companies store the information in 50 groups per product line).
• Sensitivities, shocks, basis changes, inforce and new business (these will vary considerably and may not be applied to each valuation period so we assume that on average 30 are stored).

Multiplying these dimensions:
1 x 200 x 24 x 35 x 1 x 6 x 50 x 30 = 1,512 million numbers. This would take at least 12 gigabytes of data storage capacity for the calculated numbers alone. The database keys and indexes used to identify and access the numbers could increase this up to 30 times.

In addition, the assumptions used for the projections and historic profit and loss and balance sheet information would need to be stored to be able to include this information in the reports. Data volumes can also increase when special "materialized views" of the data are created to speed up production of certain reports. Whilst the storage dimensions may change for stochastic models (e.g. monthly calculations may become annual), the number of scenarios being run would increase dramatically, so the volume of data would almost certainly be greater than that estimated above. A data storage capacity of several terabytes may therefore be needed.

The size and number of dimensions of the data warehouse can have implications for the speed of accessing information and the complexity involved in managing it as an entity. The overall storage required may also be larger than with other options because of the need to store keys for each dimension. Alternative options may use approaches like file directory trees for some of the dimensions.

Ongoing tuning

When a company is working with databases of this magnitude, it is vital that the databases are optimized and tuned on a regular basis. If this is not done, extracting or adding information to the database becomes unacceptably slow. Increasingly, this tuning activity can only be performed by high-cost IT specialists. Even under the most lenient of charge-back models, organizations find that the cost of having this level of technical expertise "on call" significantly adds to the total cost of ownership for the solution.

Lack of flexibility

One of the biggest drawbacks of the single data warehouse approach is a lack of flexibility. End- users are unlikely to be able to change the databases and systems attached to the data warehouse, particularly if the data warehouse is used by staff outside the actuarial department. These changes must typically be made by the IT department and must be planned, specified, coded and tested. IT departments usually have long development lists so changes must be prioritized and may not be addressed immediately if the company has more pressing requirements.

Despite the best endeavours of the members of data warehouse development teams, changes will almost certainly be required over time because of the development of new product features or product lines, changes in regulations, the acquisition of new subsidiaries or blocks of business and new management or auditor requirements.

A common outcome of the relative inflexibility of data warehouses and the time required to modify them is that end-users, such as actuaries, download data from the data warehouse and other sources into spreadsheets and then manipulate the data in those spreadsheets in order to meet deadlines.

While such an approach is initially intended to be a stop-gap measure until the data warehouse has been modified to meet new requirements, it is common for these "stop-gap measures" to proliferate over time and become permanent.

As the controls and audit trails revolve around the data warehouse, there are usually few in place to address these ad hoc spreadsheet-based processes that spring up and begin to proliferate. The data warehouse is no longer the "single source of truth" and this gradually degrades the overall control provided by the data warehouse approach and increases operational risk. This degradation can begin within a year -- or even before the completion of the data warehouse development project, if interim workarounds are used during the development phase to meet deadlines and budgets.

Time and cost of changes

Changes to the data warehouse and associated tools take time and incur substantial costs. Because of the need to engage the IT department to plan, specify, code and test changes to the database and associated tools, it may take anywhere from days to months to have changes implemented, even after the changes have reached the top of the IT department's priority list. The cost of each change can be substantial with IT departments commonly charging tens or hundreds of thousands of dollars to effect non-trivial amendments. While the data warehouse development by joint IT and actuarial teams may have taken place with a significant budget and great enthusiasm, it is sometimes difficult to obtain an ongoing commitment to sufficient budgets and sustain interest from personnel in adequately maintaining the data warehouse over time.

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.

Part two of this article -- on the spreadsheet-based automation approach and a hybrid approach -- will be posted on this site next week.

Back to top