Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
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 lolaStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started