Reference no: EM132563006

Advanced Spreadsheets and Decision Support Systems

Project Specifications

An operating theatre manager regularly needs to schedule operations for a list of patients. All operations are equally urgent. Operating theatres are an expensive resource in the hospital so the manager wants to use them as efficiently as possible, by scheduling the operations to take place over as few days as possible.

There are two surgeons who carry out the operations in separate operating theatres. The surgeons expect to work six-hour days on average and will be very unhappy if they regularly work over seven and a half hours a day.

For each patient on the list, the manager knows which of the two surgeons will carry out the operation and, based on hospital records, has calculated the expected (mean) duration and standard deviation for similar operations.

A simple way of finding a feasible (but possibly sub-optimal) schedule for this problem is to use a greedy heuristic. You will use the following greedy heuristic for this particular scheduling problem:

1. Select a surgeon S whose schedule has not been planned.

2. Create an ordered list L of patients who require treatment by that surgeon in descending order of expected operation duration.

3. Consider patient P at the top of list L awaiting operation O. Add P to the earliest acceptable daily schedule for surgeon S. A daily schedule is acceptable for surgeon S if adding operation O will not:

a) take the total expected duration of operations for that day over 6 hours, or
b) take the total expected duration of operations on that day plus the sum of standard deviationsfor operations on that day over 7.5 hours.

If no such acceptable daily schedule exists, then create a new daily schedule at the end of the current schedule for surgeon S to accommodate patient P.

4. Remove patient P from list L.

5. If list L is not empty, then return to Step 3.

6. If there are surgeons whose schedules have not been planned, return to Step 1.

In order to make the heuristic deterministic (i.e. to guarantee the same result each time it is run on the same problem instance), we need specify how ties are broken in Step 2. Ties should be broken by selecting the patient with the higher standard deviation for operation duration first. If two patients have the same expected operation duration and standard deviation for operation duration, then the patient with the smaller index (based on the original list of patients) should be chosen first.

You must implement the greedy heuristic for the theatre scheduling problem in a VBA project in Excel. Your programme must be capable of:

i) Reading in any sized data table
ii) Running the greedy heuristic
iii) Outputting the results

You are encouraged to embellish your application by using buttons, tables, charts, and forms according to your personal taste. You can also add other functionalities that you consider useful (e.g., you may want to create aUserForm to get the origin and destination from the user). Initiative and creativity will be rewarded!

Instructions

The file must include all essential VBA macros as well as any relevant instructions for how to use the model. No other file will be accepted or marked (e.g. a Word file describing the model).

Attachment:- Advanced Spreadsheets and Decision Support Systems.rar