a nut case 4.xlsx

6
Microsoft Excel 14.0 Answer Report Worksheet: [A Nut Case 4.xlsx]DATA Report Created: 17-Oct-15 5:03:29 PM Result: Solver found a solution. All Constraints and optimality conditio Solver Engine Engine: Simplex LP Solution Time: 0,016 Seconds. Iterations: 3 Subproblems: 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0,000001, Use Auto Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Toleranc Objective Cell (Max) Cell Name Original Value Final Value $F$6 $0.00 $756.80 Variable Cells Cell Name Original Value Final Value $B$5 Products (pounds) X1 0 1184 $C$5 Products (pounds) X2 0 400 $D$5 Products (pounds) X3 0 0 $E$5 Products (pounds) X4 0 0 Constraints Cell Name Cell Value Formula $F$15 Whole Used 1184 $F$15>=$H$15 $F$16 Cluster Used 400 $F$16>=$H$16 $F$17 Cluster Used 400 $F$17<=$H$17 $F$18 Crunch Used 0 $F$18<=$H$18 $F$19 Roated Used 0 $F$19<=$H$19 $F$9 Nuts Used 870.4 $F$9<=$H$9 $F$10 Chocolate Used 713.6 $F$10<=$H$10 $F$11 Hulling Used 1584 $F$11<=$H$11 $F$12 Roasting Used 2968 $F$12<=$H$12 $F$13 Coating Used 1464 $F$13<=$H$13 $F$14 Packaging Used 3600 $F$14<=$H$14 profit ($) (Max)

Upload: rang-truong

Post on 05-Dec-2015

237 views

Category:

Documents


2 download

TRANSCRIPT

Page 1: A Nut Case 4.xlsx

Microsoft Excel 14.0 Answer ReportWorksheet: [A Nut Case 4.xlsx]DATAReport Created: 17-Oct-15 5:03:29 PMResult: Solver found a solution. All Constraints and optimality conditions are satisfied.Solver Engine

Engine: Simplex LPSolution Time: 0,016 Seconds.Iterations: 3 Subproblems: 0

Solver OptionsMax Time Unlimited, Iterations Unlimited, Precision 0,000001, Use Automatic ScalingMax Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative

Objective Cell (Max)Cell Name Original Value Final Value

$F$6 $0.00 $756.80

Variable CellsCell Name Original Value Final Value Integer

$B$5 Products (pounds) X1 0 1184 Contin$C$5 Products (pounds) X2 0 400 Contin$D$5 Products (pounds) X3 0 0 Contin$E$5 Products (pounds) X4 0 0 Contin

ConstraintsCell Name Cell Value Formula Status

$F$15 Whole Used 1184 $F$15>=$H$15 Not Binding$F$16 Cluster Used 400 $F$16>=$H$16 Binding$F$17 Cluster Used 400 $F$17<=$H$17 Not Binding$F$18 Crunch Used 0 $F$18<=$H$18 Not Binding$F$19 Roated Used 0 $F$19<=$H$19 Not Binding$F$9 Nuts Used 870.4 $F$9<=$H$9 Not Binding$F$10 Chocolate Used 713.6 $F$10<=$H$10 Not Binding$F$11 Hulling Used 1584 $F$11<=$H$11 Not Binding$F$12 Roasting Used 2968 $F$12<=$H$12 Not Binding$F$13 Coating Used 1464 $F$13<=$H$13 Not Binding$F$14 Packaging Used 3600 $F$14<=$H$14 Binding

Net profit per unit Total profit ($)(Max)

Page 2: A Nut Case 4.xlsx

Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative

Slack184

0100150200

229.686.4

2016632

21360

Page 3: A Nut Case 4.xlsx

Microsoft Excel 14.0 Sensitivity ReportWorksheet: [A Nut Case 4.xlsx]DATAReport Created: 17-Oct-15 5:03:29 PM

Variable CellsFinal Reduced Objective Allowable Allowable

Cell Name Value Cost Coefficient Increase Decrease$B$5 Products (pounds) X1 1184 0 0.7 1E+030 0.425$C$5 Products (pounds) X2 400 0 -0.18 0.628 1E+030$D$5 Products (pounds) X3 0 -0.42 -0.07 0.42 1E+030$E$5 Products (pounds) X4 0 -0.17 0.11 0.17 1E+030

ConstraintsFinal Shadow Constraint Allowable Allowable

Cell Name Value Price R.H. Side Increase Decrease$F$15 Whole Used 1184 0 1000 184 1E+030$F$16 Cluster Used 400 -0.628 400 100 400$F$17 Cluster Used 400 0 500 1E+030 100$F$18 Crunch Used 0 0 150 1E+030 150$F$19 Roated Used 0 0 200 1E+030 200$F$9 Nuts Used 870.4 0 1100 1E+030 229.6$F$10 Chocolate Used 713.6 0 800 1E+030 86.4$F$11 Hulling Used 1584 0 3600 1E+030 2016$F$12 Roasting Used 2968 0 3600 1E+030 632$F$13 Coating Used 1464 0 3600 1E+030 2136$F$14 Packaging Used 3600 0.28 3600 540 460

Page 4: A Nut Case 4.xlsx

Microsoft Excel 14.0 Limits ReportWorksheet: [A Nut Case 4.xlsx]DATAReport Created: 17-Oct-15 5:03:29 PM

ObjectiveCell Name Value

$F$6 $756.80

Variable Lower Objective Upper ObjectiveCell Name Value Limit Result Limit Result

$B$5 Products (pounds) X1 1184 1000 628 1184 756.8$C$5 Products (pounds) X2 400 400 756.8 400 756.8$D$5 Products (pounds) X3 0 0 756.8 0 756.8$E$5 Products (pounds) X4 0 0 756.8 0 756.8

Net profit per unit Total profit ($)(Max)

Page 5: A Nut Case 4.xlsx

Products-Decision Whole Cluster Crunch Roasted

Variables X1 X2 X3 X4

Products (pounds) 1184 400 0 0

Net profit per unit $0.70 -$0.18 -$0.07 $0.11

Constraints

Material Whole Cluster Crunch Roasted

Nuts 60% 40% 20% 100%

Chocolate 40% 60% 80% 0%

Hulling 1.00 1.00 1.00 1.00

Roasting 2.00 1.50 1.00 1.75

Coating 1.00 0.70 0.20 0.00

Packaging 2.50 1.60 1.25 1.00

Whole 1

Cluster 1

Cluster1

Crunch 1

Roated1

Molokai Nut Company (MNC)

Page 6: A Nut Case 4.xlsx

$756.80

LHS RHS

Used Available

870.4 <= 1100

713.6 <= 800

1584 <= 3600

2968 <= 3600

1464 <= 3600

3600 <= 3600

1184 >= 1000

400 >= 400

400 <= 500

0 <= 150

0 <= 200

Total profit ($)(Max)