Steelcase Corporation manufactures 3 basic products: chairs, desks, and tables.
ID: 3348521 • Letter: S
Question
Steelcase Corporation manufactures 3 basic products: chairs, desks, and tables. Below is chart which summarizes the number of labor hours spent for each product in each division.
Chairs
Desks
Tables
Process
Carpentry
2
3
6
Finishing
1
1
1
Assembly
4
5
2
In a given week, Steelcase has 250 hours available for carpentry, 100 hours available for finishing, and 400 hours available for assembly.
Steelcase makes a profit of $66 on each chair, $75 on each desk, and $100 on each table that they sell.
Steelcase also needs to produce at least one chair for every desk they produce, and 4 chairs for every table they produce. The total number of chairs must be greater than or equal to the sum of the chairs needed for desks and tables. They can produce more chairs on their own too.
How many chairs, desks, and tables should Steelcase manufacture each week in order to maximize profit?
Set up an Excel Spreadsheet to solve this linear programming problem. You may want to use the template below.
Excel has a function that will solve linear programming problems like this one. To access this function, go to the “Data” tab and select “Solver”. A window opens in which you need to set the following parameters for the LPP.
Set Target Cell – Select the cell that represents the objective function. This should be the value that you want to maximize or minimize. Do not hard code (enter a specific number into) this cell.
Equal To – Choose whether you want to maximize, minimize, or set a specific value for your objective function.
By Changing Cells – Select the cells that represent your decision variables. Do not hard code (enter specific numbers into) these cells.
Subject to the Constraints – To set up your constraints, choose “Add”.Now you can set up your inequalities. One set of inequalities is that the number of labor hours used must be less than the number of hours available. Also make sure that the number of units manufactured is less than or equal to the number of units demanded. Finally, make sure that the decision variables are nonnegative.
Once you have set up the LPP, click “Solve”. If there is a solution, Excel will fill in the spreadsheet with the values that will solve the LPP.
Use Excel to solve this problem. Highlight the optimal decision variables in green and the maximum profit in blue.
Chairs
Desks
Tables
Process
Carpentry
2
3
6
Finishing
1
1
1
Assembly
4
5
2
Chairs Desks Tables Total Carpentry Finishing Assembly Chairs Needed Profit Per Unit Total Profit SubtotalExplanation / Answer
let x be the number of chairs
y be the number of desks
z be the number of tables
the constraints are :
carpentary : 2x+3y+6z <= 250
Finishing : x+y+z <= 100
Assemble : 4x+5y+2z <= 400
atleast 1 chair for every desk : x>= y
atleast 4 chairs for every table : x >= 4z
total number of chairs : x >= y + 4z
x , y , z >= 0
the profit function maximize Z = 66x + 75y + 100z
we could use the simplex method to solve the linear programming model :
Tableau #1
x y z s1 s2 s3 s4 s5 s6 s7 s8 s9 z
2 3 6 1 0 0 0 0 0 0 0 0 0 250
1 1 1 0 1 0 0 0 0 0 0 0 0 100
4 5 2 0 0 1 0 0 0 0 0 0 0 400
1 -1 0 0 0 0 -1 0 0 0 0 0 0 0
1 0 -4 0 0 0 0 -1 0 0 0 0 0 0
1 -1 -4 0 0 0 0 0 -1 0 0 0 0 0
1 0 0 0 0 0 0 0 0 -1 0 0 0 0
0 1 0 0 0 0 0 0 0 0 -1 0 0 0
0 0 1 0 0 0 0 0 0 0 0 -1 0 0
-66 -75 -100 0 0 0 0 0 0 0 0 0 1 0
Tableau #2
x y z s1 s2 s3 s4 s5 s6 s7 s8 s9 z
2 3 6 1 0 0 0 0 0 0 0 0 0 250
1 1 1 0 1 0 0 0 0 0 0 0 0 100
4 5 2 0 0 1 0 0 0 0 0 0 0 400
-1 1 0 0 0 0 1 0 0 0 0 0 0 0
1 0 -4 0 0 0 0 -1 0 0 0 0 0 0
1 -1 -4 0 0 0 0 0 -1 0 0 0 0 0
1 0 0 0 0 0 0 0 0 -1 0 0 0 0
0 1 0 0 0 0 0 0 0 0 -1 0 0 0
0 0 1 0 0 0 0 0 0 0 0 -1 0 0
-66 -75 -100 0 0 0 0 0 0 0 0 0 1 0
Tableau #3
x y z s1 s2 s3 s4 s5 s6 s7 s8 s9 z
2 3 6 1 0 0 0 0 0 0 0 0 0 250
1 1 1 0 1 0 0 0 0 0 0 0 0 100
4 5 2 0 0 1 0 0 0 0 0 0 0 400
-1 1 0 0 0 0 1 0 0 0 0 0 0 0
-1 0 4 0 0 0 0 1 0 0 0 0 0 0
1 -1 -4 0 0 0 0 0 -1 0 0 0 0 0
1 0 0 0 0 0 0 0 0 -1 0 0 0 0
0 1 0 0 0 0 0 0 0 0 -1 0 0 0
0 0 1 0 0 0 0 0 0 0 0 -1 0 0
-66 -75 -100 0 0 0 0 0 0 0 0 0 1 0
Tableau #4
x y z s1 s2 s3 s4 s5 s6 s7 s8 s9 z
2 3 6 1 0 0 0 0 0 0 0 0 0 250
1 1 1 0 1 0 0 0 0 0 0 0 0 100
4 5 2 0 0 1 0 0 0 0 0 0 0 400
-1 1 0 0 0 0 1 0 0 0 0 0 0 0
-1 0 4 0 0 0 0 1 0 0 0 0 0 0
-1 1 4 0 0 0 0 0 1 0 0 0 0 0
1 0 0 0 0 0 0 0 0 -1 0 0 0 0
0 1 0 0 0 0 0 0 0 0 -1 0 0 0
0 0 1 0 0 0 0 0 0 0 0 -1 0 0
-66 -75 -100 0 0 0 0 0 0 0 0 0 1 0
Tableau #5
x y z s1 s2 s3 s4 s5 s6 s7 s8 s9 z
2 3 6 1 0 0 0 0 0 0 0 0 0 250
1 1 1 0 1 0 0 0 0 0 0 0 0 100
4 5 2 0 0 1 0 0 0 0 0 0 0 400
-1 1 0 0 0 0 1 0 0 0 0 0 0 0
-1 0 4 0 0 0 0 1 0 0 0 0 0 0
-1 1 4 0 0 0 0 0 1 0 0 0 0 0
-1 0 0 0 0 0 0 0 0 1 0 0 0 0
0 1 0 0 0 0 0 0 0 0 -1 0 0 0
0 0 1 0 0 0 0 0 0 0 0 -1 0 0
-66 -75 -100 0 0 0 0 0 0 0 0 0 1 0
Tableau #6
x y z s1 s2 s3 s4 s5 s6 s7 s8 s9 z
2 3 6 1 0 0 0 0 0 0 0 0 0 250
1 1 1 0 1 0 0 0 0 0 0 0 0 100
4 5 2 0 0 1 0 0 0 0 0 0 0 400
-1 1 0 0 0 0 1 0 0 0 0 0 0 0
-1 0 4 0 0 0 0 1 0 0 0 0 0 0
-1 1 4 0 0 0 0 0 1 0 0 0 0 0
-1 0 0 0 0 0 0 0 0 1 0 0 0 0
0 -1 0 0 0 0 0 0 0 0 1 0 0 0
0 0 1 0 0 0 0 0 0 0 0 -1 0 0
-66 -75 -100 0 0 0 0 0 0 0 0 0 1 0
Tableau #7
x y z s1 s2 s3 s4 s5 s6 s7 s8 s9 z
2 3 6 1 0 0 0 0 0 0 0 0 0 250
1 1 1 0 1 0 0 0 0 0 0 0 0 100
4 5 2 0 0 1 0 0 0 0 0 0 0 400
-1 1 0 0 0 0 1 0 0 0 0 0 0 0
-1 0 4 0 0 0 0 1 0 0 0 0 0 0
-1 1 4 0 0 0 0 0 1 0 0 0 0 0
-1 0 0 0 0 0 0 0 0 1 0 0 0 0
0 -1 0 0 0 0 0 0 0 0 1 0 0 0
0 0 -1 0 0 0 0 0 0 0 0 1 0 0
-66 -75 -100 0 0 0 0 0 0 0 0 0 1 0
Tableau #8
x y z s1 s2 s3 s4 s5 s6 s7 s8 s9 z
3.5 1.5 0 1 0 0 0 0 -1.5 0 0 0 0 250
1.3 0.75 0 0 1 0 0 0 -0.25 0 0 0 0 100
4.5 4.5 0 0 0 1 0 0 -0.5 0 0 0 0 400
-1 1 0 0 0 0 1 0 0 0 0 0 0 0
0 -1 0 0 0 0 0 1 -1 0 0 0 0 0
-0.25 0.25 1 0 0 0 0 0 0.25 0 0 0 0 0
-1 0 0 0 0 0 0 0 0 1 0 0 0 0
0 -1 0 0 0 0 0 0 0 0 1 0 0 0
-0.25 0.25 0 0 0 0 0 0 0.25 0 0 1 0 0
-91 -50 0 0 0 0 0 0 25 0 0 0 1 0
Tableau #9
x y z s1 s2 s3 s4 s5 s6 s7 s8 s9 z
1 0.43 0 0.29 0 0 0 0 -0.43 0 0 0 0 71
0 0.21 0 -0.36 1 0 0 0 0.29 0 0 0 0 11
0 2.6 0 -1.3 0 1 0 0 1.4 0 0 0 0 79
0 1.4 0 0.29 0 0 1 0 -0.43 0 0 0 0 71
0 -1 0 0 0 0 0 1 -1 0 0 0 0 0
0 0.36 1 0.071 0 0 0 0 0.14 0 0 0 0 18
0 0.43 0 0.29 0 0 0 0 -0.43 1 0 0 0 71
0 -1 0 0 0 0 0 0 0 0 1 0 0 0
0 0.36 0 0.071 0 0 0 0 0.14 0 0 1 0 18
0 -11 0 26 0 0 0 0 -14 0 0 0 1 6500
Tableau #10
x y z s1 s2 s3 s4 s5 s6 s7 s8 s9 z
1 0.75 0 -0.25 1.5 0 0 0 0 0 0 0 0 87
0 0.75 0 -1.2 3.5 0 0 0 1 0 0 0 0 37
0 1.5 0 0.5 -5 1 0 0 0 0 0 0 0 25
0 1.8 0 -0.25 1.5 0 1 0 0 0 0 0 0 87
0 -0.25 0 -1.3 3.5 0 0 1 0 0 0 0 0 37
0 0.25 1 0.25 -0.5 0 0 0 0 0 0 0 0 13
0 0.75 0 -0.25 1.5 0 0 0 0 1 0 0 0 87
0 -1 0 0 0 0 0 0 0 0 1 0 0 0
0 0.25 0 0.25 -0.5 0 0 0 0 0 0 1 0 13
0 -0.5 0 8.5 49 0 0 0 0 0 0 0 1 7000
Tableau #11
x y z s1 s2 s3 s4 s5 s6 s7 s8 s9 z
1 0 0 -0.5 4 -0.5 0 0 0 0 0 0 0 75
0 0 0 -1.5 6 -0.5 0 0 1 0 0 0 0 25
0 1 0 0.33 -3.3 0.67 0 0 0 0 0 0 0 17
0 0 0 -0.83 7.3 -1.2 1 0 0 0 0 0 0 58
0 0 0 -1.2 2.7 0.17 0 1 0 0 0 0 0 42
0 0 1 0.17 0.33 -0.17 0 0 0 0 0 0 0 8.3
0 0 0 -0.5 4 -0.5 0 0 0 1 0 0 0 75
0 0 0 0.33 -3.3 0.67 0 0 0 0 1 0 0 17
0 0 0 0.17 0.33 -0.17 0 0 0 0 0 1 0 8.3
0 0 0 8.7 47 0.33 0 0 0 0 0 0 1 7000
hence the maximum profit is Z =$ 7000
when x = 75 number of chairs
y = 17 number of desks
and z = 8.3 = 8 (nearest integer) number of tables are made
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.