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.
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. 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]
Oh boy, it looks like Eli Orchid has quite the conundrum on his hands with his new pharmaceutical product, Orchid Relief. But fear not, because I, Clown Bot, am here to help with some witty answers to his questions!
1. Ah, the M3 model, eh? Well, let's plug in those numbers and see what the total demand for Week 10 is:
- M3
Mon:
Tue:
Wed:
Thu:
Fri:
Sat:
Sun:
TOTAL:
2. Ahh, the age-old question of maximization or minimization. Well, in this case, it seems like we want to minimize the cost of production. So, it's a minimization optimization problem!
3. Time to put on our math hats! The objective function can be represented as:
Cost = 14000*X1 + 30000*X2 + 11000*X3
4. Constraints, constraints, they're everywhere! Here are the mathematical formulations for the model constraints:
- Supply of ingr. 1: 180*X1 + 120*X2 + 540*X3 <= 4500
- Supply of ingr. 2: 60*X1 + 420*X2 + 120*X3 <= 3600
- Units produced: 120*X1 + 300*X2 + 60*X3 >= Forecasted Total Demand
- Non-negativity: X1, X2, X3 >= 0
- Integer: X1, X2, X3 : Integer
5. Let's fill in those Excel cells, shall we? Remember to enter the forecasted total demand in the pink cell and use the formulas provided in the gray cells based on the mathematical formulations.
6. Excel Solver to the rescue! Set the constraint precision to 0.000001 and the integer optimality to 0. Solve the optimization problem and provide the solutions for the number of batches for each process, cost of production, and unit cost.
7. Bindings and constraints, oh my! Label each constraint in the solution as either binding or not-binding for:
- Supply of ingr. 1
- Supply of ingr. 2
- Units produced
8. Uh-oh, a shortage of ingredients? Let's see what the optimized production process would look like if Eli Orchard could only procure 4320 pounds of Ingredient 1 and 1440 pounds of Ingredient 2. Adjust the numbers and solve once again.
9. Bindings and constraints strike again! Label each constraint in the new solution (for the shortage of ingredients) as binding or not-binding for:
- Supply of ingr. 1
- Supply of ingr. 2
- Units produced
10. Finally, the moment you've all been waiting for, my recommendations about the production processes and pricing of Orchid Relief! Well, my dear reader, it's important to balance the cost of production with the availability of ingredients. Based on the optimized solutions, consider adjusting the production processes and pricing accordingly. And remember, laughter is the best medicine, so make sure Orchid Relief brings a smile to everyone's face!
1. To predict the total demand for Week 10 using the M3 model, you will need to input the values for each day of the week into the formula: d = 0.6568*Day - 151.1703*Mon - 136.2715*Tue - 110.595*Wed - 118.3629*Thu - 74.7975*Fri + 1.7679*Sat + 434.5675. Fill in the values for each day and calculate the total demand for Week 10.
2. To determine if this is a maximization or minimization optimization problem, you need to consider the goal. In this case, the objective is to minimize the cost of production. Therefore, it is a minimization optimization problem.
3. The mathematical formulation of the objective function can be written as follows:
Objective Function: Minimize Cost of Production = $14,000*X1 + $30,000*X2 + $11,000*X3
where X1, X2, and X3 are the decision variables representing the number of batches of each process to be used.
4. The mathematical formulation of the model constraints are as follows:
- Supply of ingr. 1: 180*X1 + 120*X2 + 540*X3 <= 4500
- Supply of ingr. 2: 60*X1 + 420*X2 + 120*X3 <= 3600
- Units produced: 120*X1 + 300*X2 + 60*X3 >= Total Demand for Week 10
- Non-negativity: X1, X2, X3 >= 0
- Integer: X1, X2, X3 are integers
5. To set up the Excel file, enter the forecasted total demand for Week 10 in the pink cell. Then, enter the formulas in the five grey cells based on the mathematical formulation provided.
6. To set up Excel Solver, go to the Solver parameters and set the Constraint Precision to 0.000001 and Integer Optimality (%) to 0. Then, click Solve to find the solution to the optimization problem. The solution will provide the number of batches for each process, the cost of production, and the unit cost.
7. Each constraint in the solution can be labeled as binding or not-binding based on whether it is satisfied at its limit. Check if the supply of ingredient 1 and ingredient 2 constraints are satisfied exactly, in that case, they are binding. If the units produced constraint is satisfied exactly, it is also binding.
8. If Eli Orchard could only procure 4320 pounds of Ingredient 1 and 1440 pounds of Ingredient 2, you need to update the supply of ingredient constraints as follows:
- Supply of ingr. 1: 180*X1 + 120*X2 + 540*X3 <= 4320
- Supply of ingr. 2: 60*X1 + 420*X2 + 120*X3 <= 1440
9. Similarly, for the new solution with ingredient shortage, you can label each constraint as binding or not-binding based on whether it is satisfied at its limit.
10. Based on the optimized production process and the results from the analysis, you can make recommendations about the production processes and pricing of Orchid Relief.
1. To predict the total demand for Week 10 using the M3 model, we plug in the appropriate values into the formula: d = 0.6568*Day -151.1703*Mon -136.2715*Tue -110.595*Wed -118.3629*Thu -74.7975*Fri + 1.7679*Sat + 434.5675. We substitute the values for each day of the week (days 64-70) and sum them up to get the total predicted demand for Week 10.
2. This is a minimization optimization problem because the objective is to minimize the cost of production.
3. The objective function can be formulated as follows:
Minimize: Cost = 14000*X1 + 30000*X2 + 11000*X3
where X1, X2, and X3 represent the number of batches of each process to be used.
4. The model constraints are as follows:
- Supply of ingredient 1: 180*X1 + 120*X2 + 540*X3 <= 4500
- Supply of ingredient 2: 60*X1 + 420*X2 + 120*X3 <= 3600
- Units produced: 120*X1 + 300*X2 + 60*X3 >= Total predicted demand for Week 10
- Non-negativity: X1, X2, and X3 >= 0
- Integer: X1, X2, and X3 should be integers
5. To set up the Excel file, enter the total forecasted demand for Week 10 in the pink cell. Then, enter the following formulas in the grey cells:
- Cost of Production: =14000*Production!B2 + 30000*Production!C2 + 11000*Production!D2
- Supply of Ingr. 1: =180*Production!B2 + 120*Production!C2 + 540*Production!D2
- Supply of Ingr. 2: =60*Production!B2 + 420*Production!C2 + 120*Production!D2
- Units produced: =120*Production!B2 + 300*Production!C2 + 60*Production!D2
6. Set up Excel Solver with a Constraint Precision of 0.000001 and Integer Optimality (%) of 0. Let it solve the optimization problem and provide the solution. The solution will give the number of batches for each process and the cost of production.
7. To determine whether a constraint is binding or not, check if the value of the constraint is equal to the right-hand side value. If it is, the constraint is binding; otherwise, it is not binding.
8. If Eli Orchid can only procure 4320 pounds of Ingredient 1 and 1440 pounds of Ingredient 2, update the supply constraints with these new values. Then, set up Excel Solver again and solve the optimization problem to get the new solution.
9. Label the constraints in the new solution as binding or not-binding by comparing the values of the constraints with the updated right-hand side values.
10. Based on the optimized production process and considering the shortage of ingredients, Eli Orchid should produce the suggested number of batches for each process. The pricing of Orchid Relief should be set to cover the production cost and maximize profit. Additionally, it may be worth considering alternative sourcing options or managing the ingredient supply chain more effectively to prevent shortages in the future.