Troubleshooting the Royal Hotel

...

(Note: Show your spreadsheet model and the output report in Excel. Answer questions d ~ h with explanations and reference to the reports without re-running solver. To provide your reference to the reports, you need to point to a specific location in the report – e.g. “the allowable increase for ‘# to Make Hydro Luxes’ is 50”)

- (20 points) Sanderson Manufacturing produces ornate, decorate wood frame doors and windows. Each item produced goes through three manufacturing processes: cutting, sanding, and finishing. Each door produced requires 1 hour in cutting, 30 minutes in sanding, and 30 minutes in finishing. Each window requires 30 minutes in cutting, 45 minutes in sanding, and 1 hour in finishing. In the coming week, Sanderson has 40 hours of cutting capacity available, 40 hours of sanding capacity, and 60 hours of finishing capacity. Assume all doors produced can be sold for a profit of $500, and all windows can be sold for a profit of $400.

- Create a spreadsheet model for this problem, solve it using Solver, and obtain the Sensitivity report (Show your spreadsheet model and the output report in Excel. In addition to the usual screenshots, paste your Sensitivity Report as a picture to your Word file). Answer questions b ~ f by using information from the Sensitivity report without re-running solver

X1= number of decorative wood frame doors

X2= number of windows

Max: 500X1+400X2

Subject to: 60X1+30X2

30X1+45X2

30X1+60X2

X1, X2>=0

[pic 5]

[pic 6]

[pic 7]

[pic 8]

The optimal answer is X1=20 doors, X2=40 windows. Maximum profit 26000.

- If the profit on doors increased to $700, would the optimal solution change? Why?

No, the optimal solution stays the same. The objective coefficients on doors could increase by 300 without changing the optimal solution.

- If the profit on windows decreased to $200, would the optimal solution change? Why?

Yes, it would change. The allowable decrease on windows is 150, 200>150, answer would change.

- Interpret the value of the shadow price for the finishing process, and explain why this value makes sense.

The shadow price for the finishing process is $0 because there is a surplus of this resource.

- If 20 additional hours of cutting capacity became available, how much additional profit could the company earn? Carry out your calculation. Is there any condition to make your result valid?

5.83333*60*20=$7000 additional profit.

- Suppose another company wanted to use 15 hours of Sanderson’s sanding capacity and was willing to pay $400 per hour to acquire it. Should Sanderson agree to this? Why?

Shadow price on sanding is $300/unit, the allowable decrease is 20 units. If other company pays 400 for 15 hours, Sanderson gets(400-300)*15=1500 more profits.

...