Course : Managing Business Information

Effective Period : September 2021

Simulation & Risk Analysi

Session 9
 These slides have been adapted from:

Evans, J.R., (2021). Business Analytics, Global Edition, 3rd Edition.

Pearson Education, Inc .-. ISBN: 987-1-292-33906-1

Chapter 12
Learning objectives of this chapter are:
• Describe the importance of risk analysis for business
• Explain how Monte Carlo simulation works
Risk Analysis
• Risk is the likelihood of an undesirable outcome. It can be
assessed by evaluating the probability that the outcome
will occur along with the severity of the outcome.
• Risk analysis seeks to examine the impact of uncertain
inputs on various outputs.
Monte Carlo Simulation
• Monte Carlo simulation is the process of generating
random values for uncertain inputs in a model, computing
the output variables of interest, and repeating this process
for many trials to understand the distribution of the output
results.
• We model uncertain inputs using probability
distributions, using historical data, or choosing a
representative distribution judgmentally.
• Monte Carlo simulation relies on generating values from
these distributions to create a probability distribution of
output values.
Example 12.1: Profit Model
• Demand, unit cost, and fixed cost
are uncertain.
– Historical demand:

Demand Probability
40,000 0.1
45,0000 0.3
50,000 0.4
55,000 0.15
60,000 0.05

• Unit cost varies between $22 and $26; use a uniform distribution.
• Fixed cost low as $350,000 or as high as $500,000, with the most
likely value being $400,000; use a triangular distribution.
Random Sampling from Probability
Distributions
• In the context of simulation, random numbers are
numbers that are uniformly distributed between 0 and 1.
• In Excel, we may generate a random
number that is greater than or equal
to 0 and less than 1 within any cell

using the function

• Whenever any cell in the spreadsheet
is modified, the values in any cell

containing the function will

change.
Sampling from Common Probability
Distributions
• A value randomly generated from a specified probability
distribution is called a random variate.
• Analysis Toolpak Random Number Generation Tool
– Can sample from uniform, normal, Bernoulli, binomial,
Poisson, patterned, and discrete distributions.
– Can also specify a random number seed - a value
from which a stream of random numbers is generated.
By specifying the same seed, you can produce the
same random numbers at a later time.
Example 12.2: Using Excel’s Random
Number Generation Tool (1 of 2)
• Generate 100 outcomes from a Poisson distribution with a
mean of 12

– Number of Variables = 1.
– Number of Random
Numbers = 100
– Distribution = Poisson
– Dialog changes and
prompts you to enter
Lambda (mean of Poisson)
= 12
Example 12.2: Using Excel’s Random
Number Generation Tool (2 of 2)
(Histogram created
manually.)

One disadvantage with using the Random Number Generation

tool is that you must repeat the process to generate a new set of
sample values.  This can make it difficult to use this tool for
simulation models.
Generating Random Variates Using
Excel Functions
• We may use Excel functions to generate random variates
for many common types of probability distributions.
– This allows us to replace uncertain input data cells in a
model with random variates.
Example 12.3: Sampling from the
Distribution of Dice Outcomes
Interval Outcome
≥ 0 and < 0.0278 2
≥ 0.0278 and < 0.0833 3
≥ 0.0833 and < 0.1667 4
≥ 0.1667 and < 0.2778 5
≥ 0.2778 and < 0.4167 6
≥ 0.4167 and < 0.5833 7
≥ 0.5833 and < 0.7222 8
≥ 0.7222 and < 0.8323 9
≥ 0.8323 and < 0.9167 10
≥ 0.9167 and < 0.9722 11
≥ 0.9722 and < 1.000 12

• The values of
divide the interval from 0 to 1 into smaller intervals that
correspond to the probabilities of the outcomes. To generate an outcome from
this distribution, all we need to do is to select a random number and determine
the interval into which it falls.
Example 12.4: Using the VLOOKUP
Function for Random Variate Generation
• Sample from the probability distribution of the predicted
change in the Dow Jones Industrial Average index.
• The formula in cell
which is copied down that column.
Uniform Distributions
• Transform a random number into a random variate from a
uniform distribution between a and b.
Example 12.5: Modeling Uncertainty
with a Uniform Random Variate
• The uniform distribution is often used when little is known
about the distribution of an uncertain variable.
• Suppose that a supplier states that the price might be as
low as $42 or as high as $50 per unit.

– If RAND generates the random number 0.6200, then

the price would be
Exponential Distributions
• Exponential random variates can be generated easily
using the Excel formula

where is the mean of the exponential

distribution, and LN is the Excel function for the natural
logarithm.
Normal Distributions
• Normal random variates can be generated in Excel using
inverse functions. Inverse functions find the value for a
distribution that has a specified cumulative probability.

generates a random variate from a normal distribution

with a specified mean and standard deviation.
– generates a random variate
from a standard normal distribution (mean = 0 and
variance = 1).
Example 12.6: A Sampling Experiment for
Evaluating Capital Budgeting Projects (1 of 2)
• In finance, one way of evaluating capital budgeting
projects is to compute a profitability index:
– PV is the present value of future cash flows.
– I is the initial investment.
• What is the probability distribution of PI when PV is
estimated to be normally distributed with a mean of $12
million and a standard deviation of $2.5 million, and the
initial investment is also estimated to be normal with a
mean of $3.0 million and standard deviation of $0.8
million?
Example 12.6: A Sampling Experiment for
Evaluating Capital Budgeting Projects (2 of 2)
• Column F:

• Column G:
Binomial Distributions
• To generate a random variate from a binomial distribution,
we may use the Excel function
This function finds
the smallest value for which the cumulative binomial
distribution is greater than or equal to alpha.
• To randomly generate a binomial random variate,
replace alpha with that is,
Example 12.7: Binomial Random
Variates
• Sixty potential customers are called each hour by a telemarketer. The
probability that any of them will make a purchase is 0.08. Over a ten-
hour period, how many customers might make a purchase?
• Ten samples result in 61 purchases, or 6.1 per hour.
• Since the mean of the binomial is np = 4.8, over a ten-hour period, we
would expect 48 successes. A larger number of samples would
average closer to 4.8.
Triangular Distributions
• The triangular distribution depends on three parameters, a
= minimum, b = maximum, and c = most likely.
• A triangular random variate, X, can be generated using one of
the following formulas that depend on the value of R (R is a
random number):
Using Excel for Triangular Random
Variates

• First generate a random number R and compute

then use the first formula to generate X;

otherwise, use the second formula.
• Excel formula:

Caution: You cannot replace the

since they must all be the same random number. Therefore, in an
application, R must be referenced from a cell outside of this formula.
Example 12.8: Using the Triangular Distribution
for a U.S. Olympic Bid Risk Assessment
• Excel file Triangular Random Variates
– The spreadsheet also shows a table with 100 samples
from the distribution.
Monte Carlo Simulation in Excel
1. Develop a spreadsheet model.
2. Determine probability distributions for uncertain input
variables.
3. Identify output variables you want to evaluate.
4. Choose the number of trials (replications) for the simulation.
5. Create a data table to summarize the values of the model
output for the replications.
6. Compute summary statistics, percentiles, confidence intervals,
frequency distributions, and histograms to interpret the results.
Example 12.9: Setting Up the Monte Carlo
Simulation Model for the Profit Model
• Refer to Example 12.1

–
–
–
Running a Simulation
• To perform the simulation, we need to generate a sufficient
number of trials, or replications, to obtain enough data to
create a reasonable distribution of the model output
values.
• In Excel, we can generate several hundred replications
using data tables.
Example 12.10: Using Data Tables and Analyzing
Results for Monte Carlo Simulation in Excel (1 of 2)
• Create a data table to replicate the simulation for the model output. List
the trials (column L). Reference cell C22 in cell M2. In the Column Input
Cell field in the Data Table dialog, enter any blank cell in the
spreadsheet (Excel file Profit Model Monte Carlo Simulation)
Example 12.10: Using Data Tables and Analyzing
Results for Monte Carlo Simulation in Excel (2 of 2)
• Analyze results.
• Compute summary
statistics, percentiles, and a
frequency distribution and
histogram. (To find the
frequency distribution, we
used COUNTIF functions to
find the cumulative
frequencies, and then found
the number of observations
in each cell by subtraction.)
Using the FREQUENCY Function
• An easier alternative to compute a frequency distribution is

to use the Excel function

– This is an “array” function and returns values in a range
of cells.
– When you enter an array formula, you must first select
the range in which to place the results. Then, after
entering the formula, you must press Ctrl+Shift+Enter
in Windows or Command+Shift+Enter on a Mac
simultaneously.
Retirement Planning
• The model developed in Chapter 11 was based on some unrealistic
assumptions that the annual salary increase and expected annual
investment returns are constant for each year.
– These values will typically change each year and investment
returns may not necessarily have a positive return each year.
Example 12.13: Retirement Planning
Model Monte Carlo Simulation (1 of 4)
• Assumptions:
– Annual salary increase may vary from 2% to 5%, uniformly
distributed.
– Data on the Vanguard Balanced Index Fund show that the
average recent return is about 6%, but that it also has a standard
deviation of 6.5% (normal distribution).
– For the Boston Trust Asset Management Fund, the average return
is about 6.5% with a standard deviation of 7% (normal
distribution).
• Create new columns to generate random values for the annual salary
increase and investment returns.
Example 12.13: Retirement Planning
Model Monte Carlo Simulation (2 of 4)

Copyright © 2021 Pearson Education Ltd. Slide - 34

Example 12.13: Retirement Planning
Model Monte Carlo Simulation (3 of 4)
• Portion of data table for retirement balance.
Example 12.13: Retirement Planning
Model Monte Carlo Simulation (4 of 4)
• Statistical analysis and risk calculations
– The probability that it will be greater than $3 million but

less than 4.5 million is

Overbooking Decisions
• In any realistic situation, the actual customer demand as
well as the number of cancellations would be uncertain.
• We may use Monte Carlo simulation to help determine the
best overbooking policy.
Example 12.15: Monte Carlo Simulation
for the Hotel Overbooking Model (1 of 2)
• Hotel Overbooking Simulation Model
• Assumptions:
– Customer demand is normally distributed with a mean
of 320 and standard deviation of 15.
– The probability that any reservation is cancelled is
0.04.
• Then the number of cancellations is a binomial random
variable, with n = number of reservations made, and p =
0.04.
Example 12.15: Monte Carlo Simulation
for the Hotel Overbooking Model (2 of 2)

• Simulation results
– The average net revenue is maximized when 315
reservations are taken; that is, overbooking the hotel
by 15 rooms, resulting in an average of 1.74
overbooked customers.
Project Management
• For many real projects, activity times are random variables. In most
cases, times must be estimated judgmentally, so we often assume
that they have a triangular distribution.
• Portion of Table 12.1 with uncertain activity times:

Activity Predecessors Minimum Time Most Likely Time Maximum Time

(days) (days) (days)
A Select steering - 15 15 15
committee
B Develop requirements - 40 45 60
list
C Develop system size - 10 14 30
estimates
D Determine prospective - 2 3 5
vendors
E Form evaluation team A 5 7 9
Example 12.16: A Spreadsheet Simulation
Model for Project Management (1 of 2)

Copyright © 2021 Pearson Education Ltd. Slide - 41

Example 12.16: A Spreadsheet Simulation
Model for Project Management (2 of 2)
• Simulation results for project completion time