We’ll show you how to create in Excel an advanced call center performance dashboard. KPIs are necessary to track the performance.
Our objective: Build a one-page excel kpi dashboard for tracking the actual status of key performance indicators.
Before we start, we’ll introduce you the most used kpis in call centers.
Call center performance dashboard – Overview
Check the actual status of
• Time to answer for the selected week
• Abandon rate for the selected week
• First call resolution for the selected week
• The period (average of the branch) from the actual week to actual week + 3 months
Let us see a quick dashboard overview:
Put the results into our kpi dashboard in Excel
We are divided this excel model into three separate worksheets for the call center performnce dashboard, the main dashboard sheet, the input data and the calculation area.
It is one of the easiest ways to build a clear and structured model.
The implementation of the raw data and calculated value is very easy: The main dashboard worksheet with the all figures is linked directly to the data table.
Key Performance Indicators for call center
Using these key performance indicators we can track and trace the overall service performance.
Time to answer: this performance dimension usually expressed in second this is the time from when a call is started until it is responded by a customer service agent. Using the average call time to answer telephone call is the base of calculation.
Abandon Rate of incoming calls: we measure this key performance indicator as a % of the number of callers who are cut off, before they touch an agent who answers their call. How we calculate this kpi? : [numbers of abandoned calls while the customer waiting] / [total incoming phone calls]
Use this formula to calculate FCR (First Call Resolution) value = [issue solved by first call] / [total issues] this is a very important dimension (my opinion it is hard to tracking), expressed as a percentage, the number of those calls and that they will be responded to at the first try during the call. In this case do not require the customer to call back.
Instructions to create call center performance dashboard
1. In this example we’ll use weekly basis.
With fixed duration the calculated KPI is based on a single duration. You must specify the start and end dates – for example from 1 January 2017. to 31 January. If you specify only a start date the KPI will be calculated from the specified date to the current date.
If you specify only the end date the KPI will be calculated from the introduction of the process to the specified end date.
2. On the calculation sheet we’re going to use VLOOKUP to find values from data sheet by kpis based on selected week.
3. A brand new feature available in Office 2016 are Icons. To insert a new icon to improve dashboard’s visual quality find Excel Icons in the Insert tab of the ribbon in the Illustrations group.
This feature is available in subscription versions of Office 365.
4. Choose your favourite picture and put it into the dashboard sheet. There’s a gallery of icons available and they’re grouped into categories to make them easily searchable.
5. To display the results week by week we’ll use spin button form control. Go to Developer tab and click Insert.
6. Right click on the Spin Button and click on ‘Format Control’.
This will open a Format Control dialogue box.
Go to ‘Control’ tab, and make the following changes to create dynamic list:
Minimum Value: 1, Maximum Value: 48, Incremental Change: 1
7. On the cell link box link the cell which contain the actual week’s value. In this example data!$C$4
8. Let’s the chart section! To create a bar chart select the $G$5:$G$16 range on the calculation sheet.
On the Series option use these values: 32% for gap width and 0% for Series Overlap.
9. To create line chart for average execute the following steps:
Go to calculation sheet and select the green marked range.
10. Repeat Step 8 to Step 9 to create the combo charts for abandon rate and FCR.
11. Let us see the kpi setup on the bottom left corner! Creating KPI targets that help you determine progress toward your goals. Each cell is linked separately to calculation sheet.
12. Finally jump to the bottom right corner to visualize the variance (+/-) by agents on the selected (actual) week.
13. On the Home tab, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules.
Select a formatting style. In this example we’ll use light red fill for negative variance and light green fill for positive varince.
After you have applied a quick style, select data or range, click Conditional Formatting on the ribbon, and then click Manage Rules to manually update the selected rules.
The call center performance dashboard is ready to use!
Conclusion: Measure your company’s performance
The clear indicators of the company’s performance are the changes in the sales volumes. Management information systems provide to these changes’ availability in time and in proper structure.
When the exploration of relationships between sales data is typically a company’s inside task, the kpi dashboards mostly are capable to evaluate the performance only retrospectively. When defining a key performance indicator (KPI) it is appropriate to notice the characteristics of the company.
These indicators are perfect in areas of sales, marketing and IT. KPIs are based on business goals.
A business goal is such a high level objective, which is measurable and expressible in quantities that can be translated to a KPI with which a company can measure the aspects of a process to a predefined target.
In the next article we discuss the main indicators and the level of success can be determined by comparing the actual results.
The evaluation has two key objectives: evaluate, classify, rank and develop, change. In addition it can serve as a basis for promotion, pay raise, relocation or prehaps dismiss.
You can download the template if you you are in hurry.