Spreadsheet Simulation

1

• Spreadsheet Simulation

2
Decision 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
3
Random 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.

4
Best-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.

5
Possible 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)
14
Origins 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.
15
Monte Carlo Simulation

• A model that mimics the behavior of a (random)
  system

Probabilistic Input (random number)
Analysis
r1, r2, , rn
Model
Controllable Input
Outcome
16
Random 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

17
Monte 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

18
Monte 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

19
Simulation

• 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.

20
Random 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.

21
Examples of Discrete Probability Distributions
22
Examples of Continuous Probability Distributions
23
Discrete 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.

24
How 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)

25
Simulating 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

26
Background 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.

28
The 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.

29
The 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.

30
Simulation -- 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).

31
Simulation -- 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.

32
Simulation -- 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.

33
Simulation -- 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.

34
Simulation for Walton Bookstore
35
Accomplishments

• 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.)

36
Probability 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.

37
Confidence 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.

38
Simulation

• 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.

39
Specialty 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.

40
Specialty Steel Products Co. Example B.1
Historical records indicate that lathe department
demand varies from week to week as follows
41
Specialty 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
42
Specialty 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
43
Specialty 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
44
Specialty 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.

45
Specialty 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
46
Specialty 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.
47
Specialty 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.

48
Specialty Steel Products Co.Example B.2
Simulating a particular capacity level

1. 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.
2. Find the random-number interval for production
   requirements associated with the random number.
3. Record the production hours (PROD) required for
   the current week.
4. Draw another random number from row three or four
   of the table.
5. Find the random-number interval for capacity
   (CAP) associated with the random number.
6. Record the capacity hours available for the
   current week.

49
Specialty 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.

50
Specialty Steel Products Co. 20-week simulation
51
Specialty 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.
52
Capital 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.

53
Capital 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

54
Capital 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.

55
Capital 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

56
Capital Bank Solution

• To reach a steady state the bank needs to employ
  all the three tellers
• (30 2 (24) 78 gt 75)

57
Capital 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

58
CAPITAL 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
59
CAPITAL Simulation Demonstration
Bill
Ann
Mapping Interarrival time 80 94 1.5 minutes
Mapping Bills Service time 40 69 2.5
minutes
60
CAPITAL Simulation Demonstration
Waiting time
61
CAPITAL 1000 Customer Simulation
62
CAPITAL 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.

64
Solution

• 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.

65
Loading _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.

66
Fitting 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.

67
Fitting 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.

68
Fitting 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.

69
Fitting a Probability Distribution -- continued
70
Fitting 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.

71
Fitting a Probability Distribution -- continued
72
Fitting 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.

73
Fitting 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.

74
Developing 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.

75
Developing 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.

76
Developing 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.

77
Developing 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.

78
Running 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.

79
Running the Simulation -- continued
80
Running 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.

81
What 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.

82
The 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

83
Monte 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.

85
Running 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.

86
Running the Simulation -- continued
87
Running 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.

88
Analyzing 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.

89
Analyzing 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.

90
Analyzing the Output -- continued
91
Analyzing 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.

92
Analyzing 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.

93
Analyzing 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.

94
Using 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).

96
The Spreadsheet
97
Using 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.

98
Using RISKSIMTABLE -- continued
99
Using 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.

100
Using RISKSIMTABLE -- continued
101
Using 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.

102
Background 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.

103
Background 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.

104
Background 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.

105
Solution

• 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.

106
Solution WALTON6.XLS
107
Developing 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.

108
Developing 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

109
Developing 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.

110
Using _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.

111
Using _at_Risk
112
Sensitivity 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.

113
Sensitivity 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.

114
Sensitivity 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.

115
Sensitivity 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.

116
CORRELATED 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.

117
CORRELATED 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.

118
CORRELATED 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.

119
CORRELATED 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.

120
CORRELATED 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.

121
CORRELATED 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.

122
CORRELATED 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.

123
CORRELATED 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.

124
CORRELATED 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.

125
CORRELATED 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.