Question

A Pharmaceutical company produces three drugs: A, B, and C. It can sell up to 500 kg of each drug at the following prices (per kg):

Drug Sales price

A $10

B $15

C $25

The company can purchase the raw material at $7 per kg. Each kg of raw material can be used to produce either one kg of Drug A or one kg of Drug B. Assume cost of these operations is negligible. For a cost of $4 per kg processed, Drug A can be converted to 0.7 kg of Drug B and 0.3 kg of Drug C. For a cost of $5 per kg processed, Drug B can be converted to 0.9 kg of Drug C. Formulate this problem as a spreadsheet model and use Solver to determine the number of kgs of the raw material to purchase to make Drug A and Drug B, and the number of kgs of Drugs A and B to further process in order to maximize profit from selling the drugs subject to producing more than using each drug and max sales constraints.

Hint: Each operation in this problem has one input and one or more outputs, whereas each operation in the Production Process problem in Session 10 had one output but 1 or more inputs.   So in this problem, instead of "Production of 1 unit of" on the top, put the "Usage of 1 unit of" on the top, and put kgs of each drug to be produced on the left.   Consider the raw material to make Drug A different from that to make Drug B (call them RM1 and RM2). Instead of labour, there is cost.

Answer 1

Suppose X kg raw material is used to make drug A

Y  kg raw material is used to make drug B

P kg of Drug A used to make Drug B and C (P<=X)

Q kg of drug B Used to Make Drug C ( Q <= Y+0.7P)

Total cost = 7X+7Y+4P+5Q

Total sale price 10 (X-P)+15(Y+0.7P-Q)+25(0.3P+0.9Q)

= 10X-10P+15Y+10.5P-15Q+7.5P+22.5Q

= 10X+8P+15Y+7.5Q

Total Profit = 10X+15Y+8P+7.5Q-7X-7Y-4P-5Q

= 3X+8Y+4P+2.5Q

so Objective function will be Max Z = 3X+8Y+4P+2.5Q

Subject to Constraints

X-P<=500

Y+0.7P-Q<=500

0.3P+0.9Q<=500

X,Y,P,Q >=0

BY PUTTING ABOVE INFORMATION IN EXCEL SOLVER IN FOLLOWING WAY

AND INSERTING INFORMATION IN EXCEL SOLVER IN FOLLOWING WAY

AND AFTER CLICKING ON SOLVE BUTTEN , WE GOT FOLLOWING SHEET

Conclusion:

For Profit Maximizing we Have to Produce 500 kg of Drug A and 1055 kg of drug B . and from 555 kg drug B we have to produce 499.5 kg drug C .

The maximum Profit will be 11327 kg

Note : Drug A, B quantity P ,Q are set to be ineger .