Data The new pharmaceutical product that the company wishes

Data:

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.

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)

60

420

120

Orchid Relief yielded per batch (units)

120

300

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 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.

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 4320 pounds of Ingredient 1 and 1440 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.

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

[write your paragraph here]

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

Solution

1) Using M3 model

        Mon : Variables Day = 64 , Mon = 1

                   Hence Demand of Monday = 64*0.6568 - 151.1703*1 + 434.5675 = 325.4324

       Tue : Demand of Tuesday = 65*0.6568 - 136.2715*1 + 434.5675 = 340.988

       Wed : Demand of Wednesday = 66*0.6568 - 110.595*1 + 434.5675 = 367.3215

      Thur : Demand of Thursday = 67*0.6568 - 118.3629*1 + 434.5675 = 360.2102

      Fri : Demand of Friday = 68*0.6568 - 74.7975*1 + 434.5675 = 404.4324

     Sat : Demand of saturday = 69*0.6568 + 1.7679*1 + 434.5675 = 481.6546

     Sun : Demand of sunday = 70*0.6568 + 434,5675 = 480.5085

    Total demand for the week = 2860.5476

2) It is a minimizing problem since we have to minimize the cost of production of Eli Orchid.

3) Objective function : Minimize (14000X1 + 30000X2 + 11000X3)

4) Model constraints: Supply of ingredient1 : 180X1 + 120X2 +540X3 <= 4500

                                    Supply of ingredient2 : 60X1 + 420X2 + 120X3 <= 3600

                                    Units produced : 120X1 + 300X2 + 60X3 >= 2861

                                    Non negativity : X1,X2,X3 >=0

                                   Integers : X1, X2, X3