2. Crystal ball training. The Vision Research.

The Vision Research spreadsheet models a business situation filled with uncertainty. Vision

Research has completed preliminary development of a new drug, code-named ClearView, that

corrects nearsightedness. This revolutionary new product could be completely developed and

tested in time for release next year if the FDA approves the product. Although the drug works

well for some patients, the overall success rate is marginal, and Vision Research is uncertain

whether the FDA will approve the product.

You begin your analysis by defining assumption cells to support this scenario.

Testing Costs

The uniform distribution describes a situation where all values between the minimum and

maximum values are equally likely to occur, so this distribution best describes the cost of testing Clear View.

Marketing Costs

Vision Research chooses the triangular distribution to describe marketing costs because the

triangular distribution describes a situation where you can estimate the minimum, maximum, and most likely values to occur.

Patients Cured

Vision Research chooses the binomial distribution to describe the uncertainties in this situation because the binomial distribution describes the random number of successes (25) in a fixed number of trials (100)

Growth rate

Since the uncertainties in this situation require a unique approach, Vision Research chooses Crystal Ball’s custom distribution to define the growth rate.

You know that you will be working with two distribution ranges: one showing growth in nearsightedness and one showing the effects of competition. Both ranges are continuous.

Enter the first range of values to show the growth of nearsightedness with low probability of competitive effects:

a. Type 0% in the Minimum field.

This represents a 0% increase in the potential market.

b. Type 5% in the Maximum field.

This represents a 5% increase in the potential market.

c. Type 75% or .75 in the Probability field.

This represents the 75% chance that Vision Research’s competitor will not enter the market

and reduce Vision Research’s share.

Now, enter a second range of values to show the effect of competition:

a. Type -15% in the Minimum field.

This represents a 15% decrease in the potential market.

b. Type -5% in the Maximum field.

This represents a 5% decrease in the potential market.

c. Type 25% in the Probability field.

This represents the 25% chance that Vision Research’s competitor will enter the market place and decrease Vision Research’s share by 5% to 15%.

The first "square" represents the 25% that our competitor gets in the market. Therefore the market growth would be between -15% and -5%. The another "square" represents the 75% chances that the market grows between 0 and 5%.

Market penetration

“Normally distributed” means that Vision Research expects to see the familiar bell-shaped

curve with about 68% of all possible values for market penetration falling between one standard

deviation below the mean value and one standard deviation above the mean value, or between

6% and 10%.

Type 5% in the left, minimum truncation field. These field share then called the truncation minimum and maximum. In this manner the distribution would be between 5% - infinite.

Forecast

Net profit forecast

If the FDA approves the drug (L9 is true), then calculate net profit by subtracting total costs

(L5) from gross profit (L19). However, if the FDA does not approve the drug, (L9 is false),

then calculate net profit by deducting both development costs (L2) and testing costs (L2)

incurred to date.

Finally we get that there are 79.21% chance to get a positive Net profit, and the Mean is $ 9.833.312.09.