Eli Orchid has designed a new pharmaceutical product, Orchid Relief, which improves the night sleep. Before initiating

Fantastic news! We've Found the answer you've been seeking!

Question:

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. (Please see data below.)

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 4520 pounds of ingredient 1 and 3610 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.

	Process 1	Process 2	Process 3
Cost of production per batch	$14,000	$30,000	$11,000
Ingredient 1 required per batch (pounds)	180	120	540
Ingredient 2 required per batch (pounds)	62	410	120
Orchid Relief yielded per batch (units)	120	310	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. To use the new M3 model (M3 model is from PP3) updated with week 9 data 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.

Supply of ingr. 1
Supply of ingr. 2
Units produced
Non-negativity	X1, X2, X3 >= 0
Integer	X1, X2, X3 : Integer

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

Excel Formulas:

Cost of Production
Supply of Ingr. 1
Unit Cost

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.

	Number of batches
Process 1
Process 2
Process 3

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

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 4300 pounds of Ingredient 1 and 1400 pounds of Ingredient 2?

	Number of batches
Process 1
Process 2
Process 3

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

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.

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

Day	Date	Weekday	Daily Demand	Day	Mon	Tue	Wed	Thu	Fri	Sat
1	4/25/16	Mon	297	1	1	0	0	0	0	0
2	4/26/16	Tue	293	2	0	1	0	0	0	0
3	4/27/16	Wed	327	3	0	0	1	0	0	0
4	4/28/16	Thu	315	4	0	0	0	1	0	0
5	4/29/16	Fri	348	5	0	0	0	0	1	0
6	4/30/16	Sat	447	6	0	0	0	0	0	1
7	5/1/16	Sun	431	7	0	0	0	0	0	0
8	5/2/16	Mon	283	8	1	0	0	0	0	0
9	5/3/16	Tue	326	9	0	1	0	0	0	0
10	5/4/16	Wed	317	10	0	0	1	0	0	0
11	5/5/16	Thu	345	11	0	0	0	1	0	0
12	5/6/16	Fri	355	12	0	0	0	0	1	0
13	5/7/16	Sat	428	13	0	0	0	0	0	1
14	5/8/16	Sun	454	14	0	0	0	0	0	0
15	5/9/16	Mon	305	15	1	0	0	0	0	0
16	5/10/16	Tue	310	16	0	1	0	0	0	0
17	5/11/16	Wed	350	17	0	0	1	0	0	0
18	5/12/16	Thu	308	18	0	0	0	1	0	0
19	5/13/16	Fri	366	19	0	0	0	0	1	0
20	5/14/16	Sat	460	20	0	0	0	0	0	1
21	5/15/16	Sun	427	21	0	0	0	0	0	0
22	5/16/16	Mon	291	22	1	0	0	0	0	0
23	5/17/16	Tue	325	23	0	1	0	0	0	0
24	5/18/16	Wed	354	24	0	0	1	0	0	0
25	5/19/16	Thu	322	25	0	0	0	1	0	0
26	5/20/16	Fri	405	26	0	0	0	0	1	0
27	5/21/16	Sat	442	27	0	0	0	0	0	1
28	5/22/16	Sun	454	28	0	0	0	0	0	0
29	5/23/16	Mon	318	29	1	0	0	0	0	0
30	5/24/16	Tue	298	30	0	1	0	0	0	0
31	5/25/16	Wed	355	31	0	0	1	0	0	0
32	5/26/16	Thu	355	32	0	0	0	1	0	0
33	5/27/16	Fri	374	33	0	0	0	0	1	0
34	5/28/16	Sat	447	34	0	0	0	0	0	1
35	5/29/16	Sun	463	35	0	0	0	0	0	0
36	5/30/16	Mon	291	36	1	0	0	0	0	0
37	5/31/16	Tue	319	37	0	1	0	0	0	0
38	6/1/16	Wed	333	38	0	0	1	0	0	0
39	6/2/16	Thu	339	39	0	0	0	1	0	0
40	6/3/16	Fri	416	40	0	0	0	0	1	0
41	6/4/16	Sat	475	41	0	0	0	0	0	1
42	6/5/16	Sun	459	42	0	0	0	0	0	0
43	6/6/16	Mon	319	43	1	0	0	0	0	0
44	6/7/16	Tue	326	44	0	1	0	0	0	0
45	6/8/16	Wed	356	45	0	0	1	0	0	0
46	6/9/16	Thu	340	46	0	0	0	1	0	0
47	6/10/16	Fri	395	47	0	0	0	0	1	0
48	6/11/16	Sat	465	48	0	0	0	0	0	1
49	6/12/16	Sun	453	49	0	0	0	0	0	0
50	6/13/16	Mon	307	50	1	0	0	0	0	0
51	6/14/16	Tue	324	51	0	1	0	0	0	0
52	6/15/16	Wed	350	52	0	0	1	0	0	0
53	6/16/16	Thu	348	53	0	0	0	1	0	0
54	6/17/16	Fri	384	54	0	0	0	0	1	0
55	6/18/16	Sat	474	55	0	0	0	0	0	1
56	6/19/16	Sun	485	56	0	0	0	0	0	0
57	6/20/16	Mon	311	57	1	0	0	0	0	0
58	6/21/16	Tue	341	58	0	1	0	0	0	0
59	6/22/16	Wed	357	59	0	0	1	0	0	0
60	6/23/16	Thu	363	60	0	0	0	1	0	0
61	6/24/16	Fri	390	61	0	0	0	0	1	0
62	6/25/16	Sat	490	62	0	0	0	0	0	1
63	6/26/16	Sun	492	63	0	0	0	0	0	0
64	6/27/16	Mon		64	1	0	0	0	0	0
65	6/28/16	Tue		65	0	1	0	0	0	0
66	6/29/16	Wed		66	0	0	1	0	0	0
67	6/30/16	Thu		67	0	0	0	1	0	0
68	7/1/16	Fri		68	0	0	0	0	1	0
69	7/2/16	Sat		69	0	0	0	0	0	1
70	7/3/16	Sun		70	0	0	0	0	0	0