Question

Part 3 – Steelcase Production

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	6	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.

	Chairs	Desks	Tables
				Total
Carpentry
Finishing
Assembly
Chairs Needed
Profit Per Unit				Total Profit
Subtotal

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.

Answer 1

Process	chairs	desks	tables
carpentry	2	3	6	250
Finishing	1	1	1	100
Assembly	4	6	2	400
Profit	66	75	100
	chairs	desks	tables
	80	10	10
carpentry	250
Finishing	100
Assembly	400
Profit	Total profit   =		7030

excel commands are as follows