Sigma Magic Help Version 15

Monte Carlo Simulation

Overview

Monte Carlo Simulation can be used to simulate a model between input(s) and output by considering the variation inherent in the data for any given process. This analysis can be used to determine probability estimates instead of point estimates. For example, if we want to determine if we should enter or exit a business, instead of predicting that we would make 25% profit on an average, Monte Carlo analysis takes uncertainty into account and provides a range - for example, the profit would most likely vary from 2% to 40% (2% being the worst-case scenario and 40% being the best case scenario). Monte-Carlo analysis estimates the variation in the output based on variation in the inputs.

Monte Carlo Simulation can be used to make business decisions in the presence of uncertainty. Monte-Carlo is also typically used in a DFSS project to determine if a process is capable of meeting customer needs. It can also be used to determine the optimized set of input parameters to achieve a given level of performance.

This template can be added to your active workbook by clicking on Stats and then selecting Monte Carlo Simulation. Note that this should update your main menu bar with additional menus required for Monte Carlo simulation. If this does not open up automatically, you can press the F4 key to activate these menus. A sample screenshot of the main menu bar is shown below.

Analysis Setup

Use the following steps to create a Monte Carlo simulation model. Click on Analysis Setup button to open the settings dialog box.

Setup

A sample screenshot of the setup menu is shown below.

1	Num Columns: You can specify the number of columns to display for the inputs area. The inputs area can be used to develop your Monte Carlo Simulation model. There are no limits on the number of rows you can use but you are limited on the number of columns (50 max) so that there is sufficient space to display analysis results and store your simulation data. If you need to build a large simulation model, build it vertically rather than horizontally.
2	Column Width: Specify the width of each cell in the inputs area. You can specify the width here or manually adjust the widths on the worksheet. This option just provides sufficient space for your text for the simulation model. This option is currently disabled and you are recommended to manually adjust the column widths on the worksheet as required directly on the worksheet.
3	Num Iterations: Specify the number of iterations for the simulation. There are no limits on the number of iterations you can run but do note that a large number of iterations may require increased memory and require additional computing resources. There may not be a benefit to increase the number of iterations beyond a certain level. It is recommended that you start with a smaller number of iterations and keep increasing this number until you do not find much difference in the results with an increase in the number of iterations.
4	Num Simulations: Specify the number of simulations you would like to run for this model. For example, if you have selected 1000 iterations and 2 simulations, then the entire Monte Carlo simulation of 1000 iterations will be performed 2 times. You will be able to change some parameters between these simulation runs and analyze each simulation separately. This is a useful feature when you want to do some what-if analysis. For example, if you are using the standard deviation for one of the inputs as 12.5 and you wonder how your results will change if your standard deviation changes to 20, you could run 2 simulations with 1000 iterations each. You will need to use a Simulation Table for this study. More information regarding the Simulation Table is shared later in this help file.
5	Help Button: Click on this button to open the help file for this topic.
6	Cancel Button: Click on this button to cancel all changes to the settings and exit this dialog box.
7	OK Button: Click on this button to save all changes and exit this dialog box.

Random

A sample screenshot of the random menu is shown below.

1	Generator: Specify the algorithm to use for the random number generator. Currently, the only available option is Auto and this uses the default method to generate the random numbers.
2	Sampling: Specify the sampling method. There are two options available: Option Description Monte Carlo In the Monte Carlo method, the random numbers are directly used as generated by the random number generator for a given distribution. Latin Hypercube The Latin Hypercube method is better since it more closely follows the given distribution. The Latin Hypercube method is expensive in the sense it requires more computational power to generate. However, the results obtained from the Latin Hypercube method probably require less number of iterations to converge to the final result compared to the Monte Carlo simulation. It is recommended that you try to use the Latin Hypercube method for your analysis unless you have a strong reason to not use it for your model.
3	Random Seed: Specify the seed for the random number generator. The seed could either be random or fixed. If the seed is random, then each time you run the simulation the system will generate different random numbers for you. However, if the seed is fixed, you will get the same set of random numbers when you run the simulation each time so that you can reproduce the model results.
4	Seed Value: Specify the value of the seed. For random numbers, the seed value is 0. Use a positive integer value for the seed if you would like it to be fixed.
5	Initial Value: Specify the initial value to display on the worksheet for input cells. The available options are: Option Description Fixed If you specify fixed, you can specify the initial value for this cell. Once the simulatoin is completed, the software will return the cell value to the original value you have set. Random If you specify random, the software will determine a random number for this cell and these values can change during the course of the simulation. Initially, the expected value (mean) will be used as the starting value.
6	Multiple Simulations: Specify how you want to simulation multiple simulation runs. There are two options available: Option Description Same The same set of random numbers are used for each multiple simulation run. This makes comparing multiple simulations runs easier if they all use the same set of random numbers. Different Each simulation uses a different set of random numbers. This option makes each multiple simulation run truly random.

Options

A sample screenshot of the random menu is shown below.

1	Cells: You can set the colors for the following cells you create for the Monte Carlo simulation. Option Description Inputs Specify the colors for all the input cells. These cells contain the random numbers used to model different inputs for your simulation. Outputs Specify the colors for all the output cells. These cells contain the outputs from the simulation computed using the model you have built. Correlations Specify the colors for the correlation matrix defined between different input variables. Sim Table Specify the colors for the simulation table defined for this simulation. Functions Specify the colors for all the function cells defined for this model. A function cell takes the output variables and summarizes the values the outputs contain (such as mean, median, standard deviation etc.)
2	Foreground Color: You can click on this button to specify the foreground color for the cells.
3	Background Color: You can click on this button to specify the background color for the cells.
4	Example Color: The example shows how the cells would appear on the worksheet. If you are not happy with these colors, click on the foreground and background colors to change the settings.
5	Default Colors: You can reset the colors for all the cells to the default values.
6	No Colors: You can remove the colors for all the cells by clicking on this button.

Inputs

Click on the Inputs button to open the inputs dialog box to define the inputs for your Monte Carlo simulation. The inputs dialog box can be used to either create a new input or update an existing input variable. It is recommended that you define the name of the cell to the left of the input cell before you define the input cell so that the variable name is automatically picked up by the software. Make sure that you define the right cell location to define your input variables. The following screenshot shows a sample input dialog box.

1	Name: Specify a name for your input variable. Make sure that the names are unique and do not contain special characters. Keep the name short to keep your text output manageable.
2	Cell Location: By default, the cell location will show the active worksheet cell that was selected before clicking on the Inputs button. You can click on the icon next to this textbox to reselect a different location on the worksheet for the input location. You can also define a range of cells and the software will create inputs at each of these cells. Note that if you do specify a range, the input variables' names are appended with name.1, name.2, etc.
3	Distribution: Specify a distribution for your input from the dropdown box. This distribution is used to generate the random numbers for this input variable. Make sure that you pick a distribution that matches how you would expect this variable to behave in the real world. For example, if the input is uniformly distributed between minimum and maximum values, use a uniform distribution. If the input is normally distributed then use a normal distribution etc.
4	Parameters: Specify the parameters for the selected distribution. Each distribution has its own set of parameters and you need to specify the parameters accordingly. For example, a normal distribution requires that you specify the mean and standard deviation.
5	Percent: The percent data at the top of the density function shows the 5% limits on the left and right side of the distribution as reference values. For example, the graph shows 90% of the values lie between 16.2 and 23.8.
6	PDF: The graphs section shows a probability density function for the input distribution based on the parameters you have specified. This graph is just shown as a guide to give you an idea of the data points that will be generated by the random number generator for this input variable.
7	Stats: The stats section shows a set of summary statistics for the distribution such as min, max, mean, median, mode, range, stdev, quartiles and percentiles.
8	Cancel Button: Click on this button to cancel all changes to the settings and exit this dialog box.
9	Add Button: If this is the first time you are specifying the input distribution, you will need to click on the Add button at the bottom right hand corner to add this distribution to the model. If you had selected a cell for which an input was already defined, then you can update any previously defined distribution settings.

Outputs

Click on the Outputs button to open the outputs dialog box. This dialog box can be used to define the outputs from your Monte Carlo analysis simulation. Make sure to select the cell on the worksheet where you want to define the outputs before you click on the Outputs button as the output variable will be defined for that cell location. The following screenshot shows a sample outputs dialog box. There may be many outputs in your Monte Carlo simulation model but only some of them may be of interest to analyze and evaluate the performance of the model. Each of those outputs can be modeled using the Outputs button. If the output is defined it can be used for analyzing process capability or sensitivity analysis.

1	Name: Specify a name for your output variable. Make sure that the names are unique and do not contain special characters. Keep the name short to keep your text output manageable.
2	Cell Location: By default, the cell location will show the active worksheet cell that was selected before clicking on the Outputs button. You can click on the icon next to this textbox to reselect a different location on the worksheet for the output location. You can also define a range of cells and the software will create outputs at each of these cells. Note that if you do specify a range, the output variables names are appended with name.1, name.2, etc.
3	Formula: Each output cell must contain a formula. The formula is used to calculate the values for the output variables. If a formula is already defined on the worksheet, it will be displayed in this textbox. If not, you can also define a formula here in this textbox and the same will be updated on the worksheet.
4	Capability Analysis: Specify the method to use for performing the capability analysis for this output. The available options are: Option Description Auto If you select this option, the software will automatically pick the best approach to use for computing process capability. It will check if your data is normally distributed and if so, it will pick the Normal capability analysis. If not, it will try to estimate the best distribution that fits your data and uses that distribution to determine your process capability analysis. Distribution If you specify a specific distribution to use (for example based on historical factors), the software will estimate the parameters for this distribution and use this distribution to estimate your process capability numbers.
5	LSL: Specify the Lower Specification Limit (LSL) for the output variable. These specification limits come from the customer. All values of the output below this value of LSL is considered to be defective. If an output does not have a lower limit, leave this field blank.
6	USL: Specify the Upper Specification Limit (USL) for the output variable. These specification limits come from the customer. All values of the output above this value of USL is considered to be defective. If an output does not have a upper limit, leave this field blank.
7	Sensitivity Analysis: Specify the number of input variables to use for the sensitivity analysis. If you specify All variables, the software will use all the input variables to compute the sensitivity analysis. However, some of the inputs may not be of interest to you. If there are a large number of input variables, you may want to limit the sensitivity analysis to only those input variables of interest to you. Specify the number of input variables and specify which of the input variables you would like to consider in the dropdown boxes below.
8	Select Inputs: Specify the input variables that you would like the software to use for sensitivity analysis in these dropdown boxes.
9	Cancel Button: Click on this button to cancel all changes to the settings and exit this dialog box.
10	Add Button: If this is the first time you are specifying the output distribution, you will need to click on the Add button at the bottom right-hand corner to add this output definition to the model. If you had selected a cell for which output was already defined, then you can update any previously defined settings. Click on the Delete button to delete any previously defined outputs.

Correlations

The correlations dialog box can be used to define correlations between input variables. This step is optional and if not defined, then all the inputs are assumed to be independent with no correlation between them. You can define the correlations between 2 or more input variables. For example, if you have 2 variables (Length and Width) then you will need to select a 3x3 range on the worksheet (i.e. a range which has 3 rows and 3 columns). If you need to define correlations between 3 variables, you will need to select a 4x4 range, etc. If you have 3 variables but you only want to define a correlation between two of the input variables, you will need to pick a 3x3 range. Note that the first row and the first column are used to define the input variables and the remaining rows and columns are used to define the correlation coefficients.

Click on the Correlations button to open the correlation dialog box. Any previously defined correlation matrix will be displayed here. Note that each Monte Carlo simulation worksheet can have a maximum of one correlation matrix table defined. The following screenshot shows a sample correlation dialog box.

1	Cell Location: The location textbox defines the location on your worksheet that stores the correlation matrix. To define the correlations, click on the select button next to the range input textbox. Select a range on the worksheet where you want to correlation matrix to be stored. Make sure the range is blank and does not include any other data since this range will be overwritten when the correlations are defined by the dialog box.
2	Input Variables: Once the range has been selected, the dropdown boxes on the right will appear for each input variable. Select the variables of interest here. If you have 10 inputs, it is not necessary to define a correlation between each of the inputs. You only need to define the correlation between those variables that are correlated. Anything that is not defined will be assumed to be uncorrelated. Once you specify the variables, the correlation matrix is shown below. Note that the correlation matrix is symmetric with a value of 1 in the diagonals. Enter the correlation coefficients in the white text boxes. Make sure that the correlation coefficients are between -1 and +1.
3	Correlation Table: The correlation table shows the currently defined correlation coefficients. Review this matrix to check if there are any errors in the correlation coefficients and update this matrix if required.
4	Update Coefficients: The correlation table shows the currently defined correlation coefficients. Note that the matrix is symmetric and the correlation coefficients can be updated in the white cells. If the values in the white cells are 0, then there is no correlation between the two selected variables. Make sure that all correlation coefficients are between -1 and +1.
5	Cancel Button: Exit this dialog box without making any changes.
6	Delete Button: Click on this button to delete the entire correlation matrix table from your simulation. Note that all the correlation coeffients will be removed and your inputs will no longer be correlated if you click on this button.
7	OK Button: Click on the OK button to save the correlation coefficients. The values defined in the dialog box will be saved to the worksheet. Note that once you define the correlation matrix you can click on the Correlations button at anytime to review what has been defined earlier.

Sim Table

A simulation table enables you to automate running multiple simulations at one go. For example, if you want to test the impact of distribution parameters on your simulation, you could use a simulation table and run the first simulation with the first set of distribution parameters and the second simulation with the second set of distribution parameters. The simulation table parameters are not random variables but take on a limited set of fixed values. Another example of its use could be when we want to make business decisions based on NPV values but the interest rates could be 7%, 7.5%, or 8%. We could run three simulations with the interest rates set at these values. The advantage of using simulation tables is that we don't have to manually change these parameters and run the simulations separately. We could use the same set of random numbers for each of these simulations so that the variability of the random numbers does not affect our decision. You can have any number of variables within the simulation table.

Click on the Sim Table button to open the simulation table dialog box. The following screenshot shows a sample simulation table dialog box.

1	Cell Location: The location textbox defines the location on your worksheet that stores the simulation table. To define the simulation table, click on the select button next to the range input textbox. Select a range on the worksheet where you want to simulation table to be stored. If you have 2 simulation runs, you need to pick three-row (one for the header). If you are only changing one variable, you need to pick 2 columns (one for row number). If you are modifying 2 variables, you need to pick three columns and so on. Depending on the size of the range you pick, the appropriate number of cells is displayed in the dialog box. Make sure the range is blank and does not include any other data since this range will be overwritten when the simulation table is defined by the dialog box.
2	Target Cell: Once the range has been selected, a textbox will appear on the top of each column. You will need to specify the location of the worksheet cell that will be modified by the simulation table. For example, if you enter K5 in this cell for each run of the simulation, the values from the simulation table will be used to update the location K5 on the worksheet before that particular simulation is run. The first simulation is run by placing the value 12 in cell K5. The second simulation is run by placing the value 13.5 in cell K5 and so on. If you have two variables defined, then you will need to specify two worksheet locations that will be modified. It is up to the simulation model you have developed how you incorporate K5 in your model equations.
3	Table Values: Define the values to be used for each simulation run. These values can either directly be edited here in the dialog box or entered on the worksheet. In the example shown above, the simulation table values are 12 for the 1st simulation, 13.5 for the 2nd simulation, and 15 for the third simulation. You can have any number of simulations (rows) defined and you can simultaneously modify any number of variables (columns) for each simulation.
4	Delete Button: Click on this button to delete the entire simulation table from your simulation. Note that all the simulation values will be removed and your inputs will no longer be correlated if you click on this button.
5	OK Button: Click on the OK button to save the simulation table to the worksheet. The values defined in the dialog box will be saved to the worksheet. Note that once you define the simulation table you can click on the Sim Table button at anytime to review what has been defined earlier.

Functions

The functions dialog box lets you summarize the basic statistical analysis of inputs and/or outputs so that the results can be displayed on the worksheet. For example, if the output of interest for your analysis is profit, then for each iteration of the simulation you will get a different value of profit. Taken overall iterations the profit will follow a distribution and will have values ranging from a minimum to a maximum value. If you are interested in the mean value of the profit, you can specify the function to calculate the mean of the profit for a given simulation number and save the result on the worksheet. This function is useful to summarize and document the results of the analysis directly on the worksheet.

Click on the Functions button to open the functions dialog box. Make sure to click on the worksheet cell where you want to define the function before clicking on the Functions button. The following screenshot shows a sample functions dialog box.

1	Function Name: Defines a name for the function. Currently, this feature is not enabled for the user to select the function name.
2	Cell Location: The location textbox defines the location on your worksheet that stores the function value. In order to define the function, click on the select button next to the range input textbox. Select a range on the worksheet where you want to simulation table to be stored.
3	Variable: Select the input or output variable for which you want to compute the function value. You can only pick one variable at a time. If you need to summarize other variables then you will need to define more than one function for your simulation.
4	Simulation Number: Specify the simulation number for which this function needs to summarize the values. If you have 3 simulations and you want a summary for each simulation, you will need to define 3 functions and store them in different cells on the worksheet.
5	Measure: Specify what sort of measure you want to report. The available options are: Option Description N Reports the number of data points for that variable Min Reports the minimum value of that variable Max Reports the maximum value of that variable Mean Reports the mean value of that variable Median Reports the median value of that variable Stdev Reports the standard deviation of that variable Skewness Reports the skewness value of that variable Kurtosis Reports the kurtosis value for that variable Percentile Reports the percentile value for that variable. You will need to specify which percentile in the Parameter textbox.
6	Parameter: Specify the parameter for some measure's such as Percentile otherwise this textbox is disabled.

If you want other functions not defined here, do note that the raw simulation output data are also stored on the worksheet and you can use these numbers to define other calculations and store the results on the worksheet.

Model Summary

The model summary summarizes the Monte Carlo simulation model that you have developed. It can be used to ensure that you have captured the model correctly. You can click on the Model Summary button on the task menu bar to get a summary of your model. A sample dialog box of the model summary is shown in the figure below.

1	Inputs: This tab shows you all the inputs you have defined in the model. The number within the parenthesis shows the number of inputs. The listbox contains the input number, the name of the input, the cell number which stores the input variable, and the distribution name & parameters for that input. You can click on the delete button at the bottom to delete any input from this list. You can also reorder the inputs if required by moving selected inputs Up or Down.
2	Outputs: This tab shows you all the outputs you have defined in the model. The number within the parenthesis shows the number of outputs. The listbox contains the output number, the name of the output, the cell number which stores the output variable, the method used to calculate process capability, and the lower and upper specification limits. You can click on the edit button to edit the output or the delete button at the bottom to delete any output from this list. You can also reorder the inputs if required by moving selected inputs Up or Down.
3	Functions: This tab shows you all the function you have defined in the model. The number within the parenthesis shows the number of functions. The listbox contains the function number, the variable name, the cell number which stores the function variable, the simulation number for which the data is calculated, and the metric (such as mean) and any required parameters. For example, if you have an output variable ABC, when you generate 100 random numbers, you will be generating 100 values of the variable ABC. You can define a function to calculate the mean value of ABC and store it in a cell, say E4. Functions can be useful to save summary statistics of your input and/or output variables on your worksheet.
4	Correlations: This tab shows you all the correlations you have defined in the model. The number within the parenthesis shows the number of correlations defined. The list box contains the correlation number, the two input variables between which the correlation is defined, and the correlation value. Note that if the correlation value is 0 (which means no correlation), then those pairs of inputs are not listed in this dialog box.
5	Sim Table: This tab shows you all the simulation table values defined in the model. The number within the parenthesis shows the number of simulations defined. The list box contains the simulation number, the cell location that needs to be modified, and the values that need to be put into that cell for each simulation run. For example, if there are 3 simulations defined for cell D4 with values 1, 2, 3. Then the first simulation is run with D4 = 1, the second simulation is run with D4 = 2, and the third simulation is run with D4 = 3.
6	Errors: This tab shows you all the errors that were detected in the model. The number within the parenthesis shows the number of errors detected. The list box contains the error number, the source of the error (input, output, function, etc.), and a brief description of the error. Note that you will not be able to simulate until you fix all the reported errors.

Run Simulation

Once you have defined the model, you can simulate to generate random numbers and compute simulation outputs and model results. Note that each time you run the simulation you may get slightly different results if you have selected the option to generate different random numbers each time. On the task menu bar, you have three options, seed, iterations, and simulations.

1	Seed: The seed value determines how the random numbers are generated. If the value of the seed is 0, then new random numbers are generated each run, otherwise, the same set of random numbers will be generated. This can be especially useful if you want repeatable results or want to compare your analysis with someone else.
2	Iterations: The number of iterations indicates the number of times random numbers will be generated. You need to have a sufficient number of iterations to ensure you can simulate the full range of variation of the input variables. If the number of iterations is small, you will get different results each time you run the simulation. If the number of iterations is too large, it will consume significant computer bandwidth to generate and store a large number of data points. The simulation results may not significantly get better after a certain point. Hence, it is recommended that you keep the number of iterations small initially and then slowly increase the number of iterations until you don't find much change in the analysis results.
3	Simulations: The number of simulations determines how may rows of the simulation table are executed. The appropriate parameters of the simulation table are set for each simulation. For example, for the simulation table described earlier, for the first simulation the J10 cell is set to 10, for the second simulation, the J10 cell is set to 20, etc. before the simulation is run. If the number of simulations is less than the number of rows of the simulation table, then only the corresponding rows of the simulation table are run. However, if the number of simulations is greater than the number of rows in the simulation table, then the simulation settings cycle through the list. For example, the 6th simulation run will use the first set, the 7th simulation run will use the second set, and so on. In most cases, we recommend that the number of simulations equals the number of rows defined in the simulation table so there is no confusion on what simulation parameters are being set for each simulation.

Graphs

After the simulation runs are executed, the simulation results are stored in the Simulation Data section on the right. The simulation results are stored in the following format: the columns consist of all the input variables and then all the output variables for the first simulation and then all the input variables and all the output variables for the second simulation and so on. The number of rows of data corresponding to the number of iterations specified for the problem. The function results are directly stored in the specified worksheet cells on the left in the model section. Let's review all the graphs that be generated for this template below.

Box Plot

Click on the Box Plot button in the task menu bar to compare multiple simulations. A box plot dialog box is opened as shown below.

1	Variable: Select the variable for which you would like to compare simulation runs. The dropdown box lists all the input and output variables. You can choose to plot a single variable or select All to compare all variables on the same plot.
2	Group: Select the simulation number you want to use to create the box plot. You can either select All where all the groups are used or you can use a signle simulation data to compare the groups.
3	Outliers: Specify whether you want to show or hide outliers. If you choose to show outliers, any data points that are too far away from the median value are shown as a dot outside the whiskers.
4	Box Plot: At the bottom, you can specify you want to display the data as a box plot or as a confidence interval of the means.
5	Statistics: On the right hand side, a brief set of statistics for the given data set are shown. Currently, the following stats are shown for each group. Option Description Group The group number for the descriptive stats shown below Min Minimum value found in the data set Max Maximum value found in the data set Mean Mean value of the data set Stdev Standard Deviation of the data set Q1 First quartile of the data set Q2 Second quartile of the data set Q3 Third quartile of the data set

Capability Analysis

Click on the Capability button in the task menu bar. A capability analysis dialog box is opened as shown below.

1	Variable: Select the variable for which you would like to analyze the capability. The dropdown box lists all the input and output variables.
2	Group: Select the group variable. The group variable is the simulation number. So, if you would like to create a histogram for the first simulation, select the group number as 1.
3	Distribution: Specify the distribution you want to fit to estimate the process capability. If you specify "Normal" for example, the software will fit the best possible Normal distribution and use this distribution to estimate process capability.
4	Specs: Specify which tails you want to include in your capability analysis. If you specify LSL, then only the lower specification limit is included in the calculations. If you specify USL, then only the upper specification limit is included in the calculations. If you specify Both, then both lower and upper specification limits are included in the process capability calculations.
5	Textbox Options: At the bottom, you can specify the specification limits to use for LSL and USL. The software will try to get these from the model, but you can feel free to change them and try out different values to see how it will impact your analysis results.
6	Capability Stats: The statistics section shows the following metrics: Option Description N Number of data points Min Minimum value found in the data set Max Maximum value found in the data set Mean Mean value of the data set Std Dev Standard Deviation of the data set LSL Lower Specification Limit USL Upper Specification Limit Cp.L Process capability index on the LSL side Cp.U Process capability index on the USL side Cpk Process capability index considering both LSL and USL Cp Potential process capability (requires both LSL and USL to compute) Z.L Sigma quality level considering the LSL Z.U Sigma quality level considering the USL Z Overall Sigma level PPM (Obs) Observed defects per million opportunities (from the raw data) PM (Exp) Expected defects per million opportunities (from the distribution fit) P Value P value of the distribution fit (to check how well the distribution fits the data)

Histogram

Click on the Histogram button in the task menu bar. A histogram dialog box is opened as shown below.

1	Variable: Select the variable for which you would like to generate the histogram. The dropdown box lists all the input and output variables.
2	Group: Select the group variable. The group variable is the simulation number. So, if you would like to create a histogram for the first simulation, select the group number as 1.
3	Distribution: Specify the distribution you want to fit and superimpose on the histogram. If you specify "Normal" for example, the software will fit the best possible Normal distribution to the data and superimpose it on the histogram in green color.
4	Prob: Specify if you want to highlight the tails of the distribution fit. If you specify P10, then the software will draw the vertical lines which show 5% of the data points to the left, 90% of the data points in the middle, and 5% of the data points on the right. These tails are based on the actual data points - the software will calculate the percentile values to determine these limits. The data shown at the top is for the best fit distribution. So, these numbers may not match the raw data if there is a poor fit for the distribution of the data points. Note that you can click on the percentages or the limit values to make changes to these numbers to see how the shaded areas change.
5	Checkbox Options: At the bottom, there are three checkbox options, you can either plot the probability density function (PDF), or the cummulative probability density function (Ascending), or the Cummulative probabilty density function (descending) for the best histogram.
6	Statistics: On the right hand side, a brief set of statistics for the given data set are shown. Currently, the following stats are shown. Option Description N Number of data points Min Minimum value found in the data set Max Maximum value found in the data set Mean Mean value of the data set Median Median value of the data set Mode Mode of the data set (not the histogram bins or the fit) Range Range value (max-min) of the data set Std Dev Standard Deviation of the data set Skewness Skewness value of the data set Kurtosis Kurtosis value of the data set Percentiles P1 refers to the 1st Percentile and so on Quartiles Q1 refers to the 1st Quartile and so on P Value The P value of the selected probability distribution

Scatter Plot

A scatter plot can be used to compare two continuous data sets to determine visually if they are correlated. Click on the Scatter Plot button in the task menu bar to create a scatter plot between two variables. A scatter plot dialog box is opened as shown below.

1	X Variable: Select the variable that you would like to use for your X axis for your scatter plot. The dropdown box lists all the input and output variables.
2	Y Variable: Select the variable that you would like to use for your Y axis for your scatter plot. The dropdown box lists all the input and output variables.
3	Group: Select the group variable. The group variable is the simulation number. So, if you would like to create a histogram for the first simulation, select the group number as 1. If you select All then all the simulation groups are used to create the scatter plot. Note that each subgroup (or simulation) is plotted using a different color.
4	Distribution: Specify the distribution you want to fit and superimpose on the histogram. If you specify "Normal" for example, the software will fit the best possible Normal distribution to the data and superimpose it on the histogram in green color.
5	Filter: Sometimes you may not want to plot all the data points on the scatter plot, especially if you have too many points in your data set. The scatter plot can become very crowded and it can consume significant computing resources to generate the scatter plot. In this case, we can use the filter to randomly select a subset of the data to create the scatter plot. Note that sequential sampling is used to select the random numbers. For example, if you select a filter of 10%, then only 10% of the data points are used to generate the scatter plot. The default value is None or no filter where all the data points are used to create the scatter plot.
6	Checkbox Options: At the bottom, there are three checkbox options, you can superimpose a fit on the data points that is either linear, quadratic, or cubic.
7	Statistics: On the right hand side, a brief set of statistics for the given data set are shown for the X and Y data set. Currently, the following stats are shown. Option Description N Number of data points X Min Minimum value found in the X data set X Max Maximum value found in the X data set X Mean Mean value of the X data set X Stdev Standard Deviation of the X data set Y Min Minimum value found in the Y data set Y Max Maximum value found in the Y data set Y Mean Mean value of the Y data set Y Stdev Standard Deviation of the Y data set Pts Q1 Percentage of data points that fall in the first quadrant (right hand top corner) Pts Q2 Percentage of data points that fall in the second quadrant (left hand top corner) Pts Q3 Percentage of data points that fall in the third quadrant (left hand bottom corner) Pts Q4 Percentage of data points that fall in the fourth quadrant (right hand bottom corner) Correlation Pearson's correlation coefficient between X and Y data sets P Value Statistical significance test for the correlation study

Sensitivity Analysis

Click on the Sensitivity button in the task menu bar to view the sensitivity analysis. A sensitivity analysis plot dialog box is opened as shown below.

1	Variable: Select the variable for which you would like to generate sensitivity analysis. The dropdown box lists all the output variables. The sensitivity analysis is performed with respect to the input variables.
2	Group: Select the simulation number you want to use to create the box plot. The sensitivity analysis results may change between different simulation runs.
3	Method: Specify the method used to determine sensitivity analysis numbers. You can choose between correlation or regression. If you pick correlation, then the Spearman rank correlation coefficients are used. If you pick regression, then the Pearson correlation coefficients are used.
4	Filter: Sometimes, instead of looking at all the data, you may want to only consider the top 10% of the data points. You want to determine if the top 10% of the data points have a different sensitivity compared to using all the data. In this case, you can choose the filter as Top 10%. Similarly, you can compute sensitivity analysis for the top 20%, top 30%, bottom 10%, the bottom 20%, and bottom 30% of the data.
5	Statistics: On the right hand side, a brief set of statistics for the given data set are shown. Currently, the following stats are shown for each group. Option Description N Number of data points in the analysis 1: Variable Name Correlation Coefficient (lists the most important variable first) 2: Variable Name Remaining variables & correlation coefficients (in decreasing order)

Trend Plot

Click on the Trend Plot button in the task menu bar to plot a trend for a range of simulated values. For example, you may have generated outputs for revenue for a company for 7 years. You want to get an idea of how the revenue is changing with time. Each revenue data point is a random variable, so you can plot the trend of the mean value or the percentiles to get an idea of how the revenue is changing with time. To create this plot, you will need to use the Trend Plot.

1	Range: First select a range for creating the trend plot. The range you pick may contain input or output variables. The data from these variables is used to create the trend plot. Make sure that the range you select is in the proper time sequence, the first data point is plotted first, the second data point is plotted second etc.
2	Simulation: Select the simulation number you want to use to create the trend plot.
3	Interval: If you would like confidence intervals on the plot, specify the period. For example, if you select P80, then the 80% confidence interval bounds are plotted. The bottom red line will be at 10% and the top red line will be at 90%.
4	Statistics: On the right hand side, a brief set of statistics for the given data set are shown. Currently, the following stats are shown for each group. Option Description 1 Print the mean value for each variable in the list 2 Print the lower confidence interval for each variable (depends on interval selected) 3 Print the upper confidence interval for each variable (depends on interval selected)

Analysis Results

Analysis Outputs

Click on the Reports button in the task menu bar to specify which reports you would like to output after running the Monte Carlo simulation. A sample reports dialog box is shown below. The following set of notes can be generated from this analysis:

Option	Description
Input Summary	Specify what information you want to display in the notes section for the model, inputs, outputs, random numbers and assumptions.
Basic Stats (Inputs)	Specify for which of the input variables you want to display the basic statistical summary. A histogram is plotted for this selection.
Basic Stats (Outputs)	Specify for which of the output variables you want to display the basi statistical summary. A histogram is plotted for this selection.
Correlation Matrix	Specify for which combination of inputs and/or outputs you want to display the correlation matrix. A scatter plot will be plotted for this selection.
Capability Analysis	Specify for which outputs you want to display the capability analysis. A capability plot will be plotted for this selection.
Sensitivity Analysis	Specify for which outputs you want to display the sensitivity analysis results. A Pie Chart of relative contribution will be plotted for this selection.
Simulations Box Plot	Specify for which output variables you want to compare the simulations box plot. A box plot will be plotted for this selection.

The notes and the graphs will be updated at the end of the simulation run based on the settings you make in the reports dialog box. Note that you may not need to rerun the simulation if you make changes to the report's settings. It will use the simulation run results from the earlier run (if available) to update the reports.

Optimize Model

You can use the Optimize Model to optimize a set of parameters to achieve a certain objective. Note that currently, Sigma Magic software uses grid point enumeration to optimize the model. Though this approach is computationally expensive, it can guarantee the finding of the global optimum value if one exists within the discretization settings specified by the user. To keep the computational burden to a minimum make sure that you only run one simulation when performing the optimization. You can currently optimize up to 5 input variables and have up to 5 objective functions for the optimization. The greater the number of input variables, the longer will be the computational load and time.

A sample dialog box of the Optimize model is shown below.

1	Num Variables: Specify the number of input variables. You can have up to 5 input variables for your optimization model.
2	Num Objectives: Specify the number of objectives
3	Num Constraints: Specify the number of constraints
4	Tabs: Go through each of the tabs and define the optimization model. First, lets work with variables.
5	Cell Location: Specify the location of the input variable that needs to be optimized. The value on the worksheet will be varied to search for an optimum.
6	Minimum: Specify the minimum value of the input variable. No values below this will be searched.
7	Maximum: Specify the maximum value of the input variable. No values above this will be searched.
8	Increment: Specify the increment value. Make sure this is not too small as enumeration is used to search the optimal solution.
9	Help Button: Open the help file for this topic.
10	Cancel Button: Cancel all changes and exit this dialog box.
11	OK Button: Save the changes and start the optimization.

1	Tab: Make sure that the Objectives tab is selected.
2	Cell Location: Specify the location of the cell that contains the objective function. The value located in this cell is optimized.
3	Objective: Specify the type of optimization. You can maximize the cell value, minimize the cell value, or achieve a specific target value.
4	Target Value: Specify the target value you would like to achieve. This field is hidden for minimization and maximization and only enabled if you want to achieve a target.
5	Weight: Specify the weights. This is especially useful if there are multiple objective cells. The weights determine the relative importance of each cell.

1	Tab: Make sure that the Constraints tab is selected.
2	LHS Value: Specify the cells that contain the constraints. You can either select a cell or enter a value in the dialog box settings. You will need to specify both the Left Hand Side (LHS) of the constraint
3	Relation: Specify if the left hand side is less than, equal to, or greater than the right hand side.
4	RHS Value: Specify the cells that contain the constraints. You can either select a cell or enter a value in the dialog box settings. You will need to specify both the Right Hand Side (RHS) of the constraint

1	Method: Specify the method to use for optimization. Currenly, this setting is "Auto" which will automatically pick between enumeration if the number of combinations are small and a combination of random/genetic algorithm if there are a large number of combinations.
2	Max Iterations: Specify the maximum number of iterations to run the optimization algorithm. Note that you can always continue to run from the last case to increase the number of runs.
3	Display: Specify if you want to update the display in between runs. This is only for the decision variables and not for the model inputs since that will increase the total optimization time.

Click on Optimize to start the optimization algorithm. The software will run the Monte Carlo simulation at all possible combinations of the input variables. Once the simulation is completed, the software will list all the possible combinations of inputs and their associated objective values and whether these sets of inputs satisfy the constraints specified. The software also lists in the last column whether that combination of inputs optimizes the problem. Note that there may be more than one optimal solution. It will give you an option to save the last optimal solution back to your worksheet. Since enumeration is used to solve this problem, the solution results in a global optimum subject to the size of the discretization increment. For a more accurate solution, you can increase the number of increments, however, this will take a lot longer to run the Monte Carlo simulation.

Notes

Here are a few pointers regarding this analysis:

• There are no limits on the number of input or output variables in this model, unlike the earlier version.
• Keep the number of iterations manageable - you can start with a smaller number and increase it until there is no change in the analysis results.
• It is recommended that you utilize the Latin Hypercube sampling for best results

Examples

Following examples can be found in the Examples folder.

• Use the Monte-Carlo simulation to calculate the expected profit for a basic business model. Refer to the file: Monte Carlo Basic Profit.xlsm.
• Use the Monte-Carlo simulation for cost estimation. Refer to the file: Monte Carlo Cost Estimation.xlsm.
• Use the Monte-Carlo simulation for travel expenses. Refer to the file: Monte Carlo Travel Expense.xlsm.
• Use the Monte-Carlo simulation for project selection. Refer to the file: Monte Carlo Project Selection.xlsm.