Question

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.

Please show all the formulas and how to do this in excel i will rate just after the question is answered

Answer 1

The problem is formulated in excel as below. The below 2 images show the formulation as well as the formulas used. The objective function to maximize is highlighted in blue and the decision variables are highlighted in green.

Post these the solver parameters need to be entered as per the below image.

Once this is done, click on solve. The solver will solve the problem and show the message stating the same. Click on ok. The excel will now have the final solution as per below image.

As we can see, the optimal solution is to produce 80 chairs, 10 desks and 10 tables. The maximum profit achievable is $7030