Question

OPRE 605.7BE Inc, Baltimore, has three flagship products: Absolute Sling Chairs, Awesome Tables, and Unbeatable Hammocks. The unit profit for these products is $45, $90, and $95, respectively. Each type of product requires manufacturing, transportation to warehouse, and marketing. The owners are very ambitious and can work 40 hours each week and so have 200 hours available each month. However, they are unable to spend more than 60 hours on any one activity (manufacturing, transportation, and marketing) in a month. The products are in such high demand that they are easily sold each month. It takes 30 minutes to manufacture one Absolute Sling Chairs. Transportation to warehouses takes 45 minutes and marketing takes 1 hour. Awesome tables take 2 hours for both the manufacturing and transportation phases, and marketing takes 1 hour. For Unbeatable Hammocks, manufacturing takes 0.4 hours; transportation takes 3 hours; and marketing also takes 1 hour.

How many of each product should OPRE 605.7BE Inc. produce each month to maximize profit?

a) Develop and implement a linear optimization model and clearly explain the sensitivity report.

b) Suppose that OPRE 605.7BE Inc. shareholders voted to limit the number of Absolute sling chairs to at most 25. How will the solution from part (a) change?

c) Suppose that OPRE 605.7BE Inc. gets an intern for the summer and therefore can increase the available hours for each activity by 5 hours each month. How will the solution from part (a) change?

Hint: for b) and c), you will need to just change the limitations but save each in a separate tab.

Answer 1

Solution :

Using excel solver,the LP is solved.
Please take note of the formulas and the inputs that need to be put in the solver dialog box. The screen shot for the same are given below.

Interpretation for sensitivity report
For the manufacturing constraint, it is non binding as the shadown price is zero. We also see there is slack of 12. This will be valid for an increase to infinite and a decrease of 12 units.

For the transport constraint, it is binding as the shadown price is 36. That is for one unit increase in the transport, will increase the objective function by 36. This will be valid for an increase upto 10 and a decrease of 12 units.

For the marketing constraint, it is binding as the shadown price is 18. That is for one unit increase in the transport, will increase the objective function by 18. This will be valid for an increase upto 20 and a decrease of 30 units.

Let me know in the comment section if anything is not clear. I will reply ASAP!
If you liked the answer, please give an upvote. This will be quite encouraging for me.Thank-you!