In this comprehensive tutorial we’ll show you how to create a user-friendly call center performance template in Excel.
Build a one-page call center template for tracking the actual status of key performance indicators and design a user friendly contextual help for better UX using a VBA macro.
Before we start, we’ll introduce you the most used indicators in our example. Using these metrics, 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.
- 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.
- FCR: Use this formula to calculate First Call Resolution = [issue solved by first call] / [total issues]
How to create call center performance template?
We’ll put the weekly results by agents (Time to answer, Abandon rate, FCR) into the top left area. Bottom-left section contain the KPI setup.
Part 3 will show the selected period (average of the branch) from the actual week to actual week + 3 months. Finally, the individual variance will be displayed on the bottom-right section.
Detailed instructions to create call center template:
1. We are divided this model into three separate worksheets for the template, the main sheet, the input section and the calculation area. It is one of the easiest ways to build a clear and structured model.
The implementation of the unprocessed data and calculated value is very easy: The main worksheet with the all figures is linked directly to the data table.
2. In this example we’ll use weekly basis. Let us see the data worksheet. 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 performance indicator 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.
3. On the calculation sheet we’re going to use VLOOKUP formula to find values from data sheet by values based on selected week.
4. Check the bottom-left corner! Let us see the metrics. Creating targets that help you determine progress toward your goals. Each cell is linked separately to the calculation sheet.
5. Okay, let us see how we build the top right section of template! This part is a weekly performance scorecard. We get the values from the linked worksheet ‘calc’.
With conditional formatting, we can visually determine when something is breaking a business rule. We’ll use shapes to highlight the variances.
6. Apply conditional formatting for the selected cells.
Execute the following steps:
• Select the given range. (In this case the time to answer row)
• Jump to Home tab and in the Styles group, choose Conditional Formatting.
• Click Icon Sets, Shapes, Traffic lights
• Enter the values using formulas and click OK
7. Repeat the last step and apply conditional formatting for the abandon rate and FCR.
8. A brand new and useful feature available in Office 2016 are Icons. To insert a new icon to improve template’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.
9. Choose your favorite picture and put it into the sheet. There’s a gallery of icons available and they’re grouped into categories to make them easily searchable.
10. To display the results week by week we’ll use spin button form control. Go to Developer tab and click Insert. Click to spin button form control icon.
11. Right click on the Spin Button. The format control pop-up box will appear. Click on ‘Format Control’ tab.
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, Page Change: 10
12. On the cell link box link the cell which contain the actual week’s value. In this example select cell $C$4 on the data sheet. Now validate it using the arrow button.
13. Let’s see the chart section! We’ll track the weekly performance using easy readable charts. To create charts select the $G$5:$G$16 range on the calculation sheet.
Go to Insert Tab on the ribbon and select the Recommended Chart section.
What are recommended charts in Excel? Select the source range in your worksheet and click this button to get a customized set of charts that Excel think will fit best with your data.
14. Go to All Charts menu and select the combo chart. Choose the chart type axis for your data series. Choose the clustered column type for series1 and Line chart type for series2.
Click OK to insert a combination chart.
15. Copy the chart to main worksheet area and clean it up! You should to remove the unnecessary elements. Right Click on the chart and select Format Chart Area.
Use this method on the Plot Area too.
16. First format the bar chart! Right click on the bar chart and choose the Format Data Series option.
On the Series option use these values: 33% for gap width and 0% for Series Overlap. Choose the right color too.
17. As a next step format the line chart! Right click on the line chart and choose the Format Data Series option.
On the Series option use solid line and red color. Finally set the compound type to dash. The average line is done.
18. Repeat Step 13 to Step 17 to create the combo charts for abandon rate and FCR.
19. Here are the formatted charts below:
20. Optional steps to visualize the variance (+/-) by agents on the selected (actual) week. Go to the bottom right corner.
On the Home tab, click the arrow next to Conditional Formatting, and then click Data Bar.
Select a formatting style. In this example we’ll use light red fill for negative variance and light green fill for positive variance.
After you have applied a quick style, select cells or range, click Conditional Formatting on the ribbon, and then click Manage Rules to update the selected rules manually.