ABC Call Volume & CX Analytics

Optimizing Manpower and Enhancing Customer Experience with Advanced Excel

Profile Picture

Anirudh Chaudhary

CX Analyst | Data-Driven Strategist

This project provides an in-depth analysis of inbound call data for ABC Insurance. Using Advanced Excel, I derived actionable insights on call volume trends, agent performance, and customer behavior to build a robust model for optimizing manpower allocation and significantly improving customer experience.

Avg. Call Duration
0
Minutes
Total Calls Analyzed
0
From Dataset
Target Abandon Rate
0%
Down from 30%
Peak Agents Required
0
For 10% Abandon Rate
ABC Call Volume Dashboard Thumbnail

Task 1: Average Call Duration per Time Bucket

+

Objective & Approach

To determine the average call duration per hour, I used an Excel Pivot Table to group calls by hour and calculate the average of the 'Duration' field for each group.

Key Formula

This calculation is equivalent to using `AVERAGEIFS`:

=AVERAGEIFS(Table1[Duration], Table1[Time], ">="&[Start], Table1[Time], "<"&[End])

Results Table

Time BucketAvg. Duration (s)
9-10 AM280
10-11 AM310
11-12 PM295
12-1 PM270
1-2 PM260
2-3 PM285
3-4 PM300
4-5 PM320
5-6 PM290
6-7 PM275
7-8 PM265
8-9 PM250

Task 2: Call Volume Analysis

+

Objective & Approach

To find call volume patterns, I used a Pivot Table to count calls per hour. This revealed two distinct peak periods, which is crucial for staffing decisions.

Results Summary

The analysis clearly shows call volume peaking between 11 AM - 12 PM and again at 3 PM - 4 PM.

Results Table

Time BucketTotal Calls
9-10 AM1250
10-11 AM1800
11-12 PM2200
12-1 PM1950
1-2 PM1600
2-3 PM1850
3-4 PM2400
4-5 PM2100
5-6 PM1750
6-7 PM1500
7-8 PM1300
8-9 PM1100

Task 3: Day Shift Manpower Planning

+

Objective

To create a staffing model that calculates the minimum agents needed per hour to reduce the call abandon rate from 30% to a target of 10%.

Methodology & Formulas

A multi-step calculation model was built in Excel based on the project assumptions:

  1. Agent Availability: Calculated net productive hours per agent per month. 
    Hrs/Agent = ((30 - (30/7*1) - 4) * (9 - 1.5)) * 60%
  2. Workload Calculation: Determined total agent hours needed per bucket to handle 90% of calls. 
    Workload = (Total Calls * 90%) * (Avg. Duration / 3600)
  3. Agents Required: Rounded up the result to ensure sufficient coverage. 
    Agents = CEILING(Workload / (Hrs per Agent / 30), 1)

Results: Minimum Agents per Hour (Day Shift)

Time Bucket Agents Required (10% Abandon Rate)
9 AM - 10 AM15
10 AM - 11 AM22
11 AM - 12 PM25
12 PM - 1 PM22
1 PM - 2 PM18
2 PM - 3 PM21
3 PM - 4 PM28
4 PM - 5 PM26
5 PM - 6 PM21
6 PM - 7 PM18
7 PM - 8 PM15
8 PM - 9 PM12

Task 4: Night Shift Manpower Planning

+

Objective

To propose a staffing plan for the night shift (9 PM - 9 AM) to handle the estimated 30% call overflow from daytime, maintaining the 10% abandon rate target.

Methodology in Excel

I calculated the total night call volume (30% of total day calls), distributed this volume across night-time buckets based on given percentages, and then applied the same manpower model from Task 3 to find the required agents for each night bucket.

Results: Minimum Agents per Hour (Night Shift)

Time Bucket Agents Required (10% Abandon Rate)
9 PM - 11 PM5
11 PM - 1 AM4
1 AM - 3 AM2
3 AM - 5 AM2
5 AM - 7 AM3
7 AM - 9 AM4
Download Full Report (PDF) Download Excel Model

Technical Skills Applied

Advanced Excel

Pivot Tables, VLOOKUP, complex formulas, and modeling.

Quantitative Analysis

Workload calculation and staffing level optimization.

Data Visualization

Creating insightful charts from raw data to tell a story.

Business Acumen

Translating data insights into strategic business decisions.

Bank Loan Case Study

An in-depth analysis of bank loan data to identify key drivers of default and build a predictive risk model.

View Case Study