Build more tools to perform stochastic / probabilistic analysis and Monte Carlo simulation
- More inverse cumulative distribution functions (Poisson, Exponential, Negative Binomial, Weibull, etc.);
- Ability to confine the distribution between certain values;
- Ability to generate / simulate time series;
- Ability to sample/simulate all inverse cumulative distribution functions without continuously recalculating the workbook (ex. run the simulation behind the scenes much like the current IRR function operates though iteration);
- Ability to correlate distributions;
- Ability to run sensitivity analysis on the results; It is currently possible to build all of the above functionalities through VBA but having dedicated tools will bring considerable efficiency improvements.
Thanks for logging this, Lyubomir. How do you do these today? Is deeper integration with R potentially useful for such scenarios, at least for the analysis?
To Anonymous @ 2020-03-05-22:39, For me at least, yes, I've used this method once or twice as a method of last resort, but other Monte Carlo plug-ins achieve this and much more sophisticated and varied probability distributions with a few clicks of the mouse. There is some very insightful data analysis that can be done if this functionality was native to Excel. I'd love for the Excel team to implement it.
Are people aware that you can do a basic simulation using the Data Table functionality?
Simply set up your model with Rand() driving the variables and then set up a data table with sim# in a column and the key values you want at the top of the columns alongside.
Then insert a data table with a dummy cell as the column variable to force a recalc for each row of the table.
I'd second point 1 to add more inverse distribution functions. You can write them as a VBA function but if you're calculating lots of simulations (for example) being a VBA function rather than a built-in function can really slow the recalc down.
Bumping this up to reaffirm that native Monte Carlo tools would be a tremendous addition and very helpful to many users. There is no easy/elegant/useful work around today.
Lyubomir Georgiev commented
Hi Ashvini. Thank you for your comment.
Currently, to do this type of probabilistic analysis, I use a combination of build in Excel functions (ex. BINOM.INV, NORM. INV, etc.) and VBA. I use VBA to:
- create inverse cumulative distribution functions not supported in Excel;
- sample/simulate all inverse cumulative distribution functions by continuously recalculating my spreadsheet and storing outputs for each iteration;
- run sensitivity analysis on the results;
There are quite a few third party Excel add-ins which facilitate the above-mentioned tasks. The issue with all of them (a part from being quite expensive) is that once a model is built using some external functionalities, you can no longer use it on computers where the third party add-in is not installed. This severely limits the ability to collaborate on the content and consume the output of the probabilistic analysis. That’s why I believe that having natively supported Excel tools is the optimal solution.