Monte Carlo Simulation

[TheRobsterUK]

Has anyone ever created a spreadsheet that used Monte Carlo Simulation?

I've been able to create one that works using the random number generator in conjunction with discrete probability distributions - that's fairly easy, just assign a probability to each discrete value in the distribution and then decide whether or not to select it using a randomly generated number.

However, I want to perform a Monte Carlo simulation that uses a continuous (not discrete) probability distribution, but have no idea how I would use the random number generator to then select a value from the distribution based on its probability of occurance.

I know it can be done, I just have no idea how!

Anyone done anything like this before?

Cheers
-Rob

[Mike Middleton]

Rob -

Yes, I do it several times each semester when I'm teaching MBAs. But after a
quick demonstration using worksheet functions, I shift to an add-in that
automates the process.

If you have a function or formula for the inverse of the cumulative function
for the desired distribution, it's easy.

For example, you can get normal-distribution random numbers using
=NORMINV(RAND(),mean,stdev). But NORMINV and RAND are numerically accurate
only in Excel 2003, and it's really slow. So commercial Monte Carlo
simulation software might use the Box-Muller algorithm.

And you can use the Data Table command to replicate trials. Then summarize
results using the array-entered FREQUENCY function or the Histogram tool.

If you'd like to look at my "Example of Simulation in Excel Without
Add-Ins," you can download RandSamp.xls from the "Decision Analysis Using
Excel" page of my university web site http://www.usfca.edu/~middleton.

- Mike
www.mikemiddleton.com

"TheRobsterUK" <[email protected]>
wrote in message
news:[email protected]...
>
> Has anyone ever created a spreadsheet that used Monte Carlo Simulation?
>
> I've been able to create one that works using the random number
> generator in conjunction with discrete probability distributions -
> that's fairly easy, just assign a probability to each discrete value in
> the distribution and then decide whether or not to select it using a
> randomly generated number.
>
> However, I want to perform a Monte Carlo simulation that uses a
> -continuous- (not discrete) probability distribution, but have no idea
> how I would use the random number generator to then select a value from
> the distribution based on its probability of occurance.
>
> I know it can be done, I just have no idea how!
>
> Anyone done anything like this before?
>
> Cheers
> -Rob
>
>
> --
> TheRobsterUK
>
>
> ------------------------------------------------------------------------
> TheRobsterUK's Profile:
> http://www.excelforum.com/member.php...fo&userid=9924
> View this thread: http://www.excelforum.com/showthread...hreadid=470103
>

[David J. Braden]

In addition to Kike's software, consider looking into PopTools, a free
add-in with *many* features, among them tools to help you with MC simulation
and bootstrapping.

Dave B

"TheRobsterUK" <[email protected]>
wrote in message
news:[email protected]...
>
> Has anyone ever created a spreadsheet that used Monte Carlo Simulation?
>
> I've been able to create one that works using the random number
> generator in conjunction with discrete probability distributions -
> that's fairly easy, just assign a probability to each discrete value in
> the distribution and then decide whether or not to select it using a
> randomly generated number.
>
> However, I want to perform a Monte Carlo simulation that uses a
> -continuous- (not discrete) probability distribution, but have no idea
> how I would use the random number generator to then select a value from
> the distribution based on its probability of occurance.
>
> I know it can be done, I just have no idea how!
>
> Anyone done anything like this before?
>
> Cheers
> -Rob
>
>
> --
> TheRobsterUK
>
>
> ------------------------------------------------------------------------
> TheRobsterUK's Profile:
> http://www.excelforum.com/member.php...fo&userid=9924
> View this thread: http://www.excelforum.com/showthread...hreadid=470103
>

[Nadia PD]

Dear Rob
Did you find the right solution for your question?
I have your problem exactly.
I have to do the example for my course using Monte Carlo simulation for the continuous function that they are unsolvable analytically like as Normal function.
But I can't do that so if you have any experience, please let me know.
Thanks
Nadia