Assessing Excel VBA Suitability for Monte Carlo Simulation

• Main Author: Alexei Botchkarev
• Format: Article
• Language: English
• Published: Bond University
• Series: Spreadsheets in Education
• Online Access: http://sie.scholasticahq.com/article/4629-assessing-excel-vba-suitability-for-monte-carlo-simulation.pdf

Monte Carlo (MC) simulation includes a wide range of stochastic techniques used to quantitatively evaluate the behavior of complex systems or processes. Microsoft Excel spreadsheets with Visual Basic for Applications (VBA) software is, arguably, the most commonly employed general purpose tool for MC simulation. Despite the popularity of the Excel in many industries and educational institutions, it has been repeatedly criticized for its flaws and often described as questionable, if not completely unsuitable, for statistical problems. The purpose of this study is to assess suitability of the Excel (specifically its 2010 and 2013 versions) with VBA programming as a tool for MC simulation. The results of the study indicate that Microsoft Excel (versions 2010 and 2013) is a strong Monte Carlo simulation application offering a solid framework of core simulation components including spreadsheets for data input and output, VBA development environment and summary statistics functions. This framework should be complemented with an external high-quality pseudo-random number generator added as a VBA module. A large and diverse category of Excel’s incidental simulation components that includes statistical distributions, linear and non-linear regression and other statistical, engineering and business functions require execution of due diligence to determine their suitability for a specific MC project. Editorial note Although the present article is not directly concerned with “spreadsheets in education”, the decision to publish it was made on more general grounds. Microsoft Excel has received plenty of bad press concerning its statistical functions over a period of many years, and Microsoft has been slow to address many of these issues. However, the situation appears to have improved significantly since the early publications of McCullough & Wilson in the journal Computational Statistics & Data Analysis (references [72] and [73] herein), in which problems with Excel’s statistical functions were highlighted. We have chosen to publish the present article so readers have some more up-to-date information on which to base their decisions. As a final comment, readers should also be aware of the existence of RExcel, a port of the statistical package R to Excel as an add-in. It is a free download and is the work of one of our own editorial board members, Emeritus Professor Erich Neuwirth, University of Vienna. Thus, one may have one’s cake and eat it too: the acknowledged accuracy and respectability of R, along with the friendly, well-known interface of Excel.