Problem 4. Simulation and Stochastic Optimization (10 points, Excel Solver is needed)

You are the Chief Data Analytic Officer of a large pharmaceutical company. Your company currently has two drugs A and B under development for treading two different diseases, with 80% and 85% efficacy respectively on each patient based on your clinical research so far. To get approval from FDA so you can market a drug, you need to show effectiveness on at least 800 out of the 1,000 patents tested in the final clinical trial (Hint: think of Binomial distribution with 1000 trials). Your team has collected the following information on these two drugs:

	Drug A	Drug B
R&D budget (x) ($ million)	3	6
Efficacy rate ( R)	80%	85%
Marketing promotion budget (y) ($ million)	7	9
Market size (# of patients in million)	40	20
Market share (S) mean*	8%	10%
Market share - standard deviation	2%	3%
Drug price per patient ($)	12	10

* Market share achieved is uncertain but can be described by a normal distribution with the given mean and standard deviation. The market share given in the table above will be reduced if there is a competitor in the market. The reduction is 20% on average but it is distributed uniformly between 18% and 22%. Your team estimates that there is a 30% chance that your company will have a competitor in each of the two markets (Hint: think of Bernoulli distribution on if you have a competitor or not).

4.1. Simulation (5 points)

4.1.1 Build a Monte Carlo simulation model to estimate the total profit that your company can be expected to achieve for the two drugs (3 points) Note: If you dont get FDA approval, you will still incur the R&D cost but not marketing cost.

4.1.2 What is the total expected profit (or loss)? (1 point)

4.1.3 What is the probability that your company will make a profit? (1 point)

4.2. Stochastic Optimization (5 points)

Now assume that the drug efficacy rate (R) can be influenced by R&D spending (x, stated in million) with the following relationship

R=1-e- ax

where for Product A, a = 0.5365 and for Product B, a = 0.3162.

4.2.1 On a different sheet from the above (4.1), setup a stochastic optimization model to find the allocation of the R&D budget among the two products that achieves the maximum expected total profit. The $9 million total R&D budget needs to be satisfied 90% of the time (3 points)

4.2.2 What is optimal allocation of the R&D budget of $9 M to each of the product and what is the expected total profit? (1 point)

4.2.3 What is the probability that your company will make a profit in this case? (1 point)