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.