1 / 8

Monte Carlo Simulation in Excel

Monte Carlo Simulation in Excel. Jake Blanchard Spring 2010. 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 Write a macro in VBA Use an add-in (Crystal Ball, @Risk, etc.). Filling Cells. Advantages

dacey
Download Presentation

Monte Carlo Simulation in Excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Monte Carlo Simulation in Excel Jake Blanchard Spring 2010 Uncertainty Analysis for Engineers

  2. 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 • Write a macro in VBA • Use an add-in (Crystal Ball, @Risk, etc.) Uncertainty Analysis for Engineers

  3. Filling Cells • Advantages • Everyone has Excel • Everyone is familiar with formulas in cells • Disadvantages • Slow • Cumbersome if you need many samples • Not many built-in distributions Uncertainty Analysis for Engineers

  4. Typical Sheet Uncertainty Analysis for Engineers

  5. Formulas Uncertainty Analysis for Engineers

  6. Scripting in VBA • Start editor from Developer Tab in Excel 2007 • If 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 editor • Then paste in script on next page Uncertainty Analysis for Engineers

  7. The Script Function dice(N, above) Count = 0 For 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

  8. Built-In Tools • Crystal Ball and @Risk both add Monte Carlo Simulation capability into Excel • In @Risk • Build a model, assuming all parameters are deterministic • Replace random variables with appropriate pdf’s • Identify output variables • Run simulation • Look at output Uncertainty Analysis for Engineers

More Related