Sensitivity Analysis with Monte Carlo Simulation

I have built myself a tool that I use to conduct Monte Carlo Simulations. With this tool I usually have (obviously based upon the business case I am modeling/simulating) some number of static fixed variables and some number of variables that have some uncertainty to them and thus I randomly simulate them for multiple scenarios. But I would like to be able to add some sensitivity analysis to it. By this I mean if I have a fixed variable, say cost at $10, I might like to conduct some sensitivity analyses on it and see my results with my costs at 8, $9, $10, $11, $12, etc.

I know how to do sensitivity analysis using data tables which won't really work here. And I know I can do a brute force approach by independently conducting my simulation at say all the different costs. But is there something more elegant I can do to achieve what I want? I suspect not but thought I might put it out there to those who have some Excel expertise.

Thanks!

Hello dknick99,

do I understand you right, that you wrote a VBA sub, which performs your MC simulation leaving some variables fixed and some are varied MC like in a random fashion?
If so, you could either create an outer loop in your VBA code, that changes those "fixed" variables only in the steps you want for the sensitivity analysis and store the modified fixed values together with the respective result or you write a second function, which does the "fixed variable" variation and result tracking while calling the your MC routine for every variation.

Both should not be too difficult, if you had programmed the MC code before anyway.

Please let me know, if I am with you correctly.

Regards,
Thomas