0
$\begingroup$

I have broken down a project in to some list of tasks. For each task, I've worked with some experts to come up with 90% confidence intervals. e.g. I'm 90% sure task A will be more than L hours and 90% sure it will be less than U hours.

I'd like to build a monte carlo simulation to model the likely duration of the project.

In Excel, how do I generate a random number with normal distribution given these 90% confidence intervals? If this question is fundamentally misguided, is there a better way to achieve my intended outcome?

$\endgroup$
2
  • 1
    $\begingroup$ For your particular problem, values of less than 0 would make no sense, but the normal distribution can take on any value in the real line. $\endgroup$
    – Peter O.
    Nov 5, 2021 at 3:38
  • $\begingroup$ Yes, I noticed this effect when I plotted the results. About 10%-15% of the results are less than 0. Fortunatly, I'm more interested in the higher sides of the cumulative probability distribution. "In 50% of the simulations, the project finished in X hours or less". $\endgroup$
    – PaulH
    Nov 5, 2021 at 13:05

1 Answer 1

2
$\begingroup$

Based on what you've described about your problem, this sounds like an application of Bayesian methods. To my understanding, you have worked with some experts to derive some idea of how long a project might take, and now you are wanting to apply this information for future/unobserved projects. Another way to frame this is that you have completed elicited priors from experts, and there are multiple papers detailing this in Bayesianism: here, here, and here as examples.

Another reason that I think Bayesian methods are appropriate for you is based on your description of your confidence intervals. Your interpretation of the confidence interval as a range wherein you are 90% sure that a value will occur is a common misinterpretation of frequentist confidence intervals. Your outline here is, however, exactly the interpretation of the Bayesian credible interval.

In your case, the prior that you desire is fairly easy to specify. You're saying that, before actually starting the project, there is a 90% probability of it taking between L and U hours. What's more, you're also willing to accept that there's a normal distribution that can describe this range (i.e., assuming that there is a mean/median value that is most likely to occur and that increasingly divergent values are less probable). In this case, your prior is a normal distribution with mean of X and SD of a certain value based on your 90% prior. Here's an example of how this process works: say that my prior belief about the time taken to complete a project is described by a normal distribution with mean of 4 hours and standard deviation of 0.5 hours. The 90% confidence/credible interval can be obtained by examining what the critical values of the normal distribution corresponding to a two-tailed 0.10 (i.e., $(1.00-0.90)/2 = 0.05$). For a 90% interval, this is a value of 1.64, so the lower bound of the 90% interval would be $4-(1.64*0.5) = 3.18$ and the upper bound would be $4+(1.64*0.5) = 4.82$.

That example is obviously not exactly what you're looking for: you actually want the opposite where, given the interval boundaries, you describe the SD of the normal prior. This is simple algebra: $lower~bound = mean-(1.64*X)$ and $upper~bound = mean+(1.64*X)$. In this case, say that the point estimate for the time to complete the test is 5 hours, and your experts say that 90% of the time, the test will be finished between 2 and 8 hours. Your needed standard deviation would be $5-(1.64*X) = 2$ or about 1.83, so the normal distribution prior has mean of 5 and standard deviation of 1.83. If you wanted to do this from the 95% interval, then rather than 1.64 you would use 1.96, and so on.

If your experts didn't give you the "mean" of the normal distribution, then you can find the midpoint between your "confidence" intervals. So, if the experts say that the task will take between 3.5 and 8 hours, then the mean of the normal prior distribution would be $(8 + 3.5)/2 = 5.75$.

At the end of the day, I'm not sure what the goal of your simulation would be. If you already know the intervals of time, then what is the simulation being used for? To simulate probable times to complete the task? If so, then you already know that (as outlined above by the fact that this is directly related to the normal distribution, which is very well-defined and doesn't need to be sampled from). In contrast, if you want to know whether observed project completion times differ from the expert expectations, then having some kind of statistic make sense since there's a clear null (that the expert's are correct) and alternative (that they are wrong).

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.