Are you looking for a simple Excel function to use in your Excel workbook that calculates the number of required staff based on the number of incoming calls, AHT (average handle time), GOS (grade of servive), in a certain number of seconds then here's one for you.
Input parameters for your functions are:
Calls
AHT
GOS - such as 70 as in 70%
in Seconds - like 60 seconds
minutes in interval - like 15 or 30 minutes.
Example in a cell type =frac_Staff(1000,68,70,60,30) and the results will show in that cell for 1000 calls, with 68 seconds AHT, GOS 70% in 60 seconds and 30 minutes interval.
Add the following function code to a module in your VBA editor to start using it.
Function frac_Staff(Calls, AHT, GOS_percent, inXsecs, mins_in_period)
Application.Volatile
On Error GoTo e:
If GOS_percent > 1 Then
GOS_percent = GOS_percent / 100
End If
Period = mins_in_period * 60
workload = (Calls * AHT) / Period
If workload = Int(workload) Then
AHT = AHT + 0.01
workload = (Calls * AHT) / Period
End If
numofstaff = 1
T0 = 1
T1 = 1
goscalc = 0
workloadtest = (Calls * AHT) / 3600
If workloadtest > 351 Then
frac_Staff = workload / 0.98
Else
Do Until goscalc >= GOS_percent
If goscalc <> GOS_percent Then
numofstaff = numofstaff + 1
T0 = T0 * (workload / (numofstaff - 1))
T1 = T1 + T0
T2 = T0 * (workload / numofstaff) * (numofstaff / (numofstaff - workload))
secs = inXsecs / AHT
Staffwork = 1 / (numofstaff - workload)
DLY = (T2 / (T1 + T2)) * 100
goscalc = (1 - ((DLY / 100) / Exp(secs / Staffwork)))
End If
Loop
frac_Staff = numofstaff
End If
e:
If Err.Number <> 0 Then frac_Staff = 2
End Function
