Question

Brown Furniture Company makes three kinds of office furniture: chairs, desks, and tables. Each product requires skilled labor in the parts fabrication department, unskilled labor in the assembly department, machining on some key pieces of equipment, and some wood as raw material. At current prices, the unit profit contribution for each product is known, and the company can sell everything it manufactures. The size of the workforce has been established, so the number of skilled and unskilled labor hours is known. The time available on the relevant equipment has also been dete1mined, and a known quantity of wood can be obtained each month under a contract with a wood supplier. The data shown below summarizes the parameters of the problem.

Requirements per Unit Tables Resources Available Desks 6 Chairs 2000 hour:s 1800 hours 1600 hours 9400 sq. ft 2 6 4 25 $14 4 Fabrication (hours) Assembly (hours) Machining (hours) Wood (sq. ft) Profit per unit 6 40 $20 30 $16

Brown Furniture Company would like to determine the best combination of furniture to make (adhering to the resource restrictions for the current month) to maximize its profit.

a) Write the Linear Program formulation for the above optimization problem. Make your fo1mulation clear by defining the variables, constraints, objective function, etc. clearly.

b) Solve it using Excel Solver. Please attach your Solver model and output (including Answer and Sensitivity Report) as Exhibit A.

As part of Exhibit A please attach 1) your spreadsheet model with formulas printed. To do this click on Ctrl-~ (the tilde key). Excel will then display all formulas. Then print your spreadsheet. Format so that it fits on 1-page landscape or portrait. 2) Your Solver dialog box. To do this when you are in the Solver dialog box click on Alt-PrtSc. Then go into any program like word and click on Paste (or Ctrl-V). Your Solver dialog box will be pasted.

Answer 1

The LP problem is Max z = 16x_1 + 20x_2 + 14x_3 subject to 4x_1 + 6x_2 + 2x_3 lessthanorequalto 2000 3x_1 + 8x_2 + 6x_3 lessthanorequalto 1800 9x_1 + 6x_2 + 4x_3 lessthanorequalto 1600 30x_1 + 40 x_2 + 25 x_3 lessthanorequalto 9400 x_1, x_2, x_3 greaterthanorequalto 0. Here x_1 rightarrow chairs x_2 rightarrow Desks x_3 rightarrow Tables. Simplex method After introducing slack variables max Z = 16 x_1 + 20 x_2 + 14 x_3 + 0 S_1 + 0 S_2 + 0 S_3 + 0 S_4 subject to 4x_1 + 6x_2 + 2x_3 + S_1 = 2000 3x_1 + 8x_2 + 6x_3 + S_2 = 1800 9x_1 + 6x_2 + 4x_3 + S_3 = 1600 30x_1 + 40x_2 + 25x_3 + S_4 = 9400 By simplex method solver, x_1 = 40, x_2 = 180, x_3 = 40 Max z = 4800