Monte Carlo Spreadsheet Simulation Using Resampling
Thin-Yin Leong - tyleong@smu.edu.sg
School of Information Systems, Singapore Management University, 80 Stamford Road, Singapore S178902
Abstract
The ubiquitous spreadsheet can be used to model situations with random values, in what is commonly referred to as Monte Carlo simulation. For simple cases, adding random functions such as Excel's RAND is enough. In general business models, complex inverse distribution functions, in combination with RAND, are needed to generate the right random values. But first the modeler must determine the appropriate best-fit distribution to use. This can be a daunting process for undergraduates and typical executives. So for expediency, simulation add-ins (with additional learning time and possible costs) may be employed. The use of add-ins, however, makes the modeling less transparent. A more direct alternative is to resample the raw data, which in many cases are not sufficient in sample size to establish statistical goodness of fit. This paper reviews the limitations of current spreadsheet resampling methods and proposes new simple yet effective formulations that better accommodate classroom and practical real-world application.
Download the PDF
10.1287/ited.7.3.188
Supplement Files
OptimalPortfolio.xls Resampling.xls
Citation Information
Leong, T. 2006. Monte Carlo Spreadsheet Simulation Using Resampling. INFORMS Trans. Ed. 7(3) 188-200. Available online at http://ite.pubs.informs.org/.
DOI: 10.1287/ited.7.3.188