Using Excel

Purpose: This project is to apply linear programming model to decide which surgery should be assigned to whichblock to reduce idle time in order to improve operating rooms utilization rate to meet industrial standard which is above 80%.

Problem Description:

Let us assume that one hospital in Cincinnati area has 3 operating (OR) room types (Table1) used by 5 total surgical groups (Table 2), which need to be allocated time in the master schedule. These ORs open five days a week (Table 3). The operating hours for all ORs varied from 6.5 hours to 9 hours per day. The total target hours are 392 for all surgical groups with each surgical group having specific target hours (Table 4).

The index i is associate with specific OR type.

Table 1: Three different OR Type
Index i	OR Type
0	Surgery and Gynecology
1	Otolaryngology, Surgery, and Gynecology
2	Oral, Gynecology, and Ophthalmology

The index j is states one specific surgical group, which associated with doctors who work in ORs.

Table 2: Five Surgical Groups
Index j	Surgical Groups
0	Surgery
1	Gynecology
2	Ophthalmology
3	Otolaryngology
4	Oral Surgery

The index k specifies the day of the week.

Table 3: Five Days of the Week
Index k	Surgical Groups
0	Monday
1	Tuesday
2	Wednesday
3	Thursday
4	Friday

The following table specify the target allocation hours for each group.

Table 4: tj: target allocation for group j
t0	189.0
t1	117.4
t2	39.4
t3	26.3
t4	19.9
Total:	392

The following are some requirements must met while scheduling surgery:

1. The number of operating rooms of type iassigned to all groups on the kthday must be less than or equal to the total number of operating rooms of that type, denoted by aik (Table 5)

Table 5: total number of ORs of type i on day k: aik
Notation	Total # of ORs can be used	Example
a0k	6	E.g. From Monday to Friday, total number of ORs for type 0 (Surgery and Gynecology) can be 6.
a1k	1	E.g. From Monday to Friday, total number of ORs for type 1 (Otolaryngology, Surgery, and Gynecology) can be 1.
a2k	3	E.g. From Monday to Friday, total number of ORs for type 2 (Oral, Gynecology, and Ophthalmology) can be 3.

2. The number of ORs of all types assigned to the jthgroup on the kthday of the week must not exceed the number of doctors in that group, denoted by pjk (Table 6)

Table 6: total number of doctors in group jon day k:pjk

Notation

Total # of doctors can be schedules

Example

p_0k

5

E.g. From Monday to Friday, for group 0 (Surgery), total number of doctors can be 5.

p_1k

1

E.g. From Monday to Friday, for group 1 (Gynecology), total number of doctors can be 5

p_2k

4

E.g. From Monday to Friday, for group 2 (Ophthalmology), total number of doctors can be 4

p_3k

2

E.g. From Monday to Friday, for group 3 (Otolaryngology), total number of doctors can be 4

p_4k

3

E.g. From Monday to Friday, for group 4 (Oral Surgery), total number of doctors can be 4

Instruction:

1. Formulate the scheduling problem with objective function subjects to constraints.
2. Run the formulation by using the standard Excel Solver Add-In.
3. Obtained the following results:

• the objective function value
• the total weekly hours allocated for all surgical groups in a given week
• the utilization rate(dividing the allocated hours by the total capacity of 392 hours)
• 4 In your workbook, Sample Schedule Sheet, draft a sample schedule, with days of the week in the rows, OR types in the columns, and the associated number of hours within each shift in the body of the table.Make sure that your schedule is easy to read and easy to understand.