Building A Probabilistic Risk Estimate Using Monte Carlo Simulations 

Check out my sequel video where I walk thru a Python based approach medium.com/analytics-vidhya/building-a-probabilistic-risk-estimate-using-monte-carlo-simulations-with-python-mcerp-7d57e63112fa

In the tab Uncertain Range+Events+Corr, there is a matrix in cells G14:I16. This is referenced in the risked ranges in cells I4 to I9 as a ",RiskCorrmat" suffix to the whatever risk distribution is being used. Read the help documentation for more examples github.com/pyscripter/XLRisk/wiki/CorrelatedInputsExample

github.com/ZhijingEu/MonteCarloSimulation

You must be an advanced user I only have Office 365 not 366 🤣. On a more serious note I think XLRisk only works with the excel desktop version (Excel 2010 onwards) but not excel online. github.com/pyscripter/XLRisk

@@CH2CH2CH2 Thank you for sharing the excelRisk ❤️ Do you have link or information for cash flow forecasting using Monte Carlo simulation?

github.com/ZhijingEu/MonteCarloSimulation/

medium.com/analytics-vidhya/building-a-probabilistic-risk-estimate-using-monte-carlo-simulations-cf904b1ab503

@@CH2CH2CH2 Thankyou

It's working! I am using this Add-ins!😍

github.com/ZhijingEu/MonteCarloSimulation

github.com/ZhijingEu/MonteCarloSimulation/

I don't quite understand your question. This tool does not assess the likelihood or impact but assumes you know enough about the phenomenon to select an appropriate probability distribution to simulate it's behavior...you can create two variables- one being a binomial on-off representing likelihood and another variable to reflect the impact range and multiply both distributions to get the overall effect. Is that what you meant ? If you have actual observed data you can even build your own custom distributions. Unfortunately you can't do it in XL Risk but commercial packages like Palisade @Risk or Oracle Crystal Ball have this feature

You can run Monte Carlo Sims using just Numpy and Scipy. However this can be a a bit tedious to set up so there are a few Python Libraries that simplify this process in terms of providing more choices for sampling methods, managing sub-simulations and summarising the results PyMCSL pymcsl.readthedocs.io/en/latest Monaco monaco.readthedocs.io/en/latest MCerp github.com/tisimst/mcerp There are lots of examples online if you do a general web search. However most tutorials skip a critical element - how to induce rank correlation between the random input variables. Without this, the simulation will assume that all input variables are independent and uncorrelated which is usually not reflective of most modelled phenomenon. Therefore simulation outcomes will end up underestimating the variability due to the Central Limit Theorem statisticsbyjim.com/basics/central-limit-theorem/ However I can recommend this one example that does cover the use of correlated variables using MCerp : towardsdatascience.com/journey-to-monte-carlo-mc-simulations-with-correlated-variables-in-python-1aef84d5742d

​@@CH2CH2CH2Thanks 👍 will definitely go through it

@@shiwaninaik3935 glad I could help. I'm planning to redo this tutorial video using Python instead of XL Risk when I get a bit of time later this year so stay tuned.

@shiwaninaik3935 zhijingeu.medium.com/building-a-probabilistic-risk-estimate-using-monte-carlo-simulations-with-python-mcerp-7d57e63112fa#bb3f

XLRisk supports both RiskPert and RiskTriang functions :- github.com/pyscripter/XLRisk/wiki/RiskFunctions

In the tab Uncertain Range+Events+Corr, there is a matrix in cells G14:I16. This is referenced in the risked ranges in cells I4 to I9 via the ",RiskCorrmat" suffix to the risk distribution being used. Read the help documentation for more examples github.com/pyscripter/XLRisk/wiki/CorrelatedInputsExample

Tx Zhijing, but you must have done something in addition to RiskCormax in order to enhance the correlation. I see that cells in I4:I9 are named i.e., I5 is "AccomCost" etc... Have you connected it somehow to Corr matrix?

You need to ensure you name the range for the Corr matrix but also specify which variable you are referring to i.e I5 Accom Cost is ", RiskCorrmat($G$14:$I$16,1)" , I6 Meals is ", RiskCorrmat($G$14:$I$16,2)" and I8 Holiday Tours is ", RiskCorrmat($G$14:$I$16,3)"

Tx, sorted out.

Not exactly MC simulations but similar concept that uses Geometric Brownian Motion to model share price behavior ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-irMSmRUlIkc.html (Spoiler Alert : It doesn't work as well as you'd think)

github.com/ZhijingEu/MonteCarloSimulation/

medium.com/analytics-vidhya/building-a-probabilistic-risk-estimate-using-monte-carlo-simulations-cf904b1ab503

In the tab Uncertain Range+Events+Corr, there is a matrix in cells G14:I16. This is referenced in the risked ranges in cells I4 to I9 as a ",RiskCorrmat" suffix to the whatever risk distribution is being used. Read the help documentation for more examples github.com/pyscripter/XLRisk/wiki/CorrelatedInputsExample

Under the tab Uncertain Range+Events+Corr, there is a table in cells G14:I16 that represents the correlation matrix. Maybe you accidentally changed the values in the non diagonal cells? (H14, I14, G15, I15, G16, H16)

@@CH2CH2CH2 Your is perfect, I'm using another one, the values for the correlation differ from -0,27 to 1 with 19 variables (diagonal all 1), I've tried to change the numbers and reduce the 19 but receive the same message.

Hmmmm... This is tricky. First try to set the non diagonals as zeroes just to see if it still runs and it's not an XLRisk problem. If not then it could be that the correlation matrix is inconsistent. kb.palisade.com/index.php?pg=kb.page&id=75 Unfortunately unlike commercial software like @Risk or Frontline Solver which have Auto Corr matrix adjustment tools, you might have to find a way to use Python to calc the eigen values... if you know a bit of coding you might be able to apply the method described here to adjust your coefficients community.wolfram.com/groups/-/m/t/1078904

Try these stackoverflow.com/questions/10939213/how-can-i-calculate-the-nearest-positive-semi-definite-matrix or stackoverflow.com/questions/43238173/python-convert-matrix-to-positive-semi-definite

Did you mean the XLXS file? It's here github.com/ZhijingEu/MonteCarloSimulation

I also have a Python version of the same worked example here : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-UISccjS1Gv4.htmlsi=5j9qtmt7P6X17B_E

@@CH2CH2CH2 Amazing!! Thank you.

@@CH2CH2CH2 Thank you.