Question

For this problem, write down the IP model in a Word document, and solve the model in an Excel file. Submit both the Word and Excel files. The Word document should include the following contents:

• The IP model (decision variables, objective function, and constraints) with necessary explanation – clear explanation of the model would help the grader justify your model, and maximize your points earned even if your model is not completely correct.

• The optimal solution obtained in Excel.

• Two screenshots: one for your Excel model setup, and the other for the “Solver Parameters” dialog.

1. (20 points) A pharmaceutical company wants to select the right mix of drugs to maximize the expected revenue without exceeding its R&D resources. Drugs considered for development are listed in the following table.  

Drug	Purpose	Expected Revenue (in $ millions)
Drug 1	Liver Cancer Treatment	450
Drug 2	Antibiotic for HIV patients	700
Drug 3	Colon Cancer	1,500
Drug 4	Cholesterol Reduction	3,500
Drug 5	Central Nervous System Treatment	4,000

The primary resource constraints are human resource constraints based on knowledge and skill levels of experts.

Sr. Sci. hrs	Jr. Sci. hrs	Lab Tech hrs	Sr. Cli Sci, hrs	Jr. Cli Sci, hrs	Stat, hrs
40,000	80,000	80,000	40,000	80,000	20,000

The human resource requirement for each drug development is shown as follows.

	Liver Cancer	HIV	Colon Cancer	Cholesterol	CNS
Sr. Sci, hrs	11,500	11,500	11,500	11,500	11,500
Jr. Sci, hrs	18,500	21,000	23,000	25,000	25,000
Lab Tech, hrs	9,000	11,500	11,500	11,500	13,500
Sr. Cli Sci, hrs	5,500	6,500	8,500	9,500	9,500
Jr. Cli Sci, hrs	9,500	12,000	14,000	17,000	17,000
Stat, hrs	4,500	5,500	6,500	6,500	7,000

Formulate and solve an integer programming model to select the drug candidates.

Answer 1

Let
DE
if the drug i is selected to be manufactured, and
D0
if it is not, where i=1,2,3,4,5

These are the decision variables

The expected revenue for the mix of drugs selected is

450D1700 D2 1500D33500D44000D

The company wants to maximize the expected revenue and hence the above is the objective function

Now the constraints

Human resource constraints

• Sr. Sci, hrs available is 40,000. The total hours spent by Sr. Sci cannot be more than this

11500D111500D211500 D311500D4+ 11500D5< 40000)

• Jr. Sci, hrs available is 80,000. The total hours spent by Jr. Sci cannot be more than this

18500D121000 D2230000D3 25000D4+ 25000 D5< 80000

• Lab Tech, hrs available is 80,000. The total hours spent by Lab Tech cannot be more than this

9000 D111500D211500D311500D413500D< 80000

• Sr. Cli Sci, hrs available is 40,000. The total hours spent by Sr. Cli Sci cannot be more than this

5500 D16500D28500D3 9500D49500 D540000

• Jr. Cli Sci, hrs available is 80,000. The total hours spent by Jr. Cli Sci cannot be more than this

9500 D112000D214000D3 +17000D4 +17000D580000

• Stat, hrs available is 20,000. The total hours spent by Stat cannot be more than this

4500 D5500D26500D3 6500D47000D520000

The IP is

Maximize

450D1700 D2 1500D33500D44000D

s.t.

• 
  11500D111500D211500 D311500D4+ 11500D5< 40000)
  
• 
  18500D121000 D2230000D3 25000D4+ 25000 D5< 80000
  
• 
  9000 D111500D211500D311500D413500D< 80000
  
• 
  5500 D16500D28500D3 9500D49500 D540000
  
• 
  9500 D112000D214000D3 +17000D4 +17000D580000
  
• 
  4500 D5500D26500D3 6500D47000D520000
  
• 
  Di 0, 1, i= 1,2,3, 4, 5
  

Prepare the following sheet

B F G K D 1 Revenue Hours Required Expecte Available Hrs Cholesterol CNS Liver Cancer HIV Colon Cancer Drug Purpose d 2 Revenue 11500 11500 21000 23000 11500 11500 6500 8500 12000 14000 5500 6500 11500 40000 25000 80000 13500 80000 9500 Drug Drug 2 Drug 3 Drug 4 Drug Liver Cancer Treatment 450 11500 3 Sr. Sci, hrs 11500 Antibiotic for HIV patients 700 r. Sci, hrs 18500 25000 Lab Tech, hrs 9000 Sr. Cli Sci, hrs 5500 Jr. Cli Sci, hrs 9500 4500 Colon Cancer 1500 5 11500 40000 17000 80000 7000 20000 Cholesterol Reduction 3500 6 9500 Central Nervous System Treatment 4000 7 17000 6500 Stat, hrs 8 9 Drug 4 Drug 5 Drug 1 Drug 2 Drug 3 10 Drugs selected 11 12 13 Maximize 14 Expected Revenue -C3*B11+C4*C11+C5*D11+C6* E11+C7*F11 15 16 s.t. constraints 17 Actual Hrs spent Avaiable Hours F3 $B$11+G3*$C$11+H3*$D$1 1+13*$E$11+J3*$F$11 18 = K3 Sr. Sci, hrs <= F4*$B$11+G4*$C$11+H4*$D$11+14*$E$11+J4*$F$11 19 r. Sci, hrs K4 F5*$B$11+G5*$C$11+H5*$D$11+15*$E$11+J5*$F$11 20 Lab Tech, hrs K5 =F6*$B$11+G6*$C$11+H6"$D$11+16* $E$11+J6* $F$11 21 -K6 Sr. Cli Sci, hrs F7*$B$11+G7*$C$11+H7"$D$11+17*$E$11+J7*$F$11 Jr. Cli Sci, hrs 22 - K7 <= F8 $B$11+G8*$C$11+H8*$D$11+18*$E$11+J8*$F$11 23 - K8 Stat, hrs

Get this

A B D G H K 1 Revenue Hours Required Expected Revenue (in Colon Choles Available Hrs Liver Drug Purpose HIV CNS Cancer Cancer terol 2 millions) 11,500 11,500 11,500 11,500 11,500 18,500 21,000 23.000 25.000 25.000 Lab Tech, hrs 9,000 11,500 11,500 11,500 13,500 Sr. Cli Sci, hrs 5,500 6,500 8,5009,500 9,500 Jr. Cli Sci, hrs 9,500 12,000 14,000 17,000 17,000 4,500 5,500 6,5006,500 7,000 Liver Cancer Treatment Sr. Sci, hrs 40,000 Drug 1 450 Antibiotic for HIV patients 4 Drug 2 700 Jr. Sci, hrs 80,000 Colon Cancer 5 Drug 3 1,500 80,000 Drug 4 Drug 5 6 Cholesterol Reduction 3,500 40,000 Central Nervous System Treatment 4,000 7 80,000 Stat, hrs 8 20,000 10 Drugs selected Drug Drug 2 Drug 3 Drug 4 Drug 1 11 12 13 Maximize 14 Expected Revenue 0 15 16 s.t. constraints 17 Actual Hrs spent Avaiable Hours 18 40,000 Sr. Sci, hrs 0 <= 0 <= 19 80,000 Jr. Sci, hrs 0 <= 20 80,000 Lab Tech, hrs 0 <= 21 40,000 Sr. Cli Sci, hrs 0 <= 22 80,000 r. Cli Sci, hrs 0 <= 23 20,000 Stat, hrs 24

Setup the solver using data--->solver

A D E Solver Parameters 1 Revenue Еxpected Drug Purpose et Objective: SB$14 S millions) 2 O value Of To: O Min O Max Liver Cancer Treatment 450 Sr. Sci, hrs 3 Drug Drug 2 Antibiotic for HIV patients Jr. Sci, hrs 4. 700 By Changing Variable Cells Drue 3 Colon Cancer 1.500 Lab Tech, hr $B$11:$FS11 Cholesterol Reduction 3,500 Sr. Cli Sci, h 6 Drug 4 Central Nervous System Treatment 4,000 Jr. Cli Sei, hr 7 Drug 5 Subject to the Constraints: Stat, hrs 8 Add SR$18:58523 <= SD$18:SD$23 Drug 3 Drug 4 10 Drugs selected Drug 1 Drug 2 Change Delete 12 13 Maximize Reset All 14 Expected Revenue 15 Load/Save 16 s.t. constraints Make Unconstrained Variables Non-Negative Actual Hrs spent Avaiable Hours 17 simplex LP Select a Solving Method: 18 0 <= 40,000 Sr. Sci, hrs Options 19 0 <= r. Sci, hrs 80.000 0 <= 20 80,000 Lab Tech, hrs Solving Method 0 <= 21 40.000 Sr, Cli Sci, hrs Select the o .l h e Sud engine Smplex 22 0 < 80.000 Jr. Cli Sci, hrs non-smogth. 0 < 23 20,000 Stat, hrs 24 Help 25 Solve Close 26

get this

A C D E F G H K Hours Required 1 Revenue Expected Revenue (in Colon Choles Cancer terol Available Hrs Liver Drug Purpose HIV CNS Cancer Smillions 2 11,500 11,500 11,500 11,500 11,500 18,500 21,000 23,000 25,000 25,000 Lab Tech, hrs 9,000 11,500 11,500 11,500 13,500 6,500 8,500 9,500 9,500 12,000 14,000 17,000 17,000 Sr. Sci, hrs Drug 1 Drug 2 Drug 3 Drug 4 Drug 5 Liver Cancer Treatment 450 3 40,000 Jr.Sci, hrs Antibiotic for HIV patients 4 700 80,000 Colon Cancer 80,000 5 1,500 Sr. Cli Sci, hrs 5,500 Jr. Cli Sci, hrs 9,500 6 Cholesterol Reduction 3,500 40,000 Central Nervous System Treatment 4,000 7 80,000 Stat, hrs 4,500 5,500 6,500 6,500 7,000 20.000 Drug 5 10 Drugs selected Drug 2 Drug 3 Drug 4 Drug 1 11 0 0 1 1 1 12 13 Maximize 14 Expected Revenue 9000 15 16 s.t. constraints 17 Actual Hrs spent Avaiable Hours 18 40,000 Sr. Sci, hrs 34500 19 73000 <= 80,000 Jr. Sci, hrs 20 36500 = 80,000 Lab Tech, hrs 21 27500 40,000 Sr. Cli Sci, hrs 22 80,000 Jr. Cli Sci, hrs 48000 <= 23 20000 = 20,000 Stat, hrs

Ans: To maximize the expected revenue, the company has to select drugs, 3,4, and 5 for development. The maximum expected revenue is $9000 millions