50.1K

Verified Solution

Link Copied!

USING EXCEL COULD YOU SHOW ME HOW IS DONE

As indicated, there are six potential operators with different rates (Erin and Frank are the professional staff members, the others are graduate students). Each operator is guaranteed a certain number of minimum hours per week. This is set at 8 hours per week for the graduate students and 7 hours for the staff. The facility is to be operated from 8:00 AM to 10:00 PM, so each day needs staff to cover the 14 hours. We will assume that other people operate the facility on weekends. a) (10 pts) Formulate a linear program that minimizes the cost operating the system, given the wage rates and the constraints given above. You might consider a doubly subscripted variable to formulate the problem. b) (10 pts) Solve this problem using Excel Solver and indicate the optimal solution and the value of the decision variables. c) (5 pts) From your results, does it appear that the lowest paid individuals are the most utilized? Does this make sense? Why are the number of hours assigned greater than 5* 14 = 70? As you review your results, make sure that all your constraints are met. Monday 6 Operator Wage Rate Alison $25/hour Ben $26/hour Casey $24/hour Dan $23/hour Erin $30/hour Frank $28/hour 0 4 Maximum Hours Available Tuesday Wednesday Thursday 0 6 0 6 0 6 8 4 0 5 5 0 3 0 6 Friday 6 0 4 5 5 3 0 lool le lola As indicated, there are six potential operators with different rates (Erin and Frank are the professional staff members, the others are graduate students). Each operator is guaranteed a certain number of minimum hours per week. This is set at 8 hours per week for the graduate students and 7 hours for the staff. The facility is to be operated from 8:00 AM to 10:00 PM, so each day needs staff to cover the 14 hours. We will assume that other people operate the facility on weekends. a) (10 pts) Formulate a linear program that minimizes the cost operating the system, given the wage rates and the constraints given above. You might consider a doubly subscripted variable to formulate the problem. b) (10 pts) Solve this problem using Excel Solver and indicate the optimal solution and the value of the decision variables. c) (5 pts) From your results, does it appear that the lowest paid individuals are the most utilized? Does this make sense? Why are the number of hours assigned greater than 5* 14 = 70? As you review your results, make sure that all your constraints are met. Monday 6 Operator Wage Rate Alison $25/hour Ben $26/hour Casey $24/hour Dan $23/hour Erin $30/hour Frank $28/hour 0 4 Maximum Hours Available Tuesday Wednesday Thursday 0 6 0 6 0 6 8 4 0 5 5 0 3 0 6 Friday 6 0 4 5 5 3 0 lool le lola

Answer & Explanation Solved by verified expert

See Answer