Thanks for the clarification...that is what I was assuming (1 work day = 8 h, 1 mo = 20 work days). From my understanding, the equations developed for Erlang C are assuming a fairly homogeneous situation where call (inquiry) demand and the "machinery" to handle the demand are consistent. For that reason, in your analysis, I would consider a "day" to be an 8-hour block (out of a 24-h calendar day) during which tangible work is performed. I'm not sure how your system works regarding inputs, but I'll assume that inquiries might be made anytime during a calendar day, and if so, they are queued, with no action taken until the next workday. Then the inquiry rate (in cell B13 on my worksheet) is a somewhat artificial number that distributes inquiries over the 8-hour workday.
In post #18, you mentioned:
I'm not sure what you mean, but wanted to point out that you can modify cells C23 (and those to the right) to consider any number of staff. B23 represents the baseline minimum number, but manually change C23 (and those to the right) to anything that might be of interest. I typically change the formula to increment by something other than 1 to identify approximately where other metrics (Target Service Level, Occupancy, Shrinkage) all appear to be met, and then explore the range in that neighborhood at a smaller resolution to identify the estimated optimal number of staff.
If we set up a sheet using this example:
...using these redefined "days" values, converted into units expected by the worksheet
| ErlangC_20240220_20240801.xlsx |
|---|
|
|---|
| H | I | J |
|---|
| 5 | 11 | d, redefine "day" as 8 h then convert to min | 5280 |
|---|
| 6 | 3 | days, redefine day as 8 h, convert to s | 86400 |
|---|
| 7 | | | |
|---|
| 8 | 2 | days, redefine day as 8 h, convert to s | 57600 |
|---|
ErlangC_not_a_swim_fan2 (3) |
|---|
...the model suggests that 16 staff would be needed to handle the 28 requests while satisfying the three metrics (target service level, max occupancy, shrinkage). Does this make sense to you?...that you might be able to close out 28 requests over 11 days with fewer staff, and without those staff feeling too overwhelmed, while also having sufficient time to deal with other things?
I have another draft version of this worksheet that might be useful for cases where the staff size is so large that the factorial function (FACT) in Excel fails because the value exceeds Excel's limit. That version revisited Erlang's original development to rewrite expressions in terms of other functions available in Excel to avoid the limitations with FACT. I never quite finalized that effort, but could if errors are generated in the row 24/25 computations.
, the log-space computation for a quantity "a" used to compute the probability (p) that a call has to wait, the probability that a call is answered immediately (pai), the average speed of answer (asa), the service level (sl), the occupancy (occ), and the number of agents after considering shrinkage (nshrinkage). All of these results are stacked together and spilled in a single output. Conditional formatting is used in the output table to highlight (green/red) where the Service Level and Occupancy requirements are not met/not met. For the user, examine the Traffic Intensity (A) and then adjust the parameters in the "n" part of the formula where the SEQUENCE function is used:
