Scribd
Upload
29 views4 pages

Cell Name Original Value Final Value

The document contains the results of using Excel Solver to optimize production levels of two parts to maximize profit given resource constraints. Solver found a solution that maximized profit to $13722.49 by producing 5.966 units of Part 1 and 10.818 units of Part 2 within the stamping, drilling, and finishing hour constraints. The solution was found in 0.016 seconds using the simplex LP algorithm.

Uploaded by

Nene Kanyanut
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
29 views4 pages

Cell Name Original Value Final Value

The document contains the results of using Excel Solver to optimize production levels of two parts to maximize profit given resource constraints. Solver found a solution that maximized profit to $13722.49 by producing 5.966 units of Part 1 and 10.818 units of Part 2 within the stamping, drilling, and finishing hour constraints. The solution was found in 0.016 seconds using the simplex LP algorithm.

Uploaded by

Nene Kanyanut
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd

Microsoft Excel 16.

0 Answer Report
Worksheet: [Lecture7_exercise.xlsx]Sheet1
Report Created: 8/31/2021 11:49:44 AM
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.016 Seconds.
Iterations: 2 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative

Objective Cell (Max)


Cell Name Original Value Final Value
$B$13Profit = 0 13722.49071

Variable Cells
Cell Name Original Value Final Value Integer
$B$11Part 1 = 0 5.966542751 Contin
$B$12Part 2 = 0 10.81784387 Contin

Constraints
Cell Name Cell Value Formula Status Slack
$E$6 stamping (hr) Usage 105 $E$6<=$G$6 Binding 0
$E$7 drilling (hr) Usage 90 $E$7<=$G$7 Binding 0
$E$8 finishing (hr) Usage 98.64869888476 $E$8<=$G$8 Not Binding 11.35130112
e NonNegative
Microsoft Excel 16.0 Sensitivity Report
Worksheet: [Lecture7_exercise.xlsx]Sheet1
Report Created: 8/31/2021 11:49:45 AM

Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$11 Part 1 = 5.966542751 0 650 501.4285714 164.6666667
$B$12 Part 2 = 10.81784387 0 910 308.75 396.2903226

Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$E$6 stamping (hr) Usage 105 91.33828996 105 32.75510204 15.92853417
$E$7 drilling (hr) Usage 90 45.91078067 90 5.889103182 21.4
$E$8 finishing (hr) Usage 98.64869888 0 110 1E+030 11.35130112
Example Problem: The Xecko Tool Company

Products: Part 1 Part 2


Profit per unit: 650 910
Resources: Usage Constraints Available Left over
stamping (hr) 4 7.5 105 ≤ 105 0
drilling (hr) 6.2 4.9 90 ≤ 90 0
finishing (hr) 9.1 4.1 98.6487 ≤ 110 11.3513

Production: Maximize Z = $650x1 + $910x2


Part 1 = 5.96654 subject to:
Part 2 = 10.8178 4x1 + 7.5x2  105
Profit = 13722.5 6.2x1 + 4.9x2  90
9.1x1 + 4.1x2  110
x1, x2  0

You might also like