Scribd
Upload
13 views5 pages

Excel Solver Optimization Report

The document contains reports from a solver in Microsoft Excel that analyzed an optimization problem to maximize total sales subject to various constraints. The solver found a solution with values of 60, 80, and 60 for variables X1, X2, and X3 respectively, achieving a maximum objective value of $243,700. Sensitivity reports provide details on how changes to variables and constraints would affect the objective value.

Uploaded by

Sumanjima
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
13 views5 pages

Excel Solver Optimization Report

The document contains reports from a solver in Microsoft Excel that analyzed an optimization problem to maximize total sales subject to various constraints. The solver found a solution with values of 60, 80, and 60 for variables X1, X2, and X3 respectively, achieving a maximum objective value of $243,700. Sensitivity reports provide details on how changes to variables and constraints would affect the objective value.

Uploaded by

Sumanjima
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: [Book1]Sheet1
Report Created: 10-06-2019 [Link]
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.016 Seconds.
Iterations: 7 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 (Max)


Cell Name Original Value Final Value
$G$9 Z -20,000 243,700

Variable Cells
Cell Name Original Value Final Value Integer
$B$5 X1 0 60 Contin
$C$5 X2 0 80 Contin
$D$5 X3 0 60 Contin

Constraints
Cell Name Cell Value Formula Status Slack
$E$11 C1 -40 $E$11<=$G$11 Not Binding 40
$E$12 C2a -10 $E$12<=$G$12 Not Binding 10
$E$13 C2b 60 $E$13>=$G$13 Not Binding 60
$E$14 C3 -7.1054274E-15 $E$14<=$G$14 Binding 0
$E$15 C4a 200 $E$15<=$G$15 Binding 0
$E$16 C4b 60 $E$16<=$G$16 Binding 0
$E$17 C5 140 $E$17>=$G$17 Not Binding 20
NonNegative
Microsoft Excel 16.0 Sensitivity Report
Worksheet: [Book1]Sheet1
Report Created: 10-06-2019 [Link]

Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$5 X1 60 0 1775 1E+030 605
$C$5 X2 80 0 1170 605 110
$D$5 X3 60 0 1060 110 3790

Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$E$11 C1 -40 0 0 1E+030 40
$E$12 C2a -10 0 0 1E+030 10
$E$13 C2b 60 0 0 60 1E+030
$E$14 C3 -7.105427E-15 110 0 60 10
$E$15 C4a 200 1137 200 400 28.57142857
$E$16 C4b 60 605 60 10 40
$E$17 C5 140 0 120 20 1E+030
Microsoft Excel 16.0 Limits Report
Worksheet: [Book1]Sheet1
Report Created: 10-06-2019 [Link]

Objective
Cell Name Value
$G$9 Z 243,700

Variable Lower Objective Upper Objective


Cell Name Value Limit Result Limit Result
$B$5 X1 60 40 208200 60 243700
$C$5 X2 80 64.61538462 225700 80 243700
$D$5 X3 60 60 243700 60 243700
X1: Deluxe packages
X2: Standard packages
X3: Economy packages
X1 X2 X3 Cost
20 100 80 20,000
Flight 1000 700 650
Hotel 300 220 190
Meals 475 250 220
SP 4500 23000 19200 Z 26,700

C1 -0.9 0.1 0.1 7.11E-15 <= 0


C2a 0.35 -0.65 0.35 -30 <= 0
C2b 0.7 -0.3 0.7 40 >= 0
C3 0.3 0.3 -0.7 -20 <= 0
C4a 1 1 1 200 <= 200
C4b 1 0 0 20 <= 60
C5 1 1 0 120 >= 120

You might also like