Reflections on a QRT Project

The start of a project – thrown in at the deep end

Entering the placement, I had no hands-on experience of reporting and a very limited knowledge of what an actual QRT was.  Chucked in the deep end somewhat, I was asked to carry out a gap analysis for the population of S.02.01.b, the old language name for the Balance Sheet.

(Note that defined terms in bold and italic text are explained at the end of this post.)

Extract of SR.12.01.02 (RFF Technical Provisions)

The screenshot below shows an extract of the Technical Provision Form which is required to be submitted on an annual basis for each Ring Fenced Fund within a Solo Entity (company).  Examples of Ring Fenced Funds include With Profit Funds and Matching Adjustment Portfolios whose assets are ‘ring fenced’, in that they cannot be removed without undergoing a strict regulatory assessment first.

This Form is basically a more granular version of the Actuarial data needed to populate the Balance Sheet, in terms of lines of business and also the explicit detail around Transitional Measures.

Note that Transitional Measures are put in place to soften the blow to companies when moving from Solvency I (“SI”) to the more stringent Solvency II (“SII”) basis.  The full brunt of SII regulations will not be fully felt for 16 years (from 01/01/2016) for companies using Transitionals.

Gap analysis

Not knowing where to start this was initially quite daunting.  However, the starting point was really to get to grips with the Form itself. Google came to the rescue here, which can at times (if used properly), be a godsend.  For reference, I have talked through a few QRT Forms in the appendices below.

Each cell which needs to be populated within a QRT has a description attached to it provided by EIOPA (European Insurance & Occupational Pensions Authority, the EU regulator).  These descriptions along with the exams, APR training, knowledge gained at previous client projects and EIOPA reference papers (for example Delegated Acts) gave me some foundation to assess what was needed.  The ultimate source, if I could not resolve what needed, were the colleagues within my team.

Once the definitions of all cells were ascertained, it was a case of trying to find out who within the company is responsible for producing these numbers. Questions regarding whether the numbers were being produced at the correct granularity also came into play.  For example: were they needed at a policy level; were the correct splits in terms of line of business available (see the S.02.01 and S.12.01 Forms in the appendices below for examples of the SII lines of business which are required to be reported on for Technical Provisions).

The development stage

In an ideal world, all numbers would be in the ‘ready to be used’ stage and for the rest of this article I will assume that this optimum scenario had nearly been reached, with the exception of there being  some outstanding questions regarding formats of these inputs.

The next stage for me would be to develop a solution in order to pick up this data from all the necessary teams and flow them through the predefined process in order for the QRT Forms to be populated.  Note this predefined process would have been agreed in management level design workshops.  It defined which tools and departments the numbers would be required to go through before they were sent to the PRA in order to achieve set efficiency and validation requirements.

To do this, a number of tools and component parts can be used including Excel, SAS, mainframe administration systems, Prophet, MoSes, SIIAssist etc.  However, to simplify things, let’s assume that only Excel and a mainframe system (owned by the IT department) are being used.

Day to day activities for me would be building a tool in Excel, with which the reporting team would be able to produce QRTs, aiming to minimise the number of inputs, with the utmost confidence that the tool would not crash out and that the numbers would be correct.

Extract of S.25.03.04 (Group SCR for Full Internal Model Companies)

The screenshot below shows the SCR QRT Form which is required to be populated on an annual basis for companies running a full internal model.  The Form shown is the Group version, i.e. the view of the all companies within an organising from the top level.

This Form is split into two parts, the top section is for the disclosure of risks components modelled and the second dissects the SCR in a number of ways.

Working across departments

Work on different aspects of the project would in practice, be done in parallel with each other.  This means I would also be assisting the development of inputs to the QRT project with the various teams.  Being the in-house subject matter experts (SMEs) on QRTs, this would be a case of setting out and explaining requirements, and building templates (within Excel) for each individual team to populate.  Note that changing current reporting processes within a company is a relatively thankless task, so relationship maintenance between key stakeholders is paramount.

Once most of the solution has been developed, it is time to get IT involved. Actuarial output in this example are CSV outputs from Excel, containing one row for each data item (or cell) in a QRT Form.  There is one CSV per Form, per spreadsheet used to populate it.  In other words, one QRT Form could be populated using more than one spreadsheet and therefore that particular QRT Form can have more than one CSV populating it.

Specifications need to be written to tell IT how to read in the data supplied by the Actuarial department, as well as where the data item goes in the QRTs. Logic such as ‘which company it belongs to, which Form it is needed for and which cell does it populate’ needs to be coded by IT to ensure the data ends up the correct place.  Again, this is another example of how good cross department relationships can aid progress.

As QRT Forms are not Actuarial specific, this IT code would also collate data from the Accounting and Assets departments to ensure that the QRTs are populated in full.

The final stage – testing

Once IT produce the output for the QRT Forms themselves, the final step is to check that the numbers are correct.  With this in mind, the tool that we built included embedded QRT Form view tabs, so this became a case of comparing the two sets of Forms one by one.

Once testing is been completed, the tool is handed over to the reporting team and run in the live environment. Figures are subsequently signed off by the business and sent to the PRA.  Time to celebrate with cake!

All references to regulatory material and QRT Forms can be found on http://www.3blocks.co/

Definitions

QRTs

What are QRTs?

• Quantitative Reporting Templates, set by EIOPA, which need to be sent to the PRA on a regular basis.
• They contain both public and private information about a life or general insurance company on a number of areas including the balance sheet, technical provisions, own funds, SCR and MCR.
• They also include the disclosure of capital requirements for non-insurance companies or non-European Economic Area (EEA) companies which fall into an insurance company’s Group.
• In effect, they allow the PRA to monitor whether a company or a group is Solvency II compliant.
• QRTs were required to be submitted for the first time for ‘Day 1’, 01/01/2016.  They are subsequently required every quarter, with an additional (larger) annual submission made for the end of each year.

Non-actuarial figures are also required such as disclosure of invested assets (investment accounting department) and details of premiums and claims (accounting department).

Gap analysis

The flow diagram below depicts a (simplified) very high level view of the thought processes of identifying how much work was required to change existing reporting processes in order to populate each item in the QRT Forms. Note that the intricacies in practice of doing a gap analysis are much more extensive.

Tools and component parts

The process diagram below shows a simplified data flow map and examples of the kind of component parts which can be involved when populating QRT Forms.

The things to note here are:

• Data can be collated from a large number of departments across the company and some may not even be in the building or city you are working in.  Communication is therefore paramount to ensure all parties are aware and in agreement of what is needed, and that the necessary inputs are received within the deadlines set.
• The bulk of the work undertaken on this project was in the stage building the two spreadsheets which are coloured purple and grey.  In practice, the suit of spreadsheets built was much more extensive.  These spreadsheets, at a click of a button, brought in the necessary data, performed any required calculations and created and validated output which could be read in by SAS.
• SAS then collated data from actuarial, accounting and assets, and pushed it all to the appropriate QRT Form.  As seen by the coloured arrows, one actuarial spreadsheet could have been used to produced data in more than one QRT.  It was a lengthy process to get the design of this process completed, agreed and signed off by the necessary stakeholders.

Kunal Patel

APR 2016