Excel - Optimization Models
Sheet1
| Par, Inc. | |||
| Parameters | |||
| Production Time (Hours) | Time Available | ||
| Operation | Standard | Deluxe | Hours |
| Cutting and Dyeing | 0.7 | 1 | 630 |
| Sewing | 0.5 | 0.83333 | 600 |
| Finishing | 1 | 0.66667 | 708 |
| Inspection and Packaging | 0.1 | 0.25 | 135 |
| Profit Per Bag | 10 | 9.00 | |
| Model | |||
| Standard | Deluxe | ||
| Bags Produced | 500.00000 | 500.00000 | |
| Total Profit | $9,500.00 | ||
| Operation | Hours Used | Hours Available | |
| Cutting and Dyeing | 850 | 630 | |
| Sewing | 666.6666666667 | 600 | |
| Finishing | 833.3333333333 | 708 | |
| Inspection and Packaging | 175 | 135 |
Answer Report 1
| Microsoft Excel 16.16 Answer Report | ||||||
| Worksheet: [ParInc-end-s6.xlsx]Sheet2 | ||||||
| Report Created: 2/14/19 1:09:49 PM | ||||||
| Result: Solver found a solution. All constraints and optimality conditions are satisfied. | ||||||
| Solver Engine | ||||||
| Engine: Simplex LP | ||||||
| Solution Time: 8590489.279 Seconds. | ||||||
| Iterations: 2 Subproblems: 0 | ||||||
| Solver Options | ||||||
| Max Time Unlimited, Iterations Unlimited, Precision 1E-06, Use Automatic Scaling | ||||||
| Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative | ||||||
| Objective Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $F$7 | Profit | $ 3,800.00 | $ 7,668.00 | |||
| Variable Cells | ||||||
| Cell | Name | Original Value | Final Value | Integer | ||
| $D$6 | X1 = # of Standard | 200 | 540 | Contin | ||
| $E$6 | X2 = # of Deluxe | 200 | 252 | Contin | ||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $F$8 | c1 | 630 | $F$8<=$H$8 | Binding | 0 | |
| $F$9 | c2 | 480 | $F$9<=$H$9 | Not Binding | 120 | |
| $F$10 | c3 | 708 | $F$10<=$H$10 | Binding | 0 | |
| $F$11 | c4 | 117 | $F$11<=$H$11 | Not Binding | 18 |
Sensitivity Report 1
| Microsoft Excel 16.16 Sensitivity Report | |||||||
| Worksheet: [ParInc-end-s6.xlsx]Sheet2 | |||||||
| Report Created: 2/14/19 1:09:50 PM | |||||||
| Variable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $D$6 | X1 = # of Standard | 540 | 0 | 10 | 3.5 | 3.7 | |
| $E$6 | X2 = # of Deluxe | 252 | 0 | 9 | 5.2857142857 | 2.3333333333 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $F$8 | c1 | 630 | 4.375 | 630 | 52.3636363636 | 134.4 | |
| $F$9 | c2 | 480 | 0 | 600 | 1E+30 | 120 | |
| $F$10 | c3 | 708 | 6.9375 | 708 | 192 | 128 | |
| $F$11 | c4 | 117 | 0 | 135 | 1E+30 | 18 |
Sheet2
| Decision Variables | |||||||
| X1 = # of Standard | X2 = # of Deluxe | ||||||
| 540 | 252 | ||||||
| Objective Function | Profit | $10 | $9 | $ 7,668.00 | |||
| c1 | 0.70 | 1.00 | 630 | ≤ | 630 | ||
| c2 | 0.5 | 0.8333333333 | 480 | ≤ | 600 | ||
| c3 | 1 | 0.6666666667 | 708 | ≤ | 708 | ||
| c4 | 0.1 | 0.25 | 117 | ≤ | 135 | ||
| Decision Variables | |||||||
| Objective Function | |||||||
| LHS Constraints |
Answer Report 2
| Microsoft Excel 16.16 Answer Report | ||||||
| Worksheet: [ParInc-end-s6.xlsx]Sheet5 | ||||||
| Report Created: 2/14/19 9:03:20 PM | ||||||
| Result: Solver found a solution. All constraints and optimality conditions are satisfied. | ||||||
| Solver Engine | ||||||
| Engine: Simplex LP | ||||||
| Solution Time: 30065429.828 Seconds. | ||||||
| Iterations: 4 Subproblems: 0 | ||||||
| Solver Options | ||||||
| Max Time Unlimited, Iterations Unlimited, Precision 1E-06, Use Automatic Scaling | ||||||
| Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative | ||||||
| Objective Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $I$8 | Obj. Fun | 9080 | 10420 | |||
| Variable Cells | ||||||
| Cell | Name | Original Value | Final Value | Integer | ||
| $D$7 | Amount in Google | 20000 | 40000 | Contin | ||
| $E$7 | Amount in Apple | 20000 | 10000 | Contin | ||
| $F$7 | Amount in Amazon | 20000 | 40000 | Contin | ||
| $G$7 | Amount in Walmart | 20000 | 10000 | Contin | ||
| $H$7 | Amount in Target | 20000 | 0 | Contin | ||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $I$9 | C1 | 50000 | $I$9<=$K$9 | Binding | 0 | |
| $I$10 | C2 | 40000 | $I$10<=$K$10 | Binding | 0 | |
| $I$11 | 10000 | $I$11<=$K$11 | Not Binding | 30000 | ||
| $I$12 | 40000 | $I$12<=$K$12 | Binding | 0 | ||
| $I$13 | 10000 | $I$13<=$K$13 | Not Binding | 30000 | ||
| $I$14 | 0 | $I$14<=$K$14 | Not Binding | 40000 | ||
| $I$15 | 100000 | $I$15<=$K$15 | Binding | 0 |
Sensitivity Report 2
| Microsoft Excel 16.16 Sensitivity Report | |||||||
| Worksheet: [ParInc-end-s6.xlsx]Sheet5 | |||||||
| Report Created: 2/14/19 9:03:21 PM | |||||||
| Variable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $D$7 | Amount in Google | 40000 | 0 | 0.103 | 1E+30 | 0.011 | |
| $E$7 | Amount in Apple | 10000 | 0 | 0.092 | 0.011 | 0.014 | |
| $F$7 | Amount in Amazon | 40000 | 0 | 0.115 | 1E+30 | 0.037 | |
| $G$7 | Amount in Walmart | 10000 | 0 | 0.078 | 0.014 | 0.012 | |
| $H$7 | Amount in Target | 0 | -0.012 | 0.066 | 0.012 | 1E+30 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $I$9 | C1 | 50000 | 0.014 | 50000 | 10000 | 10000 | |
| $I$10 | C2 | 40000 | 0.011 | 40000 | 10000 | 30000 | |
| $I$11 | 10000 | 0 | 40000 | 1E+30 | 30000 | ||
| $I$12 | 40000 | 0.037 | 40000 | 10000 | 30000 | ||
| $I$13 | 10000 | 0 | 40000 | 1E+30 | 30000 | ||
| $I$14 | 0 | 0 | 40000 | 1E+30 | 40000 | ||
| $I$15 | 100000 | 0.078 | 100000 | 30000 | 10000 |
Amazon Not Tech
| Decision Variables | ||||||||||
| Amount in Google | Amount in Apple | Amount in Amazon | Amount in Walmart | Amount in Target | ||||||
| 40000 | 10000 | 40000 | 10000 | 0 | ||||||
| Obj. Fun | 10.30% | 9.20% | 11.50% | 7.80% | 6.60% | 10420 | ||||
| C1 | 1 | 1 | 50000 | ≤ | 50000 | |||||
| C2 | 1 | 0 | 0 | 0 | 0 | 40000 | ≤ | 40000 | ||
| 0 | 1 | 0 | 0 | 0 | 10000 | ≤ | 40000 | |||
| 0 | 0 | 1 | 0 | 0 | 40000 | ≤ | 40000 | |||
| 0 | 0 | 0 | 1 | 0 | 10000 | ≤ | 40000 | |||
| 0 | 0 | 0 | 0 | 1 | 0 | ≤ | 40000 | |||
| 1 | 1 | 1 | 1 | 1 | 100000 | ≤ | 100000 |
Answer Report 3
| Microsoft Excel 16.16 Answer Report | ||||||
| Worksheet: [ParInc-end-s6.xlsx]Sheet5 | ||||||
| Report Created: 2/14/19 9:05:03 PM | ||||||
| Result: Solver found a solution. All constraints and optimality conditions are satisfied. | ||||||
| Solver Engine | ||||||
| Engine: Simplex LP | ||||||
| Solution Time: 30065457.485 Seconds. | ||||||
| Iterations: 4 Subproblems: 0 | ||||||
| Solver Options | ||||||
| Max Time Unlimited, Iterations Unlimited, Precision 1E-06, Use Automatic Scaling | ||||||
| Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative | ||||||
| Objective Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $I$8 | 10420 | 9410 | ||||
| Variable Cells | ||||||
| Cell | Name | Original Value | Final Value | Integer | ||
| $D$7 | Amount in Google | 40000 | 10000 | Contin | ||
| $E$7 | Amount in Apple | 10000 | 0 | Contin | ||
| $F$7 | Amount in Amazon | 40000 | 40000 | Contin | ||
| $G$7 | Amount in Walmart | 10000 | 40000 | Contin | ||
| $H$7 | Amount in Target | 0 | 10000 | Contin | ||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $I$9 | 50000 | $I$9<=$K$9 | Binding | 0 | ||
| $I$10 | 10000 | $I$10<=$K$10 | Not Binding | 30000 | ||
| $I$11 | 0 | $I$11<=$K$11 | Not Binding | 40000 | ||
| $I$12 | 40000 | $I$12<=$K$12 | Binding | 0 | ||
| $I$13 | 40000 | $I$13<=$K$13 | Binding | 0 | ||
| $I$14 | 10000 | $I$14<=$K$14 | Not Binding | 30000 | ||
| $I$15 | 100000 | $I$15<=$K$15 | Binding | 0 |
Sensitivity Report 3
| Microsoft Excel 16.16 Sensitivity Report | |||||||
| Worksheet: [ParInc-end-s6.xlsx]Sheet5 | |||||||
| Report Created: 2/14/19 9:05:03 PM | |||||||
| Variable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $D$7 | Amount in Google | 10000 | 0 | 0.103 | 0.012 | 0.011 | |
| $E$7 | Amount in Apple | 0 | -0.011 | 0.092 | 0.011 | 1E+30 | |
| $F$7 | Amount in Amazon | 40000 | 0 | 0.115 | 1E+30 | 0.012 | |
| $G$7 | Amount in Walmart | 40000 | 0 | 0.078 | 1E+30 | 0.012 | |
| $H$7 | Amount in Target | 10000 | 0 | 0.066 | 0.012 | 0.066 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $I$9 | 50000 | 0.037 | 50000 | 10000 | 10000 | ||
| $I$10 | 10000 | 0 | 40000 | 1E+30 | 30000 | ||
| $I$11 | 0 | 0 | 40000 | 1E+30 | 40000 | ||
| $I$12 | 40000 | 0.012 | 40000 | 10000 | 30000 | ||
| $I$13 | 40000 | 0.012 | 40000 | 10000 | 30000 | ||
| $I$14 | 10000 | 0 | 40000 | 1E+30 | 30000 | ||
| $I$15 | 100000 | 0.066 | 100000 | 30000 | 10000 |
Amazon as Tech
| Decision Variables | ||||||||||
| Amount in Google | Amount in Apple | Amount in Amazon | Amount in Walmart | Amount in Target | ||||||
| 10000 | 0 | 40000 | 40000 | 10000 | ||||||
| 10.30% | 9.20% | 11.50% | 7.80% | 6.60% | 9410 | |||||
| 1 | 1 | 1 | 50000 | ≤ | 50000 | |||||
| 1 | 0 | 0 | 0 | 0 | 10000 | ≤ | 40000 | |||
| 0 | 1 | 0 | 0 | 0 | 0 | ≤ | 40000 | |||
| 0 | 0 | 1 | 0 | 0 | 40000 | ≤ | 40000 | |||
| 0 | 0 | 0 | 1 | 0 | 40000 | ≤ | 40000 | |||
| 0 | 0 | 0 | 0 | 1 | 10000 | ≤ | 40000 | |||
| 1 | 1 | 1 | 1 | 1 | 100000 | ≤ | 100000 |