Introduction to Risk

1
Example 11.2

• Introduction to _at_Risk

2
Background Information

• Recall that Walton Bookstore buys calendars for
  7.50, sells them at a regular price of 10, and
  gets a refund for all calendars that cannot be
  sold.
• 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.

3
WALTON4.XLS

• The Data sheet of this file contains the
  historical data.
• 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.

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

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

6
(No Transcript)
7
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.

8
(No Transcript)
9
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.

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

11
(No Transcript)
12
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.

13
(No Transcript)
14
(No Transcript)
15
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.

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

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

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

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

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

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

22
Running the Simulation -- continued
23
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.

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

25
Running the Simulation -- continued
26
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.

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

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

29
(No Transcript)
30
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.

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

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

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

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

35
(No Transcript)
36
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.

37
Using RISKSIMTABLE -- continued
38
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.

39
(No Transcript)
40
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.