This is the second and final instalment of our series detailing our recent project with Hannover Re.
The project consisted of two parts, both related to the usage of a SQL database combined with an Microsoft Excel front-end. In our previous article 'Time is quality', we discussed the first stage of the project on process optimisation – we now look at the second stage, covering the assumption management of MoSes/RiskAgility Financial Modeller (RiskAgility FM) models.
(Please note: Hannover Re used MoSes throughout the duration of the project, then migrated to RiskAgility FM once it was completed. As the implemented process does not depend on the underlying projection model, we refer to related models as being in RiskAgility FM.)
Setting the scene
In the first article, we described the derivation of the required model points and assumptions that are required by Hannover Re’s RiskAgility FM model. The optimisation of the underlying process focused on the time component – preparing the model in a more efficient, standardised and less time-consuming way. The final process was implemented in a SQL database, allowing employees from different locations to interact with it using an Excel front-end.
It is quite a common approach using Excel to organise assumptions in a RiskAgility FM model. Typically, several Excel workbooks are used, each relating to the assumptions of specific parts of the model, such as different liability Lines of Business (LOBs) and economic scenarios. These workbooks include an integrated macro that exports the assumption tables into the relevant model folder. Usually, the format of the exported files is tbl (tab delimited) or csv (comma delimited). The process is visualised in Figure 1.
Figure 1 – Common approach of maintaining assumptions and the link to the model
Click to enlarge
Although the Excel based process is easy to handle, it lacks the following features:
- Versioning of assumptions: Several team members have access to the assumption sheets and may edit the data. Consequently, in order to implement assumption change control, users need to duplicate workbooks, which leads to an increasing number of assumption sheets.
- Access restrictions: Responsibility for the assumptions is often split between different departments and/or sub-teams. For example, the user who is in charge of the asset input should not be allowed to edit any assumptions that are linked to the liabilities.
Assuming one user changes assumption set A and another user performs changes to assumption set B in a different local version of the same workbook, it is challenging to guarantee that both changes will find their way into the final assumption sheets. Furthermore, it is complex to track all the changes step-by-step and as the number of changes increases the problem becomes increasingly complex.
In order to derive a final assumption set that can be used in the production model, it is essential to set up an approval process. Therefore, the person who is responsible for the overall model needs to know:
- the most current set of assumptions for the given purpose (e.g. Best Estimate, sensitivities, business planning etc.)
- the changes made
- who made the changes and why
- Reproduction of results:
Finally, there is the challenge of having the ability of reproducing results. Even if all assumptions are approved and the workbooks are stored for each model run, users must keep in mind that the export of the assumptions from the workbook must be started manually. This raises questions as to whether assumptions in the model folder coincide with those assumptions shown in the related workbook.
Therefore, from an actuarial perspective, a pure Excel based assumption process cannot fulfil all requirements as models have many different applications including Solvency II, asset liability management (ALM), and IFRS, each needing their own set of assumptions.
The main difference between our software solution, Unify, and the database approach, which was implemented in this project, is in the concept at which level the requirements from above are achieved. Unify targets and maintains the whole package of assumption files, whereas the database approach focusses on the files’ content. This project was undertaken before Unify was released, but the process was built in such a way, that it could easily be controlled by Unify.
Optimised, database driven process
Together with Hannover Re, we decided to move the process of assumption management into a SQL database and create functionality to meet actuarial requirements (see Figure 2 ).
Figure 2 – Reorganised process using the assumption manager
Click to enlarge
Regarding the optimised process, it is important to note that:
- The process in Figure 2 replaces the original Excel-based process shown in Figure 1. No changes to the input folders or the model itself are required.
- While the assumption management and derivation of the assumptions and model points take place in the same SQL database, the two components are separated into two different database schemas in order to distinguish clearly between the management and derivation – and to permit different user rights. For example, a model user whose only responsibility is to perform runs will only be able to read the assumptions, but neither derive nor edit them. The derived assumptions can be transferred into the management schema once its derivation is completed.
To make assumption management more efficient, the model input tables were split into different types, where each table type is stored in a different kind of database table. Here, the character of each assumption table is taken into account. Some data is changed regularly whereas others are edited only once during a full reporting cycle. The different types are described in the following:
- Row-by-row tables:
Row-by-row tables store and version each row of a specific assumption table. Typically, these tables are maintained by different users and develop during the course of a reporting process. If a row of a table has been changed, the changes can be identified for each field by comparing the content with the content of the previous version. For example, changes to the assumption table where the product data is stored (see Figure 3), can be tracked in detail.
- Block tables:
Block tables are organised in blocks, for example, modelpoints, which can be grouped by LOB or other criteria. A change to a single row leads to a new version of the corresponding block.
Complete files can be put into the container, without tracking any changes of the content of the files. Typically, these tables are changed less frequently, for example, mortality tables or economic scenario files. Usually, they will comprise of tab-delimited text files or comma-delimited text files, but may also contain FoxPro programs, MoSes Data Definition Files (DDF) or other file types.
All tables contain additional information, so-called metadata, which describes the validity date, the valuation date, the purpose of the assumptions, and the tracked changes to the data. The validity date corresponds to the versioning of the assumptions whereas the valuation date and the purpose can be defined by the user. For example, the final MCEV 2015 reporting consists of the valuation date 2015/12 plus purposes such as Best Estimate, interest down, mortality up etc.
Figure 3 visualises the storage of a row-by-row assumption table and its metadata, where the assumption data itself is shown on the right side and the metadata is shown on the left. It is worth mentioning that the design of the assumptions table itself is no different to the Excel workbook. For example, the user can add new columns using the Excel front-end, which are automatically added to the SQL database table in the background. Thus, the user cannot only control the content of the data table but also the table structure.
Figure 3 - Storage of assumption tables in the database
Click to enlarge
On a higher level, all assumptions are organised in reporting projects, such as MCEV 2015. Therefore, each project consists of a so-called ‘full list’ where the user can define which tables should be used. For example, after any changes are made to a previous model, a new set of assumption tables need to be added to the full list and any unused tables can be removed.
The user interface
As already mentioned, the Excel front-end provides controls for the assumption manager as well as for the derivation tools. In addition, the Excel ribbon holds a new tab – “Assumption Manager”, which includes all the functionality shown in Figure 4. The only difference in working within the Assumption Manager add-in is that the data is sent to or retrieved from the SQL database – instead of storing it in Excel.
Figure 4 – Excel front-end (editing of a table)
Click to enlarge
Once the user connects to the database via Excel, the front-end view is based on the following two features:
- Access rights: The controls provided by the ribbon depends upon the access rights of the user, where the definition of access rights is flexible. For Hannover Re, the access is linked to a combination of lines of business and user roles. Thus, each element or group of elements is linked to the access rights that were defined in the database. For example, an admin button leading to a special menu (such as, adding new users) is only visible to those users who are registered as admin users in the database.
- Context sensitivity:
The ribbon design changes in accordance with the current work steps of the user. For example, the ribbon contains an edit and a save button as soon as a table is opened and the user has entered the editing mode. Additionally, an export button is displayed in the menu if the user enters the exporting mode.
These features were only possible because of the dynamic design of the ribbon. The Excel add-in connects to the database and carries out SQL queries in the background. The results are then used to change the ribbon’s design or displayed values. For example, the list of all tables which exist in the database is displayed in a dropdown menu which is updated automatically once a new table is added.
The impact of these database operations on the performance of the Excel workbook is not significant, although this may of course differ depending on IT infrastructure and the configuration of the working machine.
Getting the model ready
As mentioned earlier, the assumptions used in a model are very important. The user and/or reviewer needs to know which set of assumptions were used for a specific run. Using Excel-based management, the assumption workbooks need to be stored and the user needs to ensure that the assumptions in the workbooks coincide with those in the model folder.
To optimise this process, a connection between the RiskAgility FM model and the SQL database is established at run time and a dedicated procedure of the database is called. Now, the most current assumptions are automatically exported to the relevant input folders within the production environment. The export is triggered within RiskAgility FM during the initialising phase of the projection.
Furthermore, the assumptions stored in the database are extended by the validity date (metadata section) as shown in Figure 3. In combination, these features enable the user and/or reviewer to review the full set of assumptions for each single projection using the run’s timestamp. In addition, the user can see if the assumptions were approved – and by whom and when.
Of course, the need to update models for regulatory changes or product developments does not decrease when using an assumption database. This requires model developers to react quickly to changing demands and ensure that testing needs are strictly separated from the development environment. Therefore, the Excel add-in consists of a second export mode that can only be used for local testing and development purposes. Here, the assumptions are exported into Excel workbooks and then stored in a user specific folder, where they can be manipulated for local runs.
Current experience from a user's perspective
The Hannover Re modelling and reporting actuaries are very satisfied with the operability of the assumption database system. The main benefits are:
- The time spent on the collection and the management of the assumptions is reduced significantly
- Direct connection to the data warehouse (treaty information) and automated export of Moses/RiskAgility FM input tables
- Achievement of a higher reproducibility and security (project/purpose combinations can be reproduced at any time)
- Enhanced data quality: validation rules can be easily parametrised for each data field
- Contains approval processes
- Further development and maintenance can be carried out by modelling actuaries on a regular basis. The Excel add-in is the only tool which needs to be installed from anyone who intends to work with the database. The common user does not need to work directly on the database and therefore does not need to learn any SQL techniques.
Benefits beyond assumption management
The following ideas weren’t part of the project with Hannover Re but have already been implemented in a proof of concept.
Having built automated processes for both the derivation and the management of assumptions, attention then turned to the question of how to improve the management of the results. Database functionalities, like approving and versioning, are also important for projection results. It would be possible to use SQL Server Reporting Services (SSRS) to design reporting templates that are similar to existing Excel reports. These can be accessed through an internet browser. After opening the reports, they are finally populated with data from the SQL database, where the results are stored.
Aggregating all these components leads to a fully automated process (see Figure 5) and the whole process could now be managed by Unify.
Figure 5 – Fully automated end-to-end process
Click to enlarge
Finally, the automation of processes carried out in this project with Hannover Re focused on model related topics only. But, the concept and techniques can easily be adapted to many different areas where a lot of manual steps are applied, e.g. accounting or mid-term business planning.