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

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

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.