|
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:
| 
