Monte Carlo Simulation with Excel and QI Macros

Combining 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 estimate 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


Why Choose QI Macros to Perform Capability Analysis and Draw Histograms?

affordable

Affordable

  • Only $329 USD - less with quantity discounts
  • No annual fees
  • Free Technical Support

easy to use

Easy to Use

  • Works Right in Excel
  • Calculates Cp Cpk Pp Ppk
  • Accurate Results Without Worry

proven and trusted

Proven and Trusted

  • 100,000 Users in 80 Countries
  • Celebrating 20th Anniversary
  • Five Star CNET Rating - Virus Free