Practical reliability analysis and design by Monte Carlo Simulation in spreadsheet - Risk and Reliability in Geotechnical Engineering - page 309

environment, such as Microsoft Excel (Microsoft Corporation, 2012), by a package of
worksheets and Visual Basic for Applications (VBA) functions/Add-Ins. The implemen-
tation is deliberately divided into three uncoupled modules, namely deterministic model-
ing, uncertainty modeling, and uncertainty propagation. The reliability analysis (including
uncertainty modeling and propagation) is decoupled from the conventional deterministic
geotechnical analysis so that the reliability analysis can proceed as an extension of the
deterministic analysis. This allows the deterministic geotechnical analysis and the reliability
analysis to be performed in a nonintrusive manner through computer simulation and avoids
additional conceptual and mathematical complexity when the reliability algorithms are
coupled with geotechnical deterministic models (e.g., FORM or SORM). This permits geo-
technical practitioners, who are not necessarily probabilistic experts, to instruct the spread-
sheet to repeatedly calculate the geotechnical deterministic models in a systematic manner
for assessing the effects of uncertainties and making risk-informed decisions conveniently.
The three modules of the spreadsheet implementation are further discussed in the following
three sections, respectively.
7.5.1 Deterministic modeling
Deterministic modeling is the process of calculating system responses (e.g., FS , 1/ FS , or BD /
FS min ) of interest for a given nominal set of values of system parameters. The system param-
eters include, but are not limited to, design parameters (e.g., B and D for drilled shafts),
design loads, soil properties, and profile of soil layers. The calculation process of the deter-
ministic model is implemented in a series of worksheets assisted by some VBA functions/
Add-In (Au et al., 2010; Wang and Cao, 2013; Wang et al., 2011b). From an input-output
perspective, the deterministic analysis worksheets take a given set of values as input, calcu-
late the system responses, and return system responses as an output. No probability concept
is involved in the deterministic model worksheet, and it can be developed by practitioners
without reliability analysis background.
7.5.2 uncertainty modeling
An uncertainty model worksheet is developed to define the uncertain system parameters
that are treated as random variables in the reliability-based analysis and design. Based
on the distribution type and statistics defined in the worksheet for each random variable,
random samples of the random variables are generated in the worksheet. In Microsoft
Excel, the generation of random samples starts with a built-in function “RAND()” for
generating uniform random samples, which are then transformed into random samples
of the target distribution type (e.g., normal distribution or discrete uniform distribution).
Detailed examples of the random sample generation process are further illustrated in the
next section. From the input-output perspective, the uncertainty model worksheet takes
no input but returns a set of random samples of the uncertain system parameters as its
output.
When deterministic model worksheet and uncertainty model worksheet are developed,
they are linked together through their input/output cells to perform the analysis and
design. The connection is carried out by simply setting the cell references for nominal val-
ues of uncertain parameters in the deterministic model worksheet to be the cell references
for the random samples in the uncertainty model worksheet in Excel. After this task, the
values of uncertain system parameters shown in the deterministic model worksheet are
equal to those generated in the uncertainty model worksheet, and the values of the system