Monte Carlo Simulation in Excel: A Practical Guide
[ What is Monte Carlo Simulation? ]
Preface
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.
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.
Excel Add-Ins | |
| @Risk | $1,195 |
| Crystal Ball | $995 |
| Risk Solver | $995 |
| DFSS Master | $399 |
| RiskAMP Add-In for Excel | $129.95 |
| Risk Analyzer | $49.95 |
| Actual prices may vary from those listed. | |
[ What is Monte Carlo Simulation? ]
Books featuring Crystal Ball
Business Modeling
> Essentials of Business Statistics, 1/e, by: James R. Evans
> Quantitative Business Modeling, 1/e, by: Jack R. Meredith, Scott M. Shafer, Efraim Turban
> Quantitative Methods for Business, 8/e, by: David R. Anderson, Dennis J. Sweeney, Thomas A. Williams
> The Art of Modeling with Spreadsheets: Management Science, Spreadsheet Engineering, and Modeling Craft, by: Stephen G. Powell, Dartmouth College, and Kenneth R. Baker, Dartmouth College
Decision Analysis
> Statistics, Data Analysis, and Decision Modeling, 2/e, by: James R. Evans and David L. Olson
> Spreadsheet Modeling and Decision Analysis, 4/e, by: Cliff Ragsdale, Georgia Southern University
Risk Analysis
> Applied Risk Analysis: Moving Beyond Uncertainty, by: Johnathan Mun, Decisioneering
> Introduction to Simulation and Risk Analysis, 2/e, by: James R. Evans, University of Cincinnati, and David L. Olson, Texas A&M University
Management Science
> Management Science and Decision Technology, by: Jeff Camm and James Evans
> Managerial Decision Modeling with Spreadsheets, by: Barry Render, Ralph M. Stair Jr., and Nagraj Balakrishnan
> Management Decision Making: Spreadsheet Modeling, Analysis, and Applications, by: George E. Monahan
> An Introduction to Management Science: Quantitative Approaches to Decision Making, 10/e, by: David Ray Anderson, Dennis J. Sweeney, Thomas Arthur Williams
> Introduction to Management Science, 7/e, by: Bernard W. Taylor, III, Virginia Polytechnic Institute & State University
> Data Models and Decisions: The Fundamentals of Management Science, by: Dimitris Bertsimas, MIT Sloan School of Management, and Robert Freund, MIT Sloan School of Management
Project Management
> Project Management: A Managerial Approach, 5/e, by: Jack R. Meredith and Samuel J. Mantel
> Project Management in Practice, by: Samuel J. Mantel (Editor), Jack R. Meredith, Scott M. Shafer, Sutton, Margaret Sutton, Jr., Samuel J. Mantel
Real Options Analysis
> Real Options Analysis: Tools and Techniques for Valuing Strategic Investments & Decisions, by: Dr. Johnathan Mun
> Real Options Analysis Course: Business Cases and Software Applications, by: Dr. Johnathan Mun