Formulation and solution: Lets assume, S = Number of small air compressors to produce to maximize the profit, M = Number of medium air compressors to produce to maximize the profit and L = Number of large air compressors to produce to maximize the profit. Then from the given question we can formulate the Linear Programing problem as follows. Maximize P = $20.50*S+ $34.00*M+$42.00*L Subject to, S 14000; (Minimum demand for Small compressors constraint) S 21000; (Maximum demand for Small compressors constraint) M 6200; (Minimum demand for Medium compressors constraint) M 12500; (Maximum demand for Medium compressors constraint) L 2600; (Minimum demand for Large compressors constraint) L 4200; (Maximum demand for Large compressors constraint) 0.4*S+0.7*M+0.8*L 23400; (Bending/Forming available time constraint) 0.6*S+1.0*M+1.2*L 23400; (Welding available time constraint) 1.4*S+2.6*M+3.1*L 46800; (Painting available time constraint) Using Excel solver the obtained optimum solution is, S= 16157.14; M = 6200 and S = 2600 and this is giving us the optimal profit as $651,221.43. Memo: The Klein Industries manufactures three types of portable air compressors, the profit and demand for these three types are also different. As well as the required materials and time for the production is limited. Due to all these constraints the objective was to find out the combination of the number of products of each type to produce to maximize the profit. To solve this problem, which can be categorized as a Linear optimization problem, I formulate a Linear programming problem with all the constraints (the conditions) and the decision variables (number to produce). Then using the solver add-in in Excel I got the optimal combination which is informing us that the company should produce 16157.14 units of small compressors, 6200 units of medium compressors and 2600 units of large compressors to maximize the profit. The maximum profit which this combination of production is giving is $651,221.43. We also need to check at the bounded constraints and the shadow prices to see if there is a possible way to increase the profit by changing some constraints. The obtained solution showed us that the Minimum demand for medium and large compressors are the binding constraints for demands. For the service time availability the painting time availability is the binding constraint. Now as we know if we can change the binding constraints the profit can be changed and that change depends on the corresponding shadow prices. The shadow price for painting is 14.6429 implying that per 1 minute increase in painting available time would increase the overall profit by $14.6429. And the maximum allowable increase is 6780 implying we can increase the paining time by another 6780 minutes to get more profit at $14.6429 per minute. Similarly the shadow price for number of medium compressors is -4.0714 (negative as this is a lower bound) implying that per 1 unit decrease in minimum demand for medium compressors would increase the overall profit by $4.0714. And the maximum allowable decrease is 2607.69 implying at the current set up if we don't change anything else just decrease the demand for medium compressors then we can increase the profit by $4.0714 per 1 unit demand decrease for another 2607.69 units. And the shadow price for number of large compressors is -3.3929(negative as this is a lower bound) implying that per 1 unit decrease in minimum demand for large compressors would increase the overall profit by $3.3929. And the maximum allowable decrease is 2187.10 implying at the current set up if we don't change anything else just decrease the demand for large compressors then we can increase the profit by $3.3929 per 1 unit demand decrease for another 2187.10 units. LP_max Linear, Integer and Mixed Integer Programming Signs < = > less than or equal to equals (You need to enter an apostrophe first.) greater than or equal to Data S M L Objective $ 20.50 $ 34.00 $ 42.00 sign Min Demand for Small 1 > Max Demand for Small 1 < Min Demand for Medium 1 > Max Demand for Medium 1 < Min Demand for Large 1> Max Demand for Large 1< Bending/Forming Constraint 0.4 0.7 0.8 < Welding Constraint 0.6 1 1.2 < Painting Constraint 1.4 2.6 3.1 < Results Variables Objective 16157.14 6200 RHS 14000 21000 6200 12500 2600 4200 23400 23400 46800 2600 $ 651,221.43 Page 1 Results LHS Slack/Surplus 651221 16157.14 -2157.14 16157.14 4842.857 6200 0 6200 6300 2600 0 2600 1600 12882.86 10517.14 19014.29 4385.714 46800 0 Microsoft Excel 14.0 Answer Report Worksheet: [Solver.xlsx]LP_max Report Created: 4/17/2015 7:35:26 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.046 Seconds. Iterations: 5 Subproblems: 0 Solver Options Max Time 100 sec, Iterations 100, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 5%, Solve Without Integer Constraints, Assu Objective Cell (Max) Cell Name $H$10 RHS LHS Original Value Final Value 0 651221.4285714 Variable Cells Cell Name $B$22 Variables S $C$22 Variables M $D$22 Variables L Original Value Final Value Integer 0 16157.14285714 Contin 0 6200 Contin 0 2600 Contin Constraints Cell Name $K$11 > < constraints $K$12 < < constraints $K$13 > < constraints $K$14 < < constraints $K$15 > < constraints $K$16 < < constraints $K$17 < < constraints $K$18 < < constraints $K$19 < < constraints $M$11 > > constraints $M$12 < > constraints $M$13 > > constraints $M$14 < > constraints $M$15 > > constraints $M$16 < > constraints $M$17 < > constraints $M$18 < > constraints $M$19 < > constraints Cell Value Formula 0 $K$11<=$L$11 16157.1428571 $K$12<=$L$12 0 $K$13<=$L$13 6200 $K$14<=$L$14 0 $K$15<=$L$15 2600 $K$16<=$L$16 12882.8571429 $K$17<=$L$17 19014.2857143 $K$18<=$L$18 46800 $K$19<=$L$19 16157.1428571 $M$11>=$N$11 0 $M$12>=$N$12 6200 $M$13>=$N$13 0 $M$14>=$N$14 2600 $M$15>=$N$15 0 $M$16>=$N$16 0 $M$17>=$N$17 0 $M$18>=$N$18 0 $M$19>=$N$19 Status Binding Not Binding Binding Not Binding Binding Not Binding Not Binding Not Binding Binding Not Binding Binding Binding Binding Binding Binding Binding Binding Binding Slack 0 4842.8571429 0 6300 0 1600 10517.142857 4385.7142857 0 2157.1428571 0 0 0 0 0 0 0 0 thout Integer Constraints, Assume NonNegative Microsoft Excel 14.0 Sensitivity Report Worksheet: [Solver.xlsx]LP_max Report Created: 4/18/2015 7:35:27 PM Variable Cells Cell Name $B$22 Variables S $C$22 Variables M $D$22 Variables L Final Value 16157.142857 6200 2600 Reduced Cost Final Value Shadow Price Objective Allowable Allowable Coefficient Increase Decrease 0 20.5 1.00000E+030 1.5322580645 0 34 4.0714285714 1.00000E+030 0 42 3.3928571429 1.00000E+030 Constraints Cell $K$11 $K$12 $K$13 $K$14 $K$15 $K$16 $K$17 $K$18 $K$19 $M$11 $M$12 $M$13 $M$14 $M$15 $M$16 $M$17 $M$18 $M$19 Name > < constraints < < constraints > < constraints < < constraints > < constraints < < constraints < < constraints < < constraints < < constraints > > constraints < > constraints > > constraints < > constraints > > constraints < > constraints < > constraints < > constraints < > constraints 0 16157.142857 0 6200 0 2600 12882.857143 19014.285714 46800 16157.142857 0 6200 0 2600 0 0 0 0 0 0 0 0 0 0 0 0 14.6429 0 0 -4.0714 0 -3.3929 0 0 0 0 Constraint R.H. Side Allowable Allowable Increase Decrease 0 1.00000E+030 0 21000 1.00000E+030 4842.8571429 0 1.00000E+030 0 12500 1.00000E+030 6300 0 1.00000E+030 0 4200 1.00000E+030 1600 23400 1.00000E+030 10517.142857 23400 1.00000E+030 4385.7142857 46800 6780 3020 14000 2157.1428571 1.00000E+030 0 0 1.00000E+030 6200 1161.54 2607.69 0 0 1.00000E+030 2600 974.19354839 2187.10 0 0 1.00000E+030 0 0 1.00000E+030 0 0 1.00000E+030 0 0 1.00000E+030 Microsoft Excel 14.0 Limits Report Worksheet: [Solver.xlsx]LP_max Report Created: 4/18/2015 7:35:27 PM Objective Cell Name $H$10 RHS LHS Cell $B$22 $C$22 $D$22 Variable Name Variables S Variables M Variables L Value 651221.42857 Value 16157.142857 6200 2600 Lower Objective Limit Result 14000 607000 6200 651221.42857 2600 651221.42857 Upper Objective Limit Result 16157.142857 651221.42857 6200 651221.42857 2600 651221.42857 Maximize Z= 651221.4 By Changing, S= M= L= 16157.14 6200 2600 Subject to, Min Demand for Small Max Demand for Small Min Demand for Medium Max Demand for Medium Min Demand for Large Max Demand for Large Bending/Forming Constraint Welding Constraint Painting Constraint 16157.14 >= 16157.14 <= 6200 >= 6200 <= 2600 >= 2600 <= 12882.86 <= 19014.29 <= 46800 <= 14,000.00 21,000.00 6,200.00 12,500.00 2,600.00 4,200.00 23,400.00 23,400.00 46,800.00 Microsoft Excel 14.0 Answer Report Worksheet: [Solver.xlsx]Question 2 Report Created: 4/19/2015 1:35:49 AM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.031 Seconds. Iterations: 5 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 $D$3 Z = Variable Cells Cell $D$5 S = $D$6 M = $D$7 L = Name Original Value Final Value 0 651221.428571 Name Original Value Final Value Integer 0 16157.1428571 Contin 0 6200 Contin 0 2600 Contin Constraints Cell Name $C$10 Min Demand for Small L = $C$11 Max Demand for Small L = $C$12 Min Demand for Medium L = $C$13 Max Demand for Medium L = $C$14 Min Demand for Large L = $C$15 Max Demand for Large L = $C$16 Bending/Forming Constraint L = $C$17 Welding Constraint L = $C$18 Painting Constraint L = Cell Value Formula 16157.1428571 $C$10>=$E$10 16157.1428571 $C$11<=$E$11 6200 $C$12>=$E$12 6200 $C$13<=$E$13 2600 $C$14>=$E$14 2600 $C$15<=$E$15 12882.8571429 $C$16<=$E$16 19014.2857143 $C$17<=$E$17 46800 $C$18<=$E$18 Status Not Binding Not Binding Binding Not Binding Binding Not Binding Not Binding Not Binding Binding Slack 2157.1428571 4842.8571429 0 6300 0 1600 10517.142857 4385.7142857 0 Microsoft Excel 14.0 Sensitivity Report Worksheet: [Solver.xlsx]Question 2 Report Created: 4/19/2015 1:35:49 AM Variable Cells Cell $D$5 S = $D$6 M = $D$7 L = Name Final Value 16157.142857 6200 2600 Reduced Cost Objective Allowable Coefficient Increase 0 20.5 1.00000E+030 0 34 4.0714285714 0 42 3.3928571429 Constraints Cell $C$10 $C$11 $C$12 $C$13 $C$14 $C$15 $C$16 $C$17 $C$18 Name Min Demand for Small L = Max Demand for Small L = Min Demand for Medium L = Max Demand for Medium L = Min Demand for Large L = Max Demand for Large L = Bending/Forming Constraint L = Welding Constraint L = Painting Constraint L = Final Shadow Constraint Allowable Value Price R.H. Side Increase 16157.142857 0 14000 2157.1428571 16157.142857 0 21000 1.00000E+030 6200 -4.0714285714 6200 1161.5384615 6200 0 12500 1.00000E+030 2600 -3.3928571429 2600 974.19354839 2600 0 4200 1.00000E+030 12882.857143 0 23400 1.00000E+030 19014.285714 0 23400 1.00000E+030 46800 14.642857143 46800 6780 Allowable Decrease 1.5322580645 1.00000E+030 1.00000E+030 Allowable Decrease 1.00000E+030 4842.8571429 2607.6923077 6300 2187.0967742 1600 10517.142857 4385.7142857 3020 Microsoft Excel 14.0 Limits Report Worksheet: [Solver.xlsx]Question 2 Report Created: 4/19/2015 1:35:49 AM Objective Cell Name Value $D$3 Z = 651221.42857 Cell $D$5 $D$6 $D$7 Variable Name Value S= 16157.142857 M= 6200 L= 2600 Lower Objective Limit Result 14000 607000 6200 651221.42857 2600 651221.42857 Upper Objective Limit Result 16157.142857 651221.42857 6200 651221.42857 2600 651221.42857 LP_max Linear, Integer and Mixed Integer Programming Signs < = > less than or equal to equals (You need to enter an apostrophe first.) greater than or equal to Data S M L Objective $ 20.50 $ 34.00 $ 42.00 sign Min Demand for Small 1 > Max Demand for Small 1 < Min Demand for Medium 1 > Max Demand for Medium 1 < Min Demand for Large 1> Max Demand for Large 1< Bending/Forming Constraint 0.4 0.7 0.8 < Welding Constraint 0.6 1 1.2 < Painting Constraint 1.4 2.6 3.1 < Results Variables Objective 16157.14 6200 RHS 14000 21000 6200 12500 2600 4200 23400 23400 46800 2600 $ 651,221.43 Page 1 Results LHS Slack/Surplus 651221 16157.14 -2157.14 16157.14 4842.857 6200 0 6200 6300 2600 0 2600 1600 12882.86 10517.14 19014.29 4385.714 46800 0 Microsoft Excel 14.0 Answer Report Worksheet: [Solver.xlsx]LP_max Report Created: 3/26/2015 6:25:26 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.046 Seconds. Iterations: 5 Subproblems: 0 Solver Options Max Time 100 sec, Iterations 100, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 5%, Solve Without Integer Constraints, Assu Objective Cell (Max) Cell Name $H$10 RHS LHS Original Value Final Value 0 651221.4285714 Variable Cells Cell Name $B$22 Variables S $C$22 Variables M $D$22 Variables L Original Value Final Value Integer 0 16157.14285714 Contin 0 6200 Contin 0 2600 Contin Constraints Cell Name $K$11 > < constraints $K$12 < < constraints $K$13 > < constraints $K$14 < < constraints $K$15 > < constraints $K$16 < < constraints $K$17 < < constraints $K$18 < < constraints $K$19 < < constraints $M$11 > > constraints $M$12 < > constraints $M$13 > > constraints $M$14 < > constraints $M$15 > > constraints $M$16 < > constraints $M$17 < > constraints $M$18 < > constraints $M$19 < > constraints Cell Value Formula 0 $K$11<=$L$11 16157.1428571 $K$12<=$L$12 0 $K$13<=$L$13 6200 $K$14<=$L$14 0 $K$15<=$L$15 2600 $K$16<=$L$16 12882.8571429 $K$17<=$L$17 19014.2857143 $K$18<=$L$18 46800 $K$19<=$L$19 16157.1428571 $M$11>=$N$11 0 $M$12>=$N$12 6200 $M$13>=$N$13 0 $M$14>=$N$14 2600 $M$15>=$N$15 0 $M$16>=$N$16 0 $M$17>=$N$17 0 $M$18>=$N$18 0 $M$19>=$N$19 Status Binding Not Binding Binding Not Binding Binding Not Binding Not Binding Not Binding Binding Not Binding Binding Binding Binding Binding Binding Binding Binding Binding Slack 0 4842.8571429 0 6300 0 1600 10517.142857 4385.7142857 0 2157.1428571 0 0 0 0 0 0 0 0 thout Integer Constraints, Assume NonNegative Microsoft Excel 14.0 Sensitivity Report Worksheet: [Solver.xlsx]LP_max Report Created: 3/26/2015 6:25:26 PM Variable Cells Cell Name $B$22 Variables S $C$22 Variables M $D$22 Variables L Final Value 16157.142857 6200 2600 Reduced Cost Final Value Shadow Price Objective Allowable Allowable Coefficient Increase Decrease 0 20.5 1.00000E+030 1.5322580645 0 34 4.0714285714 1.00000E+030 0 42 3.3928571429 1.00000E+030 Constraints Cell $K$11 $K$12 $K$13 $K$14 $K$15 $K$16 $K$17 $K$18 $K$19 $M$11 $M$12 $M$13 $M$14 $M$15 $M$16 $M$17 $M$18 $M$19 Name > < constraints < < constraints > < constraints < < constraints > < constraints < < constraints < < constraints < < constraints < < constraints > > constraints < > constraints > > constraints < > constraints > > constraints < > constraints < > constraints < > constraints < > constraints 0 16157.142857 0 6200 0 2600 12882.857143 19014.285714 46800 16157.142857 0 6200 0 2600 0 0 0 0 0 0 0 0 0 0 0 0 14.6429 0 0 -4.0714 0 -3.3929 0 0 0 0 Constraint R.H. Side Allowable Allowable Increase Decrease 0 1.00000E+030 0 21000 1.00000E+030 4842.8571429 0 1.00000E+030 0 12500 1.00000E+030 6300 0 1.00000E+030 0 4200 1.00000E+030 1600 23400 1.00000E+030 10517.142857 23400 1.00000E+030 4385.7142857 46800 6780 3020 14000 2157.1428571 1.00000E+030 0 0 1.00000E+030 6200 1161.54 2607.69 0 0 1.00000E+030 2600 974.19354839 2187.10 0 0 1.00000E+030 0 0 1.00000E+030 0 0 1.00000E+030 0 0 1.00000E+030 Microsoft Excel 14.0 Limits Report Worksheet: [Solver.xlsx]LP_max Report Created: 3/26/2015 6:25:26 PM Objective Cell Name $H$10 RHS LHS Cell $B$22 $C$22 $D$22 Variable Name Variables S Variables M Variables L Value 651221.42857 Value 16157.142857 6200 2600 Lower Objective Limit Result 14000 607000 6200 651221.42857 2600 651221.42857 Upper Objective Limit Result 16157.142857 651221.42857 6200 651221.42857 2600 651221.42857 Maximize Z= 651221.4 By Changing, S= M= L= 16157.14 6200 2600 Subject to, Min Demand for Small Max Demand for Small Min Demand for Medium Max Demand for Medium Min Demand for Large Max Demand for Large Bending/Forming Constraint Welding Constraint Painting Constraint 16157.14 >= 16157.14 <= 6200 >= 6200 <= 2600 >= 2600 <= 12882.86 <= 19014.29 <= 46800 <= 14,000.00 21,000.00 6,200.00 12,500.00 2,600.00 4,200.00 23,400.00 23,400.00 46,800.00 Microsoft Excel 14.0 Answer Report Worksheet: [Solver.xlsx]Question 2 Report Created: 3/26/2015 6:35:15 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.031 Seconds. Iterations: 5 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 $D$3 Z = Variable Cells Cell $D$5 S = $D$6 M = $D$7 L = Name Original Value Final Value 0 651221.428571 Name Original Value Final Value Integer 0 16157.1428571 Contin 0 6200 Contin 0 2600 Contin Constraints Cell Name $C$10 Min Demand for Small L = $C$11 Max Demand for Small L = $C$12 Min Demand for Medium L = $C$13 Max Demand for Medium L = $C$14 Min Demand for Large L = $C$15 Max Demand for Large L = $C$16 Bending/Forming Constraint L = $C$17 Welding Constraint L = $C$18 Painting Constraint L = Cell Value Formula 16157.1428571 $C$10>=$E$10 16157.1428571 $C$11<=$E$11 6200 $C$12>=$E$12 6200 $C$13<=$E$13 2600 $C$14>=$E$14 2600 $C$15<=$E$15 12882.8571429 $C$16<=$E$16 19014.2857143 $C$17<=$E$17 46800 $C$18<=$E$18 Status Not Binding Not Binding Binding Not Binding Binding Not Binding Not Binding Not Binding Binding Slack 2157.1428571 4842.8571429 0 6300 0 1600 10517.142857 4385.7142857 0 Microsoft Excel 14.0 Sensitivity Report Worksheet: [Solver.xlsx]Question 2 Report Created: 3/26/2015 6:35:15 PM Variable Cells Cell $D$5 S = $D$6 M = $D$7 L = Name Final Value 16157.142857 6200 2600 Reduced Cost Objective Allowable Coefficient Increase 0 20.5 1.00000E+030 0 34 4.0714285714 0 42 3.3928571429 Constraints Cell $C$10 $C$11 $C$12 $C$13 $C$14 $C$15 $C$16 $C$17 $C$18 Name Min Demand for Small L = Max Demand for Small L = Min Demand for Medium L = Max Demand for Medium L = Min Demand for Large L = Max Demand for Large L = Bending/Forming Constraint L = Welding Constraint L = Painting Constraint L = Final Shadow Constraint Allowable Value Price R.H. Side Increase 16157.142857 0 14000 2157.1428571 16157.142857 0 21000 1.00000E+030 6200 -4.0714285714 6200 1161.5384615 6200 0 12500 1.00000E+030 2600 -3.3928571429 2600 974.19354839 2600 0 4200 1.00000E+030 12882.857143 0 23400 1.00000E+030 19014.285714 0 23400 1.00000E+030 46800 14.642857143 46800 6780 Allowable Decrease 1.5322580645 1.00000E+030 1.00000E+030 Allowable Decrease 1.00000E+030 4842.8571429 2607.6923077 6300 2187.0967742 1600 10517.142857 4385.7142857 3020 Microsoft Excel 14.0 Limits Report Worksheet: [Solver.xlsx]Question 2 Report Created: 3/26/2015 6:35:15 PM Objective Cell Name Value $D$3 Z = 651221.42857 Cell $D$5 $D$6 $D$7 Variable Name Value S= 16157.142857 M= 6200 L= 2600 Lower Objective Limit Result 14000 607000 6200 651221.42857 2600 651221.42857 Upper Objective Limit Result 16157.142857 651221.42857 6200 651221.42857 2600 651221.42857 LP_max Linear, Integer and Mixed Integer Programming Signs < = > less than or equal to equals (You need to enter an apostrophe first.) greater than or equal to Data S M L Objective $ 20.50 $ 34.00 $ 42.00 sign Min Demand for Small 1 > Max Demand for Small 1 < Min Demand for Medium 1 > Max Demand for Medium 1 < Min Demand for Large 1> Max Demand for Large 1< Bending/Forming Constraint 0.4 0.7 0.8 < Welding Constraint 0.6 1 1.2 < Painting Constraint 1.4 2.6 3.1 < Results Variables Objective 16157.14 6200 RHS 14000 21000 6200 12500 2600 4200 23400 23400 46800 2600 $ 651,221.43 Page 1 Results LHS Slack/Surplus 651221 16157.14 -2157.14 16157.14 4842.857 6200 0 6200 6300 2600 0 2600 1600 12882.86 10517.14 19014.29 4385.714 46800 0 Microsoft Excel 14.0 Answer Report Worksheet: [Solver.xlsx]LP_max Report Created: 3/26/2015 6:25:26 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.046 Seconds. Iterations: 5 Subproblems: 0 Solver Options Max Time 100 sec, Iterations 100, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 5%, Solve Without Integer Constraints, Assu Objective Cell (Max) Cell Name $H$10 RHS LHS Original Value Final Value 0 651221.4285714 Variable Cells Cell Name $B$22 Variables S $C$22 Variables M $D$22 Variables L Original Value Final Value Integer 0 16157.14285714 Contin 0 6200 Contin 0 2600 Contin Constraints Cell Name $K$11 > < constraints $K$12 < < constraints $K$13 > < constraints $K$14 < < constraints $K$15 > < constraints $K$16 < < constraints $K$17 < < constraints $K$18 < < constraints $K$19 < < constraints $M$11 > > constraints $M$12 < > constraints $M$13 > > constraints $M$14 < > constraints $M$15 > > constraints $M$16 < > constraints $M$17 < > constraints $M$18 < > constraints $M$19 < > constraints Cell Value Formula 0 $K$11<=$L$11 16157.1428571 $K$12<=$L$12 0 $K$13<=$L$13 6200 $K$14<=$L$14 0 $K$15<=$L$15 2600 $K$16<=$L$16 12882.8571429 $K$17<=$L$17 19014.2857143 $K$18<=$L$18 46800 $K$19<=$L$19 16157.1428571 $M$11>=$N$11 0 $M$12>=$N$12 6200 $M$13>=$N$13 0 $M$14>=$N$14 2600 $M$15>=$N$15 0 $M$16>=$N$16 0 $M$17>=$N$17 0 $M$18>=$N$18 0 $M$19>=$N$19 Status Binding Not Binding Binding Not Binding Binding Not Binding Not Binding Not Binding Binding Not Binding Binding Binding Binding Binding Binding Binding Binding Binding Slack 0 4842.8571429 0 6300 0 1600 10517.142857 4385.7142857 0 2157.1428571 0 0 0 0 0 0 0 0 thout Integer Constraints, Assume NonNegative Microsoft Excel 14.0 Sensitivity Report Worksheet: [Solver.xlsx]LP_max Report Created: 3/26/2015 6:25:26 PM Variable Cells Cell Name $B$22 Variables S $C$22 Variables M $D$22 Variables L Final Value 16157.142857 6200 2600 Reduced Cost Final Value Shadow Price Objective Allowable Allowable Coefficient Increase Decrease 0 20.5 1.00000E+030 1.5322580645 0 34 4.0714285714 1.00000E+030 0 42 3.3928571429 1.00000E+030 Constraints Cell $K$11 $K$12 $K$13 $K$14 $K$15 $K$16 $K$17 $K$18 $K$19 $M$11 $M$12 $M$13 $M$14 $M$15 $M$16 $M$17 $M$18 $M$19 Name > < constraints < < constraints > < constraints < < constraints > < constraints < < constraints < < constraints < < constraints < < constraints > > constraints < > constraints > > constraints < > constraints > > constraints < > constraints < > constraints < > constraints < > constraints 0 16157.142857 0 6200 0 2600 12882.857143 19014.285714 46800 16157.142857 0 6200 0 2600 0 0 0 0 0 0 0 0 0 0 0 0 14.6429 0 0 -4.0714 0 -3.3929 0 0 0 0 Constraint R.H. Side Allowable Allowable Increase Decrease 0 1.00000E+030 0 21000 1.00000E+030 4842.8571429 0 1.00000E+030 0 12500 1.00000E+030 6300 0 1.00000E+030 0 4200 1.00000E+030 1600 23400 1.00000E+030 10517.142857 23400 1.00000E+030 4385.7142857 46800 6780 3020 14000 2157.1428571 1.00000E+030 0 0 1.00000E+030 6200 1161.54 2607.69 0 0 1.00000E+030 2600 974.19354839 2187.10 0 0 1.00000E+030 0 0 1.00000E+030 0 0 1.00000E+030 0 0 1.00000E+030 Microsoft Excel 14.0 Limits Report Worksheet: [Solver.xlsx]LP_max Report Created: 3/26/2015 6:25:26 PM Objective Cell Name $H$10 RHS LHS Cell $B$22 $C$22 $D$22 Variable Name Variables S Variables M Variables L Value 651221.42857 Value 16157.142857 6200 2600 Lower Objective Limit Result 14000 607000 6200 651221.42857 2600 651221.42857 Upper Objective Limit Result 16157.142857 651221.42857 6200 651221.42857 2600 651221.42857 Maximize Z= 651221.4 By Changing, S= M= L= 16157.14 6200 2600 Subject to, Min Demand for Small Max Demand for Small Min Demand for Medium Max Demand for Medium Min Demand for Large Max Demand for Large Bending/Forming Constraint Welding Constraint Painting Constraint 16157.14 >= 16157.14 <= 6200 >= 6200 <= 2600 >= 2600 <= 12882.86 <= 19014.29 <= 46800 <= 14,000.00 21,000.00 6,200.00 12,500.00 2,600.00 4,200.00 23,400.00 23,400.00 46,800.00 Microsoft Excel 14.0 Answer Report Worksheet: [Solver.xlsx]Question 2 Report Created: 3/26/2015 6:35:15 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.031 Seconds. Iterations: 5 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 $D$3 Z = Variable Cells Cell $D$5 S = $D$6 M = $D$7 L = Name Original Value Final Value 0 651221.428571 Name Original Value Final Value Integer 0 16157.1428571 Contin 0 6200 Contin 0 2600 Contin Constraints Cell Name $C$10 Min Demand for Small L = $C$11 Max Demand for Small L = $C$12 Min Demand for Medium L = $C$13 Max Demand for Medium L = $C$14 Min Demand for Large L = $C$15 Max Demand for Large L = $C$16 Bending/Forming Constraint L = $C$17 Welding Constraint L = $C$18 Painting Constraint L = Cell Value Formula 16157.1428571 $C$10>=$E$10 16157.1428571 $C$11<=$E$11 6200 $C$12>=$E$12 6200 $C$13<=$E$13 2600 $C$14>=$E$14 2600 $C$15<=$E$15 12882.8571429 $C$16<=$E$16 19014.2857143 $C$17<=$E$17 46800 $C$18<=$E$18 Status Not Binding Not Binding Binding Not Binding Binding Not Binding Not Binding Not Binding Binding Slack 2157.1428571 4842.8571429 0 6300 0 1600 10517.142857 4385.7142857 0 Microsoft Excel 14.0 Sensitivity Report Worksheet: [Solver.xlsx]Question 2 Report Created: 3/26/2015 6:35:15 PM Variable Cells Cell $D$5 S = $D$6 M = $D$7 L = Name Final Value 16157.142857 6200 2600 Reduced Cost Objective Allowable Coefficient Increase 0 20.5 1.00000E+030 0 34 4.0714285714 0 42 3.3928571429 Constraints Cell $C$10 $C$11 $C$12 $C$13 $C$14 $C$15 $C$16 $C$17 $C$18 Name Min Demand for Small L = Max Demand for Small L = Min Demand for Medium L = Max Demand for Medium L = Min Demand for Large L = Max Demand for Large L = Bending/Forming Constraint L = Welding Constraint L = Painting Constraint L = Final Shadow Constraint Allowable Value Price R.H. Side Increase 16157.142857 0 14000 2157.1428571 16157.142857 0 21000 1.00000E+030 6200 -4.0714285714 6200 1161.5384615 6200 0 12500 1.00000E+030 2600 -3.3928571429 2600 974.19354839 2600 0 4200 1.00000E+030 12882.857143 0 23400 1.00000E+030 19014.285714 0 23400 1.00000E+030 46800 14.642857143 46800 6780 Allowable Decrease 1.5322580645 1.00000E+030 1.00000E+030 Allowable Decrease 1.00000E+030 4842.8571429 2607.6923077 6300 2187.0967742 1600 10517.142857 4385.7142857 3020 Microsoft Excel 14.0 Limits Report Worksheet: [Solver.xlsx]Question 2 Report Created: 3/26/2015 6:35:15 PM Objective Cell Name Value $D$3 Z = 651221.42857 Cell $D$5 $D$6 $D$7 Variable Name Value S= 16157.142857 M= 6200 L= 2600 Lower Objective Limit Result 14000 607000 6200 651221.42857 2600 651221.42857 Upper Objective Limit Result 16157.142857 651221.42857 6200 651221.42857 2600 651221.42857