Prof EXCEL
Answer Report 1
| Microsoft Excel 16.0 Answer Report | ||||||
| Worksheet: [Fixed Cost example solver solution.xlsx]solver solution | ||||||
| Report Created: 9/3/2021 4:27:13 PM | ||||||
| Result: Solver found a solution. All Constraints and optimality conditions are satisfied. | ||||||
| Solver Engine | ||||||
| Engine: Simplex LP | ||||||
| Solution Time: 0.031 Seconds. | ||||||
| Iterations: 8 Subproblems: 0 | ||||||
| Solver Options | ||||||
| Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling | ||||||
| Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative | ||||||
| Objective Cell (Min) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $K$7 | Objective Function: Min Costs Min Costs | $0 | $13,250 | |||
| Variable Cells | ||||||
| Cell | Name | Original Value | Final Value | Integer | ||
| $C$5 | Decision variables x1 | 0 | 6000 | Integer | ||
| $D$5 | Decision variables x2 | 0 | 0 | Integer | ||
| $E$5 | Decision variables x3 | 0 | 4000 | Integer | ||
| $F$5 | Decision variables x4 | 0 | 0 | Integer | ||
| $G$5 | Decision variables y1 | 0 | 1 | Binary | ||
| $H$5 | Decision variables y2 | 0 | 0 | Binary | ||
| $I$5 | Decision variables y3 | 0 | 1 | Binary | ||
| $J$5 | Decision variables y4 | 0 | 0 | Binary | ||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $K$11 | Plant Capacity with Set-up - machine 1 LHS | 0 | $K$11<=$M$11 | Binding | 0 | |
| $K$12 | Plant Capacity with Set-up - machine 2 LHS | 0 | $K$12<=$M$12 | Binding | 0 | |
| $K$13 | Plant Capacity with Set-up - machine 3 LHS | 0 | $K$13<=$M$13 | Binding | 0 | |
| $K$14 | Plant Capacity with Set-up - machine 4 LHS | 0 | $K$14<=$M$14 | Binding | 0 | |
| $K$15 | Machine 4 and machine 1 cannot be used simultaneously LHS | 1 | $K$15<=$M$15 | Binding | 0 | |
| $K$16 | Required Demand LHS | 10000 | $K$16>=$M$16 | Binding | 0 | |
| $C$5:$F$5=Integer | ||||||
| $G$5:$J$5=Binary |
solver solution
| Fixed Cost (Westfall Garden) Solver Solution. (see formulation below). | ||||||||||||
| x1 | x2 | x3 | x4 | y1 | y2 | y3 | y4 | |||||
| Decision variables | 6000 | 0 | 4000 | 0 | 1 | 0 | 1 | 0 | ||||
| Coefficients | Min Costs | |||||||||||
| Objective Function: Min Costs | 1.25 | 1.50 | 1.00 | 2.00 | 750 | 500 | 1000 | 300 | $13,250 | |||
| Constraints | Coefficients | LHS | RHS | |||||||||
| Plant Capacity with Set-up - machine 1 | 1 | -6000 | 0 | <= | 0 | |||||||
| Plant Capacity with Set-up - machine 2 | 1 | -7500 | 0 | <= | 0 | |||||||
| Plant Capacity with Set-up - machine 3 | 1 | -4000 | 0 | <= | 0 | |||||||
| Plant Capacity with Set-up - machine 4 | 1 | -5000 | 0 | <= | 0 | |||||||
| Machine 4 and machine 1 cannot be used simultaneously | 1 | 1 | 1 | <= | 1 | |||||||
| Required Demand | 1 | 1 | 1 | 1 | 10000 | >= | 10000 | |||||
Decision Variables xi = number of hoses to produce on machine ‘i', i=1,…,4 yi = to setup machine ‘i’ or not (0=no, 1=yes), i=1,…,4 Objective Function: Min Costs = 1.25x1 + 1.50x2 + 1.00x3 + 2.00x4 + 750y1 + 500y2 + 1000y3 + 300y4 s.t. x1 - 6000y1 < 0 x2 - 7500y2 < 0 x3 - 4000y3 < 0 x4 - 5000y4 < 0 y1 + y4 < 1 x1 + x2 + x3 + x4 > 10,000 xi > 0, int., i=1,…,4 yi = 0,1 (binary decision variables), i=1,…,4