Spreadsheet Simulation

1

• Spreadsheet Simulation

2
Introduction to Simulation

• In many spreadsheets, the value for one or more
  cells representing independent variables is
  unknown or uncertain.
• As a result, there is uncertainty about the value
  the dependent variable will assume
• Y f(X1, X2, , Xk)
• Simulation can be used to analyze these types of
  models.

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
Why Analyze Risk?

• Plugging in expected values for uncertain cells
  tells us nothing about the variability of the
  performance measure we base decisions on.
• Suppose an 1,000 investment is expected to
  return 10,000 in two years. Would you invest
  if...
• the outcomes could range from 9,000 to 11,000?
• the outcomes could range from -30,000 to
  50,000?
• Alternatives with the same expected value may
  involve different levels of risk.

5
Methods of Risk Analysis

• Best-Case/Worst-Case Analysis
• What-if Analysis
• Simulation

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

7
Possible Performance Measure Distributions Within
a Range
8
What-If Analysis

• Plug in different values for the uncertain cells
  and see what happens.
• This is easy to do with spreadsheets.
• Problems
• Values may be chosen in a biased way.
• Hundreds or thousands of scenarios may be
  required to generate a representative
  distribution.
• Does not supply the tangible evidence (facts and
  figures) needed to justify decisions to
  management.

9
Simulation

• Resembles automated what-if analysis.
• Values for uncertain cells are selected in an
  unbiased manner.
• The computer generates hundreds (or thousands) of
  scenarios.
• We can analyze the results of these scenarios to
  better understand the behavior of the performance
  measure and make decisions using solid empirical
  evidence.

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

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

12
Examples of Discrete Probability Distributions
13
Examples of Continuous Probability Distributions
14
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.

15
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)

16
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

17
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

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

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

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

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

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

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

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

25
Simulation for Walton Bookstore
26
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.)

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

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

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

29
http//www.baskent.edu.tr/kilter
30
Capital Bank

• Data
• Service time distributionsAnns 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

30
http//www.baskent.edu.tr/kilter
31
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.

31
http//www.baskent.edu.tr/kilter
32
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

32
http//www.baskent.edu.tr/kilter
33
Capital Bank Solution

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

33
http//www.baskent.edu.tr/kilter
34
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

34
http//www.baskent.edu.tr/kilter
35
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
35
http//www.baskent.edu.tr/kilter
36
CAPITAL Simulation Demonstration
Bill
Ann
Mapping Interarrival time 80 94 1.5 minutes
Mapping Bills Service time 40 69 2.5
minutes
36
http//www.baskent.edu.tr/kilter
37
CAPITAL Simulation Demonstration
Waiting time
37
http//www.baskent.edu.tr/kilter
38
CAPITAL 1000 Customer Simulation
38
http//www.baskent.edu.tr/kilter
39
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.

39
http//www.baskent.edu.tr/kilter
40
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.

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

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

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

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

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

46
Fitting a Probability Distribution -- continued
47
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.

48
Fitting a Probability Distribution -- continued
49
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.

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

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

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

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

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

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

56
Running the Simulation -- continued
57
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.

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

59
Running the Simulation -- continued
60
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.

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

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

63
Analyzing the Output -- continued
64
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.

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

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

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

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

69
The Spreadsheet
70
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.

71
Using RISKSIMTABLE -- continued
72
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.

73
Using RISKSIMTABLE -- continued
74
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.

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

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

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

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

79
Solution WALTON6.XLS
80
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.

81
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

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

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

84
Using _at_Risk
85
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.

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

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

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