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: .
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

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 3rd digit to the right of the
decimal point if the 4th 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.

(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.

(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,50th
percentile, 80th percentile, 90th percentile, and
maximum of number of arrivals by shift by day of week. Shift times are as follows (in military
time):

day: hours 7 to 14:59
eve: hours 15 to 22:59
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.

.0/msohtmlclip1/01/clip_image002.gif”>