Troubleshooting the Royal Hotel
Essay Preview: Troubleshooting the Royal Hotel
Report this essay
BAN-230 A Spreadsheet Modeling and Decision MakingAssignment 3: due Monday, Oct 23, 2017Submit BEFORE 11:59pm to BlackboardDeliverables:One Word file: Follow the requirements illustrated in the Assignment Sample. Use your name as the file nameOne Excel file with five worksheets:Problem 1 spreadsheet modelProblem 1 sensitivity reportProblem 2 spreadsheet modelProblem 2 sensitivity reportNote: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)(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.MiamiOrlandoTallahasseeEustis$260$220$290Clermont$230$240$310The company wants to determine the least costly plan for filling their orders for the coming week.Formulate an LP model for this problemX13= cost per ton for Miami distributors processed from Eustis plantX14 = cost per ton for Orlando distributors processed from Eustis plantX15 = cost per ton for Tallahassee distributors processed from Eustis plantX23= cost per ton for Miami distributors processed from Clermont plantX24= cost per ton for Orlando distributors processed from Clermont plant
X25= cost per ton for Orlando distributors processed from Clermont plantMin: 260X13+220X14+290X15+230X23+240X24+310X25Subject to: X13+X14+X15<=20 X23+X24+X25<=20 X13+X23>=10X14+X24>=15X15+X25>=10, X>=0Implement 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]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.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. 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.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.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.Interpret the reduced cost for shipping from Eustis to MiamiThe sensitivity Report shows that with every additional ton of concentrate shipeed from Eustis to Miami would increase cost by 50.