Markov cohort simulation in Excel - Probabilistic sensitivity analysis (Part 1)

In this video I show you how to prepare your Excel Markov model to run probabilistic sensitivity analysis (PSA). You will learn about giving cells in Excel names and see examples of sampling from the beta and gamma distributions.
Note - After recording the video I noticed a small error. On the parameters sheet, for cell F11 we should have '=BETA.INV(RAND(),60,40)' not '=BETA.INV(RAND(),90,10)'. Apologies for that!

[@rhubarbo]

Thanks so much! I just used this to make a model for for colleagues to play with. That was a really helpful video - especially the tips on making it faster!

[@TMSnowsill]

I'm glad it was helpful 😀

[@aoahumad]

Super clear, thank you for this!

[@TMSnowsill]

Thanks!

[@YasmineKhan-e1z]

Dear Tristan, Thank you for your clear videos, they truly are helpful. I face an issue: when I turn my PSA to 1 (using BETA distribution), some values of my transition matrix take negative values (always the one with the "1-SUM(...)"). I have 29 health states and thus multiple transition probabilities, some TPS are very close to 0 so I thought that was the reason but I increased the alpha and beta but the problem remained. Do you have an idea of what the problem is? Thank you. Kind regards, Yasmine

[@PanXingming]

Thank you! Your vedio helped me a lot!

[@TMSnowsill]

Glad to hear that!

[@toluwaseadebowale8106]

Thanks for this video. very helpful! Please how can I plot a tornado diagram with the values from the PSA? Thanks

[@user-kr7zs8rs7c]

Hi, Thank you for your video! it really very helpful. I have a question how do we determine the Alpha and Beta parameters?

[@naveenhegde4755]

In some article the upper bound and lower bound of the probability distribution (from which random values picked) will be represented; is there any way to fix upper and lower bound in gamma distribution ?

[@user-kr7zs8rs7c]

Hi, how do we determine the Alpha and Beta parameters?

[@naveenhegde4755]

if the health pay off (utility weight) is in negative then what probability distribution to be considered for PSA?

[@tetrakys1083]

Hi, thank you for this video, it's very clear. I have a question about the beta distribution, assuming I have uncertainty around the probability 0.03 in the example (like a confidence interval or standard deviation), how should the formula be modified? Thanks.

[@TMSnowsill]

Thanks :) If you have a mean and standard deviation you can use the method of moments (see en.wikipedia.org/wiki/Beta_distribution#Method_of_moments). If you have a confidence interval sometimes the easiest way to handle it in Excel is by using Solver. Set up cells for your beta distribution parameters [alpha] and [beta]. Then input two cells for the true confidence limits, and two cells which give the confidence limits according to the beta distribution parameters (i.e., '=BETA.INV(0.025,[alpha],[beta])' and '=BETA.INV(0.975,[alpha],[beta])'). You then have a final cell which contains the sum of squared differences '=SUMXMY2([true_limits],[current_limits])'. You run solver to minimise the value in that last cell by changing [alpha] and [beta].

[@tetrakys1083]

@@TMSnowsill Thank you! Hadn't thought about Solver, I was thinking to calculate the standard deviation from the CI with the usual formula and then use the method of moments to calculate alpha and beta.

[@TMSnowsill]

Yeah they'd give similar results if the distribution is approximately normal. I think with modelling you're not always looking for the most elegant solution, just a correct solution that lets you move onto the next component!

[@hdorreman]

Thank you for this. Really useful. I think you made a small mistake in cell F11 of the Parameter sheet. You copied it from F10, the values given were 90 and 10. I think they should be 60 and 40. In the results now the deterministic value of u_diseased = 0.6 and the Probabilistic value is somewhere around 0.9.

[@TMSnowsill]

Hi there - yes thank you. I had already made a note of it in the description for the video, unfortunately it is not possible to edit a video once it is uploaded and I couldn't find a way to draw more attention to the error.

[@meenakshisharma9121]

Hi how to perform one way sensitivity analysis in excel

[@TMSnowsill]

I will definitely get around to putting a video up for this

[@nibir9]

If I use beta=alpha=1, will that be correct?

[@TMSnowsill]

Actually beta=alpha=1 is a special case of the Beta distribution which is the uniform(0, 1) distribution. The cumulative distribution function for this and its inverse are just the identity function (F(x) = x) so =BETA.INV(RAND(),1,1) is identical to =RAND(). beta=alpha=1 suggests that you have no information at all about what the transition probability could be, which is hopefully not the case!