ABC Insurance Company

By Sumit K Prajapat

Project 

Description

This project is about analyzing the calls received by ABC Insurance Company. Our main goals are to find the average call duration for different times of the day, visualize the number of calls over time, and propose a plan to handle calls efficiently.

Objectives:

• Calculate the average call duration for each time period.
• Create a chart to show how many calls are received at different times.
• Plan the number of agents required to reduce the abandoned calls and improve customer experience.
• Develop a strategy for managing night calls effectively.

Approach:

• First, we will find out how long calls usually last during the day and night.
• Then, we'll make a graph to see when the most calls come in.
• Next, we'll calculate how many agents we need to make sure most calls are answered.
• Finally, we'll come up with a plan to handle night calls better so customers don't feel ignored.

​

​

Approach

• Approach:
• Data Collection: Gathered the dataset containing inbound call information from ABC Insurance Company.
• Average Call Duration: Calculated the mean call duration for each time bucket.
• Call Volume Analysis: Created a column chart in Excel to visualize call volume trends over time.
• Manpower Planning: Utilized Excel formulas to determine the minimum number of agents needed for each time bucket, aiming to reduce the abandon rate.
• Night Shift Planning: Analyzed call distribution between day and night, proposing a manpower plan for the night shift.
• Visualization: Presented findings with Excel-generated charts and graphs.
• Reporting: Compiled a concise report summarizing insights and recommendations for improved customer service and reduced abandon rates.

​

Tech Stack Used

• Software: Microsoft Office 365
• Purpose: Microsoft Excel was used for data manipulation, data cleaning, calculations, and data visualization. It provided a user-friendly interface and a wide range of functionalities, making it suitable for tasks such as calculating average call duration, creating graphs to visualize call volume trends, and using formulas to perform manpower planning.
• Excel is to be a versatile tool for handling the dataset and genarating meaningful insights and reports for the project.

​

​

Insight

• Average Call Duration: We determined the average call duration for each time bucket, helping in resource allocation and agent management.
• Call Volume Trends: Visualizing call volume revealed peak call hours, aiding in optimizing agent schedules for better service.
• Manpower Planning: We proposed the minimum agents needed per time bucket to reduce abandon rates and ennsure timely responses.
• Night Shift Management: A manpower plan for the night shift was devised to enhance customer experience.
• Overall, the project enables ABC Insurance to improve call center operations, enhance customer service, and increase satisfaction.

​

​

Average Call Duration

​

​

• Time Bucket 1 (9 am - 12 pm): [insert average duration in seconds].
• Time Bucket 2 (12 pm - 3 pm): [insert average duration in seconds].
• Time Bucket 3 (3 pm - 6 pm): [insert average duration in seconds].
• Time Bucket 4 (6 pm - 9 pm): [insert average duration in seconds].
• These insights will aid in understanding agent performance and optimizing call handling strategies during different time periods.

​

Call Volume Analysis

​

​

• We have created a chart showing call volume trends with time represented in hourly buckets. This visual representation will help optimize agent schedules and enhance customer service by identifying peak call hours in our presentation

Manpower Planning

• To reduce the abandon rate to 10% and ensure that at least 90 out of 100 calls are answered, we need to allocate a minimum of [insert number] agents during each time bucket from 9 am to 9 pm.

Night Shift Manpower Planning

• To improve customer experience, we recommend allocating sufficient agents to handle the night calls. 

Result

• In this project, we analyzed ABC Insurance's calls and found some cool stuff! We calculated how long calls usually last during the day, made graphs to see when most calls come in, and proposed the right number of agents needed to handle calls and reduce abandoned ones. We even made a plan for the night shift to serve customers all the time. Using Excel for math. ABC Insurance Comapany can now improve their call center.
• keep customers happy!

​

Thank You 

By Sumit K Prajapat

​

Drive Link: Excel File

Vedio Link: First Video

Second video

Third vedio

This video link is for our analysis explanation