Question

2Project 02 Using Linear Programming to Make Business 3Decisions chairs, rocking chairs, chaise lounges, and large tables. Each piece of furniture passes through three different departments: fabrication, assembly, and finishing, The table 8 below summarizes the amount of time each piece must spend in each department, the 9labor-hours available in each department, the profit from each piece, and the existing 10orders for each product. The company must produce a significant number of pieces to fill 12 13 14 15 Production Department (Labor-Hours per Unit) Product Fabrication Assembly Finishing Profit/Unit Orders 17 Small Table 18 Regular Chair 19 Rocking Chair 20 Chaise Lounge 21 Large Table 22 Available Labor-Hour 200 160 3 $ 24.00 180 2 $ 31.00 240 100 8.00 3$17.00 5$52.00 3500 2500 3000

Problem 1 (10 pts): Construct a mathematical model (define your variables, write an objective function and constraints).

Problem 2 (10 pts): Use Excel's Solver tool to determine the optimal solution that will maximize profit. Summarize your results. In the Solver toolbox, choose "Simplex LP".

Problem 3 (10 pts): Discuss the effect on the optimal solution in Problem 2 if the profit on a small table increases to $12. In the Solver toolbox, rchoose "Simplex LP". If you Copy/Paste from Problem 2, you will have to re-set all parameters in the Solver toolbox.

Problem 4 (10 pts): Discuss the effect on the optimal solution in Problem 2 if the profit on a large table decreases to $30. In the Solver toolbox, choose "Simplex LP". If you Copy/Paste from Problem 2, you will have to re-set all parameters in the Solver toolbox.

Problem 5 (10 pts): Discuss the effect on the optimal solution in Problem 2 if the orders for the rocking chairs are canceled (and the cancellation came before the rocking chairs went into production). In the Solver toolbox, choose "Simplex LP". In the Solver toolbox, choose "Simplex LP". If you Copy/Paste from Problem 2, you will have to re-set all parameters in the Solver toolbox.

Problem 6 (10 pts): Discuss the effect on the optimal solution in Problem 2 if the orders for the chaise lounges are increased to 300. In the Solver toolbox, choose "Simplex LP". In the Solver toolbox, choose "Simplex LP". If you Copy/Paste from Problem 2, you will have to re-set all parameters in the Solver toolbox.

Problem 7 (10 pts): Discuss the effect on the optimal solution in Problem 2 if the available hours in the assembly department are increased to 3300. In the Solver toolbox, choose "Simplex LP". In the Solver toolbox, choose "Simplex LP". If you Copy/Paste from Problem 2, you will have to re-set all parameters in the Solver toolbox.

Problem 8 (10 pts): Discuss the effect on the optimal solution in Problem 2 if the available hours in the fabrication department are increased to 2800. In the Solver toolbox, choose "Simplex LP". In the Solver toolbox, choose "Simplex LP". If you Copy/Paste from Problem 2, you will have to re-set all parameters in the Solver toolbox.