Eli Orchid has designed a new pharmaceutical product, Orchid Relief, which uses two new ingredients. Eli Orchid can procure limited amounts of each ingredient for each month, that is, the company has 75 tons of ingredient 1 and 60 tons of ingredient 2 available each month.

Eli Orchid can manufacture the new product using any of its three existing processes. The processes are different in their cost per batch of Orchid Relief produced, use different amounts of each ingredient, and yield different amounts of Orchid Relief. Details of the three processes are summarized in the following table.

	Process 1	Process 2	Process 3
Cost of production per batch	$14,000	$19,000	$11,000
Ingredient 1 required per batch (tons)	3	2	7
Ingredient 2 required per batch (tons)	1	7	2
Orchid Relief yielded per batch (tons)	2	5	1

Eli Orchid needs to find how many batches to produce with each process. It is interested to do this in the least costly way that produces at least 50 tons of Orchid Relief per month, given the limited availability of the two ingredients.

Verbally state the objective function and all the constraints, define the decision variables and provide a mathematical formulation of the optimization problem. Create an excel spreadsheet that represents your mathematical formulation. Use Excel Solver to find the optimal solution and obtain the sensitivity report. Make sure that you provide proper labeling of objective function, decision variables, and constraints so that the Excel sensitivity report is easier to read.

Then answer the following questions:

1. How many decision variables do we have in this problem?

2. Except the non-negativity constraints, how many constraints the problem has?

3. What is the optimal objective?

4. How much of ingredient 1 is optimally used?

5. What is the cost of production of each ton of Orchid Relief?

6. Currently at least 50 tons of Orchid Relief is produced. If Eli Orchid requires one more ton of output to be produced, how much more would it cost? (this is also commonly called the marginal cost of each additional ton of Orchid Relief produced not necessarily the same as the answer to the last question)

7.

We would like to produce 80 tons of Orchid Relief (instead of 50). The impact on optimal cost can be found using the sensitivity report only.

Select one:

True

False

8.

How much more would it cost to produce 80 tons of Orchid Relief?

Select one:

a) 0

b) It is not possible to produce this much

c) 263,333

d) 8778

9. Up to how much should Eli Orchid be willing to pay more to obtain an amount of 10 more tons of ingredient 1? (Find the answer using sensitivity report only. If the answer cannot be determined using sensitivity report, enter -1000)

10. Up to how much should Eli Orchid be willing to pay more to obtain an amount of 10 more tons of ingredient 2? (Find the answer using sensitivity report only. If the answer cannot be determined using sensitivity report, enter -1000)

11. The management thinks that the pharmaceutical market is going to improve over the next month so that it can sell 5% more Orchid Relief and at the same time procure 3% more of ingredient 2. However, thinking that they might have too much ingredient 1, the plan is to cut on some of the ingredient 1 and reduce its supply by 5%. How much more does it cost to produce the higher required amount of Orchid Relief? (Find the answer using sensitivity report only. If the answer cannot be determined using sensitivity report, enter -1000)

12. By how much should the cost of the third process drop so that management is willing to use it to produce Eli Orchid? (Find the answer using sensitivity report only. If the answer cannot be determined using sensitivity report, enter -1000)

13. If the cost of process 2 increased to $36,000 per batch, how much would then be the cost of producing the 50 tons of Orchid Relief? (Find the answer using sensitivity report only. If the answer cannot be determined using sensitivity report, enter -1000)

14. A new maintenance strategy is going to come into place that increases the cost of each of the three processes by $6000. How much would then be the cost of producing the 50 tons of Orchid Relief? (Find the answer using sensitivity report only. If the answer cannot be determined using sensitivity report, enter -1000)

15. Going back to the original problem from question 3, Eli Orchid would like to make sure that at most 30% of all batches produced is produced using process 2. Formulate this limitation and implement in Excel. What would be the new total cost of production?