Please find the question as an attachment. Also, please provide the answer in the attached Excel sheet. You will have to use the solver.
1. A hospital is trying how many RN's to hire to provide the 24-hour-day coverage it needs on the floor. The hospital is concerned with both costs for wages of RN's hired and level of care provided. As a start to this process of hiring, a minimum number of RN's required for different times has been estimated and is shown below. The 24-hour day has been divided into 7 segments of length 2, 4, or 8 hours. The minimum number of RN's required differs by time of day due to many factors; the minimum numbers of RN's required for each of the seven time segments are:
Time minimum # RNs required
6 am - 10 am 30
10 am - noon 25
noon - 2 pm 28
2 pm - 4 pm 20
4 pm - 8 pm 22
8 pm - 10 pm 17
10 pm - 6 am 15
Though the economy in the region served by this hospital is weak, the region has the same nursing shortage the rest of the country has. The hospital is trying to compete by offering a set of work times that is flexible and competitive wages that include extra compensation for hours worked after 5:00 pm. There are five possible work schedules or shifts:
1) 8 hours from 6 am to 2 pm
2) 6 hours at 10 am to 4 pm
3) 8 hours at noon to 8 pm
4) 8 hours at 2 pm to 10 pm
5) 8 hours at 10 pm to 6 am
Usual wage, for hours worked between 6 am and 5 pm is $25 per hour. Work beginning at 5 pm through 10 pm is paid $30 per hour, and work between 10 pm and 6 am is paid $33 per hour. For example, an RN who works from 2 pm to 10 pm is paid 3 hours * $25/hour + 5 hours*$30/hr = $225 per day.
a. Formulate an algebraic linear programming model to find the minimum cost per day set of RN's to provide at least minimal coverage during the 24 hours.
b. Enter the parameters of your model into excel. Use excel solver to find the solution to your model. What are the solution and its total cost per day? Please include in your answer file your excel and Solver setup with the optimal solution, as instructed in the answer file instructions for question 26. What is the solution?
c. Which of the seven time segments are only minimally covered in your solution?
d. For each of the time segments indicated in part c (those that are only minimally covered), how much would total cost increase per unit increase of that segment's minimum number of RN's? Answer for each segment separately so that only one value changes at a time. For each answer, indicate by how much the minimum # can be increased (if it is the only one being changed) before the total increase in cost rate is no longer valid.
e. Do your answers in part d remain valid if the minimum number of RN's for all the time segments indicated in part c (those that are only minimally covered) are increased by 1 simultaneously? Please show work to justify and explain your answer. Hint: use 100% Rule.
f. Do your answers in part d remain valid if the minimum number of RN's for all seven of the time segments are increased by 1 simultaneously? Please show work to justify and explain your answer. Hint: use 100% Rule.
An algebraic programming problem is solved using Excel Solver.