Develop BI tools for analyzing insurance programs

Our customer, an international industrial group, has numerous insurance programs underwritten and managed at Group level and rebilled to sites and entities worldwide. Program data and premiums allocated by entity are recorded and calculated in shared Excel files, which are difficult to manage. These multiple files are also a source of error, and it is difficult to draw up comparative analyses (between entities, annual trends, etc.) in a reliable and easily reproducible way.

For this customer, we developed and implemented a unique Power BI model for consolidating and analyzing data from the main insurance programs (premiums, brokerage fees, taxes, limits, deductibles, etc.) and their rebilling to the various Group sites and entities. The Power BI summary reports developed enable the various insurance managers to quickly carry out most of their analyses and reports in a repeatable and standardized way, based on up-to-date and shared source data.

This tool is a real decision-making aid, essential to a proactive and effective risk management policy, as it enables insurance managers to re-appropriate the data they need to define investment strategy, program architecture and negotiate renewals with insurers.

Accessible on the move (PC, tablet, smartphone), the reports are based on multiple, heterogeneous internal and external data sources emanating from various tools and Excel files. This source data is now structured and centralized in a dedicated workspace, which hosts files that have been validated and approved by the Insurance Department to guarantee their reliability.

Main achievements

• Collection of internal and external data from the Group’s 10 main insurance programs
  
• Construction of a data model enabling multi-program analysis by country, entity, site, year, etc.
  
• Conducted workshops with key users and program managers to understand their needs and define target dashboards
  
• Development of the data model and dashboards in PowerBI
  
• Putting data sources and reports online in dedicated workspaces
  
• Training and support of users in the use of PowerBI

Key figures :

30 Dashboards developed in Power BI

10 insurance programs integrated into the model

5 years of reliable historical data integrated into the initialization process