Welcome to the Erlang module
User manual

Version 3.1

Created by

Table of contents
  1. Introduction
  2. Functions
  3. Files
  4. Structure of the User Manual
  5. Versions of the Erlang Module
  6. How to Install the Online Version
  7. How to Install the Offline Version
  8. How to Integrate the Erlang Module
  9. Function Parameters
  10. Excel Functions
  11. Troubleshooting
  12. Demo Sheet Help
  13. Read Me Sheet
  14. Registration Sheet
  15. Erlang C Sheet
  16. Erlang X sheet
  17. Daily Staffing
  18. Including Shifts
Introduction

CCmath is a highly specialized software company that offers solutions for the most challenging contact-center setups. Please contact us at info@ccmath.com or visit our website on www.ccmath.com.

The Erlang functions that are provided by CCmath are Excel add-ins that are used by many contact centers as a planning aid. We are proud to mention that CCmath is the only supplier of the Erlang X, Blending and Erlang Chat add-ins worldwide.

The Erlang excel add-ins contains useful functions written in the very fast C++ programming language that are related to resource planning for contact centers. Multiple versions exist, from single function add-ins to 'the works': a complete add-in with all the planning functions mentioned below right at your fingertips.

Functions

Each function set was developed by specialists who work in the academia based on the theoretical Erlang models:

Files

The Erlang Excel add-in archive includes the following files:

The Excel demo sheet is an excellent starting point and an intuitive example of how the functions can be applied for calculating the service levels, day patterns, shifts, and intra-day distributions.

Structure of the User Manual

The current user manual is structured in four main parts:

Versions of the Erlang module

Depending on the customer's needs, CCmath commercializes two versions of the Erlang module, an online and an offline version. Both have the same functionalities and their own advantages. The online version offers initially a 28 days free trial, after wich you are offered a yearly license fee. This version calls the functions located on the CCmath server and is usable anywhere there is active internet connection. The online version of the Erlang module can be downloaded from our website. The offline version calls the functions locally and therefore it is faster than the online version, but it can only be used on one machine like personal laptop or computer.

How to install the online version

For the online version, it is only required to register in order to use the product. This can be done in the demo sheet, in the registration sheet. For more information, please check the registration sheet description.

How to install the offline version

For each licensed machine, the registration program has to be executed, which generates a unique code. The user must send this code via e-mail to CCmath. Afterwards, the user will receive the dll file. This dll file must be placed in the C:\Windows directory, or anywhere else as denoted by the path environmental variable. If you cannot do this, please check Problem 5 from the troubleshooting section.

How to integrate the Erlang module

In order for the Erlang module to function properly, there are some settings that must be configured. First, go to the Office button, Excel options, Trust center, Trust Center Settings, Macro Settings, select Disable all macros with notification, and press the ok button.

There are two different methods of integrating the Erlang functions into your own work. The first way is to open the demo sheet, delete all sheets and then insert your own data. The disadvantage is that you cannot use your own VBA code, because it is protected. The advantage is that the autocomplete works. The second way is to start from an existing document and include the xla. Depending on the version of Microsoft Office that you have, the following steps must be taken:

Function parameters

All the function parameters included in the Erlang module are described below. It is important to note that the time unit of all parameters must be the same.

Volume the average number of arrivals per unit of time. (Volume ≥ 0)
AHT the average handling time of a call. (AHT > 0)
Agents represents the number of agents; it can be real. (Agents ≥ 0)
Lines the total number of trunk lines, consisting of: lines in use by customers in service, and lines in use by customers that wait. (Lines ≥ Agents, integer)
TTA the time to answer is the maximum allowed waiting time. Customers that wait shorter than the TTA have received, per definition, a good service. The service level is defined as the percentage of customers that are served within the TTA. The time unit is the same as the others and, hence, is not necessarily in seconds! (TTA ≥ 0)
Patience the average time a customer is willing to wait in the queue. A simple estimator for the patience is calculated by dividing the total waiting time (including the waiting times of the abandoned customers) by the number of abandonments. It is important to filter out extreme values in advance. (Patience ≥ 0)
RetrialPercentage the probability that a customer who abandons, redials. (0 ≤ RetrialPercentage ≤ 1)
ServiceLevel the expected service level of an arbitrary non-blocked customer. (0 < ServiceLevel < 1)
ASA the average waiting time that an arbitrary customer with infinite patience would incur. (WaitingTime > 0)
BlockingProbability the expected blocking probability. (0 < BlockingProbability < 1)
Abandonment the probability that an arbitrary customer will abandon. (0 < Abandonment < 1)
SLDefinition a flag to switch between different modes of calculating the service level. 0 = virtual service level; 1 = answered; 2 = offered.
Variance the variance of the forecast error, i.e., the difference between the actual number of calls and the forecast. (Variance ≥ Volume).
Threshold the number of agents that are kept idle before taking outbound calls into service. (Threshold ≤ Agents)
Excel Functions

The Erlang Excel add-in represents mainly a collection of Excel functions written in the C++ programming language that are related to resource planning for calculating forecasts, staffing levels, and for generating shifts and rosters. Below you can find a description for each function and the necessary parameters. For more information regarding the parameters, please check the function parameters section.

Erlang X Functions

Some of the parameters in the Erlang X functions are optional. They are denoted by a *. Depending on their values, either the Erlang C, Erlang X, or a combination of the two models are used. Note that when a parameters is left empty, all remaining parameters must also be empty.

X_SLA(Volume, AHT, Agents, TTA, Lines*, Patience*, RetrialPercentage*, SLDefinition*, Variance*)
Calculates the expected service level.

X_AGENTS_SLA(ServiceLevel, Volume, AHT, TTA, Lines*, Patience*, RetrialPercentage*, SLDefinition*, Variance*)
Calculates the optimal number of agents such that the service-level objective is satisfied.

X_VOLUME(ServiceLevel, AHT, Agents, TTA, Lines*, Patience*, RetrialPercentage*, SLDefinition*)
Calculates the maximum average number of arrivals per unit of time such that the service-level objective is satisfied.

X_ASA(Volume, AHT, Agents, Lines*, Patience*, RetrialPercentage*)
Calculates the average waiting time.

X_AGENTS_ASA(ASA, Volume, AHT, Lines*, Patience*, RetrialPercentage*)
Calculates the optimal number of agents such that the waiting-time objective is satisfied.

X_ABANDON(Volume, AHT, Agents, Lines*, Patience*, RetrialPercentage*, Variance*)
Calculates the fraction of customers that abandon.

X_AGENTS_ABANDON(Abandonment, Volume, AHT, Lines*, Patience*, RetrialPercentage*)
Calculates the optimal number of agents such that the abandonment objective is satisfied.

X_RETRIALRATE(Volume, AHT, Agents, Lines*, Patience*, RetrialPercentage*)
Calculates the average number of customers that retrial per unit of time.

X_BLOCKING(Volume, AHT, Agents, Lines*, Patience*, RetrialPercentage*)
Calculates the fraction of customers that are blocked.

X_OCCUPANCY(Volume, AHT, Agents, Lines*, Patience*, RetrialPercentage*)
Calculates the occupancy of the agents.

X_AGENTS_BLOCKING(BlockingProbability, Volume, AHT)
Calculates the optimal number of agents such that the blocking objective is satisfied.

Blending Functions

In the blending model, agents will work on inbound as well as outbound calls. If an agent becomes available, they will prioritize inbound calls and will only take on outbound calls when Threshold other agents are left idle. Note that when Threshold equals Agents, this model is equivalent to the Erlang C model.

BL_SLA(Volume, AHT, Agents, TTA, Threshold)
Calculates the expected service level in a blending model.

BL_ASA_THRESHOLD(Volume, AHT, Agents, Threshold)
Calculates the average waiting time in a blending model.

BL_OCCUPANCY_THRESHOLD(Volume, AHT, Agents, Threshold)
Calculates the occupancy of the agents in a blending model.

BL_OUTBOUND_THRESHOLD(Volume, AHT, Agents, Threshold)
Calculates the average number of outbound calls per unit of time in a blending model.

BL_THRESHOLD(Volume, AHT, Agents, ServiceLevel, TTA)
Calculates the threshold such that the service-level objective is satisfied in a blending model.

BL_ASA_SLA(Volume, AHT, Agents, ServiceLevel, TTA)
Calculates the average waiting time based on the service-level objective in a blending model.

BL_OCCUPANCY_SLA(Volume, AHT, Agents, ServiceLevel, TTA)
Calculates the occupancy of the agents based on the service-level objective in a blending model.

BL_OUTBOUND_SLA(Volume, AHT, Agents, ServiceLevel, TTA)
Calculates the average number of outbound calls per unit of time based on the service-level objective in a blending model.

Chat Functions

In the model for chats, the distinguishing feature is that agents can handle multiple chats in parallel. The difference between the previous functions and the chat functions is the AHT parameter. The AHT should here be range of cells instead of a single number. The number of elements in the range determines the maximum number of concurrent chats that an agent can do.

CHAT_SLA(Volume, AHT, Agents, TTA, Lines, Patience)
Calculates the expected service level for chats.

CHAT_AGENTS_SLA(ServiceLevel, Volume, AHT, TTA, Lines, Patience)
Calculates the optimal number of agents such that the service-level objective is satisfied for chats.

CHAT_ASA(Volume, AHT, Agents, Lines, Patience)
Calculates the average waiting time for chats.

CHAT_AGENTS_ASA(ASA, Volume, AHT, Lines, Patience)
Calculates the optimal number of agents such that the waiting-time objective is satisfied for chats.

Troubleshooting

Problem 1: You are unable to register

Solutions:

a) Please make sure you have an active internet connection.

b) Add the document as a permitted program in your firewall's or antivirus software's settings. If it is already a permitted program, try deleting it from the list of permitted programs and try adding it again.

Problem 2: Output is -3

What does this mean: Your registration has expired or you are not registered yet.

Solutions:

a) If the registration has expired, please contact CCmath for payment options.

b) If you are not registered yet, register using the registration tab.

Problem 3: Output is -2

What does this mean: There is an internal error.

Solutions:

Please contact CCmath with the function that you have used, so we can check these problems into more detail.

Problem 4: Output is -1

What does this mean: There is an error with the input

Solutions:

a) Please check that each parameter is in the allowed set. If this is not the case, then it could be that the combination is infeasible, which might be the case for the following functions:

b) If you cannot solve this problem, please contact CCmath.

Problem 5: You cannot copy the dll file into C:\Windows.

Solutions:

a) Make sure that you have administrator rights.

b) Ask your system administrator to do this for you.

Demo sheet help

The demo sheet represents a basic application, which demonstrates the use of the Erlang functions. It is structured into six tabs:

Read Me Tab

The colors for input and output can be seen here. The input cells are orange, while the output cells are light grey.

The demo sheet contains four different sheets which are intuitively named:

Registration sheet (online version only)

If you want to register for using the Erlang plug-in, click the button. The registration is only required once. First you have to enter your name and then your e-mail address, press the ok button to store the data into our database.

When the message "Registration successful!" or "Product is already registered!" is shown, the functions on the other sheets can be used. If you get a different message, please contact CCmath.

Erlang C Tab

Erlang C model is defined as a queuing system where customers arrive to the system according to a Poisson process and are served by a fixed number of servers. Customers that find all servers busy are queued in a queue with infinite buffer capacity. The service times are exponentially distributed. This model uses the following input parameters from the historical data:

Within this Excel sheet, you can calculate three different scenarios using different input parameters:

a) Calculation of average speed of answer (ASA) and service level (SL)

In order to solve this task, the user must introduce the following parameters:

The output of this computation can be visualized in the average speed of answer cell and the service level cell.

Example:

We would like to determine the service levels and the average speed of answer. We know that on average we have 240 calls per hour, the average handling time is 3 minutes, the acceptable waiting time is 20 seconds, and the number of agents is 14.

The first step is to transform the given data into units that are expressed in minutes. The arrival rate should be transformed in minutes; in this case there will be 4 calls per minute and the acceptable waiting time will be 0.333 minutes.

The output of this computation can be visualized in the average speed of answer cell and the service level cell, which will be 0.723 min, respectively the service level will be 61.43%.

b) Calculation of number of agents on basis of service levels (SL)

In order to solve this task, the user must introduce the following parameters:

The output of this computation can be visualized in the number of agents cell.

Example:

We would like to determine the required number of agents. We know that on average we have 240 calls per hour, the average handling time is 3 minutes, the acceptable waiting time is 20 seconds, and the service level is 80%.

The first step is to transform the given data into units that are expressed in minutes. The arrival rate should be transformed in minutes; in this case there will be 4 calls per minute and the acceptable waiting time will be 0.333.

Given the input data, the output of the software will be the need of 16 agents, the average speed of answer will be 0.153 minutes and the productivity will be of 75%.

c) Calculation of number of agents on basis of ASA

In order to solve this task, the user must introduce the following parameters:

The output of this computation can be visualized in the number of agents cell.

Example:

We would like to determine the number of agents. We know that on average we have 240 calls per hour, the average handling time is 3 minutes, the acceptable waiting time is 20 seconds, and the average speed of answer is 20 seconds.

The first step is to transform the given data into units that are expressed in minutes. The arrival rate should be transformed in minutes; in this case there will be 4 calls per minute, the acceptable waiting time will be 0.333 minutes, and the average speed of answer will be 0.333 minutes.

The rounded output of this computation can be visualized in cell E6, which in this case will be 15 agents. The service level will be 77.13% and the productivity will be 80%.

Erlang X Tab

Erlang X model is a model where the queued customers are impatient and abandon the queue after an exponentially distributed amount of time. Abandoned customers redial with a certain probability. This model can also take forecast errors into account. The Erlang model uses the following input parameters from the historical data:

Within this Excel sheet, you can calculate three different scenarios using different input parameters:

a) Calculation of the average speed of answer and service level

In order to solve this task, the user must introduce the following parameters:

The output will include the following parameters:

Example:

We would like to determine the service levels and the average speed of answer. We know that on average we have 240 calls per hour, the average handling time is 3 minutes, the average patience of callers is 1 minute, the acceptable waiting time is 20 seconds, the number of lines is 100, and the number of agents is 14.

The first step is to transform the given data into units that are expressed in minutes. The arrival rate should be transformed in minutes; in this case there will be 4 calls per minute and the acceptable waiting time will be 0.333 minutes.

The output will be the following: the average speed of answer is 0.1 minutes, the service level is 87.10%, the probability of abandonment is 0.0743, the probability of having any blocked calls is 0, while the calculated productivity is 79.34%.

b) Calculation of the number of agents on basis of the service level

In order to solve this task, the user must introduce the following parameters:

The output will include the following parameters:

Example:

We would like to determine the required number of agents, given the fact we know that on average we have 240 calls per hour, the average handling time is 3 minutes, the average patience of callers is 1 minute, the acceptable waiting time is 20 seconds, the number of lines is 100, and the service level is 80% within 20 seconds.

The first step is to transform the given data into units that are expressed in minutes. The arrival rate should be transformed in minutes; in this case there will be 4 calls per minute and the acceptable waiting time will be 0.333 minutes.

The output will be the following: the average speed of answer is 0.146 minutes, the number of required agents is 13, the probability of abandonment is 0.1055, and the probability of having any blocked calls is 0, while the calculated productivity is 82.57%.

c) Calculation of the number of agents on basis of abandoned calls

In order to solve this task, the user must introduce the following parameters:

The output will include the following parameters:

Example:

We would like to determine the required number of agents given that we know that on average we have 240 calls per hour, the average handling time is 3 minutes, the average patience of callers is 1 minute, the acceptable waiting time is 20 seconds, the number of lines is 100, and the abandonments percentage is 5%.

The first step is to transform the given data into units that are expressed in minutes. The arrival rate should be transformed in minutes; in this case there will be 4 calls per minute and the acceptable waiting time will be 0.333 minutes.

The output will be the following: the number of agents is 16, the average speed of answer is 0.042 minutes, the service level is 94.54%, the probability of having any blocked calls is 0, while the calculated productivity is 71.25%.

Daily Staffing

In this sheet, the user can compute the daily staffing requirements in 30 minutes time intervals, by using the following input parameters:

The output will be the staffing requirements at every 30 minutes time intervals for 12 hours starting from 9:00 until 21:00.

Steps that must be followed

  1. If we would like to determine a pattern of a day, first we need to observe call volumes of 8 weeks of the same day during the day split in 30 minutes intervals, this data can seen starting from cell B41 until C64. Then, it is important to determine the total volume per each day of the sample data.
  2. We need to determine the fraction of calls for each time interval (cells K41:R64), and calculate the average fraction of calls per time interval of the sample data (cells T41:T64).
  3. Based on the average fraction of calls, we can calculate the number of calls that can occur for each 30 minute interval given our forecasted total daily call volume.
  4. By using the agentsServiceLevelErlangX function and the given input parameters, we can compute the number of agents.

Example:

If we combine these earlier mentioned input parameters:

with this daily pattern:

The output per 30-minute time interval will be as follows:

Including Shifts

This sheet extends the daily staffing by including agent shifts and includes the following input parameters:

Example:

If we have the following input parameters:

The output will be as follows:

where:

In addition to the intra-day performance, the Erlang module computes also the overall daily performance by calculating the total number of agent hours, the overall service level, and overall abandonments.