Not able to understand formula under D24 and column O RHS formula? using excel s
ID: 470569 • Letter: N
Question
Not able to understand formula under D24 and column O RHS formula?
using excel solver, please help me on this thanks.
A hospital with ten operating rooms (OR) must schedule surgeries every day. Each operating room, when it is used, is budgeted for eight hours. There is a fixed cost of $4,800 to staff an open room, and once the OR is opened, this cost is incurred whether or not it is used. Assume that the duration of the procedures is known with certainty as given in the table below. The first column of the table below represents the surgeon identifier and the second column is the duration of the procedure in hours. A surgeon may have multiple procedures.
Procedures
Duration
s0390
2.17
s1532
2.58
s1802
2.75
s2734
6
s2734
5.5
s2770
1.83
s5173
3.33
s6820
1
s6930
4.67
s7099
3.25
s7270
2.75
s8487
4.67
s8512
4.25
s8526
2.5
s8534
0.58
s8534
0.42
s9564
1.67
s9564
1.17
Develop a Linear Integer Programming model to help the hospital efficiently (minimum cost) schedule the procedures. Assume that a surgeon can operate in two rooms at the same time.
Assume that all the surgeons with multiple procedures cannot be in two rooms at the same time, i.e., the procedures must be performed consecutively, even if the operating room is used more than the budgeted eight hours. Find the schedule that minimizes costs and satisfies the surgeons’ needs.
Assume that you can extend the use of operating rooms more than the 8 regular budgeted hours at a cost of $1600 per hour per OR. Find an optimal schedule that minimizes costs. (Hint: You do not need to consider the use of a linear model here. i.e., you can develop a nonlinear integer programming model).
Procedures
Duration
s0390
2.17
s1532
2.58
s1802
2.75
s2734
6
s2734
5.5
s2770
1.83
s5173
3.33
s6820
1
s6930
4.67
s7099
3.25
s7270
2.75
s8487
4.67
s8512
4.25
s8526
2.5
s8534
0.58
s8534
0.42
s9564
1.67
s9564
1.17
Explanation / Answer
The RHS in column O represents the surgeon can operate in maximum two ORs at a same time. But if multiple procedures are required surgeon should be in one OR.
Thus when procedures are not repeating the RNS for that procedure is 2 and when the procedure is repeating the RHS is 1.
The constraint $C$25 =$C$22, ensures that every procedure is planned and none of the procedure is left out. C22 is actual total of time required to complete all procedures whereas C25 is actual completion time.
Row D22 to M22 gives actual total time the OR is busy for procedure. Row D23 to M23 determines whether the completion time is more than 8 hours, if more than 8 hours it will give value 8 and if less than 8 hours it will give actual time the OR is busy.
The D24 to M24 calculates the whether the OR requires more than 8 hours, that is whether the OR requires overtime. If the OR requires more than 8 hours then the cell gives value C23 – C22, overtime required for the OR. If OR requires less than 8 hours then cell gives value 0 hours overtime required.
The cell O23 is fixed of the opening OR and O24 is cost of working overtime per hour per procedure.
Cell N23 gives count of optimal number of OR required to open.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.