Hello,

I am busy designing a resource tool in Excel. One that caters for inbound and outbound taking forecasting into consideration as well. I know there is a lot of material on this subject but I am looking for some guidance on a specific area. A lot of the generic WFM tools cater for a service level 90/30, 80/20, etc. My question is, how do they factor this into the calculation of the required resources? Is there a formula that is used and would it be possible to share it?

Your assistance and advice is as always appreciated.

Regards
Christopher Mills

Chris,

What exactly are you trying to do?

The erlangC calculation will take
Target Answer time, Average Handle time and Required
Service level then give you staff required.

There are several downloads available free for Excel
that do this. You may be doing the work for nothing!

Online Calculator

Free Excel addin

The last big CCV Thread on this.

Hope this helps

DaveA

Hello Dave,

Thank you for the feedback. I have these tools already and they are all very helpful. What I am attempting to do is create a customized resource calculator that will cater for inbound activity as well as outbound (telesales, lead generation) etc. I currently have 2 separate models,1 catering for inbound and the other catering for outbound (avail leads, % DMP's, %conversion,etc.).
What I am trying to find out is what is the actual formula that is used say should a 80/20 service level be required, to calculate occupancy. How does this formula then change according to a 90/30 service level?
The tools mentioned do it for me but I am looking for the not-seen bits. The calculations behind the pretty frontends.

I hope this carifies my requirements.

Regards
Christopher Mills

YOU'RE NOT GOING TO LIKE THIS

Have fun!


ErlangC for Excel

Ok some definitions first:

Cols

A = Calls expected in 1/2 Hr. (statistical average)

B = 1800 (Seconds in 1/2hr)

C = Average Handle time (inc Wrap up)

D = Agents (calculated)

E = Target Service Level (80 or 90)

F = Target Answer Time (Seconds)

G = Traffic Intensity

H = Utilisation (as decimal)

I = ERLANGS

J = Service level as Decimal

K = Utilisation as Percentage

L = Unqueued calls (answered immediatly)

M = Service level (as Percentage)

N = Actual Agent Req

Formulas

Col

A to F : NONE

G = (A2/B2)*C2

H = G2/D2

I = POISSON(D2,G2,FALSE)/(POISSON(D2,G2,FALSE)+(1-H2)*POISSON(D2-1,G2,TRUE))

J = 1-I2*EXP(-(D2-G2)*F2/C2)

K = H2 (formatted %)

L = (1-I2)

M = J2 ( Formatted %)

N = I2*C2/(D2*(1-H2))

You can run the following code attached to the sheet this runs on.

CODE NOT TESTED ON STAND ALONE SHEET!

Works fine intergrated though

Sub Erlang_C_Calc

Application.ScreenUpdating = False

levels = 25 ' Loop to keep increasing agents until GOS target reached
For i = 2 To levels
noofstaff = "e" & i
target = "a2"
actual = "j" & i
Do Until Range(actual) >= Range(target)
Range(noofstaff) = Range(noofstaff) + 1
Loop
Next i
Application.ScreenUpdating = True

End Sub

You'll obviously have to point the code at the right area of your spreadsheet

Hope this points you in the right direction.
It took me ages to get right!

HTH

DaveA

From one geek to another, Dave: you rock.

Wow Dave,

I must say that I disagree with your comment "YOU'RE NOT GOING TO LIKE THIS". This is hopefully the breakthrough I need to get me one step further. Thank you for taking the time to type it and work it all out.

Just some FYI:
The tool I am busy on makes for some interesting thinking because on a generic inbound resourcing you can pretty my define how many contacts you can handle at what average handling time and therefore schedule accordingly. On an outbound campaign, you generally work against a lead list that oftenhas a percentage of inaccurate information, you also need to consider how many decision makers you will come into contact with and then determine what the expected level of understanding of your product will be, as this will directly impact the duration of your contact handling time. The real complexity for me has occurred when trying to blend the inbound and outbound resource modelling, as some CC prefer to operate separate inbound and outbound CC's while others want to operate a blended environment.

It may take me a while to get this all "right" in my head but it certainly is on the right track. Will keep you posted.


Regards
Christopher Mills

Right oh....

I'me trying to get a single point equation based on the IB to OB Ratio.
At the moment I'm running it at 1:2 but it's proving a bit harder than I thought!

Reversing the standard equation is proving a bit of a pain. It looks like it might be a trapdoor equation if so I'm stuffed:-(

No penguin for this post!

DaveA

Hi Dave,

What are you calculating in column N?

Typo on my part in the first post

ASA: Average Speed of Answer.

Sorry!

DaveA

(An embaressed Penguin)

HI me again, keep popping up all places. Been building your spreadsheet but a bit of help would be much appreciated.

You state "you can run the following code attached to the sheet this runs on?

What do you mean?

And what is this?

And how do I do that?

Thanks

Kevin