How do I solve P3? The second picture is the P2 information needed to solve P3

Here is P2 information, which is solved P2 picture 1

picture 2

X Calibri General Insert : Delete 27 7 - 11 AA === EA Ideas Paste BIU, $ % 899 CH 4 Conditional Format as Cell Formatting Table Styles Styles Format Sort & Find & Filter Select Editing Ideas Cells 19 Number Clipboard Sensitivity Alignment Font N20 fx o L K M 1 F E H N G B A D E 1 2 3 4 5 6 7 20% Consider a possible shortage of ingredients in the following week. What would the optimized production process look like if Eli Orchid could only procure 4320 pounds of ingredient 1 and 1440 pounds of ingredient 2? 8 Follow these steps: 1. In the appropriate cells below type in the numbers obtained from the original optimal solution (no shortage) produced in P2. 2. In the Constraints table label each constraint in the original solution as binding or not-binding (select from the drop-down list). 3. Go back to P2, introduce the appropriate modifications that reflect the shortage situation desctibed above and re-run the analysis. 4. Write down the new numbers (resulting from the shortage situation) in the appropriate cells below (Shortage column). 5. In the Constraints table (Shortage column) label each constraint in the new solution as binding or not-binding (select from the drop-down list). 9 10 11 12 13 P2 Shortage Shortage 14 15 16 Number of batches - Process 1 Number of batches - Process 2 Number of batches - Process 3 Constraints P2 Supply of ingr. 1 Supply of ingr. 2 Units produced P2 Shortage 18 19 20 21 Cost of production Unit cost ReadMeFirst P1 P2 P3 P4 13! Ready 40 7:09 PM 12/4/2020 O Type here to search MA 0 Cells Ideas Number Sensit Editing Styles Font Clipboard Alignment K17 fi M N H E D F G A 1 2 3 4 4 5 30% 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. Therefore, a batch is an integer). 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. 6 7 8 9 Please use the above description and the model below to help Eli Orchid 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 Reliet in units must be greater than or equal to the total forecasted demand (in units) for week 10 (see P1). 10 11 12 Specifically 1. In cell K20 calculate the forecasted total demand for days 64 70 (based on P1). 2. Build the appropriate formulas in all other calculation cells (grey). 3. Set up Excel Solver and run it to provide the solution to the optimization problem. Note: See the ReadMeFirst tab for Solver instructions. 13 14 15 16 Process 3 $11,000 540 1920 - 3300 - Constraints Used Supply of ingredient 1 (pounds) Supply of ingredient 2 (pounds) Available 4500 3500 Process 1 S14,000 180 60 120 Process 2 $30,000 120 420 300 18 19 20 21 22 23 Cost of production per batch Ingredient 1 required per batch (pounds) Ingredient Z required per batch (pounds) Orchid Relief yielded per batch (units) 120 60 Produced Units of Orchid Relief Forecasted 2760.58333 2820 ReadMeFirst P1 P4 P2 P3 Ready O 2 o Type here to search B D F G M N Specifically: 1. In cell K20 calculate the forecasted total demand for days 64 70 (based on P1). 2. Build the appropriate formulas in all other calculation cells (grey). 3. Set up Excel Solver and run it to provide the solution to the optimization problem. Note: See the ReadMeFirst tab for Solver instructions. Process 2 $30,000 Available Process 3 $11,000 540 4500 Constraints Used Supply of ingredient 1 (pounds) Supply of ingredient 2 (pounds) Process 1 $14,000 180 60 120 Cost of production per batch Ingredient 1 required per batch (pounds) Ingredient 2 required per batch (pounds) Orchid Relief yielded per batch (units) 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 1920 = 3300