Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Monte Carlo Simulation
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 |
#2
|
|||
|
|||
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" wrote in message news:TheRobsterUK.1vshib_1127441103.9347@excelforu m-nospam.com... 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 |
#3
|
|||
|
|||
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" wrote in message news:TheRobsterUK.1vshib_1127441103.9347@excelforu m-nospam.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can excel do Monte Carlo simulation? | Excel Discussion (Misc queries) | |||
How can i set up a MONTE CARLO SIMULATION macro in Excel? thx | Excel Worksheet Functions | |||
Software that works well with excel to conduct monte carlo simula. | Excel Discussion (Misc queries) | |||
How can i made a monte Carlo simulation with Excel ? | Excel Discussion (Misc queries) | |||
How do I perform a Monte Carlo simulation in Excel? | Excel Discussion (Misc queries) |