Monte Carlo Simulation in Excel: A Practical Guide

There are many things that faster computers have made possible in recent years. For scientists, engineers, statisticians, managers, investors, and others, computers have made it possible to create models that simulate reality and aid in making predictions. One of the methods for simulating real systems is the ability to take into account randomness by investigating hundreds of thousands of different scenarios. The results are then compiled and used to make decisions. This is what Monte Carlo simulation is about.

Advertisement

Monte Carlo simulation is often used in business for risk and decision analysis, to help make decisions given uncertainties in market trends, fluctuations, and other uncertain factors. In the science and engineering communities, MC simulation is often used for uncertainty analysis, optimization, and reliability-based design. In manufacturing, MC methods are used to help allocate tolerances in order to reduce cost. There are certainly other fields that employ MC methods, and there are also times when MC is not practical (for extremely large problems, computer speed is still an issue). However, MC continues to gain popularity, and is often used as a benchmark for evaluating other statistical methods.

This article will guide you through the process of performing a Monte Carlo simulation using Microsoft Excel. Although Excel will not always be the best place to run a scientific simulation, the basics are easily explained with just a few simple examples. If you frequently use Excel for modeling, whether for engineering design or financial analysis, I highly suggest one of the Excel add-ins listed below.

MC Simulation Software

The popularity of Monte Carlo methods have led to a number of superb commercial tools. The programs listed below work directly with Excel as add-ins. Crystal Ball and @Risk are the two most popular and are very high quality (which you would expect from the price). Risk Solver is an amazing new add-in created by the makers of the famous Excel Solver add-in. Risk Solver runs at lightning speed and certainly rivals Crystal Ball and @Risk.