Erlang formula for resource planning/forecasting

2012-08-22T07:17:00.001-07:00

Instead of using an Excel add-in to get ERLANG functions/formulae into your forecasts and resourcing plans, try using this code inserted into a VBA module (all of it).

Then you can use functions like =AGENTS() without needing an addin.

Option Explicit

'Accuracy of functions, used by Traffic, Agents, FractionalAgents

Const MaxAccuracy = 0.00001

Const MaxLoops = 100

'**********************************************************************

'Version 1.8 date 15 October 2001

'Added NBAgents to calculate agents required based on average speed of answer,

'supplied by Michael Benn

'Added IntCeiling function to replace Worksheet function - better portability in VB

'Changes to SLA function to reflect exponential distribution of wait times,

'changes to Abandon to reflect exponential queueing times

'changes to Agents - ditto

'changes to FractionalAgents - ditto

'this change suggested and programmed by Tim Bolte and Jørn Lodahl

'*********************************************************************

'-----------------------------------------------------------------------

' Basic telephony functions e.g. ErlangB/C

'-----------------------------------------------------------------------

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

'The Erlang B formula calculates the percentage likelyhood of the call

' being blocked, that is that all the trunks are in use and the caller

' will receive a busy signal.

' 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

Dim Val As Single, Last As Single, b As Single

Dim count As Long, MaxIterate As Long

On Error GoTo ErlangBError

If (Servers < 0) Or (Intensity < 0) Then

ErlangB = 0

Exit Function

End If

MaxIterate = Fix(Servers)

Val = Intensity

Last = 1 ' for server = 0

For count = 1 To MaxIterate

b = (Val * Last) / (count + (Val * Last))

Last = b

Next count

ErlangBExit:

ErlangB = MinMax(b, 0, 1)

Exit Function

ErlangBError:

b = 0

Resume ErlangBExit

End Function

'-----------------------------------------------------------------------

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

'The Extended Erlang B formula calculates the percentage likelyhood of the call

' being blocked, that is that all the trunks are in use and the caller

' will receive a busy signal. The Extended version allows input of a percentage

' figure for those blocked callers who will immediately retry.

' 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 = Number of blocked callers who will retry immediately (0.1 = 10%)

Dim Val As Single, Last As Single, b As Single, Retries As Single, Attempts As Single

Dim count As Long, MaxIterate As Long

On Error GoTo ErlangBXError

If (Servers < 0) Or (Intensity < 0) Then

ErlangBExt = 0

Exit Function

End If

MaxIterate = Fix(Servers)

Retries = MinMax(Retry, 0, 1)

Val = Intensity

Last = 1 'for servers = 0

For count = 1 To MaxIterate

'find out the blocking factor (for servers = 2 to n)

b = (Val * Last) / (count + (Val * Last))

'and the increased traffic intensity

Attempts = 1 / (1 - (b * Retries))

b = (Val * Last * Attempts) / (count + (Val * Last * Attempts))

Last = b

Next count

ErlangBXExit:

ErlangBExt = MinMax(b, 0, 1)

Exit Function

ErlangBXError:

b = 0

Resume ErlangBXExit

End Function

'-----------------------------------------------------------------------

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

'The Engset B formula calculates the percentage likelyhood of the call

' being blocked, that 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.

' Servers = Number of telephone lines

' Events = Number of calls

' Intensity = average intensity per call

Dim Val As Single, Last As Single, b As Single, Ev As Single

Dim count As Long, MaxIterate As Long

On Error GoTo EngsetError

If (Servers < 0) Or (Intensity < 0) Then

EngsetB = 0

Exit Function

End If

MaxIterate = Fix(Servers)

Val = Intensity

Ev = Events

Last = 1 'for servers = 0

For count = 1 To MaxIterate

b = (Last * (count / ((Ev - count) * Val))) + 1

Last = b

Next count

EngsetExit:

If b = 0 Then EngsetB = 0 Else EngsetB = MinMax((1 / b), 0, 1)

Exit Function

EngsetError:

b = 0

Resume EngsetExit

End Function

'-----------------------------------------------------------------------

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

'This formula gives the percentage likelyhood of the caller being

' placed in a queue.

' 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

Dim b As Single, c As Single

Dim count As Long, MaxIterate As Long

On Error GoTo ErlangCError

If (Servers < 0) Or (Intensity < 0) Then

ErlangC = 0

Exit Function

End If

b = ErlangB(Servers, Intensity)

c = b / (((Intensity / Servers) * b) + (1 - (Intensity / Servers)))

ErlangCExit:

ErlangC = MinMax(c, 0, 1)

Exit Function

ErlangCError:

c = 0

Resume ErlangCExit

End Function

'-----------------------------------------------------------------------

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

'This function has been supplied by Edwin Barendse

'This formula gives the number of telephone lines required to

' handle the Busyhour traffic in Erlang against a required blocking factor

' Intensity = Busyhour Traffic in erlangs

' Blocking = blocking factor percentage e.g. 0.10 (10% of calls may receive busy tone)

Dim b As Single, count As Long, SngCount As Single

Dim MaxIterate As Long

On Error GoTo NBError

MaxIterate = 0

If (Intensity <= 0) Or (Blocking <= 0) Then

NBTrunks = 0

Exit Function

End If

MaxIterate = 65535 'max integer value

For count = IntCeiling(Intensity) To MaxIterate

SngCount = count

b = ErlangB(SngCount, Intensity)

If (b <= Blocking) Then Exit For

Next count

If count = MaxIterate Then count = 0 'did not find the answer so return as error

NBExit:

NBTrunks = count

Exit Function

NBError:

count = 0

Resume NBExit

End Function

'-----------------------------------------------------------------------

Public Function NumberTrunks(Servers As Single, Intensity As Single) As Long

'This formula gives the maximum number of telephone trunks required to

' handle the answered and queuing calls. (up to a maximum of 255)

' 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

Dim b As Single, Server As Single

Dim count As Long, MaxIterate As Long

On Error GoTo TrunkError

If (Servers < 0) Or (Intensity < 0) Then

NumberTrunks = 0

Exit Function

End If

MaxIterate = 65535 'max integer value

For count = IntCeiling(Servers) To MaxIterate

Server = count

b = ErlangB(Server, Intensity)

If b < 0.001 Then Exit For

Next count

TrunkExit:

NumberTrunks = count

Exit Function

TrunkError:

count = 0

Resume TrunkExit

End Function

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

'The Servers formula calculates the number of servers required to

' service the given traffic intensity with the given blocking factor.

' Blocking = The blocking factor requires <1, e.g. 0.80 = 80%

' 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

Dim Val As Single, Last As Single, b As Single

Dim count As Long

On Error GoTo ServersError

If (Blocking < 0) Or (Intensity < 0) Then

Servers = 0

Exit Function

End If

Val = Intensity

Last = 1

b = 1

count = 0

While (b > Blocking) And (b > 0.001)

count = count + 1

b = (Val * Last) / (count + (Val * Last))

Last = b

Wend

ServersExit:

Servers = count

Exit Function

ServersError:

count = 0

Resume ServersExit

End Function

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

'The Traffic formula calculates the traffic intensity in erlangs for the number

' of servers (trunks) with the given blocking factor.

' Servers = Number of trunks handling the traffic, whole number

' Blocking = The blocking factor achieved <1, e.g. 0.10 = 10%

Dim b As Single, Incr As Single, Trunks As Single

Dim MaxI As Single

On Error GoTo TrafficError

Trunks = Fix(Servers)

If Servers < 1 Or Blocking < 0 Then

Traffic = 0

Exit Function

End If

'find a reasonable maximum number to work with

MaxI = Trunks

b = ErlangB(Servers, MaxI)

Do While b < Blocking

MaxI = MaxI * 2

b = ErlangB(Servers, MaxI)

Loop

'find the increment to start with (multiple of 10)

Incr = 1

Do While Incr <= (MaxI) / 100

Incr = Incr * 10

Loop

Traffic = LoopingTraffic(Trunks, Blocking, Incr, MaxI, 0)

Exit Function

TrafficError:

Traffic = 0

Exit Function

End Function

'-----------------------------------------------------------------------

' Private functions, used within the module

'-----------------------------------------------------------------------

Private Function LoopingTraffic(Trunks As Single, Blocking As Single, Increment As Single, MaxIntensity As Single, MinIntensity As Single) As Single

'This function tries values from MinIntensity to MaxIntensity, increasing the traffic by Increment until

'the approximate blocking is found, processing then loops with stepping of Increment/10 (e.g. 10, 1, 0.1, 0.01, 0.001)

'until the value is found to the precision required (defined by global constant MaxAccuracy)

Dim Incr As Single, LoopNo As Integer

Dim b As Single, MinI As Single, MaxI As Single, Intensity As Single

On Error GoTo LoopTError

MinI = MinIntensity

MaxI = MaxIntensity

b = ErlangB(Trunks, MinI)

If b = Blocking Then

LoopingTraffic = MinI

Exit Function

End If

Incr = Increment

Intensity = MinI

LoopNo = 0

'Large numbers for trunks caused locking as precision of single variable Intensity is reduced with very high values

' added maxloop protection

Do While Incr >= MaxAccuracy And LoopNo < MaxLoops

b = ErlangB(Trunks, Intensity)

If b > Blocking Then

MaxI = Intensity

Incr = Incr / 10

Intensity = MinI

End If

MinI = Intensity

Intensity = Intensity + Incr

LoopNo = LoopNo + 1

Loop

LoopTExit:

LoopingTraffic = MinI

Exit Function

LoopTError:

MinI = 0

Resume LoopTExit

End Function

'-----------------------------------------------------------------------

Private Function Secs(Amount As Single) As Integer

'Convert a number of hours into seconds

Dim S As Integer

S = Fix(Amount * 3600 + 0.5)

Secs = S

End Function

'-----------------------------------------------------------------------

Private Function MinMax(Val As Single, Min As Single, Max As Single) As Single

'Apply minimum and maximum bounds to a value

MinMax = Val

If Val < Min Then MinMax = Min

If Val > Max Then MinMax = Max

End Function

'-----------------------------------------------------------------------

Private Function IntCeiling(Val As Single) As Long

'Ceiling function, rounds to the nearest numerically higher integer

Dim S As Single

On Error GoTo CeilError

If Val < 0 Then

S = Val - 0.9999

Else

S = Val + 0.9999

End If

CeilExit:

IntCeiling = Fix(S)

Exit Function

CeilError:

S = 0

Resume CeilExit

End Function

'-----------------------------------------------------------------------

' Call Center functions

'-----------------------------------------------------------------------

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

'Calculate the percentage of calls which will abandon after the time given

' Agents is the number of agents available

' AbandonTime is time in seconds before the caller will 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

Dim BirthRate As Single, DeathRate As Single, TrafficRate As Single

Dim Aband As Single, c As Single, Server As Single, Utilisation As Single

On Error GoTo AbandError

BirthRate = CallsPerHour

DeathRate = 3600 / AHT

'calculate the traffic intensity

TrafficRate = BirthRate / DeathRate

Server = Agents

Utilisation = TrafficRate / Server

If Utilisation >= 1 Then Utilisation = 0.99

c = ErlangC(Server, TrafficRate)

'take all queueing calls (C) and subtract calls queueing within abandontime

Aband = c * Exp((TrafficRate - Server) * (AbandonTime / AHT))

AbandExit:

Abandon = MinMax(Aband, 0, 1)

Exit Function

AbandError:

Aband = 0

Resume AbandExit

End Function

'-----------------------------------------------------------------------

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

'Calculate the number of agents required to service a given number of calls to meet the service level

' SLA is the % of calls to be answered within the ServiceTime period e.g. 0.95 (95%)

' 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

Dim BirthRate As Single, DeathRate As Single, TrafficRate As Single

Dim Erlangs As Single, Utilisation As Single, c As Single, SLQueued As Single

Dim NoAgents As Long, MaxIterate As Long, count As Long

Dim Server As Single

On Error GoTo AgentsError

If SLA > 1 Then SLA = 1

BirthRate = CallsPerHour

DeathRate = 3600 / AHT

'calculate the traffic intensity

TrafficRate = BirthRate / DeathRate

'calculate the number of Erlangs/hours

Erlangs = Fix((BirthRate * (AHT)) / 3600 + 0.5)

'start at number of agents for 100% utilisation

If Erlangs < 1 Then NoAgents = 1 Else NoAgents = Int(Erlangs)

Utilisation = TrafficRate / NoAgents

'now get real and get number below 100%

While Utilisation >= 1

NoAgents = NoAgents + 1

Utilisation = TrafficRate / NoAgents

Wend

MaxIterate = NoAgents * 100

'try each number of agents until the correct SLA is reached

For count = 1 To MaxIterate

Utilisation = TrafficRate / NoAgents

If Utilisation < 1 Then

Server = NoAgents

c = ErlangC(Server, TrafficRate)

'find the level of SLA with this number of agents

SLQueued = 1 - c * Exp((TrafficRate - Server) * ServiceTime / AHT)

If SLQueued < 0 Then SLQueued = 0

If SLQueued >= SLA Then count = MaxIterate

'put a limit on the accuracy required (it will never actually get to 100%)

If SLQueued > (1 - MaxAccuracy) Then count = MaxIterate

End If

If count <> MaxIterate Then NoAgents = NoAgents + 1

Next count

AgentsExit:

Agents = NoAgents

Exit Function

AgentsError:

NoAgents = 0

Resume AgentsExit

End Function

'-----------------------------------------------------------------------

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

'Calculate the number of agents required to service a given number of calls to meet the average speed of answer

' ASA is the Average Speed of Answer in seconds

' 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

Dim BirthRate As Single, DeathRate As Single, TrafficRate As Single

Dim Erlangs As Single, Utilisation As Single, c As Single, AnswerTime As Single

Dim NoAgents As Long, MaxIterate As Long, count As Long

Dim Server As Single

On Error GoTo AgentAError

If ASA < 0 Then ASA = 1

BirthRate = CallsPerHour

DeathRate = 3600 / AHT

'calculate the traffic intensity

TrafficRate = BirthRate / DeathRate

'calculate the number of Erlangs/hours

Erlangs = Fix((BirthRate * (AHT)) / 3600 + 0.5)

'start at number of agents for 100% utilisation

If Erlangs < 1 Then NoAgents = 1 Else NoAgents = Int(Erlangs)

Utilisation = TrafficRate / NoAgents

'now get real and get number below 100%

While Utilisation >= 1

NoAgents = NoAgents + 1

Utilisation = TrafficRate / NoAgents

Wend

MaxIterate = NoAgents * 100

'try each number of agents until the correct ASA is reached

For count = 1 To MaxIterate

Server = NoAgents

Utilisation = TrafficRate / NoAgents

c = ErlangC(Server, TrafficRate)

AnswerTime = c / (Server * DeathRate * (1 - Utilisation))

If (AnswerTime * 3600) <= ASA Then count = MaxIterate

If count <> MaxIterate Then NoAgents = NoAgents + 1

Next count

AgentAExit:

AgentsASA = NoAgents

Exit Function

AgentAError:

NoAgents = 0

Resume AgentAExit

End Function

'-----------------------------------------------------------------------

Public Function NBAgents(CallsPH As Single, AvgSA As Single, AvgHT As Integer) As Long

Dim b As Single, count As Long, SngCount As Single

Dim MaxIterate As Long

On Error GoTo NBError

MaxIterate = 0

If (CallsPH <= 0) Or (AvgSA <= 0) Or (AvgHT <= 0) Then

NBAgents = 0

Exit Function

End If

MaxIterate = 65535 'max integer value

For count = 1 To MaxIterate

SngCount = count

b = ASA(SngCount, CallsPH, AvgHT)

If (b <= AvgSA) Then Exit For

Next count

If count = MaxIterate Then count = 0 'did not find the answer so return As Error

NBExit:

NBAgents = count

Exit Function

NBError:

count = 0

Resume NBExit

End Function

'-----------------------------------------------------------------------

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

'Calculate the Average Speed to Answer for the given number of agents

' 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

Dim BirthRate As Single, DeathRate As Single, TrafficRate As Single

Dim Utilisation As Single, AnswerTime As Single, AveAnswer As Integer

Dim c As Single, Server As Single

On Error GoTo ASAError

BirthRate = CallsPerHour

DeathRate = 3600 / AHT

'calculate the traffic intensity

TrafficRate = BirthRate / DeathRate

Server = Agents

Utilisation = TrafficRate / Server

If Utilisation >= 1 Then Utilisation = 0.99

c = ErlangC(Server, TrafficRate)

AnswerTime = c / (Server * DeathRate * (1 - Utilisation))

AveAnswer = Secs(AnswerTime)

ASAExit:

ASA = AveAnswer

Exit Function

ASAError:

AveAnswer = 0

Resume ASAExit

End Function

'--------------------------------------------------------------

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

'Calculate the number of calls which can be handled by the given number of agents whilst still

' achieving the grade of service

' NoAgents is the number of agents available

' 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

Dim Calls As Single, xAgent As Long, MaxIterate As Long, xNoAgent As Long

On Error GoTo CapacityError

'Make sure Number of agents available is a whole number

xNoAgent = Fix(NoAgents)

'Maximum number of calls at 100% utilisation

Calls = IntCeiling(3600 / AHT) * xNoAgent

xAgent = Agents(SLA, ServiceTime, Calls, AHT)

'Now count down call load until the current level of agents is met

While (xAgent > xNoAgent) And (Calls > 0)

Calls = Calls - 1

xAgent = Agents(SLA, ServiceTime, Calls, AHT)

Wend

CapacityExit:

CallCapacity = Calls

Exit Function

CapacityError:

Calls = 0

Resume CapacityExit

End Function

'-----------------------------------------------------------------------

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

'Calculate the number of agents required to service a given number of calls to meet the service level

' SLA is the % of calls to be answered within the ServiceTime period e.g. 0.95 (95%)

' 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

Dim BirthRate As Single, DeathRate As Single, TrafficRate As Single

Dim Erlangs As Single, Utilisation As Single, c As Single, SLQueued As Single

Dim LastSLQ As Single, Fract As Single, OneAgent As Single, NoAgentsSng As Single

Dim NoAgents As Long, MaxIterate As Long, count As Long

Dim Server As Single

On Error GoTo FAgentsError

If SLA > 1 Then SLA = 1

BirthRate = CallsPerHour

DeathRate = 3600 / AHT

'calculate the traffic intensity

TrafficRate = BirthRate / DeathRate

'calculate the number of Erlangs/hours

Erlangs = Fix((BirthRate * (AHT)) / 3600 + 0.5)

'start at number of agents for 100% utilisation

If Erlangs < 1 Then NoAgents = 1 Else NoAgents = Int(Erlangs)

Utilisation = TrafficRate / NoAgents

'now get real and get number below 100%

While Utilisation >= 1

NoAgents = NoAgents + 1

Utilisation = TrafficRate / NoAgents

Wend

SLQueued = 0

MaxIterate = NoAgents * 100

'try each number of agents until the correct SLA is reached

For count = 1 To MaxIterate

LastSLQ = SLQueued

Utilisation = TrafficRate / NoAgents

If Utilisation < 1 Then

Server = NoAgents

c = ErlangC(Server, TrafficRate)

'find the level of SLA with this number of agents

SLQueued = 1 - c * Exp((TrafficRate - Server) * ServiceTime / AHT)

If SLQueued < 0 Then SLQueued = 0

If SLQueued > 1 Then SLQueued = 1

If SLQueued >= SLA Then count = MaxIterate

'put a limit on the accuracy required (it will never actually get to 100%)

If SLQueued > (1 - MaxAccuracy) Then count = MaxIterate

End If

If count <> MaxIterate Then NoAgents = NoAgents + 1

Next count

NoAgentsSng = NoAgents

If SLQueued > SLA Then 'any fraction to calculate?

OneAgent = SLQueued - LastSLQ ' difference made by 1 agent

Fract = SLA - LastSLQ 'difference we want

NoAgentsSng = (Fract / OneAgent) + (NoAgents - 1)

End If

FAgentsExit:

FractionalAgents = NoAgentsSng

Exit Function

FAgentsError:

NoAgentsSng = 0

Resume FAgentsExit

End Function

'-----------------------------------------------------------------------

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

'Calculate the number of calls which can be handled by the given number of agents whilst still

' achieving the grade of service

' NoAgents is the number of fractional agents available

' 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

Dim Calls As Single, xAgent As Single, MaxIterate As Long, xNoAgent As Single

On Error GoTo FCapacityError

'xNoAgent is single so we can accept fractional numbers

xNoAgent = NoAgents

'Maximum number of calls at 100% utilisation

Calls = IntCeiling(3600 / AHT * xNoAgent)

xAgent = FractionalAgents(SLA, ServiceTime, Calls, AHT)

'Now count down call load until the current level of agents is met

While (xAgent > xNoAgent) And (Calls > 0)

Calls = Calls - 1

xAgent = FractionalAgents(SLA, ServiceTime, Calls, AHT)

Wend

FCapacityExit:

FractionalCallCapacity = Calls

Exit Function

FCapacityError:

Calls = 0

Resume FCapacityExit

End Function

'-----------------------------------------------------------------------

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

'Calculate the percentage of calls which will queue for the given number of agents

' 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

Dim BirthRate As Single, DeathRate As Single, TrafficRate As Single

Dim Q As Single, Server As Single

On Error GoTo QError

BirthRate = CallsPerHour

DeathRate = 3600 / AHT

'calculate the traffic intensity

TrafficRate = BirthRate / DeathRate

Server = Agents

'just use ErlangC

Q = ErlangC(Server, TrafficRate)

QExit:

Queued = MinMax(Q, 0, 1)

Exit Function

QError:

Q = 0

Resume QExit

End Function

'-----------------------------------------------------------------------

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

'Calculate the average queue size for a given number of agents

' 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

Dim BirthRate As Single, DeathRate As Single, TrafficRate As Single

Dim c As Single, Server As Single, QSize As Single, Utilisation As Single

On Error GoTo QSizeError

BirthRate = CallsPerHour

DeathRate = 3600 / AHT

'calculate the traffic intensity

TrafficRate = BirthRate / DeathRate

Server = Agents

Utilisation = TrafficRate / Server

If Utilisation >= 1 Then Utilisation = 0.99

c = ErlangC(Server, TrafficRate)

QSize = (Utilisation * c) / (1 - Utilisation)

QSizeExit:

QueueSize = Fix(QSize + 0.5)

Exit Function

QSizeError:

QSize = 0

Resume QSizeExit

End Function

'-----------------------------------------------------------------------

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

'Calculate the average queue time for those calls which will queue

' 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

Dim BirthRate As Single, DeathRate As Single, TrafficRate As Single

Dim c As Single, Server As Single, QTime As Single, Utilisation As Single

On Error GoTo QTimeError

BirthRate = CallsPerHour

DeathRate = 3600 / AHT

'calculate the traffic intensity

TrafficRate = BirthRate / DeathRate

Server = Agents

Utilisation = TrafficRate / Server

If Utilisation >= 1 Then Utilisation = 0.99

'calculate average in the queue time for queued calls

QTime = 1 / (Server * DeathRate * (1 - Utilisation))

QTimeExit:

QueueTime = Secs(QTime)

Exit Function

QTimeError:

QTime = 0

Resume QTimeExit

End Function

'-----------------------------------------------------------------------

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

'Calculate the average waiting time in which a given percentage of the calls will be answered

' Agents is the number of agents available

' SLA is target percentage of calls to be answered e.g. 0.85 = 85%

' 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

Dim BirthRate As Single, DeathRate As Single, TrafficRate As Single, Utilisation As Single

Dim c As Single, Server As Single, STime As Single, QTime As Single

Dim Ag As Integer, Adjust As Single

On Error GoTo STimeError

Adjust = 0

BirthRate = CallsPerHour

DeathRate = 3600 / AHT

'calculate the traffic intensity

TrafficRate = BirthRate / DeathRate

c = ErlangC(Agents, TrafficRate)

If c < (1 - SLA) Then Error 9999 'none will be queued so return 0 seconds

Server = Agents

Utilisation = TrafficRate / Server

If Utilisation >= 1 Then Utilisation = 0.99

QTime = 1 / (Server * DeathRate * (1 - Utilisation)) * 3600

STime = QTime * (1 - ((1 - SLA) / c))

'check rounding errors here and adjust

Ag = Module1.Agents(SLA, Fix(STime), CallsPerHour, AHT)

If Ag <> Agents Then Adjust = 1

STimeExit:

ServiceTime = Fix(STime + Adjust)

Exit Function

STimeError:

STime = 0

Adjust = 0

Resume STimeExit

End Function

'-----------------------------------------------------------------------

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

'Calculate the service level achieved for the given number of agents

' 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 one hour period

' AHT (Average handle time) is the call duration including after call work in seconds e.g 180

Dim BirthRate As Single, DeathRate As Single, TrafficRate As Single

Dim Utilisation As Single, c As Single, SLQueued As Single

Dim Server As Single

On Error GoTo SLAError

BirthRate = CallsPerHour

DeathRate = 3600 / AHT

'calculate the traffic intensity

TrafficRate = BirthRate / DeathRate

Utilisation = TrafficRate / Agents

If Utilisation >= 1 Then Utilisation = 0.99

Server = Agents

c = ErlangC(Server, TrafficRate)

'now calculate SLA % as those not queuing plus those queuing

'revised formula with thanks to Tim Bolte and Jørn Lodahl for their input

SLQueued = 1 - c * Exp((TrafficRate - Server) * ServiceTime / AHT)

SLAExit:

SLA = MinMax(SLQueued, 0, 1)

Exit Function

SLAError:

SLQueued = 0

Resume SLAExit

End Function

'-----------------------------------------------------------------------

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

'Calculate the number of telephone lines required to service a given number of calls and agents

' 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

Dim BirthRate As Single, DeathRate As Single, TrafficRate As Single

Dim Utilisation As Single, c As Single, AnswerTime As Single

Dim NoTrunks As Long

Dim Server As Single, R As Single

On Error GoTo TrunkError

BirthRate = CallsPerHour

DeathRate = 3600 / AHT

'calculate the traffic intensity

TrafficRate = BirthRate / DeathRate

Server = Agents

Utilisation = TrafficRate / Server

If Utilisation >= 1 Then Utilisation = 0.99

c = ErlangC(Server, TrafficRate)

AnswerTime = c / (Server * DeathRate * (1 - Utilisation))

'now calculate new intensity using average life time of call (queuing time + handle time)

R = BirthRate / (3600 / (AHT + Secs(AnswerTime)))

NoTrunks = NumberTrunks(Server, R)

'if there is traffic (Trafficrate>0) then always return at least 1 trunk

If (NoTrunks < 1) And (TrafficRate > 0) Then NoTrunks = 1

TrunkExit:

Trunks = NoTrunks

Exit Function

TrunkError:

NoTrunks = 0

Resume TrunkExit

End Function

'-----------------------------------------------------------------------

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

'Calculate the utilisation percentage for the given number of agents

' 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

Dim BirthRate As Single, DeathRate As Single, TrafficRate As Single

Dim Util As Single

On Error GoTo UtilError

BirthRate = CallsPerHour

DeathRate = 3600 / AHT

'calculate the traffic intensity

TrafficRate = BirthRate / DeathRate

Util = TrafficRate / Agents

UtilExit:

Utilisation = MinMax(Util, 0, 1)

Exit Function

UtilError:

Util = 0

Resume UtilExit

End Function

'-----------------------------------------------------------------------

Excel VBA error help

Select a sheet based on button label/caption

Erlang formula for resource planning/forecasting

Use Excel/Access VBA to check if sheet exists

Runtime error 1004 : Unable to get the Average property of the WorksheetFunction class