Foreign Exchange

Business Challenge

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:

  • Premiums
  • Expenses
  • Claims
  • Cash
  • Foreign Exchange

Solution Architecture

  • 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


Business Value Realized

  • Data is available daily, no waiting for quarter end processing
  • Foreign exchange calculations are standard and repeatable
  • Data is accurate, and all decision makers use the same data source
  • The data source is easily accessed by anyone who needs it, using tools that they are comfortable with such as spreadsheets
  • Accountants can focus on analysis and decision support, instead of data acquisition and manipulation

Cost: ~$70,000

Cost Savings/ ROI

  • ~$10,000 Accountants time is not spent on data acquisition and manipulation.
  • ~$50,000+ Earnings information is accurate and allows for more effective decision making.
  • ~$20,000-$30,000 SQL Server software was already installed and available for use in this project so no additional software costs were incurred, instead the client's existing software investment was leveraged to provide further value to the business.
  • ~$5,000 Users don't need to learn new tools to effectively access and use the data.
  • $Priceless! Establishing a relationship with an on-island software vendor that understands the business challenges, can provide thought leadership and vision with solution architecture addressing current and future needs, and who is genuinely invested in a partnering type engagement with the client.

Future Capability

  • Data Mart structure can be extended to enable analysis on other dimensions such as Line of Business, Broker, Client
  • General Ledger Integration could be further automated
  • Operational and Management Reporting off existing or extended cubes.
  • Dashboards over the cube data to allow for Operational monitoring, Executive information, Client facing data, collaboration, self-service business intelligence.

The Team


Project Manager

Business Intelligence Consultant

Business Intelligence Consultant

Know someone that could use this solution? Share this page