Jake Blanchard Spring 2010 Uncertainty Analysis for Engineers 1 Monte Carlo Simulation in Excel There are at least three ways to do MCS in Excel Fill a bunch of cells with appropriate random numbers ID: 733767
Download Presentation The PPT/PDF document "Monte Carlo Simulation in Excel" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Monte Carlo Simulation in Excel
Jake BlanchardSpring 2010
Uncertainty Analysis for Engineers
1Slide2
Monte Carlo Simulation in Excel
There are at least three ways to do MCS in ExcelFill a bunch of cells with appropriate random numbers
Write a macro in VBAUse an add-in (Crystal Ball, @Risk, etc.)
Uncertainty Analysis for Engineers
2Slide3
Filling Cells
AdvantagesEveryone has ExcelEveryone is familiar with formulas in cells
DisadvantagesSlowCumbersome if you need many samplesNot many built-in distributions
Uncertainty Analysis for Engineers
3Slide4
Typical Sheet
Uncertainty Analysis for Engineers
4Slide5
Formulas
Uncertainty Analysis for Engineers
5Slide6
Scripting in VBA
Start editor from Developer Tab in Excel 2007If you don’t see Developer Tab, Go to Office Button in upper left and then to Excel Options (at bottom of window)
Once editor opens, go to Insert/Module in VBA editorThen paste in script on next page
Uncertainty Analysis for Engineers
6Slide7
The Script
Function dice(N, above)
Count = 0For
i
= 1 To N
roll1 = Ceil(6 *
Rnd
)
roll2 = Ceil(6 *
Rnd
)
tot = roll1 + roll2
If tot > above Then
Count = Count + 1
End If
Next
dice = Count / N
End Function
Public Function Ceil(
ByVal
X As Double) As Double
Ceil = (
Int
(X) - (X -
Int
(X) > 0))End Function
Uncertainty Analysis for Engineers
7Slide8
Built-In Tools
Crystal Ball and @Risk both add Monte Carlo Simulation capability into ExcelIn @Risk
Build a model, assuming all parameters are deterministicReplace random variables with appropriate pdf’s
Identify output variables
Run simulation
Look at output
Uncertainty Analysis for Engineers
8