Last active
April 1, 2022 02:16
-
-
Save kyleboehlen/cf03e1e68a6423552294a86c4ff4df69 to your computer and use it in GitHub Desktop.
ErlangC WFM functions for Excel (VBA)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
'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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment