ModelRisk needs to be installed in order for the model to work.
An example of a Monte Carlo simulation risk analysis model for the Defense and Aerospace industries In 2007, the US Air Force developed a manual called the ‘Cost Risk and Uncertainty Analysis Handbook’ in which various Monte Carlo simulation modelling techniques were described. It illustrated these techniques in a model estimating the cost of developing and manufacturing of a fictitious missile system, which we have reproduced.
Technical difficulty: 2 Techniques used: Monte Carlo simulation in Excel, correlation, multiple simulations, embedded simulation results calculation ModelRisk functions used: VoseTriangleAltObject, VoseUniformObject, VoseLognormalAltObject, VoseLognormalObject, VoseNormalAltObject, VoseBernoulli, VoseCopulaMultiNormal, VoseSimPercentile, VoseSimTable, VoseInput, VoseOutput, VoseSimulate
The risk analysis model is built using Excel with our Monte Carlo simulation add-in called ModelRisk. It uses the following formatting convention: The key uncertain variables are gathered together in a table: The Random sample column simulates each variable, and the Point estimate column shows the single values estimate for comparison. The Distribution column writes out in full the distribution that was used, using Object functions, which are then sampled in the Random sample column. We could have simply used random sampling distributions, e.g. VoseUniform instead of VoseUniformObject referenced by VoseSimulate, but this way one can review all the distribution parameters much better, which could be important as many of those parameters are the results of calculations and would not otherwise be visible. A correlation matrix is uses to define the correlation between these variables. The manual specifies that all variables have a correlation of at least 0.25, but some variables have a different, usually higher level of correlation – highlighted within the table: The original model used rank order correlation as correlation using copulas (ModelRisk did not appear until 2009, introducing the more advanced copula correlation method). The Normal copula used in the model provides the same correlation pattern. The model is set up to run two simulations: with and without correlation. When run with correlation, the distributions are sampled using the Normal copula. When run without correlation, the distributions are sampled using random numbers. The VoseSimTable function in cell O6 makes the switch between each simulation run.px A section of the model randomly adds some costs for possible additional common support equipment using a Bernoulli distribution for each item: Extra levels of correlation are created between costs in several ways. Two types of factors are used, schedule/technical penalty factors and cost estimate uncertainty factors, which are themselves correlated, and which then multiply up cost estimates. In addition, the cost estimates for some parts are based on the simulated total cost for the air vehicle, creating a dependency relationship. These correlation methods, i.e. explicitly using common factors or making one cost a function of another, are very good practice, and tend to produce more realistic estimate than correlation matrices – as well as being easier to understand. Finally, the costs are calculated in the bottom section. The costs are calculated side-by-side using the point estimates and the random samples: The manual defines a cost overrun as the difference between the simulated cost at some cumulative probability and the point estimate. A table has been created to show these cost overruns at various probabilities using the VoseSimPercentile function:
The model takes about 10 seconds to run 5,000 samples for each scenario. It is set up to directly show a number of reports within the ModelRisk ResultsViewer at the end of the simulation, which are described below. The model runs two simulations, labeled ‘Uncorrelated’ and ‘Correlated’, to show the effect of adding the correlation matrix. The first tab shows the descending cumulative distribution of the total development cost under each scenario: This plot illustrates how adding the correlation matrix has broadened the distribution of estimated cost so that, for example, it is estimated that there is a 10% probability the cost will exceed $1.32 million with correlation, but if correlation was ignored the estimate would be $1.13 million. The second tab shows box plots for the missile system total cost, together with the development and production cost sub-totals: Box plots provide a useful way of comparing the distributions of several variables together. For example, it shows that the production cost contributes considerably more uncertainty to the total than the development cost. The third tab reproduces the cost overrun calculations from the spreadsheet (cells G66:M70). This is made possible by using the Add Simulation Embedded Excel Report feature of ModelRisk: It is common in reviewing the results of a risk analysis to want to create particular statistical measures that are customized to the problem. For example, one may wish to see the variable: (P90 - P10)/P50 which is one way of measuring the variation of an output variable (the difference between the 10th and 90th percentiles) in proportion to its median value (P50). The various VoseSimXxx functions allow you to create virtually any statistic you might be interested in, which can then be added to your report. The last tab shows a tornado plot for the total cost with correlation, listing the input variables in descending order of their contribution to the total cost uncertainty: It illustrates, for example, that the total cost is most sensitive to the Air Vehicle – Guidance and Control uncertainty factor. It shows that the total average cost varies from $769k to $1.064M depending on whether this uncertainty factor is low or high. In a real problem, one would look at the most influential variables and consider how one could narrow their range and thus have a more precise estimate of the total cost.