In my last post I commented on how simple Monte-Carlo analysis was to run without the need for expensive Risk Management software. I posted an example of a simple spreadsheet to do just that in the 'Toolbox' page of this blog. I wanted to post a short guide for it to get you started, should you wish to use it.
Begin by navigating to the 'Toolbox' page using the links on the right-hand side of the PM Shed homepage. Select the link and download the spreadsheet.
Once you have it open, start entering your risk information in the table in the top right. A brief risk description should be put in column B, and the Cost Impact and Probability figures should go in columns C and D respectively (feel free to add extra rows if necessary, but the sheet's formulae will need to be updated)
Column E will then calculate the 'Factored Value' (Impact x Probability), and the sum of this in cell E14 will show the basic 'Management Reserve (MR)' or 'Technical Contingency' for the list of risks.
Columns H onwards display 10,000 simulated runs through our project using MS Excel's 'RANDBETWEEN' function (a random number generator, shown in column G for information only). The total risk cost for each 'run' is shown in row 12, and row 13 then shows whether the current MR was sufficient to cover this cost and displays a YES or a NO accordingly.
Cell G15 simply shows the percentage of 'YES's accross all runs. This percentage tells us what confidence we should have that the MR funding is appropriate for the level of risk on the project. Simply summing all the Cost x Probability figures for each risk will likely give a confidence of around 50%. As you will see with example figures I have entered, the percentage is around 53%. If you press F9, new random numbers will be generated for all 10,000 runs, but you will notice that the percentage changes very little due to the large sample size of the analysis.
The last step is to add some additional 'confidence funding' in cell E13. As you increase the figure in this cell, you will see the confidence percentage increase. It would be fairly simple to augment the spreadsheet such that the desired percentage could be entered and the required confidence funding calculated for you - but I'll leave that to you!
Use the spreadsheet as you see fit, and please post comments if you have any questions, suggestions or.. well, comments!
No comments:
Post a Comment