Optimal LP Solution and Quality Control
Task 7: Solving the LP formulated using the parameters estimated.
Recall that the LP formulated in task 1 was:
Objective function:
Constraints:
Demand for :
Demand for :
Demand for :
Demand for :
Demand for :
Machining time constraint:
Assembly time constraint:
Finishing time constraint:
All decision variables are non-negative.
We shall use the following parameter estimates (obtained in tasks 2-6):
P1 | P2 | P3 | P4 | P5 | |
demand (number of units required) for product . | 10000 | 12000 | 9000 | 15000 | 16000 |
cost for producing each unit of in a regular run. | $50 | $70 | $38 | $98 | $110 |
cost for producing each unit of in a special run. | $80 | $90 | $66 | $136 | $140 |
machining time (minutes) per unit of product . | 2 | 2 | 4 | 4 | 4 |
assembly time (minutes) per unit of product . | 4 | 2 | 2 | 4 | 2 |
finishing time (minutes) per unit of product . | 1 | 1 | 1 | 1 | 2 |
Use these parameter estimates to solve the above LP using any solver (such as Excel Solver’s Simplex method) to obtain the optimal production plan. You may modify the LP module I have provided (under “Course Content”) to solve the LP. Report your results as follows:
Minimum cost attainable: |
Number of units produced | P1 | P2 | P3 | P4 | P5 |
Regular Run | |||||
Special Run |
Resources in regular run | Minutes used | Minutes available |
MACHINE TIME | 18000 | |
ASSEMBLY TIME | 24000 | |
FINISH TIME | 24000 |
Task 8. Sensitivity Analysis:
(a) By how much does the total cost change as the demand for each product type changes by 1 unit?
Increase (or decrease) the demand for the products (one product at a time) by 1 unit and see how it affects your optimal objective function value.
Report your results as follows:
Demand for P1 changes by 1 unit:
Increasing demand for P1 by 1 unit increases cost by $ ….
Decreasing demand for P1 by 1 unit decreases cost by $ ….
Demand for P2 changes by 1 unit:
Increasing demand for P2 by 1 unit increases cost by $ ….
Decreasing demand for P2 by 1 unit decreases cost by $ ….
Demand for P3 changes by 1 unit:
Increasing demand for P3 by 1 unit increases cost by $ ….
Decreasing demand for P3 by 1 unit decreases cost by $ …
Demand for P4 changes by 1 unit:
Increasing demand for P4 by 1 unit increases cost by $ ….
Decreasing demand for P4 by 1 unit decreases cost by $ …..
Demand for P5 changes by 1 unit:
Increasing demand for P5 by 1 unit increases cost by $ ….
Decreasing demand for P5 by 1 unit decreases cost by $ …..
(b) At most how much should the company be willing to pay to?
(i) Increase the availability of machining time by one hour during regular run?
(ii) Increase the availability of assembly time by one hour during regular run?
(iii) Increase the availability of finishing time by one hour during regular run?
Increase the availability of the resource by 1 unit (one resource at a time) and see how it affects your optimal objective function value. Interpret your results accordingly and explain your reasoning.
Report your results as follows:
The company should be willing to pay up to $…. to increase the availability of machine time by one hour during regular run. This is because ….
The company should be willing to pay up to $…. to increase the availability of assembly time by one hour during regular run. This is because ….
The company should be willing to pay up to $…. to increase the availability of finishing time by one hour during regular run. This is because ….
Note that we are computing the shadow prices for the resources. Shadow prices may be interpreted as follows: Recall that during regular runs, machining costs are $4 per minute and thus $240/hour (refer to the interpretation of the regression coefficients in Task 3). If increasing the availability of machine time decreases cost by $C, then the company should be willing to pay up to $C more per hour than its current cost to increase the availability of machine time by one hour during regular run. That is, for the first 3000 hours it will still pay $240 per hour, but for each additional hour it should be willing to pay up to $240+C per hour.
Task 9. SQL query to obtain the data for classification task 9.
Download the data files from Blackboard.
Create tables “defective” and “quality” from these CSV files.
Ensure that defective has 3184 records, and quality has 2500 records (one for each batch)
I present a step-by-step approach for obtaining the necessary data for the classification task. Alternate correct approaches will do just as well.
The batch numbers of “Poor” quality batches can be obtained using the following query:
select batchnbr
from defective d, production p
where d.serialnbr = p.serialnbr
group by batchnbr
having count(*) > 1;
If this identifies k poor quality batches, then the remaining 2500 – k are good quality batches.
Add a new column called batchquality to the quality table.
The value of batchquality should be “poor” if it is a poor quality batch and “good” otherwise.
Now you have the data required for our classification task (Task 10).
Save the results in a csv file “qualityInput.csv”
Note that you can obtain this data in “qualityInput.csv” using a single SQL query.
Task 10. Obtained classification and present their classification accuracy.
Use any implementation of a decision tree learning algorithm that you have used before (in your data mining class or your MMIS 671 course) to come up with a small number of rules to classify the quality of the batches based on the input features. You should partition the data into training and test sets with 20% of the examples help back for testing.
If you plan to use Python, I have provided detailed guidance in the form of a notebook Coral_Bleaching_DecisionTree.ipynb that uses scikit-learn’s DecisionTreeClassifier.
If you plan to use the R , you can use the library rpart for classification. I have provided an example of a similar classification problem using rpart under the module “Task 10: Classification Tree Example)”.
You may also use XLMiner (https://www.xlminer.com/) or any other software that you are familiar with.
Specify the rules that you obtain.
Rule 1. IF ….. THEN quality =
Rule 2. IF ….. THEN quality =
.
.
Rule k. IF ….. THEN quality =
Present the classification accuracy of your rules for the training and test sets in the following format:
Results with 2000 training cases:
Number of batches | Predicted Poor Quality | Predicted Good Quality | Marginal Total |
Actually Poor Quality | |||
Actually Good Quality | |||
Marginal Total | 2000 |
Results with 500 test cases:
Number of batches | Predicted Poor Quality | Predicted Good Quality | Marginal Total |
Actually Poor Quality | |||
Actually Good Quality | |||
Marginal Total | 500 |
Leave a Reply
Want to join the discussion?Feel free to contribute!