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.

Explanation & Answer

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Order Now and we will direct you to our Order Page at Litessays. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.