To calculate the realized and unrealized gains and losses (RGLOE/UGLOE)
on treaty transactions due to the effect of foreign exchange fluctuations,
and book these values to the General Ledger to form part of performance
analysis and investment decisions for the business.
The Reinsurance system being used to manage treaties was not able to capture and calculate the effect of foreign
exchange fluctuations in the way that the company needed. This meant that a large effort was required by the accountants
every month to collate the data, perform the calculations and then post the data into the General Ledger.
This manual data collection and manipulation was time consuming, prone to error and not easily repeatable.
Nor did it allow for the information to be effectively reused for any meaningful analysis.
Build a Data Mart that extracts data daily from the Reinsurance transaction processing system. Perform
the earnings calculations in the Data Mart and store the data over useful analysis dimensions such as Time,
Treaty, Office, Balance Type Gross/Ceded/Net and Original or Reported currency.
Reinsurance transactions stored in the data mart include:
- Foreign Exchange
- Integration Services packages that extract Reinsurance data and perform calculations on a daily basis
- SQL Server Data Mart to store reinsurance data transformed and aggregated for analysis
- Analysis Services cubes to allow slicing and dicing of data across different dimensions
- Excel spreadsheets over cubes to allow for data access and ad-hoc querying