Question

West End Hospital is pursuing a sixty-two-bed expansion and would like to determine how many beds should be allocated to cardiac surgical patients versus oncology patients to maximize revenues. The average length of stay for cardiac surgical patients is 4.7 days whereas the average length of stay for oncology patients is seven days. Assume the hospital is open 365 days a year. The average revenue generated from from an oncology patient is $9,500 and the average revenue generated from a cardiac surgical patient is $10,750. The hospital currently has four hundred beds, and its laboratories, radiology department, and operating rooms have excess capacity and would be able to handle the increased demand from the expansion with existing staff. the laboratories could process up to an additional ten thousand tests annually. the average cardiac surgical patient requires 2.5 lab tests whereas the average oncology patient requires two lab tests. the radiology department can process up to an additonal five thousand imaging requests annually. the average cardiac surgical patient requires two imaging services whereas the oncology patient requires four. Last, fifteen hundred additional surgeries could be accommodated in the hospital's existing operating rooms.

a. Formulate this as a linear programming problem.

b. Solve the problem using Excel Solver.

c. What is the optimal allocation of beds to cardiac surgical versus oncology patients?

Please show answers using Excel!

Answer 1

Decision variables

Let 'x' be the no. of pages allocated to cardiac surgical patients

Let 'y' be the no. of pages allocated to oncology patients

=> No. of bed units available for cardiac patients annually = x * 365

Thus, no. of cardiac surgical patients that can be accomodated = (x * 365) / 4.7 = x * 77.66

=> No. of bed units available for oncology patients annually = y * 365

Thus, no. of oncology patients that can be accomodated = (y * 365) / 7 = y * 52.14

Objective

Maximize Z = 10750 * x * 77.66 + 9500 * y * 52.14

Constraints

1. x + y <= 62 {Total no. of additional beds are 62}

2. 2.5 * x * 77.66 + 2 * y * 52.14 <= 10000 {Lab can process additional 10000 tests (2.5-Cardiac, 2-Oncology)}

3. 2 * x * 77.66 + 4 * y * 52.14 <= 5000 {Radiology can process additional 5000 imagings (2-Cardiac, 4-Oncology)}

4. x * 77.66 + y * 52.14 <= 1500 {Operating rooms can process additional 1500 surgeries: Assuming every cardiac and oncology patient requires a surgery}

5. x, y >= 0

The optimal allocation is 19 beds to Cardiac surgical patients only and 0 beds to Oncology patients since there in not enough capacity in operating rooms to conduct more than 1500 surgeries