Worksheet-based and VBA-based approaches

ShareThis

There are two basic approaches for developing Monte Carlo simulations in Excel:  the worksheet-based approach, and the VBA approach.

The worksheet approach is generally easier to implement, does not require use or knowledge of the Visual Basic programming language, and provides a very efficient solution to many types of problems.  In this approach, you build a compact model of the problem on an Excel worksheet - typically in a single row - and then copy and paste the model (the spreadsheet row) as many times as you wish in order to generate the iterations of the model.  For example, if you want to run the model 10,000 iterations to generate your data set for analysis, you would copy & paste the row containing your model 10,000 times.

There are at least three significant limitations of the worksheet approach.  First, the current version of Excel has a maximum of 65,536 rows per spreadsheet.  Thus your Monte Carlo programs would have a practical limitation on number of iterations that can be run.  Of course it's possible to use multiple separate worksheets, or adjacent column groups on the same worksheet, to increase the number of iterations several fold.  However, for a highly complex problem requiring millions of iterations in order to generate confidence around the resulting data set, it becomes impractical to use the worksheet approach.

Second, the worksheet approach requires reducing the problem to a single spreadsheet row, or at least to a compact enough group of cells to enable easy replication of the single instance to the desired number of iterations.  A problem such as the "Commute Time" example is simple enough to reduce to a few columns on a single spreadsheet row.  However, if you want to analyze the impact of a few dozen variables on the entire income statement and balance sheet of a company, the worksheet approach again becomes impractical.

Third, the worksheet approach consumes considerable system resources, and therefore may result in system lock-ups or other poor system performance when you need more than a few thousand iterations of the simulation.  A single simulation can use multiple instances of the RAND() function, for example.  Multiplying that single simulation by 20,000 iterations can cause the spreadsheet to bog down unbearably.

For these situations in which the worksheet approach is inadequate, we turn to Excel's built-in programming language, Visual Basic for Applications (or "VBA").  Using the programming language we can write computer code which reads input from a worksheet and writes output back to the worksheet.  Output can be in the form of printing a single output data row for each iteration (constrained by the same limits on number of rows described above), or the VBA code can tally the results itself and output only the final numbers (average, standard deviation, range, etc. of multiple variables) to designated spreadsheet cells.

ShareThis

Prev | Contents | Next