Spreadsheet Simulation - PowerPoint PPT Presentation

1 / 125
About This Presentation
Title:

Spreadsheet Simulation

Description:

Spreadsheet Simulation * Using RISKSIMTABLE -- continued * Using RISKSIMTABLE -- continued After running the simulations, the Report window shows the results for all ... – PowerPoint PPT presentation

Number of Views:338
Avg rating:3.0/5.0
Slides: 126
Provided by: Mois93
Category:

less

Transcript and Presenter's Notes

Title: 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.
Write a Comment
User Comments (0)
About PowerShow.com