Wednesday, 1 March 2017

Monte Carlo - Run Monte Carlo Simulation without expensive add-ins

(Stanislaw Ulam - inventor of Monte Carlo Simulation)

This Spreadsheet...
  • This spreadsheet allows you to run Monte Carlo simulation without use of expensive add-ins such as Crystal Ball
  • Allows definition of many different input distributions.
  • Input distribution may also be input from actual data.
  • Shows charts of input and output distributions, along with basic statistics.
  • Copies results to Histograms Etc spreadsheet for more detailed results.

Download a copy of version 1.0 here.

This spreadsheet makes the task of running a Monte Carlo simulation very easy

Both Inputs and Results are shown on the Inputs sheet. 

You may create up to 20 input distributions. 

Select the type from the drop down list.

Dependent on the type, the list of parameters changes.

Enter the values for the parameters of the distribution. Also select whether to turn off the distribution and whether the distribution is dependent on another.

You may plot each input distribution. Change the value in "Select Input Distribution #" to view a different one.

Use the "Realization Value" in your formula, calculation or model for which you want to run Monte Carlo.  

Put the results of your calculation in the "Result Values". These are the values that will be saved for each realization of the Monte Carlo in order to build a result distribution. Click the "Run Monte Carlo" button to run the simulation for the desired number of realizations.

In the example in the spreadsheet, I have put some calculations in the "Scratch Area". You can use this area for any calculations. When the Monte Carlo is run, only the "Inputs" sheet is re-calculated, so any calculations must be on this sheet.  

The realizations are saved on the Results sheet and the summary statistics are shown in the Results section on the Inputs sheet.

You may also plot each result distribution. Change the value in "Selected Result Distribution #" to view a different one.

The Beta Fit spreadsheet, to fit a Beta function to 3 points, can be downloaded here.

More detailed instructions are contained on the Instructions sheet within the spreadsheet. 

No comments:

Post a Comment