Monte Carlo in 15 Minutes: Analyzing the Results in Excel

1. Creating a Histogram using Excel’s “Data Analysis” Tool:

(Note: The “Data Analysis” tool is part of Excel’s Analysis ToolPak, and you might need to enable it first if you haven’t.)

1. Enabling Analysis ToolPak:
   • Go to the File tab.
   • Click Options.
   • In the “Excel Options” dialog box, click Add-Ins.
   • In the “Add-Ins” box, check if Analysis ToolPak is listed under “Inactive Applications”.
   • If yes, click on Analysis ToolPak and then click on the Go button.
   • In the “Add-Ins” dialog box, check Analysis ToolPak and click OK.
2. Creating the Histogram:
   • Go to the Data tab.
   • Click on Data Analysis in the Analysis group.
   • In the “Data Analysis” dialog box, choose Histogram and click OK.
   • For the Input Range, select all your simulation results (e.g., G2:G1001).
   • For the Bin Range, you can specify the intervals you want (like 10-day intervals) or let Excel decide.
   • Check the Chart Output box to generate a histogram chart.
   • Click OK.

Refer screenshot

Now, you should see a histogram that gives you a visual representation of how the results from your Monte Carlo simulation are distributed.

Refer result screenshot

2. Calculating Basic Statistical Measures:

1. Mean:
   • Use the formula =AVERAGE(range). For our example, it would be =AVERAGE(G2:G1001).
2. Median:
   • Use the formula =MEDIAN(range). For our example, it would be =MEDIAN(G2:G1001).
3. Mode:
   • Use the formula =MODE.SNGL(range). For our example, it would be =MODE.SNGL(G2:G1001).
4. Standard Deviation:
   • Use the formula =STDEV.P(range). For our example, it would be =STDEV.P(G2:G1001).

3. Calculating Percentiles:

1. 90th Percentile:
   • Use the formula =PERCENTILE.INC(range, percentile).
   • For our example and the 90th percentile, it would be =PERCENTILE.INC(G2:G1001, 0.9).

Remember, these steps provide you with insights into the range and concentration of your results. The histogram offers a visual representation, while the statistical measures give precise values to describe the distribution of outcomes.