Creating a Workforce Management Forecasting tool. Looking for feedback

[_RKKC_]

Hello,

I'm setting up a tool that will help to forecast staffing required to handle call volumes and would like some feedback. I have shared the file through the link below. The excel sheet does contain Macros. I'll post them for review as well.

Many thanks to anyone who is willing to look this over. I'm trying to make it user friendly and, most importantly...free.

https://1drv.ms/x/s!AoBQDHxxxmbCadXjowwe1gCwpkI

Code to update the weekly data.

Private Sub UpdateWeeklyData_Click()

'
' ClearCopyNewWeekData Macro
' Delete Week 8; Cut/Paste Weeek 1-7 to week 2-8; Cut/Paste new data to week 1.
'
Dim check As Integer, result As String
check = Range("T3").Value
If check > 0 Then
MsgBox "Only submit with a full week's data."
End
End If
    Range("AY38:BE59").Select
    Selection.ClearContents
    Range("B38:AX59").Select
    Range("AX59").Activate
    Selection.Cut
    Range("I38").Select
    ActiveSheet.Paste
    Range("B3:H24").Select
    Selection.Cut
    Range("B38").Select
    ActiveSheet.Paste
    Range("A1").Select


End Sub

Code to process erlang calculations. Many thanks to Kyle Boehlen for posting this on his site!

    '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

 

[_RKKC_]

Like a dummy, I forgot to include a link to Kyle Boehlen's site. Again, many thanks to him for taking this piece of work out of the picture for me.

http://www.kyleboehlen.com/kbwp/web-design-software/erlangc-vba-functions/