Building a worksheet-based Monte Carlo simulation to calculate time for commuting to work

ShareThis

Now that we've built a couple of models to illustrate the basic concepts, we'll take the next step of building a model that actually solves a problem that would be difficult to solve without a Monte Carlo method approach.

We will calculate the time it takes to commute from home to the office.  We are trying to answer the following two questions:

1.  How much time do I need to allow in order to have 75% confidence that I will arrive on time?
2.  How much time should I allow in order to have 99.5% confidence that I will arrive on time (on days that start with an important meeting)?

Later in this tutorial we will cover how to model variables that follow normal distribution curves.  For now, though, we'll keep it a bit less complex.  Our algorithm for getting to work is as follows:

• 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.

On the next page we'll develop an Excel equation for each of the three steps of our commute.

Prev | Contents | Next