ErlangC WFM functions for Excel (VBA)

erlangc.vb

'Developed by Kyle Boehlen
'At Ken Garff SCC
'Last Updated 6/27/17 by Kyle Boehlen
'Contains all of the erlang functions needed such as Sigma, Factorial, SigmaFactorialLoop, ErlangC, Service Level, Traffic Intensity, and agents needed.


Function Factorial(ByVal n As Double) As Double
    'Variable for answer
    Dim dblAnswer As Double

    'Factorial function loop
    dblAnswer = n
    For i = n - 1 To 1 Step -1
        dblAnswer = dblAnswer * i
    Next i

    'Return answer
    Factorial = dblAnswer

End Function


Function Sigma(ByVal n As Integer) As Integer
    'Declare variable to hold answer
    Dim intAnswer As Integer

    'Sigma function loop
    For i = 0 To n
        intAnswer = intAnswer + i
    Next i

    'Return answer
    Sigma = intAnswer

End Function


Function SigmaFactorialLoop(ByVal dblTrafficIntensity As Double, ByVal intAgentsNeeded As Integer) As Double
    'Function is easier than using both sigma and factorial when calculating erlangc
    'Declare variables and variable for answer
    Dim n, k, dblAnswer As Double

    'Declar counter
    Dim i As Integer

    'Starting variable values
    n = 1
    dblAnswer = 0

    'Calcuate
    For i = intAgentsNeeded To 0 Step -1
        k = n * i / dblTrafficIntensity
        dblAnswer = dblAnswer + k
        n = k
    Next i

    'Return answer
    SigmaFactorialLoop = dblAnswer

End Function


Function ErlangC(ByVal intCallVolume As Integer, ByVal intReportingPeriod As Integer, ByVal dblAHT As Double, ByVal intAgentsNeeded As Integer) As Double
    'Declare variables needed and variable for answer
    Dim dblAgentOccupancy, dblTrafficIntensity, dblAnswer As Double

    'Define variables needed to calculate
    dblTrafficIntensity = TrafficIntensity(intCallVolume, intReportingPeriod, dblAHT)
    dblAgentOccupancy = dblTrafficIntensity / intAgentsNeeded

    'Return ErlangC
    dblAnswer = 1 / (1 + ((1 - dblAgentOccupancy) * SigmaFactorialLoop(dblTrafficIntensity, intAgentsNeeded)))

    'Makes sure ErlangC is between 0 and 1
    If dblAnswer <= 0 Then
        dblAnswer = 0
    ElseIf dblAnswer >= 1 Then
        dblAnswer = 1
    End If

    'Return answer
    ErlangC = dblAnswer

End Function

Function ServiceLevel(ByVal intCallVolume As Integer, ByVal intReportingPeriod As Integer, ByVal dblAHT As Double, ByVal dblServiceLevelTime As Double, ByVal intAgentsNeeded As Integer) As Double
    'Declare variables needed to calculate service level and variable for answer
    Dim dblServiceLevel, dblTrafficIntensity, dblErlangC, e As Double

    'Calculate prerequisites
    dblTrafficIntensity = TrafficIntensity(intCallVolume, intReportingPeriod, dblAHT)
    dblErlangC = ErlangC(intCallVolume, intReportingPeriod, dblAHT, intAgentsNeeded)
    e = Exp((intAgentsNeeded - dblTrafficIntensity) * dblServiceLevelTime / dblAHT * -1)

    'Calculate service level
    dblServiceLevel = 1 - (dblErlangC * e)

    'Return answer
    ServiceLevel = dblServiceLevel

End Function


Function TrafficIntensity(ByVal intCallVolume As Integer, ByVal intReportingPeriod As Integer, ByVal dblAHT As Double) As Double
    'Declare variable for traffic intensity
    Dim dblTrafficIntensity As Double

    'Calculate traffic intensity
    dblTrafficIntensity = (intCallVolume / (intReportingPeriod * 60)) * dblAHT

    'Return traffic intensity
    TrafficIntensity = dblTrafficIntensity

End Function

Function AgentsNeeded(ByVal intCallVolume As Long, ByVal dblAHT As Double, ByVal dblServiceLevelGoal As Double, ByVal dblServiceLevelTime As Double) As Long
'       VVV Change this value (in minutes) if your reporting period changes from 30 minutes
    Const intReportingPeriod As Integer = 30 'Change this value (in minutes) if your reporting period changes from 30 minutes
'       ^^^ Change this value (in minutes) if your reporting period changes from 30 minutes

    'Declare variables needed to calculate agents needed
    Dim dblTrafficIntensity, dblServiceLevel As Double
    Dim intAgentsNeeded As Integer

    'Calculate traffic intensity
    dblTrafficIntensity = TrafficIntensity(intCallVolume, intReportingPeriod, dblAHT)

    'Start agents out as the integer of intensity and see if we already have enough agents
    intAgentsNeeded = Int(dblTrafficIntensity)
    dblServiceLevel = ServiceLevel(intCallVolume, intReportingPeriod, dblAHT, dblServiceLevelTime, intAgentsNeeded)

    'Looping until the service level is higher than the goal
    Do Until dblServiceLevelGoal <= dblServiceLevel
        intAgentsNeeded = intAgentsNeeded + 1
        dblServiceLevel = ServiceLevel(intCallVolume, intReportingPeriod, dblAHT, dblServiceLevelTime, intAgentsNeeded)
    Loop

    'Make sure it returns at least one agent
    If intAgentsNeeded <= 1 Then
        intAgentsNeeded = 1
    End If

    'Return agents needed
    AgentsNeeded = intAgentsNeeded

End Function