power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Monte Carlo modeling in Excel using the minimal VBA approach

ShareThis

As a final example, we'll use a comical example to illustrate the "minimal VBA" approach to Monte Carlo modeling in Excel.

The model calculates the probability of the "Bad Day" index being within given ranges.  This model was inspired by the circumstances which led to my actually having the time to develop this tutorial:  I am recovering from being hit by a car, fortunately suffering only a few broken ribs and a leg injury rather than something more permanent.  The day after the accident while in the hospital on heavy painkillers, I received a phone call from the counterparty in a major deal I was working on, saying they had decided to pull out.  By the end of the week my teenager had - well, let's just say "made a bad decision."  So, I decided to put the doctor-recommended "take-it-easy" time to productive use by extending the original worksheet-only approach tutorial, now to include the long-promised VBA approach.  I  hope you have benefited from the fruit of my "Bad Day."

The Bad Day model takes four different causes of bad days - headaches, kids, bosses, and money - and for each of these four variables lets the user specify the frequency, average severity, and standard deviation of severity.  The worksheet then uses the Rand() function to simulate a given day for each of the four variables, then adds the four values to create the "Bad Day Index" for that day.

Just for fun, as an exercise, you might take the existing model and enhance it with a "Perfect Storm" multiplier - if 2 bad things occur on a given day, the total badness is multiplied by, say, 1.2, and if 3 occur, the multiplier is 2.0, etc.

Here's the input screen:

 

Prev | Contents | Next

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.