How to Conduct a Monte Carlo Simulation in Excel: A Step-by-Step Guide – Mastering Monte Carlo in Excel in Just 15 Minutes

1. Set Up the Spreadsheet:

1. Open a new Excel sheet.
2. Enter your task data in cells A1 to D4:

| Task | Best Case (days) | Most Likely (days) | Worst Case (days) |
|------|------------------|--------------------|-------------------|
| A    | 2                | 5                  | 10                |
| B    | 1                | 4                  | 7                 |
| C    | 3                | 5                  | 9                 |

3. In the cells E2 to E4, we’ll enter the formula for our simulation.

2. Formulate the Simulation:

In cell E2, input the following formula:

=ROUND(B2 + (C2-B2+1)*RAND() + (D2-C2)*RAND(),0)

This formula essentially simulates a random number between the best case and worst case using a triangular distribution.

• Drag this formula down for cells E3 and E4 for the other tasks.
• In cell E5, sum up the simulated durations for each task: =SUM(E2:E4). This provides the total estimated duration for that simulation.

3. Prepare for Multiple Simulations:

In a real-world scenario, we’d like to run the simulation multiple times (e.g., 1000) to understand the distribution of possible outcomes.

1. Label column G as “Simulation Results” in cell G1.
2. We’ll store each result in column G from G2 onwards.

4. Create a Macro to Run Simulations:

Using Excel VBA (Visual Basic for Applications), we can automate the process of running the simulation multiple times.

1. Press ALT + F11 to open the VBA editor.
2. Click Insert > Module to add a new module.
3. Paste the following code into the module window:
4. Close the VBA editor.

Sub RunMonteCarlo()
    Dim i As Integer
    For i = 2 To 1001  'This runs the simulation 1000 times
        Calculate
        Cells(i, 7).Value = Cells(5, 5).Value
    Next i
End Sub

5. Run the Macro:

1. Press ALT + F8 to display the “Macro” dialog.
2. Select RunMonteCarlo from the list and click “Run”.

After you’ve run the macro, column G should be populated with 1,000 simulated project durations.

6. Analyze the Results:

1. You can use Excel’s “Data Analysis” tool to generate a histogram for your results. This will show you the distribution of potential project durations.
2. Calculate mean, median, mode, and standard deviation to understand the central tendency and dispersion of your data.
3. Calculate percentiles (e.g., 90th percentile) to determine the worst-case scenarios with a 10% probability.

By the end of these steps, you’ll have a clearer understanding of the potential durations of your project. The real power of the Monte Carlo simulation is in its ability to give insight into the range of possible outcomes, not just a single estimate. Remember, the more simulations you run, the more accurate and distributed your results will be.

Reference screenshot in excel.

Monte Carlo in 15 Minutes: Analyzing the Results in Excel