XLSTAT: Run a Simulation Model with Correlations Between Distributions and Compute Process Capability Indicators in Excel

 

Background

Simulation models help estimate statistics like the mean, median, or confidence intervals for variables with uncertain values, assuming known or estimated distributions. If result variables depend on these uncertain inputs via formulas, they also follow a distribution. Sim uses simulations to generate empirical distributions for input and output variables and compute related statistics.

These models are widely used in fields such as finance, insurance, medicine, oil and gas, accounting, and sales forecasting.

A simulation model includes four key elements:

• Distributions: Assigned to random variables to model uncertainty. XLSTAT offers over 30 types, such as triangular distributions.

• Scenario variables: Fixed values in the model, except during tornado analysis when they vary within defined bounds.

• Result variables: Outputs dependent on random and scenario variables through Excel formulas.

• Statistics: Metrics (e.g., standard deviation) tracked for result variables.

A valid model includes at least one distribution and one result variable and can span a single sheet or an entire Excel workbook.

 

Dataset for Example

Using the Simple Simulation Model dataset, we enhance the model by adding a correlation matrix and an SPC (Statistical Process Control) analysis. The model simulates shop performance, where benefit = sales − costs.

Historical data analyzed with the distribution fitting tool revealed:

• Costs follow a normal distribution: mean = 120, standard deviation = 10

• Sales follow a normal distribution: mean = 80, standard deviation = 20

We assume a Spearman correlation coefficient of 0.8 between costs and sales, reflected in the correlation matrix(lower triangle is sufficient). Ensure the row/column headers match the variable names used in distribution definitions.

An SPC analysis is also included for sales, costs, and benefit. During yearly planning, we defined upper/lower specification limits and a target value, aligned with the static model.

This model can be found in the Model sheet.

 

Setting up

• Open XLSTAT and go to Monte Carlo Simulations > Run.

In the Simulation - Run dialog box:

• Set Number of simulations to 1000

• Enable Correlation/covariance matrix

• Select the correlation matrix, including row and column headers.

In the Charts - Sensitivity tab:

• Enter the parameters of the  tornado and spider analysis .

• Select the standard cell value as default value. Choose  10 data points in the interval from -10% up to +10% of the value deviation:

In the SPC tab:

• We activate the  calculate process capabilities option.

• Choose for the selection field LSL the three Excel cells below the cell LSL, because the column label is not necessary. Fill in  USL in the same way.

• In the field Name select the 3 cells with the names of the model elements for which the SPC analysis should be carried out: "sales", "costs" and "benefit" located to the left of the model elements.

• Last, activate the target option to calculate SPC values that need a target value.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

In the model summary the proximity matrix is displayed: 

After the following tables that contain the details of the distribution and result variables, additional results of the SPC analysis are displayed:

Finally, the correlation matrix of the distribution and result variables is displayed. The Spearman correlation between costs and sales is close to 0.8. With more simulation iterations, this correlation would converge even more closely to 0.8.