Want to Perform a Monte Carlo Simulation in Excel?

Follow this Monte Carlo Example using Excel and QI Macros

This example combines Excel's Random Number Generator with QI Macros Histogram to Predict Performance.

At ASQ WCQI 2022, Stephanie Parker gave an excellent presentation on how to do Monte Carlo simulation using a case study about glass, involving the thickness of the glass and the height of a bushing. I'm going to use Excel and QI Macros to recreate what she covered.

First, install Excel's Data Analysis Toolpak

  1. Click on File -> Options -> Add-Ins -> Excel Add-ins -> Go:
  2. Excel Data Analysis Toolpak Enable

  3. Select Analysis Toolpak:
  4. Excel Analysis Toolpak Addin

Next, determine what inputs are required and the formula to describe Y = f(x)

In this example, panes of glass are attached using clamps through machined holes protected by bushings.

  1. x1 = thickness of the glass
  2. x2 = height of the bushing
  3. Y = f(x) = x1-x2

In this case, both x1 and x2 are normally distributed. Other distributions can be used.

Use Excel's Data Analysis Toolpak to generate random numbers for each x. Starting mean and standard deviation can be estimated from a number of samples (glass and bushing). Recommended number of data points, a minimum of 1,000.

  1. On a blank Excel worksheet, choose Data -> Data Analysis -> Random Number Generator:
  2. Excel Random Number Generator

  3. Generate 1,000 points for Glass using estimated mean and standard deviation shown below:
  4. Random Numbers for Glass

    Random Numbers for Generation

  5. Repeat for Bushing Height:
  6. Random Number Bushing

  7. Add a formula for the difference and extend the calculations in Column C down to Row 1,001:
  8. Excel difference formula

  9. Select the Difference column data and run a QI Macros Histogram with LSL = 0 and no USL:
  10. monte-carlo-simulation-before

  11. As you can see, Cpk = -0.45, so this is not capable of meeting customer requirements. So, the team had to figure out how to adjust the bushing height. Using 3-D printed bushings, they ran 30 more samples to get a baseline mean and standard deviation. Then, they re-ran the Monte Carlo simulation:
  12. Monte Carlo Simulation After

    The change pushed Cpk over 2.0 (Six Sigma).

This has been a simple example of Monte Carlo Simulation using Excel's Random Number Generator and QI Macros' Histogram.

Another example might be cycle time on an assembly line that combines the cycle time for each machine or process (Y = x1 +x2 +x3). Or we could simulate arrivals at a hospital ER using a Poisson distribution. NOTE: There are several Key Distributions available in Excel's Random Number Generator:

Excel Random Number Generator Distributions

QI Macros Draws Histograms and These Charts Too!