Need help with the following problem, am stuck trying to set it up correctly. Can you help with the objective function and constraints, also the formulas for solver?
The Lenox Hill hospital in New York City is a large, private, 600-bed facility complete with laboratories, operation rooms, and X-ray equipment. In seeking to increase revenues, Lenox Hill's administration has decided to make a 90-bed addition on a portion of adjacent land currently used for staff parking. The administrators feel that the labs, operating rooms, and X-ray department are not being fully utilized at present and do not need to be expanded to handle additional patients. The addition of 90 beds, however, involves deciding how many beds should be allocated to the medical staff for medical patients and how many to the surgical staff for surgical patients.
The hospital's accounting medical records departments have provided the following pertinent information. The average hospital stay for medical patient is eight days and the average medical patient generates $2,280 in revenues. The average surgical patient is in the hospital five days and receives a $1,515 bill. The laboratory is capable of handling 15,000 tests per year more than it was handling. The average medical patient requires 3.1 lab tests and the average surgical patient takes 2.6 lab tests. Furthermore, the average medical patient uses one X ray, while the average surgical patient requires 2 X rays. If the hospital were expanded by 90 beds, the X-ray department could handle up to 7,000 X rays without significant additional cost. Finally, the administration estimates that up to 2,800 additional operations could be performed in existing operating room facilities. Medical patients, of course, require no surgery, while each surgical patient generally has one surgery performed.
Formulate this problem so as to determine how many medical beds and how many surgical beds should be added in order to maximize revenues. Assume that the hospital is open 365 days a year. Then solve the problem.
A complete, Neat and Step-by-step Solution is provided in the attached Excel file.