A furniture company manufactures desks and chairs. Each desk uses four units of wood and requires two workers. Each chair uses three units of wood and requires one worker. A desk contributes $600 to profit, and a chair contributes $250. Marketing restrictions require that the number of chairs produced be at least twice the number of desks produced. There are currently 300 workers and 900 units of wood available.
Define the decision variables necessary for the LP formulation as follows:
X = # Desks to manufacture
Y = # Chairs to manufacture
1) Formulate appropriately as an LP to maximize profit. Provide the algebraic formulation in your submission. Must be neatly presented. (Note: Integer constraints are NOT needed in this problem.)
2) Input and solve the formulation using Excel. After running Solver, generate both answer report and sensitivity report.
3) Answer the following questions in the Sensitivity Report worksheet. DO NOT modify or rerun the model. You must explain all the steps on how you answered the question based on the output of the one standard LP output which can include Excel’s sensitivity report. There is NO use of SolverTable Add-In with this problem. Just use the standard Solver output. If you feel that it is not possible to answer the question with the single output, explain why this is the case. (Hint: Refer to Chapter 3 slides 39-51 and textbook pg. 87-90)
a) How many desks will the company manufacture?
b) How many chairs will the company manufacture?
c) How much profit will the company receive from the optimal production strategy?
d) What happens to the optimal solution if the unit profit of a desk doubles? Must explain how you arrived to your answer based on the given output. Do not reformulate the LP to find out the implication.
e) What happens to the optimal solution if the unit profit of a chair decreases by 20%? Must explain how you arrived to your answer based on the given output. Do not reformulate the LP to find out the implication.
f) What happens to the optimal profit if the company loses 80 units of wood? Must explain how you arrived to your answer based on the given output. Do not reformulate the LP to find out the implication.
g) What happens to the optimal profit if the company loses 50 workers? Must explain how you arrived to your answer based on the given output. Do not reformulate the LP to find out the implication.
h) What happens to the optimal profit if the company hires another 70 workers? Must explain how you arrived to your answer based on the given output. Do not reformulate the LP to find out the implication.