Transcript Decision Technology - Villanova University

Decision Technology
Modeling, Software and Applications
Matthew J. Liberatore
Robert L. Nydick
John Wiley & Sons, Inc.
Financial Simulation
Using @Risk
CASH FLOW FOR GM
GM is trying to estimate the cash flows from a new car
that will sell for 5 years. During the current year
(year 0) a fixed development cost of $1.4 billion is
incurred and is depreciated on a straight-line basis
over the next 5 years.
Year 1 unit sales follow a triangular distribution with a
worst case of 100,000 units, most likely case of
150,000, and best case of 170,000.
Sales during years 2-5 are assumed to decay at the same
rate each year. This annual decay rate follows a
triangular distribution with a best case of 5%, most
likely case of 8%, and a worst case of 10%.
Each year a car sells for $15,000.
CASH FLOW FOR GM
During year 1 each car sold incurs a variable
cost of $10,000. Variable cost increases 4%
per year.
The tax rate is 40% and the cash flows are
discounted at 15% per year.
Assume that all cash flows occur at the end of
the year.
Estimate the NPV of the cash flows from the
new car.
What fraction of the time will the new model add
value?
CASH FLOW FOR GM
After making cell D22 an output cell and
running for 1000 simulations, we obtain the
output for this problem.
The NPV is $43 million.
After viewing the histogram of profits, we see
that there is a 32% chance that the project
will have negative cash flows.
CASH FLOW FOR LILLY
In the car business a new model usually sees reduced
sales every year. A new drug; however, sees
increased sales the first few years followed by
reduced sales. Consider the following problem.
Lilly is producing a new drug that will be sold for 10
years. Year 1 unit sales are assumed to follow a
triangular distribution with worst case 100,000
units, most likely case 150,000 units, and best case
170,000.
The year 0 fixed cost is $2.1 billion and is depreciated
on a 10-year straight line basis.
Sales are equally likely to increase for 3,4,5, or 6 years
with the average percentage increase during those
years following a triangular distribution with worst
case 6%, most likely case 9%, and best case 11%.
CASH FLOW FOR LILLY
During the remainder of the life of the drug unit sales
will decrease according to a triangular distribution
with best case 8%, most likely 12% and worst case
18%.
During each year a unit sells for $15,000.
Year 1 variable cost is $10,000.
The unit variable cost of producing the drug increases at
4% per year.
Estimate the mean NPV of the cash flows.
What is the probability that the drug will add value?
What source of uncertainty is the most important driver
of NPV?
CASH FLOW FOR LILLY
We use Autoconvergence to determine the
number of iterations for @Risk to run. Select
Iterations Auto in the Simulation Setting
window and change to 1%.
This will make @Risk keep running iterations
until during the last 100 iterations the mean,
standard deviation, and selected other
statistics change by 1% or less.
The histogram of NPV shows the probability of
having a negative value.
The mean NPV can also be found in the
Summary Statistics.
CASH FLOW FOR LILLY
A tornado graph can be used to determine key
drivers of NPV.
Select Simulation Settings and Sampling dialog
box, select All for Collect Distribution
Samples.
From the output Results window, select the
Graph selected items button and Tornado
Graph option.
The results show that the three key drivers for
NPV are: Unit Sales (89%), length of growth
(40%), and growth rate (18%).
CASH FLOW FOR LILLY
This means that Year 1 Unit sales has a .89
correlation with NPV, length of growth has a
.4 correlation, and annual growth has a .18
correlation.
The uncertainty about Year 1 Unit sales and
length of growth are very important for
determining NPV.
If you spent money to reduce the uncertainty of
one of the four sources of randomness, what
cell would you change? Re-run the
simulation after you reduce the uncertainty
of that cell to show the improvement in NPV.
MANAGERIAL ISSUES
We studied how GM and Lilly might make a
decision about whether to introduce a new
car or a new drug, respectively.
How does your company make these types of
decisions?
What information does our approach provide
that other approaches lack?
OPTIMAL CONSTRUCTION BID
A firm is considering bidding on a construction
contract. If they win the bid, the cost to complete
the project is uncertain but normally distributed
with a mean of $25,000 and a standard deviation of
$3,000.
It costs $1,000 to prepare the bid.
There are 6 potential competitors and it is estimated that
there is a 50% chance that each competitor will bid
on the project (independent probabilities).
If a competitor places a bid, their bid follows a normal
distribution with a mean of $50,000 and a standard
deviation of $10,000.
OPTIMAL CONSTRUCTION BID
Assume that our bid must be in multiples of
$1000 and we are considering bids from
$30,000 to $60,000.
What should be bid to maximize expected profit?
Assume that the low bid always wins.
Run the simulation for 1000 iterations.
How will your optimal bid change if there are 12
competitors?
Why is the best bid less when there are 12
competitors compared to 6?
OPTIMAL CONSTRUCTION BID
In order to solve this problem, you will need to
determine how many competitors will bid.
To accomplish this you will use the
=riskbinomial(n,p) function. This will
simulate the number of successes in n trials
each of which has a probability of success p.
For example, =riskbinomial(100,0.9) will
simulate how many free throw attempts a
player makes out of 100 if her probability of
making any individual free throw is 90%.
NEW DRUG DEVELOPMENT
The pharmaceutical business deals with a very
high degree of uncertainty. Over 90% of all
products under development fail to come to
market resulting in large losses.
Products that do come to market can earn
multibillion profits annually for 10-15 years.
Eli Daisy wants to determine whether a new
drug is worth developing.
NEW DRUG DEVELOPMENT
Before coming to market, the new drug must go
through the following stages:
 Initial R&D
 Preclinical Testing
 Testing I (first phase clinical trials)
 Testing II (second phase clinical trials)
Only after all drug stages succeed can the drug
be sold.
If the drug fails at any stage, then development is
terminated.
A success at any stage leads us to pursue the next
stage.
NEW DRUG DEVELOPMENT
We would like to determine the risk adjusted
NPV (15% discount rate per year).
We would also like to determine the key drivers
for the drug’s profitability.
For each stage we will model the cost,
probability of success, and time required to
complete the stage with a triangular random
variable.
We also model the profit earned if the drug
makes it to market as a triangular random
variable.
NEW DRUG DEVELOPMENT
The following costs (in millions) and time estimates (in
years) for each stage have been determined:
Stage
Best Worst
Most Likely
Initial R&D Cost 50
120
70
Init R&D Time
3
7
4
Preclin Test Cost 10
30
15
Prec Test Time
.5
3
1
Testing I Cost
350 600
480
Testing I Time
3
6
4
Testing II Cost
3500 6000
4200
Testing II Time
3
6
4
Profit
6000014000
18000
NEW DRUG DEVELOPMENT
The following probabilities for each stage have
also been determined.
Stage
Worst
Most Likely
Best
Init R&D .2
.35
.42
Precl Test .3
.5
.6
Testing I .4
.5
.6
Testing II .7
.9
.96
We assume that these random variables are
independent.
Retirement Investing

A 25 year old person wants to invest for 40 years
until retirement.
 She plans to invest $1000 at the beginning of each
of the next 40 years.
 Each year, she plans to put fixed percentages – the
same each year – of this $1000 in stocks, bonds
and T-bills. However, she is not sure which
percentage to use.
 She does have historical annual returns from
stocks, bond and T-bills from 1946-2001.
Retirement.xls
 This
file contains the historical data for the
stocks, bond and T-bills.
 This file also includes inflation factors for
these years.
 For example, for 1993 the annual returns for
stocks, bonds, and T-bills were 9.99%,
18.24% and 2.90%, and the inflation rate
was 2.75%.
Solution
 We
must decide how to use the historical
returns and inflation factors to generate
future values of these quantities.
 We will use a “scenario” approach.
 Each historical year is a possible scenario,
where the scenario specifies the returns and
inflation factor for that year.
 Then for any future year, we randomly
choose one of these scenarios, using
RISKDISCRETE function.
Solution -- continued
 Intuitively more
recent scenarios ought to
have a larger chance of being chosen.
 A weight is given to each scenario, starting
with 1 for 2001. A “damping factor” is
multiplied by the weight from the next year.
 To change weights to probabilities, divide
each weight by the sum of all the weights.
We use a damping factor of 0.98.
Developing the Model

The model can be developed as follows.
1.
2.
Inputs. Enter the data in the shaded regions.
These include the historical returns and inflation
factors, the alternative sets of investment weights
we plan to test, and other inputs.
Weights. The investment weights we will use for
the model are in row 10 to 12. We do this with a
RISKSIMTABLE and VLOOKUP combination.
Specifically, enter the formulas
=RISKSIMTABLE({1,2,3}) in cell A16 and
=VLOOKUP($A$16,$A$10,$D$12,2) in cell
B16 and copy the latter to the cells C16 and D16.
Developing the Model
3.
4.
Probabilities. Enter value 1 in cell F75. Then enter
the formula =$B$4*F75 in cell F74 and copy it up
to cell F20. Sum these values with the SUM
function in cell F76. Then to convert them to
probabilities, enter the formula =F21/$F$76 in cell
G20 and copy it down to cell G75.
Scenarios. We want to simulate 40 scenarios in
columns K through O, one for each year of Sally’s
investing. To do this, enter the formulas
=RISKDISCRETE($A$20:$A$75, $G$20:$G$75)
and =1+VLOOKUP($K20, $A$20:$A$75,L$18) in
cells K20 and L20, and then copy this latter formula
to the range M20:O20.
Developing the Model
5.
6.
Beginning, ending cash. The bookkeeping part begins by
entering the formula =B5 in cell J20 for the initial
investment. Then enter the formulas
=J20*SUMPRODUCT($B$16:$D$16,L20:N20) and
=$B$5+P20 in cells P20 and J21 for ending cash in the first
year and beginning cash in the second year. The former
shows how the beginning cash grows in a given year. The
latter implies that Sally reinvests her previous money, plus
she invests a new $1000. Copy these formulas down column
invests a new $1000. Copy these formulas down column J
and P.
Deflators. We eventually want to deflate future dollars to
today’s dollars. The proper way to do this is to calculate
deflators. Do this by entering the formula =1/O20 in cell
Q20. Then enter the formula Q20/O21 in cells Q21 and copy
it down.
Developing the Model
cash. Calculate the final value in today’s
dollars in cell K15 with the formula =P59*Q59
 Final
Then designate this cell as an @RISK output cell.
Note that multiplying by the deflator for year 40 is
similar to taking an NPV. The only difference is that
the inflation rates differ through the 40 years,
whereas NPV calculations typically involve the
same discount rate each year.
 The number of iterations is 1000 and the number of
simulations is 3.
 The results show that the simulation which invests
the most heavily in stocks is easily the winner.
@Risk Results -- continued
 The
histogram for simulation 1 indicates a
lot of variability – and skewness – in the
distribution of final cash.
 A useful concept we might introduce here is
value at risk (VAR). It is defined as the 5th
percentile of a distribution and is often the
value investors worry about.
@Risk Results -- continued
 You
also could run this simulation with
other investment weights, both for the 40year horizon and for shorter time horizons
such as 10 or 15 years.
 Even though the stock strategy appears to
be best for a long horizon, it might not fare
as well for a shorter horizon. You could also
try getting newer data, after 2001.
Winning at Craps

We would like to use simulation to estimate
the probability of winning a single game of
craps.
1.
Simulate tosses. Simulate the results of 40 tosses
in the range B5:D44 by entering the formula
=RISKDUNIFORM({1, 2, 3, 4, 5, 6}) in cells
B5 and C5 and the formula =SUM(B5:C5) in
cell D5. Then copy these to the range B6:D44.
RISKDUNIFORM takes a list of numbers and
randomly selects one of the numbers from the
list, where each number has equal probability.
This step is like rolling two die (columns B and
C) and summing the results (column D).
Developing the Model
2.
3.
First toss outcome. Determine the outcome of the first
toss with the formulas =IF(OR(D5=7,D5=11),1,0),
=IF(OR(D5=2,D5=3,D5=12),1,0) and
=IF(AND(E5=0,F5=0),”Yes”,”No”) in cells E5 and F5,
and G5. Similarly, the OR condition in cell F5 checks
whether he loses right away. In cell G5, we use the AND
condition to check whether both cells E5 and F5 are 0, in
which case the game continues. Otherwise, the game is
over.
Outcomes of other tosses. Assuming the game continues
beyond the first toss, Joe’s point is the value in cell D5.
Then we are waiting for a toss to have the value in D5 or
7, whichever occurs first. To implement this logic, enter
the formulas =IF(OR(G5=“No”,G5=“”),
“”,IF(D6=$D$5,1,0)), =IF(OR(G5=“No”,G5=“”),
“”,IF(D6=7,1,0)) and =IF(OR(G5=“No”,G5=“”),
“”,IF(AND(E6=0,F6=0),”Yes”,”No”)) in cells E6, F6,
and G6. Then copy these to the range E7:G44.
Developing the Model
4.
5.
Game outcomes. We keep track of two aspects
of the game in @Risk output cells, whether Joe
wins or loses and how many tosses are required.
To find these, enter the formula =SUM(E5:E44)
and =COUNT(E5:E44) in cells B47 and B48.
Note that both functions, SUM and COUNT,
ignore blank cells.
Simulation summary. Although we get various
summary measures in the @RISK Results
window when we run the simulation, it is useful
to see some key summary measures right on the
model sheet.
Developing the Model
To get these, enter the formula
=RISKMEAN(B47) in cell B50 and copy it to
cell B51. As the labels indicate, the
RISKMEAN in cell B50, being an average of
0’s and 1’s, is just the fraction of iterations
where Joe wins. The average in cell B51 is the
average number of tosses until the game’s
outcome is determined.
@Risk Results
 After
running @RISK, we obtain the summary
results in cells B50 and B51 of the simulation.
 Our main interest is in the average in cell B50
because it represents our best estimate of the
probability of winning, 0.494. (A probability
argument can be used to show that the exact
probability of winning in craps is 0.493.)
 We also see that the average number of tosses
needed to determine the outcome of a game was
3.395. (The maximum number of tosses ever
needed was 29.)
March Madness





We can simulate the NCAA basketball tournament to
estimate the probability that each teams wins.
We use 2005 tournament pairings and each team’s
Sagarin’s nationally syndicated rating.
The Sagarin’s ratings can be used to estimate the
probability that a team wins any particular game. Those
results can be used to play out the tournament.
Suppose team A plays team B and Sagarin’s ratings for
these teams are 85 and 78. Sagarin predicts that the point
differential will be the difference between the ratings.
We assume the actual point differential is normally
distributed with mean equal to Sagarin’s prediction (7)
with a standard deviation for each game of 10.
March-Madness-Men2005.xls
 We
will only outline the simulation model.
 The file contains the full details.
 The entire simulation is on a single Model
sheet. Columns A to C list team indexes,
team names, and Sagarin ratings.
 Winners from one round are automatically
carried over to the next round with
appropriate formulas.
 Portions
of the Model sheet appear on the
next slides.
Developing the Model

We now describe the model.
1.
2.
Teams and ratings. We first enter the teams and their
ratings, as shown.
Simulate rounds. Jumping ahead to the fourth-round
simulation shown on the previous slide, we capture the
winners from the previous round 3 and then simulate the
games in round 4. The key formulas are in columns N and
O. For example, the formulas in cells N126 and O126 are
=VLOOKUP(L126,LTable,3)VLOOKUP(L127,LTable,3) and
=RISKNORMAL(N126,10) The first of these looks up
the ratings of the two teams involved and subtracts them to
get the predicted point spread. The second formula
simulates a point spread with the predicted point spread as
its mean.
Developing the Model
 Outputs.
As shown by the boxed-in cells in Figure
12.44, we designate seven cells as @RISK output
cells: the index of the winner, the indexes of the two
finalists, and the indexes of the four semifinalists
(the Final Four teams). However, the results we
really want are tallies, such as the number of
iterations where North Carolina (or any other team)
wins the tournament. This takes some planning.
In the @RISK Reports dialog box, if we check the
Output Data option, we get a sheet called Outputs
Data Report that lists the values of all @RISK
output cells for each of the iterations. (We used 5000
iterations.)
Developing the Model
After we have these, we can use COUNTIF
functions to tally the number of wins (or finalist
or semifinalist appearances) for each team,
right in the original Model sheet. Some of these
tallies appear on the next slide. For example,
the formula in cell U5 is
=COUNTIF(‘Outputs Data
Report’!$I$8:$I$5007,S5)
Developing the Model
 In
this case, the range I8:I5007 of the Outputs
Data Report contains the indexes of the 5000
winners, so this formula simply counts the
number of these that are index 1.
 As you can see, the top-rated team in the
Chicago region, Illinois, won the tournament in
1014 of the 5000 iterations and reached the Final
Four almost half of the time.
 In contrast, the lowly rated Fairleigh Dickinson
did not make the Final Four in any of the 5000
iterations.
@Risk Results
 This
simulation came out somewhat
differently than in previous years.
 The two teams predicted by the simulation
to win most often, North Caroline and
Illinois, actually made it to the finals, and
the higher rated of these two teams, North
Carolina, actually won the championship.
 Simulations of sporting events are not
always this accurate!
SUMMARY

In this chapter, we:

Showed how @Risk could be used for
financial simulation and additional
applications.

Specific areas covered include: cash flow
models, a bidding problem, retirment
planning, a game of chance, and a NCAA
basketball tournament simulation.
COPYRIGHT
Copyright  2003 Matthew J. Liberatore and Robert L. Nydick. All rights reserved.
Reproduction or translation of this work beyond that named in Section 117 of the
United States Copyright Act without the express written consent of the copyright
owners is unlawful. Requests for further information should be addressed to
Matthew J. Liberatore and Robert L. Nydick. Adopters of the textbook are granted
permission to make back-up copies for their own use only, to make copies for
distribution to students of the course the textbook is used in, and to modify this
material to best suit their instructional needs. Under no circumstances can copies be
made for resale. Matthew J. Liberatore and Robert L. Nydick assume no
responsibility for errors, omissions, or damages, caused by the use of these programs
or from the use of the information contained herein.