Question

Tazer management now has concluded that the company cannot devote enough money to research and development to undertake all of these projects. Only $1.5 billion is available, which may be not enough for all the projects. The first row of table shows the amount needed (in millions of dollars) for each of these projects. The second row estimates each project’s probability of being successful. If a project is successful, it is estimated that the resulting drug would generate the revenue shown in the third row. Thus, the expected revenue (in the statistical sense) from a potential drug is the product of its numbers in the second and third rows, whereas its expected profit is this expected revenue minus the investment given in the first row. These expected profits are shown in the bottom row of table. The objective is to choose the projects that will maximize the expected profit while satisfying the budget constraint.

a. Formulate algebraic form for this problem. (Please clearly define all the decision variables, clearly write down the objective function and each constraints)

b. Formulate and solve this model on a spreadsheet.

TABLE Data for the Tazer Project Selection Problem

	Project
	1 (Up)	2 (Stable)	3 (Choice)	4 (Hope)	5 (Release)
R&D investment ($million)*	450	300	620	510	200
Success rate	60%	30%	30%	20%	45%
Revenue if successful ($million)	1,400	1,200	2,200	3,000	600

Answer 1

Expected Revenue and expected profit are computed as under:

	Project
	1	2	3	4	5
R&D Investment ($ million)	450	300	620	510	200
Success rate	60%	30%	30%	20%	45%
Revenue if successful ($ million)	1400	1200	2200	3000	600
Expected revenue	840	360	660	600	270
Expected profit	390	60	40	90	70

a) Algebraic model is as follows:

Decision variables:

Let Xi=1, if project i is undertaken, otherwise Xi=0

Objective function:

Max 390X1+60X2+40X3+90X4+70X5

Constraints:

450X1+300X2+620X3+510X4+200X5 <= 1500  (total funds available for investment = $ 1.5 billion = $ 1500 million)

Xi ={0,1} (binary)

---------------------------------------------------------------------------

b) Spreadsheet model is as follows:

Enter Solver Parameters as follows:

Click Solve to generate the solution.

After that, values appear automatically in yellow cells

Click OK

----------------------------------------------------------

Result:

X1=1

X2=1

X3=0

X4=1

X5=1

Therefore, projects 1,2,4,5 should be undertaken.

Total expected profit = $ 610 million