• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Running monte carlo simulation

C

crazyhorse55

New Member
Hi guys,


I have not been using these forums very long, but take an interest in some of the topics now and again (just registered now to ask this question)


Anyway, I am looking to run a monte carlo simulation using Excel for a risk management module at university. Basically from a list of risks (excluding costs/profit and loss), I had to chose one that could be measured numerically. The risk chosen was exposure to a chemical leak, and I was told this would be a good topic to run as it has defined limits so to speak - what would be safe exposure and what would be dangerous. I am having a little difficulty getting started, I know that I need the safe exposure limits, but not sure where to go from there.


I think this can be done, here is a link to something similar http://annhyg.oxfordjournals.org/content/51/2/161.full

but again not sure where to start really.


Please could anyone help? Im not looking for someone to actually do this for me, just a case of pointing me in the right direction with what I need to get started with data wise and basics for running the simulation


Thanks in advance.
 
Hi Crazyhorse55,


Welcome to the forums...


Can you give me sometime so that i may start working over it. I just looked at it and it appears interesting? Have you done some ground work on this bca simulation is just one part of the entire case. Other databases that the link is mentioned are you done with them?


Regards,

Faseeh
 
Crazyhorse55


Firstly, Welcome to the Chandoo.org forums.


Have you read: http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/
 
Hi Faseeh and Hui,


Thanks for your reply, I have done no ground work Im afraid as I am very confused with where to begin,


This is the first time I have run practical simulation of my own, I have done just very basic stuff like mann whitney, z test etc mainly looking at differences between sets of data in the past.


I have not had a look at the other databases mentioned in the link, in my case my raw data (the exposure limits) will come from this http://www.hpa.org.uk/webc/HPAwebFile/HPAweb_C/1219908739137


If you look at page 8, it shows a table of some exposure limits, at the minute just for simplicity I am not looking at any other sources, but I may once I get this up and running.


Yes please take as long as you need,


Hui, I had a scan of this link previously, but I will now make time to look over properly.


Kind Regards
 
Hi crazyhorse55,


You keep working on the guide lines Hui has given, mean while i try to do something.. :)


Regards,

Faseeh
 
for these types of simulations, I have found that using the solver add-in is the best method bar none. I guess it's how involved and how much time you want to invest. Stats in excel is pretty vast area, there are a few good books out there, but the one I have found to be invaluable is:


Microsoft Excel 2010: Data Analysis and Business Modeling [Paperback]

Wayne L. Winston Ph.D. (Author)
 
Hi crazyhorse55,


Since i made my last post on this thread, i have been trying to grip the contents of the article that's link you have pasted in, in your first post.


I can have prepared the simulation for the table that you second post has mentioned on page no. 08, but complete simulation of this model is beyond my level of understanding. That is more a job of statistician then some one like me.


Secondly, a word of caution, that the simulation method Hui has reffed to relies on Normal Distribution while your simulation needs a Log Normal Distribution.


Regards,

Faseeh
 
Faseeh, CrazyHorse55


The Monte Carlo techniques using Log Normal is the same

You will just use the

Code: 
=LOGNORM.INV(x, Mean, SD) function

instead of the

=NORM.INV(x, Mean, SD)
function
 
@Crazyhorse55

With 30+ posts a day if you don't keep on top of active posts, they get out of hand
 
Hi guys, just an update,


I have my limits at 0.1 and 2.5, and have set 5000 random numbers (within this range) using excel. I am using a normal distribution, can someone point me in the right direction, Im a little lost.


Thanks
 
Hi crazyhorse55,


Please see this file, read comments and give feedback:


http://dl.dropbox.com/u/60644346/Monte%20Carlo%20Sample.xlsx


Regards,
 
Wow this is perfect, I had concerns about how exactly I could get 0.08 into the simulation, so my tutor adviced me to start at 0.1, then I was able to use the formula in the excel sheet attached. But your method seems much better


http://www.fileconvoy.com/dfl.php?id=g1c72cad87b2330ed999177199d03907b34dd4d66e


Do I have to do anything further as part of the simulation? Or is that it all it involves?


By the way thank you very much for your help Faseeh, I would not have been able to do this without you!!!!


kind regards
 
I didnt know how to edit the previous post, but I have changed slightly what Faseeh had to randbetween 0 and 500 and then divide by 100. This was the only way I could get figures such as 0.02 The technique I used in my attempt would not let me get anything less than 0.1 and I think there are other issues with using that formula also.


But yes thanks for your help, just to repeat the previous question, is anything further required of the data, or is that the simulation complete?


Thanks again.
 
Crazyhorse55


To get values between 0.08 and 2.5 use

=RAND()*(2.5-0.08)+0.08
 
You must log in or register to reply here.
Back
Top