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 Day Date Weekday Daily Demand Day 1 4/25/2016 Mon 297 2 4/26/2016 Tue 293 3 4/27/2016 Wed 327 4 4/28/2016 Thu 315 5 4/29/2016 Fri 348 6 4/30/2016 Sat 447 7 5/1/2016 Sun 431 8 5/2/2016 Mon 283 9 5/3/2016 Tue 326 10 5/4/2016 Wed 317 11 5/5/2016 Thu 345 12 5/6/2016 Fri 355 13 5/7/2016 Sat 428 14 5/8/2016 Sun 454 15 5/9/2016 Mon 305 16 5/10/2016 Tue 310 17 5/11/2016 Wed 350 18 5/12/2016 Thu 308 19 5/13/2016 Fri 366 20 5/14/2016 Sat 460 21 5/15/2016 Sun 427 22 5/16/2016 Mon 291 23 5/17/2016 Tue 325 24 5/18/2016 Wed 354 25 5/19/2016 Thu 322 26 5/20/2016 Fri 405 27 5/21/2016 Sat 442 28 5/22/2016 Sun 454 29 5/23/2016 Mon 318 30 5/24/2016 Tue 298 31 5/25/2016 Wed 355 32 5/26/2016 Thu 355 33 5/27/2016 Fri 374 34 5/28/2016 Sat 447 35 5/29/2016 Sun 463 36 5/30/2016 Mon 291 37 5/31/2016 Tue 319 38 6/1/2016 Wed 333 39 6/2/2016 Thu 339 40 6/3/2016 Fri 416 41 6/4/2016 Sat 475 42 6/5/2016 Sun 459 43 6/6/2016 Mon 319 44 6/7/2016 Tue 326 45 6/8/2016 Wed 356 46 6/9/2016 Thu 340 47 6/10/2016 Fri 395 Mon 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 Tue 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 Wed 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 Thu 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 6/11/2016 Sat 6/12/2016 Sun 6/13/2016 Mon 6/14/2016 Tue 6/15/2016 Wed 6/16/2016 Thu 6/17/2016 Fri 6/18/2016 Sat 6/19/2016 Sun 6/20/2016 Mon 6/21/2016 Tue 6/22/2016 Wed 6/23/2016 Thu 6/24/2016 Fri 6/25/2016 Sat 6/26/2016 Sun 6/27/2016 Mon 6/28/2016 Tue 6/29/2016 Wed 6/30/2016 Thu 7/1/2016 Fri 7/2/2016 Sat 7/3/2016 Sun 465 453 307 324 350 348 384 474 485 311 341 357 363 390 490 492 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 Fri Sat 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 Decision variables Process 1 - X1 Process 2 - X2 Process 3 - X3 Cost of production per batch # batches produced Data for constraints Production requirements and output Ingredient 1 required per batch 2 Ingredient required per batch Orchid Relief yielded per batch Process 1 Process 2 Process 3 180 120 540 Supply of ingredient 1 60 420 120 Supply of ingredient 2 120 300 60 Units of Orchid Relief produced Unit Cost Data for the objective function Process 1 $14,000 Process 2 Process 3 $30,000 $11,000 Objective function Cost of production Constraints Left hand side of constraints Right hand side of constraints <= 4500 <= 3600 >= Calculations Pawel Kalczynski: Enter the total (sum of) forecasted demand for days 64-70