Introduction to Risk

1
Example 11.5

• Introduction to _at_Risk

2
Background Information

• Using the same data as from the previous example,
  we would like to simulate the net present value
  (NPV) of the profit from all five restaurants
  during the 10-year period form 2000 and 2009.
• We requires two additional assumptions.
• First, we assume that a restaurants profit in
  any year is 5 of revenue for that year.
• Second, we continue to assume that the growth
  rates for different restaurants in any particular
  year are correlated according to the correlation
  matrix given. However, we assume that growth
  rates across years are probabilistically
  independent.

3
Background Information -- continued

• Use these assumptions to analyze the stream of
  yearly profits and the NPV of profits from all
  restaurants, using a discount rate of 10.

4
Solution

• For the 10-year period, we require 50 growth
  rate, 10 for each restaurant.
• We could correlate these correctly by building an
  appropriate 50 x 50 correlation matrix and
  letting the Index argument of the RISKCORRMAT
  function vary from 1 to 50. This would be
  tedious.
• The newest version of _at_Risk allows it to model
  this common correlation structure fairly easily.

5
Solution -- continued

• They added a third argument to the RISKCORRMAT
  function, which in this example will be year.
• For each year, the Index argument will vary from
  1 to 5, and we will require only one 5 x 5
  correlation matrix, as before.
• Essentially, each years correlated values are
  based on this common correlation matrix, but
  different years growth rate are independent.

6
MCDONALDS2.XLS

• The simulation model appears on the next slide.
• This file contains the setup for the model.

7
(No Transcript)
8
Developing The Simulation Model

• The model can be developed as follows.
• Inputs. Enter the inputs in the shaded range. The
  only two new inputs are the profit percentage and
  the discount rate.
• Revenues. It is possible to calculate the
  revenues efficiently if we index the restaurants
  from 1 to 5 in row 24 and list the years in
  column A. After entering these, enter the formula
  B7(1RISKNORMAL(MeanGrowth,StdevGrowth,RISKCORRM
  AT(Cmat,B24,A25))) for restaurant 1 and year
  2000 in cell B25 and copy it across to cell F25.
  For the other years, enter the formula
  B25(1RISKNORMAL(MeanGrowth,StdevGrowth,RISKCORR
  MAT(Cmat,B24,A26))) in cell B26 and copy it to
  the range B26F34.

9
Developing The Simulation Model -- continued

• Profits. Calculate the yearly profits by entering
  the formula ProfitPctSUM(B25F25) in cell G25
  and copying it down. We would like to designate
  this whole Profits range as an _at_Risk output
  range. To do so, highlight it and click on the
  Add Outputs button on the _at_Risk toolbar. You will
  be asked to give this output range a name. We
  suggest Profits, although any name will do. After
  doing this, you will notice that the formulas in
  the Profits range change slightly. For example,
  the formula in cell G27 becomes
  RISKOUTPUT(,Profits,3)ProfitPctSUM(B27F27).
  This indicates that cell 27 is the third cell in
  the Profits output range.

10
Developing The Simulation Model -- continued

• NPV. Calculate the NPV of profits in cell B36 and
  designate it as an additional _at_Risk output cell
  with the formula RISKOUTPUT( )NPV(DiscRate,Profi
  ts).

11
Using _at_Risk

• We again set the number of iterations to 1000 and
  the number of simulations to 1.
• After running _at_Risk, we obtain outputs for the
  NPV cell and the Profits range.
• The best ways to summarize the NPV output are the
  usual ones.
• As in the previous single-year example, this
  variation is about twice as large as if the
  growth rates we uncorrelated, as shown on the
  next slide.

12
(No Transcript)
13
(No Transcript)
14
Summary Charts

• If we designate a whole range, such as Profits,
  as an _at_Risk output range, we can still examine
  each individual cell of this range.
• The real advantage to designating an output range
  is that we can obtain an _at_Risk summary chart for
  the whole-range, as shown on the next slide.
• This type of chart is perfect for time series
  variables. It allows use to see the trend and
  variability through time.

15
(No Transcript)
16
Summary Charts -- continued

• The middle line in this chart tracks mean values.
• The inner band extends to one standard deviation
  on each side of the mean, and the outer band
  extends to the 5th and 95th percentiles.
• The situation shown here is typical.
• As we project farther into the future, the amount
  of variability increases.
• However, the upward exponential growth is clear.
  It is a result of the expected 10 growth per
  year.