Build your own Monte Carlo spreadsheet

• Main Author: Derek S Christie
• Format: Article
• Language: English
• Published: Bond University
• Series: Spreadsheets in Education
• Online Access: http://sie.scholasticahq.com/article/4672-build-your-own-monte-carlo-spreadsheet.pdf

This article gives instructions for building a simple but general purpose spreadsheet for collating Monte Carlo (MC) outputs using a VBA macro. Two user defined functions for sampling with and without replacement give the spreadsheet the facility to do a variety of resampling and permutation tests, while a third user defined function generates random values from a triangular distribution to be used in risk analysis. The spreadsheet is suitable for classroom use, and a collection of ten simple but interesting examples gives students an introduction to MC simulation, MC integration, MC confidence intervals, randomization and permutation tests, and MC risk analysis. Supplementary material includes the full Monte Carlo spreadsheet, along with further examples in each topic and a file containing the data used.