Title: Spreadsheet Simulation
1 2Decision Making under Uncertainty Typical
Situation
- The decision maker often has no control over the
random event - Financial Risks stock prices, interest rates,
currency rates, commodity prices, defaults, etc. - Market Risks demand, input costs, prices
- Political Risks (strikes (Venezuela oil fields),
nationalization) - Natural Risks weather, natural disasters
Make a decision, x
Random event, r
Profit, f(x,r)
Today
Future date
Time
3Random Variables Risk
- A random variable is any variable whose value
cannot be predicted or set with certainty. - Many input cells in spreadsheet models are
actually random variables. - the future cost of raw materials
- future interest rates
- future number of employees in a firm
- expected product demand
- Decisions made on the basis of uncertain
information often involve risk. - Risk implies the potential for loss.
4Best-Case/Worst-Case Analysis
- Best case - plug in the most optimistic values
for each of the uncertain cells. - Worst case - plug in the most pessimistic values
for each of the uncertain cells. - This is easy to do but tells us nothing about the
distribution of possible outcomes within the
best-case and worst-case limits.
5Possible Performance Measure Distributions Within
a Range
6 Monte Carlo Simulation
- Develop a model that behaves like the real
problem, with a special consideration of the
random or probabilistic input variables - Conduct a series of computer runs (called trials)
to learn the behavior of the simulation model - Compute the summary (output) statistics and make
inferences about the real problem -
7(No Transcript)
8(No Transcript)
9(No Transcript)
10(No Transcript)
11(No Transcript)
12(No Transcript)
13(No Transcript)
14Origins of Monte Carlo
Physicists at Los Alamos Scientific Laboratory
were investigating radiation shielding and the
distance that neutrons would likely travel
through various materials. Despite having most of
the necessary data, such as the average distance
a neutron would travel in a substance before it
collided with an atomic nucleus or how much
energy the neutron was likely to give off
following a collision, the problem could not be
solved with analytical calculations. John von
Neumann and Stanislaw Ulam suggested that the
problem be solved by modeling the experiment on a
computer using chance. Being secret, their work
required a code name. Von Neumann chose the name
"Monte Carlo". The name is a reference to the
Monte Carlo Casino in Monaco where Ulam's uncle
would borrow money to gamble.
15Monte Carlo Simulation
- A model that mimics the behavior of a (random)
system
Probabilistic Input (random number)
Analysis
r1, r2, , rn
Model
Controllable Input
Outcome
16Random event r
Analysis
r1, r2, , rn
Simulation Model
Decision x
Profit
- Choose x (decision variable)
- Simulate some number, say n, random outcomes from
a given distribution, r1, r2,, rn - Compute simulated profits f(x, r1), f(x, r2), ,
f(x, rn) (objective function) - Evaluate results, e.g. estimate the expected
profit or standard deviation by
17Monte Carlo Simulation
- Advantages
- Allows statistical sampling from a large number
of potential scenarios. - Can simulate years of real time in seconds of
computer time - Compresses time
- Can modify a simulation and observe the results
without changing the real system (e.g., effect of
an expensive new machine, changes in traffic
light timing) - Experimental tool
- Can get results for very complex systems
18Monte Carlo Simulation
- Advantages (contd)
- Can represent random events succinctly, via high
level parameters - e.g. modeling stock prices as Normally
distributed random variables - Need only two parameters, mean (µ) and standard
deviation (s) to specify distribution, while
allowing for infinitely many possible outcomes - Disadvantages
- May be difficult to characterize risk and
uncertainty accurately - Programming and experimental effort can be costly
( and time) compared to other analytical methods - Not an optimization technique
- Simulation results are not exact
- Statistical analysis is useful to interpret
simulation output
19Simulation
- To properly assess the risk inherent in the model
we need to use simulation. - Simulation is a 4 step process
- 1) Identify the uncertain cells in the model.
- 2) Implement appropriate RNGs for each uncertain
cell. - 3) Replicate the model n times, and record the
value of the bottom-line performance measure. - 4) Analyze the sample values collected on the
performance measure.
20Random Number Generators
- A RNG is a mathematical function that randomly
generates (returns) a value from a particular
probability distribution. - We can implement RNGs for uncertain cells to
allow us to sample from the distribution of
values expected for different cells.
21Examples of Discrete Probability Distributions
22Examples of Continuous Probability Distributions
23Discrete vs. Continuous Random Variables
- A discrete random variable may assume one of a
fixed set of (usually integer) values. - Example The number of defective tires on a new
car can be 0, 1, 2, 3, or 4. - A continuous random variable may assume one of an
infinite number of values in a specified range. - Example The amount of gasoline in a new car can
be any value between 0 and the maximum capacity
of the fuel tank.
24How RNGs Work
- The RAND() function returns uniformly distributed
random numbers between 0.0 and 0.9999999. - Suppose we want to simulate the act of tossing a
fair coin. - Let 1 represent heads and 2 represent tails.
- Consider the following RNG
- IF(RAND()lt0.5,1,2)
25Simulating the Roll of a Die
- We want the values 1, 2, 3, 4, 5 6 to occur
randomly with equal probability of occurrence. - Consider the following RNG
- INT(6RAND())1
26Background Information
- In August, Walton Bookstore must decide how many
of next years nature calendars to order. - Each calendar costs the bookstore 7.50 and is
sold for 10. - After February 1 all unsold calendars are
returned to the publisher for a refund of 2.50
per calendar. - Walton believes that the number of calendars it
can sell by February 1 follows this probability
distribution.
- Walton wants to maximize the expected profit from
calendar sales. - Open Walton1.xls
27 WALTON1.XLS
- For a fixed order quantity, we will show how
Excel can be used to simulate 50 replications (or
any other number of replications). - Each replication is an independent replay of the
events that occur. - To illustrate, suppose we want to estimate the
expected profit if Walton orders 200 calendars.
To do this we need to simulate 50 independent
simulations. - This file contains the setup needed to begin the
simulation.
28The Simulation
- Inputs Enter the cost data in the range B4B6,
the probability distribution of demand in the
range D5F9, and the proposed order quantity,
200, in cell B9. Create a cumulative probability
column in column E by entering the value 0 in E5
and then the formula D5E5 and copy it down
column E. - Generate Random Number Enter a random number in
cell B19 with the formula RAND( ) and copy it to
the range B19B68. Then freeze the random numbers
in this range.
29The Simulation -- continued
- Generate demands The key to the simulation is
the generation of the customers demands in the
range C19C68 from the random numbers in column B
and the probability distribution of demand. To do
this we - Divide the interval from 0 to 1 into five
segments. The lengths of the segments relate to
the probabilities of various demands. - Then we associate a demand with each random
number depending on which interval the random
number falls into.
30Simulation -- continued
- To accomplish this we can follow one of two ways
- The first is to use a nested IF statement in cell
C19 (and copy it down C). - The second and simpler way is to use the VLOOKUP
function. To do this we create a lookup table
in the range E5F9 and name it Lookup. Then enter
the formula VLOOKUP(B19,Lookup,2)in cell C19
and copy it to the range C19C68. The function
compares the random number to the values in E5E9
and returns the appropriate demand in F5F9. - Revenue Once the demand is known, the number of
calendars sold is the smaller of the demand and
the order quantity. To calculate revenue for the
first replication in D19 we enter
B5MIN(C19,B9).
31Simulation -- continued
- Ordering Cost The cost of ordering the calendars
does not depend on the demand it is the unit
cost multiplied by the number ordered. Calculate
this in cell E19 with the formula B4B9. - Refund If the order quantity is greater than the
demand, there is a refund of 2.50 for each
calendar left over, otherwise there is no refund.
Therefore, enter the total refund for the first
replication in cell F19 with the formula
B6MAX(B9-C19,0). - Profit Calculate the profit for this replication
in G19 with the formula D19-E19F19.
32Simulation -- continued
- Copy to other rows Do the same bookkeeping for
the other 49 replications by copying the range
D19G19 to the range D20G68. - Summary Measures Each profit value in column G
corresponds to one randomly generated demand.
First, calculate the average and standard
deviation of the 50 profits in cells B12 and B13
with the formulas AVERAGE(Profits) and
STDEV(Profits). Similarly, calculate the
smallest and largest profit with the MIN and MAX
functions.
33Simulation -- continued
- Confidence Interval for expected profit Finally,
calculate a 95 confidence interval for the
expected profit in cells E13 and E14 with the
formulasAvgProfit-TINV(0.05,49)StDevProfit/SQRT
(50)AvgProfitTINV(0.05,49)StDevProfit/SQRT(50)
- At this point we need to look and see what we
have accomplished. - Lets look at the results of the simulation.
34Simulation for Walton Bookstore
35Accomplishments
- So here is what we have accomplished
- In the body of the simulation rows 19-68, we
randomly generated 50 possible demands and the
corresponding profits. - There are only five possible demand values and
also for our order quantity, 200, the profit is
500 regardless of whether demand is 200, 250, or
300. - There are 14 trials with profit equal to - 250,
9 trials with profit equal to 125, and 27 trials
with profit equal to 500. - The average of the 50 profits is 222.50 and
their standard deviation is 328.58. (Answers may
differ because of the random numbers.)
36Probability Distributions
- The probability distribution of profit is as
follows - P(Profit -250) 14/50
- P(Profit -125) 9/50
- P(Profit -500) 27/50
- We also estimate the mean of this distribution to
be 222.50 and its standard deviation to be
328.58. - It is important to be aware that with computer
simulation each time it is run the answers will
be slightly different. - This is the reason for the confidence interval.
37Confidence Interval
- The confidence intervals can be found in cells
E13 and E14. - This interval expresses our uncertainty about the
mean of the profit distribution. - Our best guess is the value we observed but
because the corresponding confidence interval is
very wide, from 129.12 to 315.88, we are not
sure of the true mean of the profit distribution.
38Simulation
- Simulation The act of reproducing the behavior
of a system using a model that describes the
processes of the system. - Time Compression The feature of simulations that
allows them to obtain operating characteristic
estimates in much less time than is required to
gather the same operating data from a real
system. - Monte Carlo simulation A simulation process that
uses random numbers to generate simulation events.
39Specialty Steel Products Co.Example B.1
- Specialty Steel Products Company produces items
such as machine tools, gears, automobile parts,
and other specialty items in small quantities to
customer order. - Demand is measured in machine hours.
- Orders are translated into required
machine-hours. - Management is concerned about capacity in the
lathe department. - Assemble the data necessary to analyze the
addition of one more lathe machine and operator.
40Specialty Steel Products Co. Example B.1
Historical records indicate that lathe department
demand varies from week to week as follows
41Specialty Steel Products Co. Example B.1
Average weekly production is determined by
multiplying each production requirement by its
frequency of occurrence.
Average weekly production requirements
200(0.05) 250(0.06) 300(0.17) 600(0.02)
400 hours
42Specialty Steel Products Co. Example B.1
Regular Relative Capacity (hr) Frequency 320
(8 machines) 0.30 360 (9 machines) 0.40 400
(10 machines) 0.30
The average number of operating machine-hours in
a week is 320(0.30) 360(0.40) 400(0.30)
360 hours
Average weekly production requirements 400 hours
43Specialty Steel Products Co.
The average number of operating machine-hours in
a week 360 Hrs.
Experience shows that with 11 machines, the
distribution would be
Average weekly production requirements 400 hours
Example B.1
44Specialty Steel Products Co.Assigning Random
Numbers
- Random numbers must now be assigned to represent
the probability of each demand event. - Random Number A number that has the same
probability of being selected as any other
number. - Since the probabilities for all demand events add
up to 100 percent, we use random numbers between
(and including) 00 and 99. - Within this range, a random number in the range
of 0 to 4 has a 5 chance of selection. - We can use this to represent our first weekly
demand of 200 which has a 5 probability.
45Specialty Steel Products Co.Assigning Random
Numbers
Event Weekly Demand (hr) Probability 200
0.05 250 0.06 300 0.17 350 0.05 400 0.30
450 0.15 500 0.06 550 0.14 600 0.02
46Specialty Steel Products Co.Assigning Random
Numbers
If we randomly choose numbers in the range of
00-99 enough times, 5 percent of the time they
will fall in the range of 00-04, 6 of the time
they will fall in the range of 05-10, and so
forth.
47Specialty Steel Products Co.Model Formulation
- Formulating a simulation model entails specifying
the relationship among the variables. - Simulation models consist of decision variables,
uncontrollable variables and dependent variables. - Decision variables Variables that are controlled
by the decision maker and will change from one
run to the next as different events are
simulated. - Uncontrollable variables are random events that
the decision maker cannot control.
48Specialty Steel Products Co.Example B.2
Simulating a particular capacity level
- Using the Appendix 2 random number table, draw a
random number from the first two rows of the
table. Start with the first number in the first
row, then go to the second number in the first
row. - Find the random-number interval for production
requirements associated with the random number. - Record the production hours (PROD) required for
the current week. - Draw another random number from row three or four
of the table. - Find the random-number interval for capacity
(CAP) associated with the random number. - Record the capacity hours available for the
current week.
49Specialty Steel Products Co.Example B.2
Simulating a particular capacity level
- If CAP gt PROD, then IDLE HR CAP PROD
- If CAP lt PROD, then SHORT PROD CAP
- If SHORT lt 100 then OVERTIME HR SHORT
- and SUBCONTRACT HR 0
- If SHORT gt 100 then OVERTIME HR 100
- and SUBCONTRACT HR SHORT 100
- Repeat steps 1 - 8 until you have simulated 20
weeks.
50Specialty Steel Products Co. 20-week simulation
51Specialty Steel Products Co. 1000-week simulation
A steady state occurs when the simulation is
repeated over enough time that the average
results for performance measures remain constant.
52Capital BankAn example of queuing system
simulation
- Capital Bank is considering opening the bank on
Saturdays morning from 900 a.m. - Management would like to determine the waiting
time on Saturday morning based on the following
data - Data
- There are 5 teller positions of which only three
will be staffed. - Ann Doss is the head teller, experienced, and
fast. - Bill Lee and Carla Dominguez are associate
tellers less experienced and slower.
53Capital Bank
- Data
- Service time distributions Anns Bill
and Carlas - Service Time Probability Service
Time Probability - 0.5 minutes 0.05 1 minute 0.05
- 1 0.10 1.5 0.15
- 1.5 0.20 2 0.20
- 2 0.30 2.5 0.30
- 2.5 0.20 3 0.10
- 3 0.10 3.5 0.10
- 3.5 0.05 4 0.05 4.5 0.05
54Capital Bank
- Data
- Customer inter-arrival time distribution
- Inter-arrival time Probability0.5
Minutes 0.651 0.151.5 0.152 0.05 - Service priority rule is first come first served
- A simulation model is required to analyze the
service.
55Capital Bank Solution
- Calculating expected values
- E (inter-arrival time) 0.5 (0.65) 1 (0.15)
1.5 (0.15) 2 (0.05) - 0.80 minutes
- 75 customers arrive per hour on the average, (60
/ 0.8 75) - E (service time for Ann) 0.1 (0.05) 1 (0.10)
3.5 (0.05) - 2 minutes
- Ann can serve 60 / 2 30 customers per hour on
the average - E (Service time for Bill and Carla) 1 (0.05)
1.5 (0.15) 4.5 (0.05) - 2.5 minutes
- Bill and Carla can serve 60 / 2.5 24 customers
per hour on the average
56Capital Bank Solution
- To reach a steady state the bank needs to employ
all the three tellers - (30 2 (24) 78 gt 75)
57Capital Bank Simulation Logic
- If no customer waits in line, an arriving
customer seeks service by a free teller in the
following order Ann, Bill, Carla. - If all the tellers are busy the customer waits in
line and takes then the next available teller. - The waiting time is the time a customer spends in
line, and is calculated by - Time service begins minus Arrival Time
58CAPITAL Simulation Demonstration
1.5
1.5
1.5
1.5
1.5
Bill
1.5
1.5
1.5
Ann
1.5
Mapping Interarrival time 80 94 1.5 minutes
Mapping Anns Service time 35 64 2 minutes
59CAPITAL Simulation Demonstration
Bill
Ann
Mapping Interarrival time 80 94 1.5 minutes
Mapping Bills Service time 40 69 2.5
minutes
60CAPITAL Simulation Demonstration
Waiting time
61CAPITAL 1000 Customer Simulation
62CAPITAL 1000 Customer Simulation
- This simulation estimates two performance
measures - Average waiting time in line (Wq) 1.67 minutes
- Average waiting time in the system W 3.993
minutes
- To determine the other performance measures, we
can use Littles formulas - Average number of customers in line Lq
(1/.80)(1.67) 2.0875 customers - Average number of customers in the system
(1/.80)(3.993) 4.99 customers.
63 WALTON4.XLS
- The company does not know exactly how many
calendars its customers will demand, but it does
have historical data on demands for similar
calendars in previous years. - Walton wants to use these historical data to
determine a reasonable probability distribution
for next years demand for calendars. - Then it wants to use this probability
distribution, together with _at_Risk, to simulate
the profit for any particular order quantity. - It eventually wants to find the best order
quantity.
64Solution
- We will use this example to illustrate many of
_at_Risks features. - We first see how it helps use to choose an
appropriate input distribution for demand. - Then we will use it to build a simulation model
for a specific order quantity and generate
outputs from this model. - Finally we will see how the RISKSIMTABLE function
enables us to simultaneously generate outputs
from several order quantities so that we can
chose a best order quantity.
65Loading _at_Risk
- The first step, if you have not already done it,
is to install Palisade Decision tools suite. - Once _at_Risk is loaded, you will see two new
toolbars, the Decision Tools toolbar shown here
and the _at_Risk toolbar shown on the next slide.
66Fitting a Probability Distribution
- Some of the historical demand data appears on the
next slide. - As the text box indicates, Walton believes the
probability distribution of demand for next
years calendars should closely match the
histogram for the historical data. - To see which probability distributions match the
histogram well, we can use _at_Risks fitting
ability, using the following steps.
67Fitting a Probability Distribution -- continued
- Model window. Click on the Show _at_Risk-Model
Window toolbar button. _at_Risk has two windows that
get you outside of Excel The Model and Results
windows. The former helps in setting up the
model the latter shows results from running a
simulation. For now, we require the Model window. - Insert a Fit Tab. Once the Model window is
showing, select the Insert/Fit Tab menu item.
This brings up a one-column spreadsheet on the
left. - Copy and paste data. We want to copy the
historical data to this mini-spreadsheet. To do
so, go back to the Excel windows, copy the
historical data, go back to the _at_Risk Model
window, and paste the data copy and paste work
in the usual way.
68Fitting a Probability Distribution -- continued
- Select candidate distributions. _at_Risk has many
probability distributions from which to select.
To see the candidates, select the Fitting/Specify
Distributions to Fit menu item. This brings up
the dialog box shown on the next slide. You can
check as many of the candidates as you like. Some
are undoubtedly unfamiliar to you so you might
want to stick with familiar distributions such as
the normal and triangular. However, we clicked on
the OK to accept the defaults shown in the figure.
69Fitting a Probability Distribution -- continued
70Fitting a Probability Distribution -- continued
- Do the fitting. Select the Fitting/Run Fit Now
menu item to see which of the candidate
distributions most closely match the historical
data. _at_Risk evaluates the fits in several
different ways, and it also allows you to check
the fits visually. After it runs, you will see a
screen as shown on the next slide. This screen
shows one of the candidate distributions
superimposed on the histogram of the data. - Examine the selected distribution. To do so,
select the Insert/Distribution Window menu item,
and fill it out as shown on the slide after the
next. Specifically, select Fit Results in the
Source box, select By Name in the Choose box and
click on Normal. _at_Risk provides a very friendly
interface for examining the resulting normal
distribution.
71Fitting a Probability Distribution -- continued
72Fitting a Probability Distribution -- continued
- It has two sliders that you can drag in either
direction to see probabilities of various areas
under the curve. Also you can enter X values or
P values directly into the boxes in the right
column to obtain equivalent information. - A caution about negative values. We should point
out that there is a potential drawback to using
this normal distribution. Although the mean
demand in this example is approximately three
standard deviations to the right of 0, so that a
negative demand is very unlikely there is still
some chance that one can occur which would not
make physical sense in our model. To ensure that
negative demand do not occur, there are two
possibilities.
73Fitting a Probability Distribution -- continued
- First, we could use a truncated normal
distribution of the form RISKNORMAL(Meandem,StDev
,0,1000). The function disallows values below the
third argument or above the fourth argument. The
other possibility is to choose a probability
distribution that, by its very definition, does
not allow negative values. On such distribution
is the Weibull distribution, which provides one
of the best fits to the historical data.
74Developing The Simulation Model
- Now that we have chosen a probability
distribution for demand, the spreadsheet model
for profit is essentially the same as we
developed earlier without _at_Risk. It appears on
the next slide. The only new things to be aware
of are as follows. - Input distribution. We want to use the normal
distribution for demand found from _at_Risks
fitting procedure. To do this, enter the fitted
mean and standard deviation in cells E4 and E5.
Then enter the formula ROUND(RISKNORMAL(MeanDem,S
tdevDem),0) in cell A13 for the random demand.
This uses the RISKNORMAL function to generate a
normally distributed demand with the fitted mean
and standard deviation. Because demands should be
integers, we use Excels ROUND function, with
second argument 0, to round this value to 0
decimals.
75Developing The Simulation Model -- continued
- Output cell. When we run the simulation, we want
_at_Risk to keep track of profit. In _at_Risks
terminology, we need to designate the Profit
cell, E13, as an output cell. There are two ways
to designate a cell as an output cell. One way is
to highlight it and then click on the Add Output
Cell button on the _at_Risk toolbar. An equivalent
way is to add RISKOUTPUT( ) to the cells
formula. Either way, the formula in cell E13
changes from B13D13-C13 to RISKOUTPUT(
)B13D13-C13. The plus sign following RISKOUTPUT
( ) does not indicate addition. It is simply
_at_Risks way of saying Keep track of the value in
this cell as the simulation progresses. Any
number of cells can be designated in this way as
output cells. They are typically bottom line
values of primary interest.
76Developing The Simulation Model -- continued
- Inputs and outputs. _at_Risk keeps a list of all
input cells and output cells. If you want to
check the list at any time, click on the Display
Inputs, Outputs button on the _at_Risk toolbar. It
provides an Explorer-like list as shown here.
77Developing The Simulation Model -- continued
- Summary functions. _at_Risk provides several
functions for summarizing output values. We
illustrate these in the range B16B19. They
contain the formulas RISKMIN(Profit),
RISKMAX(Profit), RISKMEAN(Profit), and
RISKSTDDEV(Profit). The values in these cells are
not of any use until we run the simulation.
However, once the simulation runs, these formulas
capture summary statistics of profit.
78Running the Simulation
- Now that we have developed the model for Walton,
the rest if straightforward. - The procedure is always the same. We specify the
simulation settings and the report setting and
then run the simulation. - Simulation settings. We must first tell _at_Risk how
we want the simulation to be run. To do so, click
on the Simulation Settings button on the _at_Risk
toolbar. Click on the Iterations tab and fill out
the dialog box as shown on the next slide. This
says that we want to replicate the simulation
1000 times, each with a new random demand.
79Running the Simulation -- continued
80Running the Simulation -- continued
- Then click on the Sampling tab and fill out the
dialog box as shown here. For technical reasons
it is always best to use Latin Hypercube
sampling, it is more efficient.
81What is Sampling?
- Sampling is the process by which values are
randomly drawn from the selected distribution. - In _at_Risk, during each iteration of the
simulation, one observation is chosen from the
input distribution. - As the number of iterations increases, the sample
of observations more closely resembles the input
distribution. - When running a simulation, it is important that
all areas of the input distribution get sampled,
especially the low probability (high uncertainty)
areas. If not, uncertainty will seem less than
it actually is.
82The Concept of Efficiency
- Statisticians have developed different ways to
sample (or draw) from distributions. - If we could do an infinite number of iterations
in our simulation, these methods would produce
equal results. - However, since we use a finite number of
iterations, sampling methods do not produce
equivalent results. - A sampling method is considered more efficient
than another if it approximates a distribution
with fewer iterations. - Two popular sampling methods
- Monte Carlo Simulation
- Latin Hypercube
83Monte Carlo vs. Latin Hypercube
- Monte Carlo simulation draws samples from the
full range of the distribution on each draw. - Is an entirely random sampling technique.
- Requires a large number of iterations to
adequately approximate the input distribution. - Why? Most observations drawn are closer to the
mean. Creates clustering. The tails (areas of
high uncertainty) are usually underrepresented in
the sampling.
- Latin Hypercube samples from all parts of the
distribution, reducing clustering. - Not entirely random (is a stratified sampling
method) - Latin Hypercube divides a distribution into
intervals (strata) of equal probability and
randomly draws from each interval. - Insures that all portions of the distribution are
sampled, including the tails. - Latin Hypercube sampling is more efficient than
Monte Carlo samplingRequires fewer iterations.
84- Notice that the tails (high uncertainty areas)
are not adequately represented. This results in
underestimating risk - Notice that even with only 8 observations, the
tails are much more adequately represented. This
results in a truer representation of risk. The
area in each strata is equal but the width of
each strata varies.
85Running the Simulation -- continued
- We also recommend checking the Monte Carlo button
on the Standard Recalc group. Although this has
no effect on the ultimate results, it means that
you will see random numbers in the spreadsheet. - Report settings. _at_Risk has many options for
displaying the outputs from a simulation. The
outputs can be placed in an _at_Risk Results window
or on new sheets of your Excel workbook. They can
also be shown in more or less detail. Click on
the Report settings button on the _at_Risk toolbar
to select some of these options. In the dialog
box on the next slide we have requested a summary
of the simulation and detailed statistics, and we
have asked that they be shown both in the _at_Risk
Results window and on new sheets in the current
workbook.
86Running the Simulation -- continued
87Running the Simulation -- continued
- Run the simulation. We are finally ready to run
the simulation! To do so, simply click on the
Start Simulation button on the _at_Risk toolbar. At
this point, _at_Risk repeatedly generates a random
number for each random input cell, recalculates
the worksheet, keeps track of all output cell
values. You can watch the progress at the bottom
left of the screen.
88Analyzing the Output
- _at_Risk generates a large number of output
measures. We discuss the most important of these
now. - Summary Report. Assuming that the top box was
checked in the _at_Risk Reports dialog box, we are
immediately transferred to the _at_Risk Results
window. This window contains the summary results
shown here.
89Analyzing the Output -- continued
- Detailed Statistics. We can also request more
detailed statistics within the _at_Risk Results
window with the Insert/Detailed Statistic menu
item. Some of these detailed statistics appear on
the next slide. All of the information in the
Summary Report is here, plus some. - Target values. By scrolling to the bottom of the
detailed statistics list, as shown on the slide
after next, you can enter any target value or
target percentile. If you enter a target value,
_at_Risk calculates the corresponding percentile,
and vice versa.
90Analyzing the Output -- continued
91Analyzing the Output -- continued
- Simulation data. The results to this point
summarize the simulation. It is also possible to
see the full results the data, demands and
profits, from all 1000 replications. To do this
select the Insert/Data menu item. A portion of
the data appears on the next slide.
92Analyzing the Output -- continued
- Charts. To see the results graphically, click on
the Profit item in the left pane of the Results
window and then select the Insert/Graph/Histogram
menu item. This creates a histogram of the 1000
profits from the simulation.
93Analyzing the Output -- continued
- The same interface is available that we saw
earlier namely, we can move the sliders at
the top of the chart to the left or right to see
various probabilities. - Outputs in Excel. Often we will want the
simulation outputs, including charts, in an Excel
workbook. The easiest way to get the numerical
information shown earlier is to fill out the
Report Settings dialog box as we did. Then
separate sheets are created to hold the reports. - This has been a quick tour through _at_Risks report
capabilities. - The best way to become more familiar with _at_Risk
is to experiment with the user-friendly interface.
94Using RISKSIMTABLE
- Waltons ultimate goal is to choose an order
quantity that provides a large average profit. - We could rerun the simulation model several
times, each time with a different order quantity
in the OrderQuan cell, and compare the results. - However, this has two drawbacks.
- First, it takes a lot of time and work.
- Second, each time we run the simulation, we get a
different set of random demands. Therefore, one
of the order quantities could win the contest
just by luck. For a fairer comparison, it would
be better to test each order quantity on the same
set of random demands.
95 WALTON5.XLS
- The RISKSIMTABLE function in _at_Risk enables us to
obtain a fair comparison quickly and easily. - This file includes the setup for this model.
- The next slide shows the comparison model.
- There are two modifications to the previous
model. - First, we have listed order quantities we want to
test in the range names OrderQuanList. - Second, instead of entering a number in cell B9,
we enter the formula RISKSIMTABLE(OrderQuanList).
96The Spreadsheet
97Using RISKSIMTABLE -- continued
- Note that the list does not need to be entered in
the spreadsheet. - However the model is now set up to run the
simulation for all order quantities in the list. - To do this, click on the Simulation Settings
button on the _at_Risk toolbar and fill out the
Iterations dialog box as shown on the next slide.
- Specifically, enter 1000 for the number of
iterations and 5 for the number of simulations.
98Using RISKSIMTABLE -- continued
99Using RISKSIMTABLE -- continued
- After running the simulations, the Report window
shows the results for all five simulations. - For example, the basic summary report appears on
the next slide. - The first five lines show summary statistics of
profit. - Although we do not show them here, the same
information can be seen graphically. A separate
histogram of profit for each simulation is easy
to obtain.
100Using RISKSIMTABLE -- continued
101Using RISKSIMTABLE -- continued
- Indeed, much of the appeal of _at_Risk is that we
can see all of these characteristics averages,
minimums, maximums, percentiles, charts and use
them to make informed decisions.
102Background Information
- As in the previous example, Walton needs to place
an order for next years calendar. - We continue to assume that the calendars will
sell for 10 and customer demand for the
calendars at this price is normally distributed
with mean 168.1 and standard deviation 57.6. - However, there are now two other sources of
uncertainty.
103Background Information -- continued
- First, the maximum number of calendars Waltons
supplier can supply is uncertain and is modeled
with a triangular distribution. - Its parameters are 125, 250, and 200. Once
Walton places and order, the supplier will charge
7.50 per calendar if he can supply the entire
Walton order. Otherwise, he will charge only
7.25 per calendar. - Second, unsold calendars can no longer be
returned to the supplier for a refund. Instead,
Walton will put them on sale for 5 a piece after
February 1.
104Background Information -- continued
- At that price, Walton believes the demand for
leftover calendars is normally distributed with
mean 50 and standard deviation 10. - Any calendars still left over, say after March 1,
will be thrown away. - Walton plans to order 200 calendars and wants to
use simulation to analyze the resulting profit.
105Solution
- As before, we first need to develop the model.
- Then we can run the simulation with _at_Risk and
examine the results. - The completed model appears on the next slide.
- The model itself requires a bit more logic than
the previous Walton model.
106Solution WALTON6.XLS
107Developing The Simulation Model
- The model can be developed with the following
steps. - Random inputs. There are three random inputs in
this model the most the supplier can supply
Walton, the customer demand when the selling
price is 10, and the customer demand for
sale-price calendars. Generate these cells in
A16, D16 and G16 with the formulas
ROUND(RiskTrian(E9,E10,E11),0),
ROUND(RiskNormal(E5,E6),0) and
ROUND(RiskNormal(F5,F6),0). Note that we
generate the random potential demand for
calendars at the sale price even though there
might not be any calendars left to put on sale.
108Developing The Simulation Model -- continued
- Actual supply. The number of calendars supplied
to Walton is the smaller of the number ordered
and the maximum the supplier is able to supply.
Calculate this value in cell B16 with the formula
MIN(MaxSupply,OrderQuan). - Order cost. Walton gets the reduced price, 7.25,
if the supplier cannot supply the entire order.
Otherwise, Walton must pay 7.50 per calendar.
Therefore calculate the total order cost in cell
C16 with the formula IF(MaxSupplygtOrderQuan,Unit
Cost1,UntiCost2)Supply
109Developing The Simulation Model -- continued
- Other quantities. The rest of the model is
straightforward. Calculate the revenue from
regular-price sales in cell E16 with the formula
UnitPrice1MIN(Supply,Demand1). Calculate the
number left over after regular-price sales in
cell H16 with the formula UnitPrice2MIN(Leftover
, Demand2). Finally, calculate profit and
designate it as an output cell for _at_Risk in cell
I16 with the formula RISKOUTPUT( )E16H16-C16.
110Using _at_Risk
- As always, the next steps are to specify the
simulation settings, specify the report settings
and run the simulation. - When there are several input cells, _at_Risk
generates a value from each of them independently
and calculates the corresponding profit on each
iteration. - Selected results appear on the next slide.
- They indicate an average profit of 255.66, a 5th
percentile of - 410.50, a 95th percentile of
514.25, and a distribution of profits that is
again skewed to the left.
111Using _at_Risk
112Sensitivity Analysis
- We now demonstrate a feature of _at_Risk that is
particularly useful when there are several random
input cells. - This feature lets us see which of these inputs is
most related to, or correlated with, an output
cell. - To perform this analysis, select the
Insert/Graph/Tornado Graph menu item from the
_at_Risk Results window.
113Sensitivity Analysis -- continued
- In the resulting dialog box, select Profit as the
output variable and click on the Correlation
Sensitivity button. - This produces the results shown here.
114Sensitivity Analysis -- continued
- The regression option produces similar results,
but we believe the correlation option is easier
to understand. - This figure shows graphically and numerically how
each of the random inputs correlates with profit
the higher the correlation, the stronger the
relationship between that input and profit. - In this sense, we see that the regular-price
demand has by far the strongest effect on profit.
115Sensitivity Analysis -- continued
- The other two inputs, maximum supply and
sale-price demand, are not nearly as important
because they are nearly unrelated to profit. - Identifying important input variables can be
important for real applications. - If a random input is highly correlated with an
important output, then it might be worth the time
and cost to learn more about this input and
possibly reduce the amount of uncertainty
involving it.
116CORRELATED VARIABLES
- All previous problems have had random numbers be
probabilistically independent. This means that if
a random value is much larger than its mean, the
other random values are unaffected. - Sometimes values are correlated. If they are
positively correlated, then a large number for
one value will tend to produce a large number for
a second value. While negatively correlated
values tend to move in opposite directions.
117CORRELATED VARIABLES
- Suppose that there are two different calendars
that are sold but their demand is negatively
correlated. This means that if a customer buys
one calendar they are unlikely to buy another
one. Assume a correlation of -0.90. - The other parameters of this problem are the same
as Calendar.xls. - Compare the profit levels for correlation values
of -0.90, 0, and 0.90.
118CORRELATED VARIABLES
- The RISKCORRMAT (correlation matrix) function is
needed. - A correlation matrix has 1s along the main
diagonal since a variable is perfectly correlated
with itself. The correlation values appear in the
other parts of the matrix and the matrix is
symmetric. - See Correlated Demand template.xls.
- We would like to run this model for 3 different
correlation values that appear in cells I9K9 as
-.90, 0, and 0.90.
119CORRELATED VARIABLES
- The correlation matrix is entered in cells J5K6
as 1, RISKSIMTABLE(I9K9), J6, and 1. - The RISKSIMTABLE command will allow _at_Risk to run
the simulation for the three different
correlation values that appear in cells I9K9. - We assume that the company orders 200 calendars
of each type (cells B9 and B10). - The data from Calendar.xls are entered into cells
B4B6 and E4E6.
120CORRELATED VARIABLES
- The formulas for Revenue, Cost, Refund, and
Profit from Calendar.xls are entered in row 14
for product 1 and then copied to row 15 for
product 2. - All values for the two products are summed in row
16. - To finish the model we must randomly generate
correlated demands for the two products in cells
B14 and B15.
121CORRELATED VARIABLES
- The demand for product 1 is entered in cell B14
as ROUND(RISKTRIANG(E4,E5,E6,RISKCORRMAT(J5K6,1
)),0) - The demand for product 2 is entered in cell B15
as ROUND(RISKTRIANG(E4,E5,E6,RISKCORRMAT(J5K6,2
)),0) - The first argument of RISKCORRMAT is the
correlation matrix range. The second is an index
of the variable (1 for product 1 and 2 for
product 2). - Correlated demand values for the two products
will now be generated.
122CORRELATED VARIABLES
- Next specify the simulation settings (1000 runs
and number of simulations 3, one for each
correlation value). - In the Detailed Statistics window you will see
Profit results for the 3 runs. The mean values
are equal at 674.995.
123CORRELATED VARIABLES
- This may be surprising but can be explained
because _at_Risk uses the same random numbers for
each run but shuffles them in different orders
to get the correct correlations. - The means are unaffected since this is like
saying the average of 30, 26, and 48 is the same
as the average of 48, 30, and 26.
124CORRELATED VARIABLES
- Notice, however, that the standard deviations for
the three runs are different (157.7571, 262.881,
and 365.5104). - This means that the variation in profit increases
as the correlation goes from negative to zero to
positive.
125CORRELATED VARIABLES
- When demands are negatively correlated, high
demands for one product tend to cancel low
demands for another product making extreme profit
values less likely. - When demands are positively correlated, high and
low demands tend to go together making extreme
profits more likely. - This is why investors are warned to diversify
their portfolio to reduce risk.