Unformatted Attachment Preview
Problem 1: Marvin Delacroix is considering building a 300-seat concert venue in the
neighborhood. Based on a simple market study, Marvin believes:
• He will run a concert every night.
• The concert hall will make a profit of $1 on each occupied seat and incur a loss
of $0.25 for each unoccupied seat.
• The probability of bad weather on any given night is 20%
• When the weather is good, the number of customers will be normally
distributed, with a mean of 275 and a standard deviation of 30.
• When the weather is bad, the number of customers will be normally
distributed, with a mean of 200 and a standard deviation of 50.
a. Set up Marvin’s problem as a model and simulate his total profit for 1 month (30 days). In
your model use integers wherever you are accounting for people/seats. What is Marvin’s
average monthly profit?
b. Create a monitoring function that you can use to assess the likelihood of different levels
of profit. What is the probability that, in any given month, Marvin’s profit will be less
than $7,000?
Problem 2: Advanced Cardiovascular Systems is a medical device firm based in San Diego, CA.
The firm is a leader in the market for heart-disease-related medical devices including balloon
catheters, which are used to open clogged arteries.
You are a new project manager at ACS, overseeing ACS latest balloon catheter model, XT-99.
Your predecessor developed a worksheet cash flow model for the XT-99. The model show the
projected number of units sold, price per unit, etc. for each year, as well as annual revenues,
costs and gross margin.
ACS uses an annual discount rate of 12% (which, once again, is an assumption) to calculate
the net present value of its projects. The model assumes that cash flows – other than the
initial development costs are incurred at the end of each year.
Study the worksheet given in Problem 2, and advise ACS whether to pursue final development
of the XT-99 or abandon it now. An explanation of assumptions underlying the model are given
below.
At the current time (the end of year 0), XT-99 is nearing final development and will
be ready to enter the market in the near future. Remaining development costs are
$13.3 million, to be incurred at the end of year 0; and $7.5 million, to be incurred at
the end of year 1. ACS expects that the product will enter the market at the end of
year 2 with initial sales of 800,000 units. Based on prior experience, the firm
believes that the demand for the XT-99 will grow gradually and then decline over its
useful life-time of 14 years, but is not sure what these growth and drop-off rates
will be. For now, the firm is estimating that demand will grow by 10% per year
through the end of year 9 and then decline at 8% per year after that.
ACS is planning to charge a premium price for the XT-99 and then bring the price
down over time as competition for the advanced technology grows. At the end of
year 2, when the XT-99 goes to market, the wholesale price will be $34.50 and
will hold at that level until the end of year 6. As above, the firm is not yet sure
how much the price will drop year-to-year starting in year 7 but is making an
initial assumption that rate will be 10% per year.
The initial costs for the XT-99 will be $15.25 per unit. Over time, as volume grows
and the firm realizes efficiencies, costs will decline (at a rate of 5.5%), although the
cost of materials is expected to grow (at a rate of 3.5% yearly). Once again, ACS is
not sure how quickly costs will drop, but is confident that the decreases will hold
only while demand for the XT-99 is growing.
You may assume that the model as given is correct.
a. Copy your base case model onto a new worksheet and name the worksheet “Problem
#4b.” Then, use the base case model, as well as the information below, to create an
@RISK simulation model:
• Although development costs in year 1 are expected to be $13.3 million, they
could range as low as $11.5 million and as high as $16 million. Use RiskUniform.
• Development costs in year two have an equal probability of being either $7.5
million or $8 million. Use RiskDiscrete.
• The number of units sold in the first year could range from 700,000 to
900,000 with all values equally likely. Use RiskIntUniform.
• The discount rate is normally distributed with a mean of 12% and a
standard deviation of 1.5%. Use RiskNormal.
• Demand growth is normally distributed with a mean of 10% and a standard
deviation of 1%; demand decline is normally distributed with a mean of 8% and a
standard deviation of .5%. Use RiskNormal.
• Cost decreases will likely average 2.5% but could be as small as 1% and as large
as 5.5%. Use RiskTriang.
Using 1000 iterations for your simulation run, what are the expected values for Units Sold,
Total Gross Margin and NPV?
Advanced Cardio Systems
NPV Analysis
Inputs
Development Costs
Year 1
Year 2
Initial Sales (units)
Selling Price
Cost per Unit
Discount Rate
Uncertainties
Changes in Demand
Growth (years 3-9)
Decline (years 10-16)
Price Decrease (years 7-16)
Cost Decrease (years 3-9)
Results Summary
Units Sold
Total Revenues
Total Costs
Total Gross Margin
Cash Flow and NPV Model
NPV
$13,300,000
$7,500,000
800,000
$34.50
$15.25
12%
10%
-8%
-10%
-2.5%
17,075,724
$425,708,344
$227,555,981
$198,152,363
$77,759,277
0
1
Development Costs ($13,300,000) ($7,500,000)
Units Sold
Price per Unit
Cost per Unit
Revenue
Cost
Cash Flows ($13,300,000) ($7,500,000)
Year
2
800,000
$34.50
$15.25
3
880,000
$34.50
$14.87
4
968,000
$34.50
$14.50
5
1,064,800
$34.50
$14.13
6
1,171,280
$34.50
$13.78
7
1,288,408
$31.05
$13.44
8
1,417,249
$27.95
$13.10
9
1,558,974
$25.15
$12.77
$27,600,000 $30,360,000 $33,396,000 $36,735,600 $40,409,160 $40,005,068 $39,605,018 $39,208,968
$12,200,000 $13,084,500 $14,033,126 $15,050,528 $16,141,691 $17,311,964 $18,567,081 $19,913,195
$15,400,000 $17,275,500 $19,362,874 $21,685,072 $24,267,469 $22,693,105 $21,037,937 $19,295,773
10
1,434,256
$22.64
$12.77
11
1,319,515
$20.37
$12.77
12
1,213,954
$18.33
$12.77
13
1,116,838
$16.50
$12.77
14
1,027,491
$14.85
$12.77
15
945,291
$13.37
$12.77
16
869,668
$12.03
$12.77
$32,465,025 $26,881,041 $22,257,502 $18,429,211 $15,259,387 $12,634,773 $10,461,592
$18,320,139 $16,854,528 $15,506,166 $14,265,672 $13,124,419 $12,074,465 $11,108,508
$14,144,886 $10,026,513 $6,751,336 $4,163,539 $2,134,969
$560,307
($646,916)