Problem 4 (15 points) A pharmaceutical company sells four drugs with an annual r

Question

Problem 4 (15 points) A pharmaceutical company sells four drugs with an annual revenue generated from each drug as a function of the number of salespeople assigned to the drug. It costs the company $50,000 per year per salesperson. The revenue per drug is given in the table below where Si is the number of salespeople assigned to drug i and the revenue is expressed in thousands of dollars. The hiring budget is limited to $1.9 million. Drug-1Revenue = 200S1^{.5} ,Drug-2 Revenue=150S2^{.75} , Drug-3 Revenue= 180S3^{.6}, Drug-4 Revenue = 300S4^{.3}

a) Formulate a mathematical model to maximize the company’s profit. Clearly define your decision variables, objective function and constraints. b) Implement your model in Excel and use Solver to find the optimal number of salespeople. Include a snapshot of your solution in the report.

Explanation / Answer

As mentioned in the question, let S1, S2, S3, and S4 are the salespersons allocated to Drug1, Drug2, Drug3, and Drug4 respectively. These are decision variables and needs to be non-negative integers, representing number of salespersons.

Objective is to have Maximum Total Revenue, sum of revenues from all the four drugs.

Constraint is about hiring budget given as $1.9 million.

In other words 50000*(S1+S2+S3+S4) <= 1900000

Formulation for excel solver is as follows:

maximize cell having value = 200S1.5 + 150S2.75 + 180S3.6 + 300S4.3

Changing cells are value cells for S1, S2, S3, and S4

Constraint is mentioned above as 50000S1+50000S2+50000S3+50000S4 <= 1900000

Drug1 Drug2 Drug3 Drug4 Decision Variables S1 S2 S3 S4 Values of DV 0 0 0 0 power of DV 0.00 0.00 0.00 0.00 SUM Revenue-Objective 0.00 0.00 0.00 0.00 0.00 LHS Sign RHS Budget-Constraint 0 0 0 0 0 <= 1900000

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.