How to estimate probable response times, from previous samples?

Question

I'm a IT manager that deals with delays from various departments in a purchase process. In a given phase we have 25 handovers and thus 25 response times. So many variable times (and without SLA) creates serious uncertainty about the final date, that reflects in IT risk and cybersecurity risk. I collected the timestamps of these handovers from 5 previous purchases to model expected date in which a purchase will reach the end step (i.e. signed contract). I want to know optimist, mean and pessimist estimates. So, I computed the following statistics: Minimum > Mean minus Standard Deviation > Mean > Mean plus Standard Deviation > Maximum

Then I found a problem. Let's compute for the following sample (values are days): 0.2, 1.0, 0.3, 0.0, 0.1

We get:

• Minimum: 0.0
• Mean minus Standard Deviation: -0.1
• Mean: 0.3
• Mean plus Standard Deviation: 0.7
• Maximum: 1.0

That -0.1 was unexpected (by me). I was expecting some value between minimum (0.0) and mean (0.3) that could tell me what a rapid response is, based on historical values. I realized that I was presuming a normal distribution, that's not the case.

So I tried to use 25th and 75th percentiles, instead of standard deviations. And then hit another problem. Let's compute for the following sample: 2.0, 2.0, 2.0, 2.0, 3.0

That is: tipically it takes 2 days, but on one occasion it took 3. Something between [2,3] should describe the reality. But my metrics become:

• Minimum: 2.0
• 25th perc.: 2.0
• Mean: 2.2
• 75th perc.: 2.0
• Maximum: 3.0

Wait. I have a 75th percentile (2.0) that's lower than mean (2.2). Statistically computed right, but I can't use 75th percentile to model a case worst than mean. I'm figuring something wrong. I was expecting that a percentile could be a value not present on sample, as if would have continuous values... some interpolation.

So, my mind is in interpolating some normal distribution, but my lacking statistics skills are not leading me to a solution. Can you please help me to model this?

I'm using Excel. Some solution feasible in excel would be great.

Answer 1

You do not give much information about these waiting times, so this answer has to be regarded as highly speculative.

Often waiting times are well-modeled as exponential. Your small sample $0.2, 1.0, 0.3, 0.0, 0.1$ has high outlier $1.0$ which may suggest such a right skewed distribution. A sample of size five is hardly enough for reliable speculation about your distribution of waiting times.

If $\bar X$ is the mean of an exponential population of size $n,$ one has $\frac{\bar X}{\mu} \sim \mathsf{Gamma}(\mathrm{shape}=n,\mathrm{rate}=n).$ This relationship can be 'pivoted' to give a 95% CI for $\mu$ of the form $\left(\frac{\bar X}{U}, \frac{\bar X}{L}\right),$ where $L$ and $U$ cut area 0.025 from the lower and upper tails of $\mathsf{Gamma}(n,n),$ respectively.

So if you have $n = 20$ exponential observations with $\bar X = 0.35,$ then a 95% CI for $\mu$ is $(0.24, 0.57).$ [Computations in R.]

0.35/qgamma(c(.975,.025),20,20)
[1] 0.2359218 0.5729946

Taking the worst-case scenario that the mean is as large as $\mu = 0.57,$ one might speculate that 99% of the time the waiting time would be less than $2.624.$

qexp(.99, 1/0.57)  # mean 0.57 implies rate 1/0.57
[1] 2.624947

Of course, this is based on only 20 observations, the assumption that future waiting times will imitate past ones, and the assumption that waiting times are exponential.

By contrast, if you have a substantial amount of data on relevant past waiting times, then you have more information and fewer assumptions. You might use the 99th percentile of that data for a 'pessimistic' prediction of the next waiting time, as shown below.

set.seed(2021)
y = rexp(1000, 1/0.35)  # fictitious exponential data
                          for illustration
summary(y)
     Min.   1st Qu.    Median      Mean   3rd Qu.      Max. 
0.0002822 0.0990812 0.2418578 0.3520501 0.4953001 2.3407786 

quantile(y, .99)
     99% 
1.535914 

Based on my fictitious data, only ten times in 1000 such situations was the waiting time more than $1.536$ and the waiting time was never more than $2.35.$

sum(y > 1.536)
[1] 10