KLEIN

profilebigmo77
klein_industriessample.xlsx
Home>Business & Finance homework help>Accounting homework help>KLEIN

Answer Report 1

Microsoft Excel 15.0 Answer Report
Worksheet: [Klein Industries.xlsx]Sheet1
Report Created: 7/22/2017 12:14:01
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0 Seconds.
Iterations: 2 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
$B$17 Z s 0 651221.428571429
Variable Cells
Cell Name Original Value Final Value Integer
$B$16 Solutions s 0 16157.1428571429 Contin
$C$16 Solutions m 0 6200 Contin
$D$16 Solutions l 0 2600 Contin
Constraints
Cell Name Cell Value Formula Status Slack
$E$20 Blending Used 12882.8571428571 $E$20<=$G$20 Not Binding 10517.1428571429
$E$21 Welding Used 19014.2857142857 $E$21<=$G$21 Not Binding 4385.7142857143
$E$22 Painting Used 46800 $E$22<=$G$22 Binding 0
$B$16 Solutions s 16157.1428571429 $B$16<=$B$27 Not Binding 4842.8571428571
$B$16 Solutions s 16157.1428571429 $B$16>=$B$26 Not Binding 2157.1428571429
$C$16 Solutions m 6200 $C$16<=$C$27 Not Binding 6300
$C$16 Solutions m 6200 $C$16>=$C$26 Binding 0
$D$16 Solutions l 2600 $D$16<=$D$27 Not Binding 1600
$D$16 Solutions l 2600 $D$16>=$D$26 Binding 0

Sensitivity Report 1

Microsoft Excel 15.0 Sensitivity Report
Worksheet: [Klein Industries.xlsx]Sheet1
Report Created: 7/22/2017 12:14:01
Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$16 Solutions s 16157.1428571429 0 20.5 1E+30 1.5322580645
$C$16 Solutions m 6200 -4.0714285714 34 4.0714285714 1E+30
$D$16 Solutions l 2600 -3.3928571429 42 3.3928571429 1E+30
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$E$20 Blending Used 12882.8571428571 0 23400 1E+30 10517.1428571429
$E$21 Welding Used 19014.2857142857 0 23400 1E+30 4385.7142857143
$E$22 Painting Used 46800 14.6428571429 46800 6780 3020

Limits Report 1

Microsoft Excel 15.0 Limits Report
Worksheet: [Klein Industries.xlsx]Sheet1
Report Created: 1/20/2018 12:50:01
Objective
Cell Name Value
$B$17 Z s 651221.428571429
Variable Lower Objective Upper Objective
Cell Name Value Limit Result Limit Result
$B$16 Solutions s 16157.1428571429 14000 607000 16157.1428571261 651221.428571084
$C$16 Solutions m 6200 6200 651221.428571429 6200.0000000035 651221.428571547
$D$16 Solutions l 2600 2600 651221.428571429 2600.0000000012 651221.42857148

With Auxilliary

Klein Industries Linear Optimization
Let s = small, m = medium, l = large
Let Z = Objective function
Maximize Z = $20.5s + $34m + $42l
Subject to;
0.4s + 0.7m + 0.8l ≤ 23400
0.6s + 1m + 1.2l ≤ 23400
1.4s + 2.6m + 3.1l ≤ 46800
14000 ≤ s ≤ 21000
6200 ≤ m ≤ 12500
2600 ≤ l ≤ 4200
Solving the model
Variables s m l
Coefficients $20.50 $34.00 $42.00
Solutions 16157.1428571429 6200 2600
Z $651,221.43
Checkpoints
Processing time Used Constraint Available Balance
Blending 0.4 0.7 0.8 12882.8571428571 ≤ 23400 10517.1428571429
Welding 0.6 1 1.2 19014.2857142857 ≤ 23400 4385.7142857143
Painting 1.4 2.6 3.1 46800 ≤ 46800 0
Totals 2.4 4.3 5.1
Projected sales
Minimum 14000 6200 2600
Maximum 21000 12500 4200
Units
Unit Profit $20.50 $34.00 $42.00
Processing time 2.4 4.3 5.1
Profit per process time $8.54 $7.91 $8.24

Answer Report 2

Microsoft Excel 15.0 Answer Report
Worksheet: [Klein Industries.xlsx]Without Auxilliary
Report Created: 1/20/2018 12:50:11
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.032 Seconds.
Iterations: 2 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
$B$17 Z s 651221.428571429 685285.714285714
Variable Cells
Cell Name Original Value Final Value Integer
$B$16 Solutions s 16157.1428571429 33428.5714285714 Contin
$C$16 Solutions m 6200 0 Contin
$D$16 Solutions l 2600 0 Contin
Constraints
Cell Name Cell Value Formula Status Slack
$E$20 Blending Used 13371.4285714286 $E$20<=$G$20 Not Binding 10028.5714285714
$E$21 Welding Used 20057.1428571429 $E$21<=$G$21 Not Binding 3342.8571428571
$E$22 Painting Used 46800 $E$22<=$G$22 Binding 0

Sensitivity Report 2

Microsoft Excel 15.0 Sensitivity Report
Worksheet: [Klein Industries.xlsx]Without Auxilliary
Report Created: 1/20/2018 12:50:11
Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$16 Solutions s 33428.5714285714 0 20.5 1E+30 1.5322580645
$C$16 Solutions m 0 -4.0714285714 34 4.0714285714 1E+30
$D$16 Solutions l 0 -3.3928571429 42 3.3928571429 1E+30
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$E$20 Blending Used 13371.4285714286 0 23400 1E+30 10028.5714285714
$E$21 Welding Used 20057.1428571429 0 23400 1E+30 3342.8571428571
$E$22 Painting Used 46800 14.6428571429 46800 7800 46800

Limits Report 2

Microsoft Excel 15.0 Limits Report
Worksheet: [Klein Industries.xlsx]Without Auxilliary
Report Created: 1/20/2018 12:20:11
Objective
Cell Name Value
$B$17 Z s 685285.714285714
Variable Lower Objective Upper Objective
Cell Name Value Limit Result Limit Result
$B$16 Solutions s 33428.5714285714 0 0 33428.5714285714 685285.714285714
$C$16 Solutions m 0 0 685285.714285714 0 685285.714285714
$D$16 Solutions l 0 0 685285.714285714 0 685285.714285714

Without Auxilliary

Klein Industries Linear Optimization
Let s = small, m = medium, l = large
Let Z = Objective function
Maximize Z = $20.5s + $34m + $42l
Subject to;
0.4s + 0.7m + 0.8l ≤ 23400
0.6s + 1m + 1.2l ≤ 23400
1.4s + 2.6m + 3.1l ≤ 46800
14000 ≤ s ≤ 21000
6200 ≤ m ≤ 12500
2600 ≤ l ≤ 4200
Solving the model
Variables s m l
Coefficients $20.50 $34.00 $42.00
Solutions 33428.5714285714 0 0
Z $685,285.71
Check points
Processing time Used Constraint Available Balance
Blending 0.4 0.7 0.8 13371.4285714286 ≤ 23400 10028.5714285714
Welding 0.6 1 1.2 20057.1428571429 ≤ 23400 3342.8571428571
Painting 1.4 2.6 3.1 46800 ≤ 46800 0
Totals 2.4 4.3 5.1
Projected sales
Minimum 14000 6200 2600
Maximum 21000 12500 4200
Units
Unit Profit $20.50 $34.00 $42.00
Processing time 2.4 4.3 5.1
Profit per process time $8.54 $7.91 $8.24