Insurance Data - Branch Performance Analysis

Project Overview

This comprehensive analysis project aims to provide a holistic view of the branch's operations by intertwining insurance market dynamics with employee performance metrics. This analysis shines a spotlight on employee performance and evaluation of key metrics for the branch. A Branch dashboard needs to be created to discuss New and renewal business number with each branch. This dashboard will be discussed between Corporate team and Individual branch heads.

Data Source

The branch's transactions and other data have been split up across 6 csv files : invoices, brokerage, fees, Individual budgets, meeting, opportunity

Tools Used

• Microsoft Excel - Data Cleaning using Power Query, Data Merging and Basic Dashboard creation
• Power BI - Visualisation using graphs and charts - Dashboard and Reporting
• Tableau - Visualisation using graphs and charts - Dashboard and Reporting
• MySQL - Quality Analysis

Data Cleaning/ Preparation

• Data Loading and Inspection
• Handling missing values
• Removing duplicates
• Deriving data from existing data using Excel formulae
• Formatting the data

EDA - Exploratory Data Analysis

Exploring data sets to represent the following KPIs and metrics in the dashboard:

1. Number of invoices generated by account executives in each income_class category
2. Target assigned, number of policies achieved and invoiced across each income_class category
3. Top open opportunities available
4. Total global meetings conducted by account executives
5. Revenue generated at each stage of opportunity

Quality Analysis using MySQL

The data present in csv files were imported to MySQL database by creating tables in database and importing the dataset from csv using LOAD DATA INFILE command. The queries used are available in the .sql files in the repository.

Key Findings/ Results

1. Only 1 employee has been contributing to 40% of invoices generated in the whole branch
2. The branch has seen significant improvement in the number of global meetings conducted in the year 2020 - 91% increase
3. The branch has exceeded the budgeted target assigned for the 'Cross-Sell' and 'Renewal' category. 'New' catgory is yet to reach the target assigned.
4. Around 5920K of revenue can be generated from the opportunities that are in the 'Qualify Opportunity' stage
5. In the available opportunities, 'Fire' opportunity has the potential to generate the highet revenue, but it does not qualify for the open opportunity.
6. 'Employee Benefits' and 'Fire' product categories cover more than 50% of the opportunities

Recommendations:

Based on the above results/ findings, following actions are recommended to improve the performance of the branch:

1. Assign targets to all employees so that more invoices are generated.
2. Employ strategies to reach the budgeted target for the 'New' category of policies
3. Generate invoices from the opportunities available in the 'Qualify opportunity' stage
4. Employ strategies to move 'Fire' opportunity from Negotiate stage to open opportunity stage as it generates the highest revenue
5. Find ways to create open opportunities across all product categories.