|
So far we've looked at a few simple "hybrid approach" examples, using VBA code
interacting with worksheet input and output. Each of these examples has
been based on a single modeled variable - the flip of a coin, the weather, etc.
Now we'll go a small step forward and give an example of a model involving
multiple random variables, all calculated in the VBA code.
For this example, we'll return to the
"commute time" problem we first modeled with the worksheet-only approach.
To recap the problem, we're modeling how long it takes to get to work given
severable variables. Our journey has three segments:
-
Drive 2 miles on a highway, with 90% probability
you will be able to average 65 MPH the whole way, but with a 10% probability
that a traffic jam will result in average speed of 20 MPH.
-
Come to an intersection with a traffic light that
is red for 90 seconds, then green for 30 seconds.
-
Travel 2 more miles on a surface street. 70% of
the time you travel at 30 MPH. 10% of the time you average 20 MPH, 10% of
the time you average 40 MPH, and 10% of the time there's a traffic jam that
takes you 30 minutes to travel these two miles.
The Inputs section of the worksheet, shown below,
lets us set the iterations and screen update interval as usual. Then it
allows us to set the values of each segment of the commute separately. The
VBA code will use these inputs to calculate the drive time for each segment
separately during each iteration.
Let's take a look at the VBA code.
| 
