UCLA Statistics Worksheet

UCLA HLTADM 413 Operations Management in HealthcareHomework Module 3
Little’s Law
As the operations management analyst, you have been asked by the leadership team to address three issues
that have arisen regarding reimbursement, billing, and patient census. There is a meeting tomorrow morning.
The team asked you mid-afternoon today to bring results with you to tomorrow’s meeting, and they are hoping
that you don’t have to stay too late tonight to provide answers to their questions. Unbeknownst to them, you
know Little’s Law!
One comment about why I switched back and forth between using I, R, T, and L, λ, and W.
You will see them referred to both ways in the literature and in use. I wanted you to get comfortable with being
able to make the translation in your head.
You gotta love Little’s law.
You will be submitting an Excel file with the three issues answered.
Point allocations for the problems are:
1. Problem 1: 10 points
2. Problem 2: 10 points
1. Problem 2: 30 points
The grading is based on completing the analytics correctly. Even if you arrive at the correct answers, full credit
will not be given if you hardwired in numbers into your formulas, rather than inserting the appropriate cell
references.
Note that in turning in your homework, you are asserting the following:
✔ The answers represent your own work. You did not copy answers from any other students, or access any
of this class’s previous years’ materials to complete this work.
✔ You can consult with other students in your class, but they should NOT send you their file, or a part of
their file, that you then copy and paste into your own file and claim as your own work, or copy their
write-up, changing a few words here and there. If a fellow student offers some help to you via zoom or
other online collaboration tool, you did NOT take screenshots of their work they may have shown you.
✔ Similarly, if you are being asked for help, you should not email your completed file to the student(s) who
is asking for help.
✔ To do any of these things constitutes academic misconduct, and will result in an F for the course.
To submit your work:
1. TYPE YOUR NAME in the Excel worksheet called Academic Conduct Signature to verify that the work you
are turning in represents your own work.
2. Rename the Excel file with hw3.
3. Submit the Excel file to Canvas. Do NOT email the file to me.
©Sandra Potthoff, PhD 2021
“Little’s Law”
Page 1
UCLA HLTADM 413 Operations Management in Healthcare
Problem 1: Volume and Reimbursement at the Heart Hospital
1. Your heart hospital (approximately 350 beds) has two types of heart DRG patient types that are admitted:
DRG1 and DRG2. On average, 35% are DRG1 with ALOS (average length of stay) of 4 days, while 65% are
DRG2 with ALOS of 2 days. On an average day, 75 patients are admitted to the hospital daily. The
reimbursement per patient per day for DRG1 is $2600, while the reimbursement per patient per day for
DRG2 is $2800.
Answer the following on Issue 1 worksheet by filling in all shaded cells. On average, how many of each
type of patient are in the hospital on any given day? How many times per month (30 days per month)
does the hospital turn over its “inventory” of patients? (Please provide answers in “days” not
“months”.) What is its average total monthly reimbursement?
Here are questions that have come up in the past.
1. Why is there a column for reimb/day (cells G9 and G10) and reimb/pt (cells H9 and H10)?
a. Answer: You are getting paid per day for each DRG patient type, and each DRG patient type has
a different ALOS. To calculate the reimb/pt, you need to multiply the reimb/day by the ALOS for
each DRG patient type.
2. Hints for completing the I = R x T table (or L = λ x W).
a. You know T outright. It is the ALOS, which is in DAYS
b. So, R has to be in DAYS. What proportion of your daily arrivals are DRG1? DRG2?
c. Now, you can calculate I, which is your average daily census. Isn’t Little’s Law amazing?
3. Cells E17 and E18. Which of the numbers you calculated above in the I = R x T table should go in these
cells if the question is asking for the average daily number of patients?
a. Well, you have 3 choices – is it I, R, or T from the table above?
4. Cell E20. Look at the information on how to calculate inventory turns in the lecture. But, your data is in
DAYS, and you want the turns per month, so you will need to multiply your result by the 30 days per
month (cell A12).
a. You know your daily arrival rate, and you know your daily inventory across both DRGs. Or, you
can take a weighted average of T (W) and use that to calculate the daily inventory turns. Then
multiply your number by the 30 to get the monthly rate.
5. Cell E21. How to calculate average monthly reimbursement.
a. First calculate the # pts/mo in cells I9 and I10 for each DRG patient type. The hint is that you will
need either I, R, or T from the table. Which one? And, then you need to multiply that number by
the 30 in cell A12 to get a monthly rate, since your table is in days.
b. Then in cells J9 and J10, you know the reimbursement per patient from H9 and H10, and the
number of patients per month in cells I9 and I10, so how do you get the total reimbursement in
cells J9 and J10? Then reference cells J9 and J10 in your answer for E21.
©Sandra Potthoff, PhD 2021
“Little’s Law”
Page 2
UCLA HLTADM 413 Operations Management in Healthcare
Problem 2: Billing
2. Your organization sends out 300,000 bills for services rendered each year. It takes, on average, 8 weeks to
process each bill.
a. Assuming 52 weeks a year, how many bills are in process at any given time, on average?
b. Suppose each bill represents, on average, $20,000 in payment owed to your organization. How
much potential revenue to your organization is sitting in your billing department, on average,
waiting to get billed?
c. Suppose you could reduce your cycle time to process a bill to 6 weeks. Now how many bills are
in process in your billing department, on average?
d. Under this new cycle time, how much potential revenue is now sitting in your billing department,
on average?
Set up a model similar to the structure provided for the Problem 1 Excel worksheet. That is, put the
input data into cells, and then refer to them when building formulas for your model. You will be showing
this model to your leadership team, so make it clear to these end users what this model is doing.
Here are questions that have come up in the past.
1. Can you provide a template for the layout of the worksheet?
a. Below is an example that would work.
b. Make sure that all of your units match. Some of the data are in years, and others are in weeks.
c. Are you being asked to solve for I, R, or T in questions a and c?
I
R
T
a.
weeks/yr
b.
payment/bill
I
R
T
c.
d.
©Sandra Potthoff, PhD 2021
“Little’s Law”
Page 3
UCLA HLTADM 413 Operations Management in Healthcare
Problem 3: OB and Nursery Census
You have been asked to estimate the average census of the OB Unit and the Newborn Nursery for the year 2018.
You know you can use the timestamp data from the Admission, Discharge, Transfer (ADT) data system to
estimate the average length of stay (W of Little’s Law) and the daily arrival rate (λ of Little’s Law). Using Little’s
Law, you can then calculate the average daily census (L).
As soon as you got the request from the leadership team, you asked the database manager to create a dataset
for you to analyze. You asked for the timestamps for any woman who was in the OB unit and any newborn who
was in the Nursery at any time during the year 2018. This means that it should have the following admit and
discharge dates:
●
●
●
Admit Date in 2017 but Discharge date in 2018
Admit Date in 2018 and Discharge date in 2018
Admit Date in 2018 but Discharge date in 2019
You asked for the following data fields: 1) Admit date; 2) Discharge date; 3) DRG (Diagnostic Related Group)
Number and Description; and 4) Major Diagnostic Category (MDC) Number and Description. The database
manager did pull the data asap, but then had to leave for the day. You opened the dataset in the worksheet
called Problem 3 OB_Nursery to begin your analysis.
Here are the steps to complete your analysis.
1) Always check the dataset to ensure that what you asked for is what you received. Sort the data by admit
date as the primary sort, and discharge date as the secondary sort. Here are the steps to do that:
a) Highlight the entire worksheet by left clicking in the box at the far left just above Row 1 and just to the
left of Column A. This highlights the entire worksheet.
b) With the Home Tab selected, go to the far right for the Editing group, and choose the down arrow for
the Sort & Filter option. Then choose Custom Sort.
c) On the textbox that appears, click in the box in the upper right by the text that says “My data has
headers.” Next, click on the down arrow for the box to the right of the Sort by option. Choose Admit_DT.
By default, Sort On should say Values, and Order should say Oldest to Newest. If they do not, choose the
down arrows for each of these to change them.
d) Click on the Add Level button at the upper left of the textbox. This adds another option for a secondary
sort. Click on the down arrow for the box to the right of the Then by option. Choose Disch_DT. By
default, Sort On should say Values, and Order should say Oldest to Newest. If they do not, choose the
down arrows for each of these to change them.
©Sandra Potthoff, PhD 2021
“Little’s Law”
Page 4
UCLA HLTADM 413 Operations Management in Healthcare
e) Click OK in the bottom right.
f)
Review the first record in the dataset. Is it a record with an Admit_DT in 2017, but a Disch_DT in 2018?
Yes, it does, and this is good. Then, there are many records with both the Admit_DT and Disch_DT in
2018, as expected. Now scroll down to the bottom of the records to check if there are any that have and
Admit_DT in 2018, but a Disch_DT in 2019. Are there any? Yes, not many, but at least one. This gives you
confidence that the dataset you asked for is the one that you received.
2) There is a column with a header of “LOS”. This means Length of Stay. Calculate the LOS by subtracting the
Admit_DT from the Disch_DT for this column. (Put the formula in the first cell in this row, and then double
click on the bottom right hand corner of the cell to copy it all the way down.)
a) To check the range of LOS values, highlight the LOS column, and With the Home Tab selected, go to the
far right for the Editing group, and choose the down arrow for the Sort & Filter option. Then choose
Filter.
b) This puts a down arrow in the LOS column. Click on the down arrow. There are some records with
LOS=0. Review these in more detail. To do this, click on the Select All box and then click on the box in
front of the 0. Then choose OK. This will show just the records with a LOS=0. For these records, the
Admit_DT =Disch_DT.
c) Clear the filter by going back up to the far right for the Editing group, and choose the down arrow for
the Sort & Filter option. Then choose Filter. This should clear the filter.
d) Unfortunately, you did not ask for the timestamp to get the time of day to calculate a partial day LOS. To
account for these LOS=0, revise the calculation for the LOS column using an IF statement to set the
LOS=1. The IF statement logic is that if the ADMIT_DT=Disch_DT, then 1, else Disch_DT – Admit_DT.
Revise the formula in the LOS column. This accounts for the patient being in the hospital for at least a
partial day, so that their time in the system is accounted for.
©Sandra Potthoff, PhD 2021
“Little’s Law”
Page 5
UCLA HLTADM 413 Operations Management in Healthcare
e) You can double check the results by re-filtering on LOS if you wish.
3) Now create a pivot table.
4) Create a pivot table for the Newborn data. Put the MDC_DESCR in the Filter, and choose NEWBORNS &
OTHERS.
5) Put the DRG_ DESCR in the Rows. Because of the filter, the table should only display the DRGS relevant to
the Newborns.
6) In the Values, pull the DRG_DESCR in twice. For the first option of DRG_DESCR in Values, choose Value Field
Settings from the dropdown arrow, next choose Summarize Values By, and then choose Count. This provides
a count of how often each DRG for Newborns appears in the dataset.
7) For the second option of DRG_DESCR in Values, choose Value Field Settings from the dropdown arrow, next
choose Show Values As, and then choose show values as % of Column Total. This provides the % of the total
each DRG represents for Newborns in the dataset.
8) Finally, pull LOS in the Values. Choose Value Field Settings from the dropdown arrow, next choose
Summarize Values By, and then choose Average. This provides the average LOS for each DRG in the pivot
table.
9) You should now have a pivot table that is filtered on NEWBORNS & OTHERS. The first column lists the DRGs
for this filter group. The next column shows the count of each DRG. The third column shows the % of total
for each DRG. The last column shows average LOS for each of the DRGs. There is also a GRAND total in the
last row of the pivot table. You should see that Grand Total average is a WEIGHTED AVERAGE, as it should
be. A WEIGHTED AVERAGE weights each DRG’s average LOS by the percent of that DRG to the overall total.
You should get a GRAND TOTAL of 1233 newborns and a GRAND TOTAL Average LOS of 1.97.
10) Copy and paste this pivot table a few rows lower. Change the filter to PREGNANCY, CHILD. This should
automatically shows the DRGS relevant to this group, and update the relevant counts, % of counts, and
average LOS. You should get a GRAND TOTAL of 1255 and a GRAND TOTAL Average LOS of 2.16.
11) For Little’s Law, the LOS is W. For λ, you know the total count for the year. Assuming 365 days in the year,
how many patients arrive per day? (Calculate them separately for Pregnancy vs Newborns, and make sure
your units match.)
12) Given W and λ for Newborns, what is L? For W and λ for Pregnancy, what is L? Set the model up so that it is
easily understandable to your leadership team when you project your results when you present them.
Remember to not hardwire your numbers, but to build formulas. Note that you could have created the pivot
tables to show only the GRAND TOTALS by not putting the DRG_DESCR in the Rows. But, it is always helpful
to look at the detail to ensure everything looks as it should. In addition, the statistics by DRG may be helpful
to the leadership team in their discussions.
©Sandra Potthoff, PhD 2021
“Little’s Law”
Page 6