Help I need to resolve this by 10:00 GMT 17th Mar :o(

[sh0t2bts]

Hi All,

I work in a contact centre and need to do some backward engineering on an
Calculation, the calculation in question is a user defined function called
"Agents" see below.

in a nut shell you provide the function the following data and it comes back
with the result of how many staff you need to answer the given number of
calls in the given interval.

Agents(number of calls, call duration, interval, service level, target)

number of calls = number of calls that need to be answered in the interval
call duration = how long will the call last on average my targets are 6 mins
interval = how long in minutes is the interval that these calls will be
offered I work on 30 minute intervals
service level = how long can the call wait in the queue before it is out of
target, my target is 20 seconds
target = target what percentage of calls need to be answered with in the
service level.

What I have is a list of how many staff are available in that 30 minute
interval, and I want to know what will the service levels be.

E.G. I have 30 staff members available between 09:00 and 09:30, I know that
the staff can answer 9 calls per hour on average so if I take the 30 staff
members and multiply it by 4.5 "9 / 2 to give me my 30 minute target" I know
that I can get 135 calls answered will no queue time and all be within
service level.

What I want to do is say I have 30 staff and I know that I am going to get
150 calls offered in that 30 minute interval I want to say to my client that
I can answer all 150 calls but the service level of 85% of the calls
answered in 20 seconds will only be 60%.


I hope this make sense and some one call help as I only have tonight to
resolve the calculation.

if you don't have the full answer or know of some other way please post back
and any help is appreciated

Kind Thanks

Mark
Function Agents(arr_rate, svce_int, time_int, svce_lev, Target)
lam = arr_rate * 60 / time_int
s = svce_int / 60
t = svce_lev / 3600
a = lam * s
SL = 0
first = Application.Max(1, Int(a) - 3)

last = first + 75
For n = first To last
If n <= a Then
SL = 0
Else
SL = 1 - Erlang(2, n, a) * Exp(-(n - a) * t / s)
End If
If SL >= Target Then
Agents = n
n = last
End If
Next n
End Function

HERE's THE ErLang UDF that is refered to in the Agents
UDF..........................


Function Erlang(Version, number, traffic)
If Version = 1 Then
dummy = 1
Dummy1 = 1
Dummy2 = 1
For n = 1 To number
Dummy1 = Dummy1 * traffic / n
Dummy2 = Dummy2 + Dummy1
Next n
dummy = Dummy1 / Dummy2
ElseIf Version = 2 Then
Dummy1 = number * Erlang(1, number, traffic)
Dummy2 = number - traffic + traffic * Erlang(1, number, traffic)
dummy = Dummy1 / Dummy2
End If
If dummy >= 1 Then
dummy = 1
End If
Erlang = dummy
End Function

 

[Charlie]

Look at www.Kooltoolz.com they have some call center tools
that might be helpful. And they are free.

Charlie O'Neill

 

[sh0t2bts]

Thank you,

I am trying their products now

Mark )

 

[Guest]

Gents,

I know that this is way late to be of use, but for future readers:

Charlie's right - there are toolsets that contain the core Erlang C equation that was used to create the Agents function.

The writers of Agents had to use a root search algorithm on Erlang C to get to the target agents. By the same token, you can use Excel's internal root search to get your answer.

With the function:
Agents(number of calls, call duration, interval, service level, target)
and the example given:
What I want to do is say I have 30 staff and I know that I am going to get
150 calls offered in that 30 minute interval I want to say to my client that
I can answer all 150 calls but the service level of 85% of the calls
answered in 20 seconds will only be 60%.

In Cell A1 you would enter the formula:
=Agents(150, call duration, 0.5 hours, 20, A2)
In Cell A2 you would enter (your starting point for the search):
0.85
In Cell B1 you would enter:
=30-A1
Then choose from the main menu:
TOOLS
GOAL SEEK
for SET CELL: B1
for TO VALUE: 0
for BY CHANGING CELL: A2
Then click OK.

Your answer will be in Cell A2 - should be around 56.1% depending upon call duration. I guessed 10.5 minutes per call.

Brgds,
Mark