Erlang Calculations for Microsoft Excel


Erlang for Excel - ErlangXL97.xla
This document applies to Erlang for Excel version 1.8


This spreadsheet add-in gives you the ability to work with standard Erlang-C calculations within Microsoft Excel. The functions provided are:
  • Abandoned - the percentage of callers who will abandon after a given time.
  • Agents - calculate the number of agents required to achieve a SLA target.
  • AgentsASA - calculate the number of agents required to achieve an ASA target.
  • Average Speed to Answer (ASA) - the average time taken to connect to an agent.
  • Call Capacity – the maximum number of calls that can be handled.
  • Fractional Agents – calculate the number of agents required to achieve a SLA target.
  • Fractional Call Capacity – the maximum number of calls that can be handled.
  • Queue Size - average number of calls queuing.
  • Queue Time - the average waiting time for those calls that are not answered immediately.
  • Queued - the percentage of calls that will be queued.
  • Trunks - the number of telephone trunks required for a given call load.
  • Service Time – the time within which a certain percentage of calls will be answered.
  • SLA - the actual percentage of calls answered with the target time.
  • Usage - percentage of agent’s time spent on the telephone.


 The add-in program must be loaded in Excel for these functions to work. Select Tools/Add-In from the menu, you should see Erlang for Excel in the list of available Add-ins - ensure that the box to left of this entry is ticked. If the Erlang Add-in is not shown, click on the Browse button and choose either C:\Program Files\Microsoft Office\Office\Library\ErlangXL97.xla (for Excel 97) or C:\Windows\Application Data\Microsoft\AddIns (for Excel 2000).
Abandon Function

 Function Abandon(Agents As Single, AbandonTime As Single, CallsPerHour As Single, AHT As Integer) As Single

  • Agents is the number of agents available
  • AbandonTime is time in seconds before the caller will normally abandon
  • CallsPerHour is the number of calls received in one hour period
  • AHT (Average handle time) is the call duration including after call work in seconds e.g. 180

Returns the percentage of calls that will abandon after the abandon time given. It is assumed that callers will wait for an answer and only abandon if waiting longer than the AbandonTime.

 e.g. =Abandon(8,30,100,180) returns a value of 0.09 (9%)
Agents function

 Function Agents(SLA As Single, ServiceTime As Integer, CallsPerHour As Single, AHT As Integer) As Integer

  • SLA is the % of calls to be answered within the ServiceTime period e.g. 0.85 (85%)
  • ServiceTime is target answer time in seconds e.g. 15
  • CallsPerHour is the number of calls received in one-hour period
  • AHT is the call duration including after call work in seconds e.g. 180 (3 minutes)

Returns the number of agents required to achieve the correct SLA.

 e.g. =Agents(0.85,15,100,180) returns the value of 8
AgentsASA function

 Function AgentsASA(ASA As Single, CallsPerHour As Single, AHT As Integer) As Integer

  • ASA is the average speed of answer in seconds
  • ServiceTime is target answer time in seconds e.g. 15
  • CallsPerHour is the number of calls received in one-hour period
  • AHT is the call duration including after call work in seconds e.g. 180 (3 minutes)

Returns the number of agents required to achieve the correct ASA.

 e.g. =Agents(15,100,180) returns the value of 8
ASA Function

 Function ASA(Agents As Single, CallsPerHour As Single, AHT As Integer) As Single

  • Agents is the number of agents available
  • CallsPerHour is the number of calls received in one-hour period
  • AHT (Average handle time) is the call duration including after call work in seconds e.g. 180

Returns the Average Speed to Answer for the given number of agents.

 e.g. =ASA(8,100,180) returns a value of 10
CallCapacity Function

 Public Function CallCapacity(NoAgents As Single, SLA As Single, ServiceTime As Integer, AHT As Integer) As Single

  • NoAgents = the number of agents available
  • SLA = target percentage of calls to be answered e.g. 0.85 = 85%
  • ServiceTime = target answer time in seconds e.g. 15
  • AHT = (Average handle time) is the call duration including after call work in seconds e.g. 180

The CallCapacity function calculates the maximum number of calls that can be processed by the given number of agents.

 e.g. =CallCapacity(8,0.85,15,180) returns a value of 103
FractionalAgents function

 Function FractionalAgents(SLA As Single, ServiceTime As Integer, CallsPerHour As Single, AHT As Integer) As Single

  • SLA is the % of calls to be answered within the ServiceTime period e.g. 0.85 (85%)
  • ServiceTime is target answer time in seconds e.g. 15
  • CallsPerHour is the number of calls received in one hour period
  • AHT is the call duration including after call work in seconds e.g. 180 (3 minutes)

Returns the number of agents (and fractions of agents) required to achieve the correct SLA.

 e.g. =FractionalAgents(0.85,15,100,180) returns the value of 7.83

 The implementation of a fractional agent calculation has no validity within the Erlang model, agents can only be added in whole numbers. It is included for situations where a more accurate costing model of agents is required.
FractionalCallCapacity function

 Function FractionalCallCapacity(NoAgents As Single, SLA As Single, ServiceTime As Integer, AHT As Integer) As Single

  • NoAgents is the number of fractional agents available as a decimal, e.g. 8.5
  • SLA is target percentage of calls to be answered e.g. 0.85 = 85%
  • ServiceTime is target answer time in seconds e.g. 15
  • AHT (Average handle time) is the call duration including after call work in seconds e.g 180 (3 minutes)

The FractionalCallCapacity function calculates the maximum number of calls that can be processed by the given number of agents. The number of agents in this case can be a fractional (decimal) number.

 e.g. =FractionalCallCapacity(8.5,0.85,15,180) returns a value of 110

 The implementation of a fractional call capacity calculation has no validity within the Erlang model, where agents can only be added in whole numbers. It is included for situations where a more accurate costing model of agents is required.
Queued Function

 Function Queued(Agents As Single, CallsPerHour As Single, AHT As Integer) As Single

  • Agents is the number of agents available
  • CallsPerHour is the number of calls received in one-hour period
  • AHT (Average handle time) is the call duration including after call work in seconds e.g. 180

Returns the percentage of calls that will have to queue.

 e.g. =Queued(8,100,180) returns a value of 0.17 (17%)
QueueSize Function

 Function QueueSize(Agents As Single, CallsPerHour As Single, AHT As Integer) As Single

  • Agents is the number of agents available
  • CallsPerHour is the number of calls received in one-hour period
  • AHT (Average handle time) is the call duration including after call work in seconds e.g. 180

Returns the average queue size.

 e.g. =QueueSize(7,100,180) returns a value of 1
QueueTime Function

 Function QueueTime(Agents As Single, CallsPerHour As Single, AHT As Integer) As Single

  • Agents is the number of agents available
  • CallsPerHour is the number of calls received in one-hour period
  • AHT (Average handle time) is the call duration including after call work in seconds e.g. 180

Returns the average queue time for those calls that will queue, compare with the ASA function that is the average time for all calls including those which get answered immediately.

 e.g. =QueueTime(8,100,180) returns a value of 60 (1 minute)
ServiceTime Function

 Public Function ServiceTime(Agents As Single, SLA As Single, CallsPerHour As Single, AHT As Integer) As Single

  • Agents = the number of agents available
  • SLA = target percentage of calls to be answered e.g. 0.85 = 85%
  • CallsPerHour = the number of calls received in one hour period
  • AHT = (Average handle time) the call duration including after call work in seconds e.g. 180

The ServiceTime function calculates the average waiting time in which a given percentage of the calls will be answered. This can be considered to be the reverse of the SLA function.

 e.g. With 8 agents, 100 calls per hour with an average handle time of 180 seconds the time in which 85% of calls will be handled will be:
=ServiceTime(8,0.85,100,180) returns a value of 7 seconds
SLA Function

 Function SLA(Agents As Single, ServiceTime As Single, CallsPerHour As Single, AHT As Integer) As Single

  • Agents is the number of agents available
  • ServiceTime is target answer time in seconds e.g. 15
  • CallsPerHour is the number of calls received in a one-hour period
  • AHT (Average handle time) is the call duration including after call work in seconds e.g. 180

Returns the actual service level achieved for the given number of agents

 e.g. =SLA(8,15,100,180) returns a value of 0.87 (87%)
Trunks Function

 Function Trunks(Agents As Single, CallsPerHour As Single, AHT As Integer) As Integer

  • Agents is the number of agents available to answer calls
  • CallsPerHour is the number of calls received in one-hour period
  • AHT (Average handle time) is the call duration including after call work in seconds e.g. 180

Returns the number of telephone lines required to service a given number of calls and agents without the caller receiving a busy tone. This equates to the number of agents available plus the maximum queue size.

 e.g. =Trunks(8,100,180) returns a value of 14
Utilisation Function

 Function Utilisation(Agents As Single, CallsPerHour As Single, AHT As Integer) As Single

  • Agents is the number of agents available
  • CallsPerHour is the number of calls received in one-hour period
  • AHT (Average handle time) is the call duration including after call work in seconds e.g. 180

Returns the utilisation percentage for the given number of agents.

 e.g. =Utilisation(8,100,180) returns a value of 0.63 (63%)
ErlangC Function

 Function ErlangC(Servers As Single, Intensity As Single) As Single

  • Servers = Number of agents
  • Intensity = Arrival rate of calls / Completion rate of calls
  • Arrival rate = the number of calls arriving per hour
  • Completion rate = the number of calls completed per hour
  • Intensity is also the traffic rate in Erlangs.

This formula gives the percentage likelihood of the caller being placed in a queue. It is shown here for completeness, you should not have to call this function directly.

 E.g. If there are 100 calls per hours which have an average duration of 3 minutes and are presented to 8 agents,
Intensity = 100 / (60 / 3)
= 100 / 20
= 5
=ErlangC(8,5) returns a value of 0.167 (17%)

 This is more simply performed using =Queued(8,100,180)
ErlangB Function

 Function ErlangB(Servers As Single, Intensity As Single) As Single

  • Servers = Number of telephone lines
  • Intensity = Arrival rate of calls / Completion rate of calls
  • Arrival rate = the number of calls arriving per hour
  • Completion rate = the number of calls completed per hour
  • Intensity is also the traffic rate in Erlangs.

The ErlangB formula calculates the percentage likelihood of the call being blocked, i.e. that all the trunks are in use and the caller will receive a busy signal (also called the Blocking formula). It is shown here for completeness, you should never have to call this function directly.

 E.g. If there are 100 calls per hours which have an average duration of 3 minutes and there are 8 telephone trunks,
Intensity = 100 / (60 / 3)
= 100 / 20
= 5
=ErlangB(8,5) returns a value of 0.07 (7%)
ErlangBExt Function

 Function ErlangBExt(Servers As Single, Intensity As Single, Retry As Single) As Single

  • Servers = Number of telephone lines
  • Intensity = Arrival rate of calls / Completion rate of calls
  • Arrival rate = the number of calls arriving per hour
  • Completion rate = the number of calls completed per hour
  • Retry = % of blocked callers who will retry immediately e.g. 0.10 (10%)

The Extended ErlangB formula calculates the percentage likelihood of the call being blocked, i.e. that all the trunks are in use and the caller will receive a busy signal. The extended version has the addition of specifying the percentage of callers who, when their call is blocked, will retry immediately. This has the effect of increasing the traffic intensity and therefore the likelihood of blocking. It is shown here for completeness and for reference. This function is used mainly for sizing telephone trunk capacity.

 E.g. If there are 100 calls per hours which have an average duration of 3 minutes, there are 8 telephone trunks and 20% of blocked callers will retry,
Intensity = 100 / (60 / 3)
= 100 / 20
= 5
=ErlangBExt(8,5,0.2) returns a value of 0.08 (8%)
EngsetB Function

 Public Function EngsetB(Servers As Single, Events As Single, Intensity As Single) As Single

  • Servers = Number of telephone lines
  • Events = Number of calls
  • Intensity = average intensity per call

The EngsetB formula calculates the percentage likelihood of the call being blocked, which is that all the trunks are in use and the caller will receive a busy signal. This uses the Engset model, based on the hindrance formula with no account taken of the patience factor. It is shown here for completeness, and is of interest only to telecom design specialists.

 E.g. If there are 100 calls per hours which have an average duration of 3 minutes and there are 8 telephone trunks,
Events = 100
Intensity = 3 / 60
= 0.05
=EngsetB(8,100,0.05) returns a value of 0.056 (6%)
NBTrunks Function

 Public Function NBTrunks(Intensity As Single, Blocking As Single) As Integer

  • Intensity = Busy hour telephone traffic in Erlangs
  • Blocking = blocking factor percentage e.g. 0.10 (10% of calls may receive busy tone)

The NBTrunks (number of busy hour trunks) formula gives the number of telephone lines required to handle the busy hour traffic for a required blocking factor.

 E.g. For a traffic load of 5 Erlangs the number of trunks required to ensure a blocking factor of 0.1% is given by:
=NBTrunks(5,0.001) returns a value of 14
Traffic Function

 Public Function Traffic(Servers As Single, Blocking As Single) As Single

  • Servers = Number of trunks handling the traffic, whole number
  • Blocking = blocking factor percentage e.g. 0.10 (10% of calls may receive busy tone)
  • The Traffic formula calculates the traffic intensity in Erlangs for the number of servers (trunks) with the given blocking factor.


 E.g. With 8 trunks and a blocking factor of 0.07, the traffic is given by:
=Traffic(8,0.07) returns a value of 4.9989

#erlangC #erlang #excel #call #agents #asa #capacity #sla #callCapacity #workforce #wfm #callcenter #average #utilisation #traffic


Linkedin Leo Bugallo
Ubicación: Córdoba, Argentina

Comentarios

Publicar un comentario

Entradas más populares de este blog

Liar, liar ... and your business?

Imagen
Back in 1997, Jim Carrey starred in what would ultimately be his best comedy: Liar, Liar. It recounts the adventures of an ambitious unscrupulous lawyer who did not shake his hand to lie when taking advantage by exaggerating and distorting reality in order to achieve a favorable verdict to win a trial. His son Max, who was soon to be five years old, was tired of his lies, and on the night of his birthday he made a wish: that his father cannot lie for a full day. From that moment, the lawyer begins a course of situations that he must face by telling the truth purely and exclusively, even when he tirelessly tries to lie, but he cannot do it by force of his son's wish that was being fulfilled. In this way, different people get to know who the lawyer really is and what he really thinks and does around his profession, and even in his personal life, reacting accordingly. For the record, I was very careful not to generate any spoilers, and if someone still couldn't see th...
Leer más

Fear of Ridiculous ... who, me?

Imagen
We recently talked with the team about how to achieve internal communication in the organization and in the best way a topic that we already had the content defined. A few days before he had mentioned that it would be good to ride the Tik Tok wave, and replicate the short videos as a communication tool. And the ideas began to appear, with a very good short video generated by a colleague, whom I wanted to enrich with the recommendation that it would be better if she starred in it herself. The answer was what many of us would have had: the fear that we all have of ridicule. And that's where I remembered this video, which over time has not lost its validity regarding leading, in our specific case, leading from creativity, innovation and being disruptive. I share it because it deserves to be seen from time to time to remind us that many times the origin of all good things is related to losing the fear of ridicule and transforming it into the source and vehicle of our ideas...
Leer más