Troubleshooting the Royal Hotel

BAN-230 A Spreadsheet Modeling and Decision Making

Assignment 3: due Monday, Oct 23, 2017

Submit BEFORE 11:59pm to Blackboard

Deliverables:

One Word file: Follow the requirements illustrated in the Assignment Sample. Use your name as the file name

One Excel file with five worksheets:

1. Problem 1 spreadsheet model
2. Problem 1 sensitivity report
3. Problem 2 spreadsheet model
4. Problem 2 sensitivity report

Note:

Late assignments will NOT be graded and can only get 50% of the full credits, unless a special permission is granted by the instructor.

(Source of the problems: Cliff T. Ragsdale, Spreadsheet Modeling and Decision Analysis, A Practical Introduction to Business Analytics, Cengage Learning)

1. (20 points) The CitruSun Corporation ships frozen orange juice concentrate from processing plants in Eustis and Clermont to distributors in Miami, Orlando, and Tallahassee. Each plant can produce 20 tons of concentrate each week. The company has just received orders of 10 tons from Miami for the coming week, 15 tons for Orlando, and 10 tons for Tallahassee. The cost per ton for supplying each of the distributors from each of the processing plants is shown in the following table.

The company wants to determine the least costly plan for filling their orders for the coming week.

1. Formulate an LP model for this problem

X13= cost per ton for Miami distributors processed from Eustis plant

X14 = cost per ton for Orlando distributors processed from Eustis plant

X15 = cost per ton for Tallahassee distributors processed from Eustis plant

X23= cost per ton for Miami distributors processed from Clermont plant

X24= cost per ton for Orlando distributors processed from Clermont plant

X25= cost per ton for Orlando distributors processed from Clermont plant

Min:                 260X13+220X14+290X15+230X23+240X24+310X25

Subject to:        X13+X14+X15<=20

                X23+X24+X25<=20

                X13+X23>=10

X14+X24>=15

X15+X25>=10,    X>=0

1. Implement the model in a spreadsheet and solve it. Paste your Sensitivity Report as a picture to your Word file

[pic 1]

[pic 2]

[pic 3][pic 4]

1. What is the optimal solution?

The optimal solution is X13=0,X14=10, X15=10, X23=10, X24=5, X25=0, minimized cost is $8600.

1. Is the optimal solution degenerate? Why?

The solution to an LP problem is degenerate if the Allowable Increase or Decrease on any constraint RHS is zero. However, according to the report, no RHS is zero. So not degenerate. None of the RHS values of any of the constraints have allowable increase or decrease of zero.

1.  Is the optimal solution unique? Why?

No, the solution is not unique. The allowable increase or decrease for the objective coefficients for more than one variable is equal to 0.

1. How would the optimal objective function value change if the plant in Clermont is forced to shut down for one day resulting in a loss of four tons of production capacity? Why?

The solution will not change because now we are only using 15 out of 20 tons of capacity that is available in Clermont. If we are losing 4 tons, we are within the 5 tons safety range.

1. What would the optimal objective function value be if the processing capacity in Eustis was reduced by five tons? Why?

The optimal objective function value is now 8700. The Eustis Used Constraint RHS is 20, 20(5)=100. 8600+100=8700.

1. Interpret the reduced cost for shipping from Eustis to Miami

The sensitivity Report shows that with every additional ton of concentrate shipeed from Eustis to Miami would increase cost by 50.