PLEASE USE SCREENSHOTS AND EXPLAIN ANSWERS

PROVIDE ALL FORMULAS USED

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 Total Carpentry Finishing Assembly Chairs Needed Profit Per Unit Total Profit Subtotal