Questions 1 and 2: Practice using Little’s Law

1. Your heart hospital (approximately 700 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, 150 patients are admitted to the hospital daily.  The reimbursement per patient per day for DRG1 is $1200, while the reimbursement per patient per day for DRG2 is $1000.

Answer the following on Q1 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?

2. Your organization sends out 300,000 bills for services rendered each year. It takes, on average, 8 weeks to process each bill.  (Put all calculated cells of your model in the spreadsheet to the right of your answers, using models referring to cells with data in them, not just typing formulas.)

1. Assuming 52 weeks a year, how many bills are in process at any given time, on average?
2. 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?
3. 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?
4. Under this new cycle time, how much potential revenue is now sitting in your billing department, on average?

Question 3: Flow Time, Flow Rate, and Capacity Analysis

The worksheet for the x-ray services process flow time and flow rate and capacity analysis that we worked on has been slightly modified for this homework question.

A copy of the spreadsheet has been made for each of the changes below.  So, the worksheets called 3A through 3K are slightly modified copies of what you saw in class.  Use the respective worksheets for each of the changes indicated below.

Make the appropriate changes in the respective spreadsheet to accurately reflect the changes as described below in A through K.  Use the spreadsheet whose number corresponds to the question below.  Consider each of the changes below separately, not cumulatively.  That is, for each of the changes below, assume you are starting out from the original theoretical time and theoretical throughput as shown on each new sheet.

For changes that impact the critical path portion of the spreadsheet (Rows 17 through 28), be sure to do the following in cells B32 through B35:

1. Indicate the appropriate flow time lever this change represents by turning it (them) RED in the choices shown in cells B31 through B35.

You will look at the impact of these changes on the daily (assume 8 hours per day) theoretical and effective throughput (R) capacity.  Assume there is a 15% loss factor (down time) for each resource to calculate effective capacity of each resource.

1. Input the appropriate numbers in cells H47 and I47.
2. Indicate which resources are affected by this change by changing the color of the text of the affected resource names (cells B38-B44) to RED.
3. Calculate the daily theoretical capacity of each resource pool in cells H38 through H44.
4. Calculate the daily effective capacity of each resource pool in cells I38 through I44.
5. Determine the theoretical and effective bottleneck resources by using the following formulas for K39 and K43:

For cell K39:

=INDEX($B$38:$B$44,MATCH(MIN($H$38:$H$44),$H$38:$H$44,0),1)

For cell K43:

=INDEX($B$38:$B$44,MATCH(MIN($I$38:$I$44),$I$38:$I$44,0),1)

1. Determine the theoretical and effective process capacity by inputting a formula that puts the correct number in the appropriate cells in column K (cells K41 and K 45).

CHANGES: Remember, each change is independent of the next one – they are NOT cumulative changes.  NOTE THAT SOME OF THE CHANGES WILL NOT AFFECT THE CRITICAL PATH PORTION OF THE SPREADSHEET, BUT WILL AFFECT THE FLOW PORTION OF THE SPREADSHEET.

1. Install new equipment that enables darkroom technicians to develop x-rays in 10 minutes instead of 12 minutes.
2. Ask the patient to carry the x-ray requests and completed x-rays back instead of sending them by messenger (assume it won’t add any time to the patient activities of walking to and from).  (NOTE that you will get a #DIV/0 in cells H38 and I38, so you will need to adjust your min function in the index/match to exclude those cells)
3. Install a PACS (Picture Archiving System), which eliminates the messenger having to take the xrays back to the physician’s office, and eliminates the need for any darkroom developing or darkroom tech skills.  The messenger still has to carry the physician order request to the xray department, however. (NOTE that you will get a #DIV/0 in cells H42&H43 and I42&I43, so you will need to adjust your min function in the index/match to exclude those cells.  You can do this by changing the MIN range to be MIN($H$38:$H$41,$H$44) in cell K39, for example.)
4. Improve the method of taking x-rays so it reduces rework on shots from 25% to 5%.
5. Email the x-ray requests (taking 1 minute) instead of sending them with a messenger.  Finished xrays must still be transported via messenger from the xray department to the physician’s office.
6. Purchase a “patient mover” that is only for patient use.  It allows the patient to travel to and from the xray department in 4 minutes each way instead of 7.
7. Reduce the downtime of the darkroom to 0% rather than the current 15% downtime.
8. Complete the last phase of X-ray development, which takes up 3 of the 12 minutes in the process, so that it takes place outside the darkroom rather than inside.   The darkroom technician should still be occupied for the entire 12 minutes of activity time.
9. Hire a second receptionist.
10. Install another darkroom.
11. Install another darkroom and merge the tasks of X-ray technicians, darkroom technicians, and receptionist.  Basically, train each of your eight employees to perform all the jobs entailed by the process.

INDEX MATCH COMBO:  Useful for extracting data from a range or table (it’s like vlookup or hlookup on steroids)

=INDEX($B$38:$B$44,MATCH(MIN($H$38:$H$44),$H$38:$H$44,0),1)

How to interpret this formula:

MATCH FUNCTION:

The MATCH function looks for a specified item in a range of cells, and then returns the POSITION of that item in the range.

Format of match function:   MATCH(lookup_value, lookup_array, match_type);

Match type can be:

1 or omitted: finds the LARGEST value that is LESS THAN OR EQUAL TO the lookup value; the values in the array must be in ASCENDING ORDER

0: finds the FIRST value that is EXACTLY EQUAL to the lookup value; values in the array can be in ANY order

-1: finds the SMALLEST value that is GREATER THAN OR EQUAL TO the lookup value; values in the array must be in DESCENDING ORDER

MATCH(MIN($H$38:$H$44),$H$38:$H$44,0)

So in our case:

the lookup value = MIN($H$38:$H$44)

the lookup array is $H$38:$H$44

the match-type is 0 (find the FIRST that is an exact match.  Note that TIES for the minimum would only return the FIRST value that is a tie, so this wouldn’t work for ties)

So, this match function will return a number that corresponds to the row that has the minimum value in it.  Suppose it’s the fourth row, then the match function would return a 4.

Now on to the index function.

INDEX FUNCTION:

The INDEX function returns the reference to a value from a table or range.

Format of index function for a table:  INDEX(table, n,m)

A table has n rows by m columns, so the n above refers to the row location, and the m refers to the column location.

Format of index function for a list: INDEX(range, n)

A range is assumed to be just one column (so you don’t specify m).

=INDEX($B$38:$B$44,MATCH(MIN($H$38:$H$44),$H$38:$H$44,0),1)

So in our case the MATCH function was applied, and we suppose it returned a value of 4.  Then the index function is being evaluated as:

=INDEX($B$38:$B$44,4,1)

The ‘table’ is $B$38:$B$44.  The match function says look in the 4^th row.  The index function says return whatever value is in the 4^th row of the 1^st column of the table  $B$38:$B$44.

Since $B$38:$B$44 is really just a range (a range is a one column table), the following should also work (leaving the last ‘1’ off the end of the formula), but I wanted you to see the table format of INDEX:

=INDEX($B$38:$B$44,MATCH(MIN($H$38:$H$44),$H$38:$H$44,0))

More on INDEX for those who are interested:

http://chandoo.org/wp/2013/09/18/index-formula-usage-and-tips/

Question 4: Queueing: Safety Capacity and Safety Time Tradeoffs

Your HMO is consolidating its telephone customer support services to a centralized location.  Based on arrival and service rate data that have been collected from electronic phone records, you have determined that your service rate is Poisson distributed with m = 20 calls per hour.  You arrival pattern is also Poisson distributed, but varies by hour as shown in the Excel worksheet called “arrivals”.

The CEO is pushing to have a response rate of less than a minute to answer a call.  The proposed new motto is “The One Minute Phone Customer Support Rep”.  That is, no customer who is calling should have to wait on hold longer than a minute before having his/her call answered.

Unfortunately, one of the operations research laws of physics is that if one wants to decrease safety time (the promised time for your service, in this case 1 minute), the larger your safety capacity (in this case the number of staff) has to be.  If one can increase the safety time, say to no longer than 4 minutes on hold rather than the 1 minute, then the safety capacity could decrease, that is, you would need less staff.

Your organization is not sure how much of a cost this would be meet the “no more than 1 minute on hold” response time rather than the “no more than 4 minutes on hold” response time.   Each additional phone support staff person costs the organization $50 per hour, including benefits.  “Half” staff are not allowed, only whole numbers (1,2,3,4, etc.)

Fortunately for you, you realize because of your extreme brilliance, you can examine this question using queueing.  The agreed upon standard is that there should be no more than a 5% probability that someone calling should have to wait more than x minutes, where x is either 1 minute or 4 minutes.

Steps to do this:

1. Download the queueing add-in (directions on the Moodle site).  Remember where on your computer you saved it, because you will need to add it in to make it usable from your excel worksheet.  The video on-line for the Queueing Module shows how to get the add-in into your excel worksheet, and written instructions are provided at the end of this document.
2. A warning that if you start this on one computer, and then go to work on it on another, with the queueing add in in some other folder, you will get #NAME stuff in any queueing analyses you have conducted so far.  It’s better to try to start and complete this assignment on the same computer when you reach the point of using the queueing add-in.  Just as in class, choose Tools, Add-Ins, Browse to get to the folder that has the add-in, and then choose okay.
3. Create a new worksheet called “queues”.  Use the queueing add-in to determine the number of staff needed when demand is 20,30,40,50,60,70,80,90, and 100 arrivals per hour Poisson distributed if you want to have a 5% or less probability of having to wait more than 1 minute.  Repeat for 4 minutes.  Please make all your inputs in HOURS so it’s easier to grade.  This means that your critical wait time should be put in hours also.  Input 20 for arrival rate, 20 for service rate, convert the 1 minute to hours for critical time, click all output options, and click show titles for the first one you do.  Determine how many staff you will need to keep it less than or equal to a 5% probability you will have to wait at least 1minute.  Then repeat for arrival rate = 30,40, etc.  For each successive queue you do, just move the cursor over into the next column before choosing OR_MM.  Once you have completed for all arrival rates up to 100, then go back and change the critical wait to 4 minutes, and start over from demand of 20 to demand of 100, keeping the probability of waiting >= 4 minutes at a 5% probability or less.
4. Once you are done, select ALL of your queueing output, and do a copy ->paste special-> values, where you highlight all your output and say copy, and then without moving your cursor, choose paste special values.  This will keep me from getting a #NAME error when I open your file.
5. Copy and paste the number of servers needed for 1 versus 4 minute response times in your  “arrivals” worksheet.

Question 5: The most time consuming part is cleaning the data (and figuring out how to clean it)

The worksheet called Blood Data has original time stamp data from a one server phlebotomist lab.  The goal of this question is to calculate some basic statistics (average, standard deviation, min, max, 25^th and 75^th percentile) on arrival rates by hour of the day to the blood draw lab at the Cherry Mountain Clinic.

1. REMOVE DUPLICATE RECORDS
   1. For some unknown reason, the data set has a lot of duplicate records.
   2. Choose Data tab, then in Data Tools, choose Remove Duplicates.
   3. When the text box comes up, click on “My data has headers” and make sure “Select All” is selected.
   4. Hit OK, and this will remove all duplicate records.
   5. This should leave you with 6392 records (if you scroll down, you will see that you get to row 6393, but your first row is your column headers, so you know you have 6392 records).
2. CALCULATE LEAD TIME (DATE FROM CALLING FOR APPOINTMENT TO DATE OF APPOINTMENT)
   1. Insert a new column to the right of Column C.
   2. This should create a blank column in Column D. Call this column Lead Time
   3. Calculate the lead time (Appt Date – Create Date), but be sure to check for blank cells and negative numbers in the formula and set it = blank if the lead time is negative or if either of the two cells being compared is missing data.) (Note that a lead time of 0 is interpreted as a “walk-in”.)  I purposely am not spelling out exactly what the formula needs to be.  You need to figure this out. Looking at the spreadsheet for Week 2 practice should help you figure this out.  I can tell you it will be an if statement that checks for blank cells and negative numbers when the lead time is calculated, and if either of those conditions is true (so it will be an OR statement within the IF statement), then return a “” (blank), else do the calculation.
   4. Make sure you set the data type in Column D to a number.
   5. DATA CHECK: Check for blank lead times and excessively long lead times.  There are a couple of ways to do this.  Let’s do a pivot table.  Create a pivot table, and pull the Lead Time into the rows and the count of Lead Time into the values.  You’ll see that there are what appear to be a few really long lead times (e.g. 181 days), but it’s hard to tell if this is real or an error.  Since we can’t talk to the person who entered the data, and the number of records with long lead times is really small relative to the total number of records, we’ll leave them in for now.  You’ll also see that you have two records with blank lead times.   Once you’ve completed this data check, you can delete the worksheet that has the pivot table on it, since there’s still more data checking and cleaning to do in the data file worksheet.
3. CALCULATE WAIT TIME ON DAY OF BLOOD DRAW
   2. Unfortunately, the CheckIn Date_Time has both the date and time, while the Begin_Time (time the blood draw started) has just the time. I couldn’t get them to subtract correctly without adding the date to the Begin_Time.    Put your cursor in cell G2, and type the following:  =C2+F2.  Copy and paste this all the way down the column.  Then, set the format of that column by choosing the downarrow in Number, choose More Number Formats, choose Custom,  and choose the m/d/yyyy h:mm option.  (It may have defaulted to that option when you created this column of data, but if it didn’t, this is how you would fix the format.) Now name the column by typing Begin Date_Time in cell G1 to identify this column of data.
   3. MORE DIRTY DATA! Now calculate the wait time in column H  (=Column G-Column E).  Call cell H column Wait Time.  Change the cell format for column H to Time, hh:mm:ss.  Choose the ‘More Format Numbers’ option from the drop down arrow to change the format of the cells. Choose the Time Category, and then choose the numbers that are  37:30:55 numbers, NOT the  13:30:55 numbers.  This is because there is a record that is probably an error in either the ‘check in date’ or ‘begin date’ (see 60091 with a create date of 04_Nov_09.)   You will see that a lot of the results in Column H show up as #######.  This is because it is a negative time, which isn’t possible.  You can see that for these cells, it looks like the CheckIn Date_Time and the Begin Date_Time appear to be reversed.  In real life, you would check with the phlebotomist to see if this was the case.  In this example, we will assume this is what happened so that we don’t end up with a lot of missing data in Wait Time.  ALWAYS DOCUMENT THESE ASSUMPTIONS IN YOUR WRITE-UPs.  Hence, the formula in column H needs to be adjusted.  Set up the formula to check for missing values in columns G or E, and if either of them are blank, then set the time to blank.  Otherwise, if G>E, then G-E, else E-G.   What is really weird is that the logic won’t work unless you check if the subtraction of the two columns is >0 (e.g. if(E-G>0, then something, else something else) so that is what the “if” statement will need to check for this.  Once you have done this calculation, you should do another pivot table to see if there are any really weird looking wait times.  Some of the wait times look quite excessive (e.g. over a few hours), but for this homework assignment, we will keep all the data points.  It is helpful to go back to the data file, sort the whole thing from largest to smallest wait time, and see if you can identify any clearly anomalous time stamps that are creating erroneous wait times.  In real life, you would need to investigate further to determine if these data points were accurate or weird.  If weird, then cut and paste these records into a different worksheet to track how many records were deleted, and conduct analyses to determine if there appear to be any patterns in the bad data.
4. CALCULATE THE DAY OF THE WEEK AND THE HOUR OF THE DAY
   1. In column I, you can set the day of the week of the appointment by typing in column I2: =if(E2=””,””,TEXT(E2, “dddd”)).  Copy and paste down the column I. (This is a different way of calculating the day of week than in the Excel practice file for week 2…)
   2. In column J, you can determine the check in hour-BUT, we need to be careful because of the problem we saw in the mixing up of the check in hour and the begin time. So, checking the following, and changing the column to a number format should return the hour of the day the patient arrived. Type  =IF(OR(E2=””,G2=””),””,MIN(HOUR(E2),HOUR(G2))).  Copy and page down the column J, and change the format to a number with no digits to the right of the decimal place.

Now you have done all the data cleaning needed to begin to analyze this data set.

5. CALCULATING THE AVERAGE # OF ARRIVALS BY HOUR
   1. Create a pivot table of the data set. If you click on a cell anywhere in the data and type Ctrl A, it will select All.  Create a table with the Appt Dt in the row labels, hour of day in the column labels, and count of hour of day in the values.
   2. We need 0’s where there are blank cells in the pivot table. To do this, right click anywhere in the pivot table, and then choose PivotTable Options.  When the box pops up, choose the Layout & Format tab, and in the section called Format, make sure the “For empty cells show:” is checked, and in the box, put a 0.
   3. If you look at the grand total in column Z, it appears that there are 3 dates for which there was no hour associated with them (so 3 missing data points). To delete these 3 data points, in the ‘Hour of the day’ down arrow showing in column B3, click on the down arrow, scroll down, and uncheck the ‘blank’ box at the bottom.
   4. For this exercise, we don’t want the grand totals for rows or columns, so right click anywhere in the pivot table, and again choose PivotTable Options. Click on the Totals& Filters tab, and unclick both the boxes for Grand Totals.
   5. Now, with your cursor anywhere in the table, hold down the Ctrl key and then type Ctrl A. This will highlight the whole table.  Now do a copy, paste special values of the appt date by hour of day table into a new worksheet.  CALL THIS WORK SHEET ARRIVALS.
   6. Now scroll down to the bottom of the table. We are going to add five summary statistics for each hour of the day below the data: the min, max, 25^th percentile, 50^th percentile, and 75^th percentile of arrivals by hour.  For example:

To do this, in column A (cell A180), type the text Min, Max (in cell A181), etc as above.  Then for the Min in column B, use the Min function for all data in column B (DON’T accidentally include the hour of the day header).  Now if you put a $ in front of the row numbers, either manually or by hitting F4 when your cursor is in the formula bar until just the row #’s have $ in front of them, you can pull that formula down to the Max row, and just change the Min to a Max.  For the percentiles, click on the Formulas, tab at the top, then choose the Insert Function of the far left, type percentile into the ‘search for a function;’, and then percentile should show up in the select a function box.  Click on percentile, and it will tell you what is needed to calculate the percentile.

1. Plot the average arrivals per hour as a line chart, put the chart in the ARRIVALS worksheet underneath the table. Make sure you’ve labeled your chart correctly on both axes, and made it look pretty.  To get the hour of the day to show up on the horizontal axis, right click in the chart, then choose select data.  Choose the Edit on the Horizontal Axis Labels in the pop up box, and then highlight the row of cells with your hour of the day header in them.  To get the labels in, make sure you are clicked in the chart, and then in the far right top of the screen, a Chart Tools tab should show up in green.  Click on that green tab, and a new toolbar should show up at the top.  Choose the Design tab, and then click the Add Chart Element button on the far left. In the drop-you can select parts of the chart to add or edit such as Chart Title and Axis Titles.
2. Once you have finished the calculations and graph in the worksheet called ARRIVALS, you can delete out the sheet with the pivot table in it so that the file you upload to Moodle is not so huge.
3. NOW YOU KNOW WHAT IT TAKES TO CLEAN DATA. ALWAYS GRAPH, REVIEW, ANALYZE DATA TO SEE WHERE THE PROBLEMS ARE, SO YOU KNOW WHAT ASSUMPTIONS YOU MIGHT NEED TO MAKE WHEN USING THE DATA.

What does the above graph tell you?  There are much larger differences between the 75%tile and maximum values than between the minimum and 25%tiles.  What does that mean from a flow point of view?  You don’t need to answer this in your homework.  Just think about it.  Variability matters!