ISDS 361 B 361b Eli Orchid has designed a new pharmaceutical product

Project
Eli Orchid has designed a new pharmaceutical product, Orchid Relief, which improves the night sleep.
Before initiating mass production of the product, Eli Orchid has been market-testing Orchid Relief in
Orange County over the past 9 weeks.
Now that the daily demand for Orange County can be predicted with reasonable accuracy using the M3
model, the COO of the company decided to use it to optimize the production of the new drug.
The daily demand values and production process data are recorded in the Excel file provided.
The new pharmaceutical product that the company wishes to introduce, Orchid Relief, uses two new
ingredients. At this stage, Eli Orchid can procure limited amounts of each ingredient. The company has
4500 pounds of ingredient 1 and 3600 pounds of ingredient 2 available for this week.
Eli Orchid can manufacture the new product using any of its three existing processes that have different
capabilities. The production with each of the processes is done in batches (a batch typically represents
one full run of a machine from when it starts a task until it finishes it). Each batch of production by each
of the processes uses different amounts of ingredients 1 and 2, and results in different number of units
of Orchid Relief produced (note the difference between a batch and units of Orchid Relief produced). The
table below outlines the cost per batch, amounts of the two ingredients required, and the number of
units of Orchid Relief yielded per batch.
Cost of production per batch
Ingredient 1 required per batch (pounds)
Ingredient 2 required per batch (pounds)
Orchid Relief yielded per batch (units)

Process 1
$14,000
180
60
120

Process 2
$30,000
120
420
300

Process 3
$11,000
540
120
60

Eli Orchid needs to determine how many batches to produce with each process in the least costly way
given the limited availability of the two ingredients. Also, the total production of Orchid Relief in units
must be greater than or equal to the total forecasted demand (in units) for the following week.

The COO of the company asked the analyst 1:

1 Round numbers to four decimal points (e.g. 0.1234), unless explicitly requested otherwise.
1

1. To use the new M3 model updated
with week 9 data (d = 0.6568*Day
-151.1703*Mon -136.2715*Tue
-110.595*Wed -118.3629*Thu
-74.7975*Fri + 1.7679*Sat + 434.5675)
to predict the total demand (in units)
for Week 10 (days 64-70).

M3
Mon.
Tue.
Wed.
Thu.
Fri.
Sat.
Sun.
TOTAL:

2. To state if this is a maximization or a
minimization optimization problem?
3. To provide the mathematical
formulation of the objective function
assuming that X1, X2, and X3 are the
decision variables representing the
number of batches of each process to
be used.
4. To provide the mathematical
formulation of the model constraints.

5. To use the “Production” tab of the
Excel file and complete the setup by:
- entering the forecasted total
demand in the pink cell
- entering formulas in the five
grey cells based on the
mathematical formulation
6. To set up Excel Solver (Assume
Constraint Precision of 0.000001 and
Integer Optimality (%) of 0) and
provide the solution to the
optimization problem.

Supply of ingr. 1
Supply of ingr. 2
Units produced
Non-negativity
Integer

X1, X2, X3 >= 0
X1, X2, X3 : Integer

Excel Formulas:
Cost of Production
Supply of Ingr. 1
Unit Cost

Number of batches
Process 1
Process 2
Process 3
Cost of production (obj.)
Unit cost ($###.##)

2

7. To label each constraint in the
solution as binding or not-binding.

Supply of ingr. 1
Supply of ingr. 2
Units produced

8. To consider a possible shortage of
ingredients in the following week.
What would the optimized production
process look like if Eli Orchard could
only procure 4320 pounds of
Ingredient 1 and 1440 pounds of
Ingredient 2?

Process 1
Process 2
Process 3

9. To label each constraint in the new
solution (for the shortage of
ingredients) as binding or not-binding.

Supply of ingr. 1
Supply of ingr. 2
Units produced

10. To make recommendations about
the production processes and pricing
of Orchid Relief.

Number of batches

Cost of production (obj.)
Unit cost ($###.##)

[write your paragraph here]

Note: this paragraph must fit on page
3. The entire project report (with the
original description) must fit on 3
pages.

3