2013 quiz,...

Due Monday December 2, 8 a.m.

A reminder of the rules: You areFORBIDDENto:

• Work with or talk with anyone else about the midterm. This is a solo effort – YOUR effort.
• Talk to anyone or ask anyone but this course’s TA or the Professor about any questions you may have.
• Give hints to another student about the exam or send them your Excel file.
• Review any past exams from prior years for this class.

Failure to abide by these rules will result in sanctions as described in the University's academic code of conduct and will result in a failing grade for the course.

Rules for Submission

(otherwise we spend as much time trying to figure out what's what as we do grading)

1. Type your name in cell G2 in the worksheet called Q21 to verify that you understand and have abided by the rules for this exam.

2. There are 20 multiple choice/fill in the blank questions online on Moodle. Be sure to complete those questions, AND the following questions below. For the questions on Moodle, you can save but not submit while you are working on the questions, and then submit ONLY when you have completed them. You can only submit your answers to the Moodle questions ONCE. The questions below should be completed in the excel file and uploaded to Moodle.

3. You will upload to Moodle the Excel File: Rename the Excel file: <yourlastname>.

4. Complete the questions online and upload your Excel file

The number in parentheses after each question number is how many points that problem is worth.

21. (8) Your hospital’s surgical services department has 8 Operating Rooms. The Operating Rooms are currently staffed from 7 am to 3 pm. Each room has its own staff.

Current demand for surgical services results in an average of 2 surgeries done per room per day.

Each surgical procedure (excluding setup and cleanup time) lasts 2.5 hours, on average.

Room Resource:Before each surgery, an OR must be set up, which takes on average 30 minutes. Thus, the room is unavailable to start a surgery until setup is complete. Set up for the next day’s surgical procedures cannot be done the day before – thus, although the staff arrives at 7 am, they must spend their first half hour of work setting up the ORs for that day’s first surgical procedure in each room. After each surgery, an OR must be cleaned, again, taking on average 30 minutes, making it unavailable for set up for the next surgery until cleanup has been complete. Thus, each surgery generates an hour of OR room ‘downtime’.

Staff Resource:Setup and cleanup are conducted by the OR staff, so the staff is available and working during set up and clean up times. However, each room’s staff takes a ½ hour break sometime during the 8 hour shift.

Complete the following daily capacity questions for the surgical services department in worksheet Q21.

Be sure your answers show formulas referring to cells, not just calculations (so, set it up as a model as indicated in the spreadsheet). If there is just an answer typed in the cell that we don’t know where it came from, or you will receive 0 credit even if you have the right answer.

Please note that you do NOT need to set up a big flow time analysis table like in the homework. Just use the table provided Excel sheet to enter the data, and then refer to those cells to answer the questions below in the appropriate Excel cells.

21a: If the ORs could be open 24 hours a day, the daily theoretical capacity of the hospital’s ORs would be: ________ surgical procedures (answer for the 8 ORs, not an individual OR)

However, since the ORs are staffed for only 8 hours a day, answer the remaining questions assuming 8 hours per OR per day, and provide an answer for all 8 ORs, not one individual OR.

21b: The daily theoretical capacity of the hospital’s ORs is: ________ surgical procedures

21c: The daily effective capacity of the hospital’s ORs is: ________ surgical procedures

21d: The daily actual throughput of the hospital’s ORs is: ________ surgical procedures

21e: The daily theoretical capacity of the hospital’s OR staffing is: ________ surgical procedures

21f: The daily effective capacity of the hospital’s OR staffing is: ________ surgical procedures

21g: Suppose both the setup and cleanup time could be reduced from 30 minutes each to 15 minutes each. The new daily effective capacity of the hospital’s ORs would be: ________ surgical procedures

1. A: worksheet Q22basecase

(30 points) Your hospital has an inpatient unit dedicated to patients who are admitted for diabetes related issues. Once a patient is admitted, the first thing that must happen is for the physician to order bloodwork. The steps of what transpires are shown below. All steps are sequential (one must be completed before the next can start), with the exception that steps 7&8 can be run concurrently to step 9 (assume lab tech is not needed for steps 7,8, and 9). When the lab tech assembles the results of the two tests in step 10, he finds that 20% of the time, there is an error, and steps 6,7,8, 9 and 10 must be repeated. (Assume he has kept a portion of the sample in the event that this rework is needed and that he never “runs out” of a sample that necessitates having to back up to re-draw more blood from the patient.) The process flow chart, and tables are shown on worksheet Q22 base case.

step	Description	Time	Type
1	MD orders bloodwork	10	Order
2	Health Unit Coordinator (HUC) writes up order	8	Transcribe
3	Lab Tech receives order and walks up to unit	15	Transport
4	Lab Tech draws the blood sample from the patient	10	Value Added
5	Lab Tech returns to the lab	15	Transport
6	Lab Tech splits the blood sample for two separate tests	5	Value Added
7 & 8	Test A has two parts, part 1 takes 10 minutes, part 2 takes 15 minutes*	10 (part 1) and 15 (part 2)	Value Added
9	Test B takes 20 minutes*	20	Value Added
10	Lab Tech assembles the results from Tests A and B	5	Inspection/ Decision
11	Computer operator enters the data into the computer for physician access	10	Data Entry

* Assume Lab Tech is NOT utilized for steps 7,8,9

For your resources, you have the following:

· Pts/batch = 1

· There is 1 MD, 1 HUC, 4 lab techs, 2 machines each for Test A1 & Test A2 and Test B, and 1 computer operator (# units in resource pool).

· On average, 35 patients arrive per day (assume 8 hour day) needing these tests completed, and they arrive in constant fashion throughout the day (no bunching of arrivals)

Complete the Flow Time Table and the Flow Capacity Table in worksheet Q22basecase.

Answer questions 1 through 5 cells F22, F23, F25, F49 and F50.

B: create 7 copies of your COMPLETED Q22basecase worksheets as follows:

Create a new completed worksheet (Excel 2010 instructions: make sure your cursor is sitting over the name tab of the worksheet at the bottom of the worksheet. Right click on the worksheet name, and choose ‘Move or Copy Sheet’. Click on the ‘Create a copy’ box and click on Q23 queue for the ‘before sheet’ choice.)

Repeat this 6 more times BEFORE STARTING EACH TABLE ENTRY BELOW (so you have a total of 7 sheets that will be completed once you have completed the basecase sheet).

Name each new worksheet as below to correspond to the question that is being asked:

Worksheet Name	Change
Q22-1	Theoretical flow time if Test B takes 10 minutes
Q22-2	Theoretical flow time if Test A part 1 takes 2 minutes
Q22-3	Theoretical flow time if Test B cannot start until Test A parts 1 and 2 have both been completed
Q22-4	Theoretical flow time if standing orders are automatically sent via computer to the lab for every admission, negating the need for Steps 1 and 2
Q22-5	Computer physician order entry is implemented, eliminating HUC writing up order; MD order time increases to 12 minutes.
Q22-6	Machines performing Test A1 and A2 are down for re-calibration 50% of the time: Effective bottleneck(s) of your process (change wording for result in F49 to be “effective” not “theoretical”) Effective capacity (pts/hr) of your process (change wording for result in F50 to be “effective” not “theoretical”)
Q22-7	Suppose the volume of patients has dropped to 24 per day, rather than 35. Assuming this is the only process that each resource works on, what is the percent of time each resource is IDLE. Idle means that the resource is sitting there waiting for something to work on. Create a table that looks like the following in worksheet Q22-7. Use references to cells (don’t type in ‘hardwired numbers’) to calculate % idle time. Resource % time idle MD HUC lab tech Test A1 Test A2 Test B Comp. Op.

23. (26) You are in a meeting in which the availability of hospital beds for the cardiology unit at the tertiary hospital of your Medical City is being discussed.

The cardiology unit has 50 beds (so the number of ‘servers’ is 50). From data you have collected, you have determined that the average length of stay (ALOS) for patients is 11.5 days, exponentially distributed. (So, since the ALOS is the service time, then what is the service rate?)

Your average daily arrival rate for patients needing a bed is 4 patients per day, Poisson distributed.

There always seems to be patients waiting for a bed, and it seems that the unit is often almost full. Many of the physicians are saying that the hospital needs to add more beds to the unit.

However, you are really, really, really intelligent, because you are taking an operations class at the University of Minnesota. You notice that there seem to be delays in patients getting their needed tests and procedures while they are in the hospital, and that physicians don’t make rounds first thing in the morning on the patients who are most likely to be able to be discharged on a given day, so that nurses can get those patients discharged as soon as possible to open up the bed for a new patient.

You believe that if you could get the hospital physicians, nurses, and other staff to ‘work smarter’ on patient flow issues, then the ALOS on the unit could decrease to 11 days (Exponentially distributed) and perhaps even 10 days.

Your colleagues don’t believe that decreasing the ALOS on the unit by half a day, or even 1 ½ days, could make much of a difference in the operating characteristics of the unit being so congested and patients waiting for a bed.

You know that you can do a queuing analysis using the queuing add-in to study this. The clinical staff have said that a patient shouldn’t have to wait for a bed any longer than 4 hours, so you will use 4 hours as your critical wait time (but be sure to convert that wait time into DAYS, since your other input is in days).

In the worksheet called Q23queue in the Excel file, use the queuing add-in to conduct this analysis. Conduct the queuing analysis 3 times, starting with an ALOS of 11.5 days, then 11 days, and finally 10 days, remembering to convert this service TIME into a service RATE for the data entry.

Enter all needed data (arrival rate, service rate, and number of servers, and critical wait time). Since you have 50 beds (servers), also enter the number 50 in the bottom right hand box called “Number of States to Show.”

Assume that the calling population and the queue capacity are infinite. (So, do NOT click the boxes that say ‘Finite Queue’ and ‘Finite Population’.)

Conduct the queuing analysis 3 times (ALOS = 11.5, 11, and 10 days, converting these service times into service rates before entering the data into the queuing addi-in). USE 3 DIGITS of precision to the right of the decimal point (round up the 3^rd digit to the right of the decimal point if the 4^th number to the right of the decimal point is .5 or greater.) Conduct each queuing analysis in the next column over from the previous one. So, your spreadsheet called 23queue should show your analyses from all three queuing models when you are done with this part of the problem.

After completing the queuing analysis, select the entire table, and do a copy, paste special, values, of your queuing results so that I can see your queuing results without getting the #NAME problem.

Please see that the Queuing add-in info is included as a separate worksheet in the Excel file to refresh your memory regarding what the output means.

See the instructions in homework #2 if you need to download the queuing add in from the jensen ORMM website.

Now reference the appropriate data from the queuing results to complete the table on the worksheet called Q23results.

1. (4) You are in a meeting where you have been asked to do a really quick analysis of the projected # of additional ICU/CCU beds needed in your hospital in 5 years, You have the following projections in column B of the worksheet called Q24on the incremental increases in # of new admissions in 5 years by patient bed type (cardiology, critical care, and other product lines). You also have data on the average LOS for each of these patient types (Col C) and % days they will spend in the ICU/CCU (col E).

Complete the cells in columns D, F, and G, using formulas, assuming that these ALOS’s (ALOS-Average Length of Stay) and % Days ICU/CCU will hold true 5 years from now, and that you are planning to run the unit at 80% occupancy. Assume that 1 bed provides 365 days of bed capacity in a year.

Put the total number of additional ICU/CCU beds needed in cell G8. Be sure that you reference the cells in the formulas, and not type in numbers, because you know the next question from the planning group will be what if the ALOS goes down, or what if the % that are ICU/CCU beds goes up, and you want to make sure you can impress them by simply changing the numbers in columns to see the impact on the final result.

1. (12) In the worksheet called Q25 you have the date and time stamps of arrival to the ED for all arrivals in 2009. It is now January 2010, and you have been asked to create summary data that shows the minimum,50^th percentile, 80^th percentile, 90^th percentile, and maximum of number of arrivals by shift by day of week. Shift times are as follows (in military time):

1. day: hours 7 to 14:59
2. eve: hours 15 to 22:59
3. night: hour 23 on to 6:59

To complete this task, you will need to do the following. I have not provided specific instructions for each because you should be able to figure out how to do this from the homework you have completed.

a. Create a day of week column in the data (1-7, where 1=mon, 7=sun)

b. Create an hour of the day column in the data (a number from 0 and 23)

c. Create a shift column in the data (day, eve, nite) (Hint, you can either create a nested ‘if’ statement, or create a VLOOKUP table.)

d. Insert a pivot table

e. Create a pivot table that will provide you with each date’s number of arrivals by shift, filtered by weekday, and choose the first day of the week as filter (which is Monday). Since you don’t need the column or row totals, you can right click in the pivot table, choose pivot table options, then choose totals & filter tab, and unclick the two Grant Totals boxes.

f. Now you can highlight the cells from A1 through to the lower right of the pivot table (click in cell A1, then scroll to the lower right of the pivot table, and click in that cell while holding down the shift key), choose copy, and then put your cursor in cell E1, and paste.

g. Now with the new pivot table, you can choose the filter to be 2 (Tuesday).

h. Repeat for all days of the week.

i. At the bottom of the column for each shift for each day of the week, use the correct formulas to calculate the minimum, the three requested percentiles, and the maximum (so 5 calculations times 3 shifts times 7 days = 105 total calculations).

j. Create a graph of your data results so it’s easier to see visually the story your data are telling. Be sure the x axis has labels that identify the day of week and shift, not just auto-assigned numbers from Excel. You can add the shift name above each column of data you created, and above that row put in the day of the week name, and highlight these two rows when you select the data to create your graph, and this should result in the correct labels on the x axis. Below is an example visualization that shows variability in number of arrivals by shift by day of week.

Play around to see what data visualization you come up with that makes it easy to see the variability. For example, you could make only the 50%tile a solid line, and make the other lines dotted lines with less weight. This would draw one’s eye to the average, but provide a ‘background of variation’ to judge the variability.

To get a space between each of the days, I had blank cells between the days I plotted. Without those blank cells, the plots for each of the days will be connected. You won’t get points taken off for this, but I just wanted to give you information on how I was able to get the space between each day in the t below.