1 Page of 42
Class Eleven Agenda & Objectives
HA 4315
Do not be intimidated by the length of this file. When I started to write it my assumption was that you would develop the Excel files described here. Thus, my instructions include a lot of material on how to create what is described. After working with this project for a long time it was clear that I would need to provide the Excel files as templates. Thus, most of what I am describing is already done. Your main task for Project Five will be to select the data and get it into templates and then to analyze it.
I.
Subjects and activities for the class A. Project Four due. B. Setting standards and evaluation of performance against them. C. Memory Jogger II, pp. 137-140, radar charts. D. Review of grading criteria for Project Five 1. Grading criteria will be found on TRACS in the Resources folder in a subdirectory called Project grading criteria. 2. File is called Project Five Grading.xls
Grade Saver Hint for Project Five Project Five is extremely interrelated. It ties back to all the prior projects as well, except the flow chart. It is not possible to do it correctly without paying close attention to the parts and how they relate. My suggestion is to have the project done before the second class where you get to work on it. Use that class to review it for correctness. It takes me about 2-3 hours to grade each one, which of course includes writing up comments. I suspect it would take the group the whole class to check it properly. I have given extra time to do it. Dont wait until the day before. You will think bad thoughts doing the project and when you receive the grades and my comments. Should you elect not to have the project finished before the class, then one group member, capable of very good attention to detail, should have as their sole job for the group the review of the project against the criteria. Given the high probability of a need for correction, members should be standing by ready to correct their parts.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 2 of 42 II. Where are we now with the radiation therapy projects?
Figure 1
III. Overview of Project Five Project Five requires several steps. There is an overview movie about it called overview.avi that will summarize these notes. Analysis of data. Weighting the items from the RT check sheet in terms of their negative impact on the patient care experience. Developing standards for evaluation of performance. Looking at current data and using those standards to evaluate the performance. Showing the performance evaluation data on a radar chart. Showing the weighted performance evaluation data on a table. Writing a memo about what all this means and how the tools used in Projects Two to Five tied in to this effort at process improvement.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 3 of 42 Because there is a lot of data to analyze I created templates for you so you would have more time to think about the data and spend less doing spreadsheet tasks.. However, because most of that activity is simply copying things and following instructions I have not evaluated it as highly as thinking about what the information means. Thus, the memo that accompanies this project will much more substantial than previous memos. The grading criteria are seen on pages 41 and 42, containing Figure 45 and Figure 46. Those criteria are discussed in more detail there. There are three files that are critical for Project Five. One is this file, the Class Fifteen notes. The second is the grading criteria. That is found in TRACS in resources/project grading criteria and is called Project Five grading.xls. The third is the file that contains the templates I created for you. This is located in TRACS in resources/Supplementary material for group projects. The file is called Project Five data set.xls.
An overview of the grading criteria is given below, Figure 2. (Figure 2 only sums to 95% right now. I shall update it later.) Each of the sections listed other than the memo is a separate page in the templates file. All spreadsheet components but one (the radar chart) have templates, so it should go fairly easily. My suggestion for this project is to break it into parts. It is also possible to break out certain sections of the Excel components so everyone is not working on them at once. The radar chart, row seven, and the weighted performance evaluation table, row eight, can be done separately.
Several elements of the memo can be written before the data elements are completed. These would be row eleven. The task there is to describe the role all of the tools in the projects you have done with the RT data are supposed to contribute to process improvement. That part of the memo can be written covering what the group did in Projects Figure 2 Two to Four, before this part is completed. Once the tools used in Project Five are done, those elements from Project Five can be added to the memo. Once you understand the tools listed
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 4 of 42 and their purpose then other elements of the memo fall into place. Of course, all of this requires that the final product be reviewed for consistency. To even further simplify your task I have added information in both the grading criteria file and in the templates file which will tell you where in the notes the instructions for that section are found. In some cases the grading criteria even provide specific instructions about what to do for specific cell addresses. It is for these reasons I do not think the data management task is nearly as hard as it may seem at first. Thus, an unusually large portion of the grade will go to the memo, 40%. IV. Overview of the Project 5 data set.xls file I have provided for you an Excel file which has most of the data and templates already set up for you. That file is called Project 5 data set.xls and is located in TRACS in resources/group project assignments supplemental instructions. You cannot do the project without it. Well, at least you would not want to do it. The file is set to open on the Hello page, which is the first. Pay attention to the text box message there. We are going to review some historical performance data. In Project 5 data set.xls those are the tabs labeled 2007 and 2008. We are going to select some items over which we could exert some control within the radiation therapy department. This is done on the Calcs page in Project 5 data set.xls. We are going to wean that list down to those things we want to improve. This ends up on the page Standards in Project 5 data set.xls. Then we shall use weighted multi-voting to determine the relative value of those items in terms of their influence on the patient care experience. I know you are feeling cheated, but I set the template up for you on the page called Voting in Project 5 data set.xls. Then we set standards for performance for six or more of these items. That occurs on the page Standards2, in Project 5 data set.xls. We evaluate performance against those standards. The data for this is on the Performance page in Project 5 data set.xls. The table to set it up for plotting on a radar chart is on the Radar Table page, in Project 5 data set.xls. We plot the results on a radar chart. The Radar Chart page is completely blank in Project 5 data set.xls. I wanted you to have something to do. There are some instructional text boxes there you can delete that are likely in the way. We also look at the performance using the weights from the weighted multi-voting. This is done on the page Weights in Project 5 data set.xls. Then we write a memo telling the boss what is going on. This is done on a word processor of your choice.
That process is captured in overview form in the flow chart in Figure 3.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 5 of 42
Overview flow chart of the standard setting process: Part of Project Five
Process starts
Take a ten minute break
Evaluate data & choose item s for which you will create standards
W rite a mem o explaining the entire process involved in evaluation of the RT departm ent, from Project Two to Project F ive.
G roup data table items with sim ilar monthly average values
Create a perform ance review table which facilitates evaluation of the perform ance against the standards using the weights from the m ulti-weighted voting table
Using a weighted m ultivoting table, determ ine the relative im portance of the item s to the patient care experience
Create a radar chart w hich captures the values from the scoring table
Using judgm ent, develop standards for each group or item as to what counts as m eeting the standard and what to call levels of perform ance below the standard
Apply the standards to the performance and post the num bers to the scoring table
S et up a scoring table for use in scoring the perform ance of the organization against the standards
Exam ine the new data and com pute the relevant values to be used in m aking judgm ents about the perform ance of the organization
Figure 3
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 6 of 42 Below is the list of videos to guide you through the process. V. Videos for Project Five
Movies for Excel 2003
(all are in TRACS/Prob Solving/Screen video files/Excel 2003/Project Five) Movie title Function While lengthy, this video guides you through the entire assignment, giving an overview of the assignment. You likely need to have read Chapter 13 notes to get the full value of the video. [This movie was made in the 2007 version, but nothing in it will essentially differ between the two versions.] Shows how to select items for which you will set up standards. Also shows how to sort them into compatible groups. How to do the voting on the significance of the items you selected to the patient care experience. This is another weighted multi-voting matrix. [This movie was made in the 2007 version, but nothing in it will essentially differ between the two versions.] This is a quick film to show how to evaluate the data to determine if the Pareto principle is in effect. Here you learn how to look at the data you have and use it to select values for your standards. These values will apply to all items in each group of standards. This movie shows how to evaluate the performance against the standards you set. This film shows how to create the radar charts This film shows how to develop a weighted evaluation of performance. Duration
Overview.avi
18:53
Select & sort.avi
8:07
Voting.avi
4:31
Pareto.avi
2:43
Setting standards.avi
14:56
Evaluating performance.avi Radar.avi Weights.avi
18.44 15:00 11:40
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 7 of 42
Movies for Excel 2007
(all are in TRACS/Prob Solving/Screen video files/Excel 2007/Project Five) Movie title Function While lengthy, this video guides you through the entire assignment, giving an overview of the assignment. You likely need to have read Chapter 13 notes to get the full value of the video. Shows how to select items for which you will set up standards. Also shows how to sort them into compatible groups. How to do the voting on the significance of the items you selected to the patient care experience. This is another weighted multi-voting matrix. This is a quick film to show how to evaluate the data to determine if the Pareto principle is in effect. Here you learn how to look at the data you have and use it to select values for your standards. These values will apply to all items in each group of standards. This movie shows how to evaluate the performance against the standards you set. This film shows how to create the radar charts This film shows how to develop a weighted evaluation of performance. Duration
Overview.avi
18:53
Select & sort.avi
10:19
Voting.avi
4:31
Pareto.avi
2:38
Setting standards.avi
19:43
Evaluating performance.avi Radar.avi Weights.avi
21:12 16:33
VI. Selection of problem elements, grouping them, and setting standards This is a lot of steps and we could do even more in the analysis if we wanted to look at how the performance data is unfolding over time relative to the standards. Im going to work you through it with examples. And, to make your job easier, I decided to set up the spreadsheet for you to a large degree. That was not my original intent, but the spreadsheet tasks began to overwhelm the analysis activities, which is where the thinking should really be taking place. The movies above help too. Essentially, you have several different ways of coming to grips with understanding the project. You have the written instructions in these notes.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 8 of 42 I have gone over the notes and the project. You have the flow chart giving the overview of all the projects, in Figure 1, page 2 and the specific flowchart for this project, Figure 3 page 5. You have detailed grading criteria, summarized in Figure 2, page 41. You have the various movies, summarized in the tables on pages 6 to 7.
Thus, whatever your method for learning, one of these should work. I shall use two different sets of data for the examples. The first will be the familiar radiation therapy data. This is what you will see on your data set Project 5 data set.xls where your templates are. Those are the tabs labeled 2007 and 2008. In my examples I shall also use a data set that is simply generic, using terms for the problems such as Problem One and the like. I shall go into much more detail on that one. If I used the real items I would be doing your work for you. You dont want to be cheated out of the learning experience. Knowing how to set standards, evaluate performance against them, and present the data to your bosses and to your own employees are critical tasks. This exercise is the way we find out if our plans are working when we are doing an improvement intervention in the processes we manage. A. Reviewing historical data
The movie selecting & sorting.avi shows this process of selecting items for which you will develop standards. I describe it in general terms below as well. First, lets look at some data. Figure 6, on page 9 below, shows the radiation therapy data I am using as my example. This is different from the data you have had before. I made up the numbers entirely so the values would be no where close to those in your projects. I did not want to give away the store. I also added the data for the rest of the year. I added column O to compute the monthly average for the problems and column P to get the sum of the problems. This example data is NOT the data in Project 5 data set.xls on the 2007 page. Your data is on that file in the pages labeled 2007 and 2008. Nor is it the data in the movies. Seeing different data sets may prove helpful for learning how to apply the tools to different data sets. An advantage we have in setting standards that you may not have in some of your jobs is that we do have historical data. There are ways to get information about what performance should be like in a process when you lack historical data.1 However, it is much easier if you have it
Some of these methods include 1) doing industrial engineering or management engineering types of analysis. In these situations you closely monitor a process, finding out what the inputs are, how long things take, and anything else that is relevant. You can get degrees learning how to do this, but it is not always complicated. So you could do it on your own. Another source of information may sometimes be found in 2) published studies about process times. I used such studies when trying to find out how long it should take to do various housekeeping functions. I was supervising housekeeping and it seemed the processes were exceedingly inefficient. Of course, whatever I found out was quickly ridiculed by the department head who looked bad as a result of my analysis. Happy days did not lie ahead for him. 3) Discussions with other people managing a similar process can provide insights. 4) At the least, you should start collecting data right away on your key processes. I did this almost immediately when I started supervising in 1975. At that time all we had were adding machines and calculators. Computers had yet to be invented.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
1
Page 9 of 42 because it gives you a sense of what the organization is capable of doing and what the problems are that it may have. Having a lot of problems spread over the entire list is bad news. It is harder to deal with a lot of widely distributed problems than the same number in a smaller list, assuming all the items needing correction are equally easy or difficult to correct. This hearkens back to Note that line 22 of the memo grading criteria, the 80/20 rule. If you can find the biggest Figure 46, page 42, relate to you being able to problems and solve those, you have done make the kind of judgments explained here. more to improve the process than to solve a One or more of the questions on the study lot of little problems. Thus, if the problems guide for the tests relates to this understanding are spread out across the board and the as well. This is explained in outline section I.B 80/20 rule is no where close to being in on page 12 as well. effect your processes are systemically defective. The work required will be much more substantial.
Figure 6
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 10 of 42
Before we see if the Pareto principle is in effect we have to have the problems we want to look at clustered together to form standards. Since we have historical data in Figure 6, on page 9, we now must decide which items to use to set standards. I made that easy by organizing the data from 2007 that is monthly onto a different sheet called Calcs, which contains only the summary information. A sample of that page is seen in Figure 7, on page 10. When it comes to the assignment we want to minimize the work needed. The assignment calls for a minimum of two standards covering a minimum of six items from the data set. What we want to do is to pick items that are close to each other in the number of instances so we do not have to make a separate standard for each item. . Figure 8, similar to what you will Figure 7 do on the page Standards, shows the results of collecting items with similar values and then sorting them in descending order based on the number of annual incidents. After doing the sorting I could see that three standards were needed. (Everything needed to get to the expected outcome of Figure 8 is in the movie selecting & sorting.avi.) In Figure 8 you can see that three of the items had values in the Figure 8 40s. Four had values in the low 20s. Two had values that averaged less than one per month. These variations in the number of instances require us to have three different performance standards. Deciding how to group the standards is a judgment call. If there is substantial percentage difference in the
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 11 of 42 number of incidents in your groupings then they likely need to be broken up into separate groups or the items replaced with numerically more similar items.
The judgment that comes into play has to do with the number of items. Obviously 3" is 40% less than 5. These values are seen in Figure 8 at B9:B10 for the phototimer and delayed films problems. However a difference of two incidents is not of the same significance as a 40% difference between 300 and 500. You have to consider not only the percentage, but the numbers themselves. This is the same as what you would do if looking at population growth. Some counties in Texas have only about 100 people. If ten people moved away that is a 10% drop in the population of the county. However, no one looking at data would find that consequential in the same way as Dallas taking a 10% drop in population. You have to get the context to understand the numbers. For a last example, Figure 10 below, shows two different data sets and how it would make sense to organize groups of standards according to how the data sort out. The numbers in Figure 10 are the monthly averages for the incident counts. I set up an evaluation of the range of the data and computed the difference between the maximum and minimum number in percentage terms. As the numbers get smaller, you can see that the variance in percentage is more extreme. While in A14:A16 the difference between 1 and 0.1 is less than one, as a percentage it is 900%.
Figure 10
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 12 of 42
You have to use some common sense here. If the numbers are fairly big it is obvious what the groupings ought to be. The small averages require considerable more thinking. Take Standard 4 on data set one in Figure 10, A14:A17. If you set performance standards that required improvement in A14, now at one a month, the other three could get much worse in percentage terms and still be rated as okay. This only matters if these very small values are sentinel events. Remember that a sentinel event is one which has the potential or actually did lead to death, disability, or serious injury. One a month of those is a lot. One every quarter is 0.33 a month. Thats a small number, but a big problem if people are unnecessarily dead as a result. Figure 12, on page 12, shows a large instrument left inside a patient. While this is not a radiation therapy incident, it is not good. Sponges are the most commonly left behind item.2 Perhaps the tack to take is that with low probability infrequent events that could harm patients, the standard should be zero and any incidence triggers an investigation. For purposes of your projects, I do not think there are any events that would be classed as sentinel events. The federal government calls them never events.3 I purposely include that evaluation in one of the templates, but you can wait to see it until later in the notes. B. Is the Pareto effect in effect?
One of the requirements in the memo is to discuss whether the Pareto effect is in play. I set up a short movie, Pareto.avi (or Pareto.wmv), to help you see how to make this determination. It is a simple process. It would have been much more complex had I required you to isolate the top items from the list of 34 instead of just those for which you are creating standards. To check for the presence of the Pareto effect first count the number of standards you have on the page Standards, like you would have on Figure 8, page 10. Since the Pareto effect is to
2
Figure 12
See SixWise.com at http://www.sixwise.com/newsletters/05/03/08/items_left_inside_people_after_surgery_just_how_common_is_this_ter rifying_ordeal_and_how_can_you_a.htm for the photo and a discussion of these types of errors. Retrieved February 17, 2008. 3 CMS Office of Public Affairs, May 18, 2006, Eliminating serious, preventable, and costly medical errors: never events, http://www.cms.hhs.gov/apps/media/press/release.asp?Counter=1863. Retrieved Sunday, February 24, 2008.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 13 of 42 decide if 20% of something accounts for 80% of something else, find out what number of items on your standards list is 20% of them. If you had nine standards, then 20% of nine is 1.8 items. Round the number to two. If the Pareto effect is in play, two items should account for 80% of the volume of the incidents. If you look back at Figure 8 you will see that the top two items in percentage terms only account for 39.8%, about 40% of the incidents. Thus, the Pareto effect is not in effect. This is bad, (assuming all problems are of equal significance4) since it means the problems are more evenly spread, thus more systemic. The best situation is when a very small number of things are causing most of the problems and the fix for them is easy. As always, it takes judgment about what can be done, if anything, about the current situation. If hard and fast rules worked organizations would be run solely by machines and computers. Given the way these data work out, it is unlikely any groups would have the Pareto effect in play. However, some of the percentages do get up around 60% to 70% for the 20% of the listed items. In those cases, tell me that while the Pareto effect, strictly speaking, is not in play, the preponderance of problems being concentrated in so few items suggests they are the vital few (Jurans term) and thus should be addressed at once. Put differently, this is another way of getting at the root cause. If these findings about whether the Pareto is in effect confirms what you did in the cause and effect diagram then your project is really coming together well as a model of analytical acumen. Similar discussions as took place in this outline section would occur in your memo. C. Voting on the importance of the issues relative to the patient care experience
At this point we know what the top problems are. We have grouped the problems into numerically similar groups for purposes of creating standards. However, we have not yet weighted the problems to see which ones have the most unfavorable impact on the patient care experience. Something that has a lot of problems does not necessarily cause any significant problems for the patient care experience. What is taking place is weighting the standards themselves relative to their importance to the patient care experience. The standards themselves are merely descriptions of problems with values associated with them. That does not tell us which are the most important. The only weighting tool taught in the course is our old buddy the multi-weighted voting matrix. The outcome of this votes tells us the importance of the issues. Nothing else does that.5 The weighted multi-voting is necessary because neither the development of the standards, the scoring of them, nor the radar chart that plots them will tell anyone which of the problems are the more important to the patient care experience. The votes on the weighted multi-voting table do that. Put differently, when you see the performance of the organization against the standards you as the manager will have to decide where to focus your attention to get the most improvement or where it is possible to achieve an improvement. The mere fact performance is
If you want to impress folks with really expensive BS perfume, you could say ceteris paribus instead of assuming all things are of equal significance. Ceteris paribus is a Latin phrase for "all else being equal that is used in scientific and other erudite circles. See http://www.iscid.org/encyclopedia/Ceteris_Paribus if you dont believe me. 5 Prior versions of the class used a prioritization matrix to determine the weighted value of something. It weighted the criteria and then applied those weights to the options under consideration.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
4
Page 14 of 42 better or worse does not indicate the impact of the problem on medical processes or outcome or on the patient care experience. Figure 8, page 10, we have nine items grouped into three standards. However, we do not know how important any of these ten items is to the patient care experience. We have to provide a weight. Then when we score the performance against the standard the outcome would reflect the relative importance of the item to the patient-care experience. The tool for this is the weighted multi-voting table you used in a previous project. Figure 13, below, should remind you of the format of the weighted multi-voting tool. I filled out this one as a sample. I did not rate my opinions of any items in your assignment as I wanted you to have something to do. I also made the name of the problem items generic, like Problem 1. However, you would have the names
Figure 13 of the items you are rating in column one. A version of this table is found on Project 5 data set.xls on page Voting. Originally I only left a blank page for you to do this. See how easy I am becoming! Two items, Problem One and Eleven, were scored as especially significant to having a negative impact on the patient care experience if the incident took place. Even more easy, in the movie voting.avi or voting.wmv, I did use examples of items from your projects when I showed how to set up the votes. What a pushover. Ask me about the patients perspective, not each other.
When voting on the importance of the standards you have set up, think about the patients receiving radiation therapy. Of the standards, which one or ones are the most important to their experience of the therapy. Those would be the ones you as the manager would want to address. Now that we know the importance of the various problems to the patient care experience the outcome of the votes will be used later in creating a weighted average. Now it is time to develop the standards themselves.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 15 of 42 D. Creation of standards
In a job situation as opposed to a school assignment you would likely develop standards for all of the problems you are tracking. If it is important enough to measure or count, it is important enough to set a standard for it and to keep track of how it does against the standard. It may seem silly to have standards when the item has no incidents. However, something with no problem now might develop one later. If you are tracking the data you will pick up a change a lot quicker than if you have to wait for someone to complain. It is really not that hard to set a standard for all multiple items. Back on I.A, page 8 and the several graphics in that outline section, I showed how to group items with similar counts or monthly averages so they can be evaluated using one standard. Now I shall show how to set two standards, one for a single item and one for a grouped item. Look at Figure 15, below. A table similar to Figure 15 is set up for your use on Project 5 data set.xls on page Standards; I made up a generic set of values for Figure 15 and went through the sorting and then the grouping by similar incident counts to get five standards. One could argue about whether Standard Two should be made into two standards, but lets leave it as it is. It also looks like we could say the Pareto principle is in play. It takes 2.4 items to get to 20% of twelve items. Three items contribute 73.4% of the incidents. It is in Figure 15 the ball park for saying the Pareto principle is in effect. Lets take the item for Standard One and the four things grouped for Standard Five. Standard Four and Five are dealing with values closer to what is likely in your projects.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 16 of 42 E. Examples of setting a standard
If we want to improve performance we need a goal. The goal should have some relationship to the values currently taking place. What are these values? The same data set you have been working with since starting with the radiation therapy projects. An example of that data I have a goal. set is seen in Figure 6, on page 9. In Standard One we see that there are about 20 of these problems every month. I do not show in these notes the monthly values for the year (trying to cut down on the number of images) but the low month was eight and the high month was thirty. Given the average and the range of values, we have to decide what would count as meeting a standard for an acceptable number of problems per month. We would not set a goal that allows more of these problems than we currently have. If we set a goal very close to or equal to the values we have now then we are saying that the current level of performance is acceptable. In some cases we may have to accept the current performance, but most managers are seeking improvements in processes. Most managers set a goal that calls for improvement. If that improvement is achieved, then in another round of evaluation and standard setting we can set new and lower standards as acceptable. This is the sense that quality improvement is continuous. Of course, we have to decide if the cost of getting additional improvement is worth it, the old problem of diminishing returns. 1. Setting standards when the incidence averages are sizeable numbers
I am going to show how to set standards for two types of data. In one case, the monthly averages are sizeable numbers. In the other case the monthly averages are small values or even less than one. First up is setting standards when the monthly averages are sizeable numbers. Problem One counts as having sizable numbers. Since the low was eight for Problem One and the average was twenty I am going to say that if the performance comes in at fifteen incidents per month or less then we met the performance standard. Since we have to score the performance against the standard, what metric will serve us? We can be completely arbitrary here, but for class project assignment purposes, use the scale described in the next paragraph. The standards will have a scale of from zero to ten, with ten meaning you met the standard completely. I put such a scale to the left in Figure 17. Once we know that fifteen or less incidents of Problem One means the standard was met we know that when the new data come in for Problem One we would give it a score of ten if that happened. But, we also have to define what happens if there are more than fifteen incidents per month. I defined those standards below, Figure 18. A similar table is set up for you in Project 5 data set.xls on page Standards2.
Figure 17
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 17 of 42 In Figure 18 I took account of the historical performance. Fair performance I defined at the level of 18-20 incidents per month. This is the current performance. That is listed on line 14. If the process is stable (only common cause variation) then the average values are what we can expect without making changes. But I want improvement. Thus, the current performance is only fair. In a TQM/CQI world almost Figure 18 never would a boss think current performance was as good as it could get. Of course, trying to get further improvements out of an already good system will lead to diminishing returns and it may well frustrate the employees into leaving. You have to think beyond constant improvement and focus your efforts on the vital few things that matter. I expect to see the historical information reported on line 14 in Figure 18 on your standards. It is what enables me to make sense of your standards and should serve as the control for how you think about them. Given how Standard One is set up if Problem One comes in at 20, the score would be somewhere between seven and eight. Since 20 is the low end of the scale, it should be closer to 7. That is the equivalent of a low C if you are getting a test back. Every time you do something on the job it is some sort of test. Standard One is designed to achieve We should expect incident values in excess of better performance. However, recalling fifteen for Problem One unless we have made the 85/15 rule, we know that some kind of change in the system. After all, the performance is not likely to improve average is 20 and systems do not change unless the managers do something to themselves. W hatever changes the system for change the system for the better. If a the better is nearly always a managementgoal such as fifteen incidents or less is inspired special cause. The assumption here set and nothing is done to help the could be that whatever solution you implemented system improve from the current twenty, then we are setting up the employees working with Problem One for failure.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 18 of 42 2. Setting standards when the incidence averages are small numbers or less than one a. Why bother when the number of incidents is low?
Now lets look at the problems grouped into Standard Five. In Figure 15, page 15, My hand would be there are four problems, Problems Nine to more steady if I Twelve. What distinguishes these had not gotten problems in Standard Five is that all of loaded last night. them occur with an incidence of less than one a month. It could be that you might simply ignore problems that are so infrequent. However, if the problem has a significant effect on the patient care experience you would still want to make improvements. The rankings done in the weighted multi-voting matrix are meant to give us an idea of the importance of the process to the patient care experience. That importance would not be dependent on the number of incidents. For example, in health care quality improvement language, the JCAHO uses the term sentinel event. CMS calls them never events. JCAHO defines sentinel event:
Sentinel event definition
A sentinel event is an unexpected occurrence involving death or serious physical or psychological injury, or the risk thereof. Serious injury specifically includes loss of limb or function. The phrase or the risk thereof includes any process variation for which a recurrence would carry a significant chance of a serious adverse outcome.6 An event that might lead to these dire outcomes is one to avoid, whether the incident count is low or not. The idea that there is a risk of a sentinel event will trigger a recording of one means that your system caught the error before it was too late. However, the process that caught the failure and stopped or corrected it was not a part of the standard procedure. Thus, if the event took place again it might well proceed on to cause a death or major injury or trauma. b. Performance data needs to look at an average instead of just the next month when the averages for the incidents are small numbers
Unlike the problems covered by Standards One to Three in Figure 15, page 15, Standards Four and Five cannot be readily evaluated simply by looking at only the next months data. The average is below one. Yet all incidents are in whole numbers. There is no way to have a single month come in a less than a whole number. Thus, if we are going to rate the items in Standard Five or even Standard Four we need to average the last three months. Consider the alternatives. If you set the standard at zero per month you are asking for perfection. If you set
6
See JCAHO definition at
http://www.jointcommission.org/NR/rdonlyres/F84F9DC6-A5DA-490F-A91F-A9FCE26347C4/0/SE_chapter_july07.pdf.
Retrieved October 6, 2007.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 19 of 42 it at one you are allowing one per month, which is more than the items in Standard Five are averaging now, so there is no challenge to improve. For the items in Standard Five we need to review the data before deciding how to rate the items. Between Problems Nine to Twelve the annual monthly incidents ranged from 0.8 to 0.4. The monthly high (data not shown) was four and the low was zero. The average among the four problems was 0.67 incidents per month. This information is listed on row three and I expect to see it on your work. Using this information I set the standards seen in Figure 20. As with Standard One, for Standard Five I used the average or fair performance category to score the performance that is currently taking place. This will be quite inappropriate if one or more of the incident counts is actually acceptable to you as it is or it is something beyond your authority to correct. If that was the case, then you would need to delete those items and set up a separate standard for them. That Figure 20 standard would allow the current performance to be graded as either met the standard or good performance. So far I have standards for one item with Standard One and four with Standard Five. The assignment calls for evaluation of the performance of six or more items. Thus, to complete the assignment I would need to pick one more group from Figure 15, page 15. F. Grading or scoring performance against the standards
Once I have my standards developed for each item or groups of items I would need to develop a table to score it. The way the table is developed will depend on how we want to show the data. In the example that follows I am simply going to act as if I have developed standards for the required six items even though I only have standards for five. Im sure you are tired of the endless examples. There are several ways to set up tables to score the performance. The method we shall use for the project is to set up a table in which each member of the group is scoring the performance against the standard. Then a radar chart is developed based on those values. The disadvantage of the radar chart is that it does not show performance over time. It is a snap shot. We could use the radar chart and include other charts that showed the performance over time. These could be line charts, bar charts, or two of the charts taken up later in the course, run charts and control charts. The disadvantages of these temporal charts is they do not readily
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 20 of 42 show the scores of individual members without becoming very cluttered. As is always the case with tools of any kind, no tool does all the jobs you want to do. Relative to tools, the text below takes considerable attention to follow. For many, the movie scoring performance.avi or scoring performance.wmv will prove easier to digest. Figure 22, below, shows the setup needed for the radar chart. A similar table is available for you on Project 5 data set.xls on the page Radar Table. Assume that the data for the next month have come in. In the current example there are two standards in use to evaluate the performance of five items on a check sheet. There are listed in columns A & B.
Figure 22 Columns C to E show the most recent data. These data are what we compare to the standard in order to score the performance of the most recent time period. Column C shows the historical data from the 12 month average of the Im paying incidents. As more months of data come in the attention. average would move forward in time, but always covering twelve months. Column D shows the actual scores performance for the most recent month, January 2008. Column E takes some attention. Row five holds the data for Problem One. Problem One is evaluated using Standard One, Figure 18, page 17. Problem One had so many incidents per month we knew that whole numbers could be used to evaluate the performance. Thus, E4 of Figure 22 is blank for Problem One. However, the four items evaluated using Standard Five, Figure 20, page 19, only have a few incidents over the last twelve months. As seen on Figure 22, the average monthly incidents in C5:C8 are less than one. Thus, to properly evaluate the January performance we have to get a three month average. Thus, E5:E8 are the average incidents for November 2007 to January 2008. You have to pay attention to make sure the right performance measure or metric is being used is appropriate for the data. Later, pages 35 to 40, I show how to develop a formula so that paying such close attention is obviated. Columns F & G will fill in when the members score the January performance of Problem One in D4 (Figure 22) against Standard One, Figure 18, page 17. The members look at what is in D4 and then compare that to Standard One and come up with a performance score. For example,
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 21 of 42 in January Problem One had twelve incidents. On Figure 18, page 17 a twelve would be described as good performance since twelve is within the range of eleven to eighteen incidents. Standard One allows for a score of 7.0 to 8.9 for performance that falls within the eleven to eighteen incident range. Twelve is much closer to the high end of the good performance range than the low end. Thus, I scored Problem One as an 8.5. My scores for all the items are in column H of Figure 22. Rather than review my reasoning process for each of the next four items that are scored using Standard Five, page 19, Ill let you review the situation and see if your judgments match mine. Members can differ on their scores. I listed for Problem One varying scores for the other members of the group. Nonetheless, the scores should be within a range that makes sense relative to the standard and the performance values. There may be a situation in which a member simply refuses to do their evaluation job correctly and puts down outlier scores. If that happens, the corrective is to note the aberrant behavior in the memo. I make my grading decisions mostly based on the average. If as a group you score the performance markedly different from what the standard would say you should do, expect me to use low numbers in evaluating your work against my standards. Again, it is okay to have outlier votes from the members, but make sure you note it in the memo so I dont have to note it with point deductions. Do not let these instructions force you into voting exactly the same as members. I used decimals and there certainly can be differences of opinion as to the exact value a performance should earn. Now lets get to the specifics of the radar chart. The radar chart is built from the table in Figure 22, page 20.
V.
Memory Jogger II, pp. 137-140, radar charts.
At this point of the project you would have evaluated the data in the Radiation Therapy Department and selected six or more of the line items as candidates for development of quantitative standards to use in evaluation of performance. My example used five items. Radar charts are the part of the tools the Memory Jogger II contains which are in the category of working with ideas. Oddly, few people are aware the chart is an available option in Excel. I only provided a blank page called Radar chart for this exercise.
A. What radar charts are intended to do
The main purpose is to compare actual performance to an set of standards for performance. The key value of the chart is to visually show the gaps, as opposed to a bunch of numbers on dense tables. The Memory Jogger II tells us the tool accomplishes the following: < Makes concentrations of strengths and weaknesses visible. < Clearly displays the important categories of performance. < If done well, clearly defines full performance in each category.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 22 of 42 < Captures the different perceptions of all the team members about organizational performance. Figure 24, to the right, shows a completed radar chart, illustrating the difference between items A through H and the gaps relative to achievement against the standard. The numbers in parentheses indicate the variance from the standard, which is assigned a value of ten (10). The actual scores are shown at the ends of the point on the spoke where the actual performance lines are connected. In this example, item H shows virtually no accomplishment, while items G, F, and D are at a six or seven. If the expected performance standards were realistic, this radar chart indicates that something broke down in the implementation stages of virtually every item. Figure 24
B. Steps in the process
1. Develop and define the categories to rate
The Memory Jogger II recommends use of brainstorming or headers from an affinity diagram to create the categories. The categories must then be defined in terms of what full performance means. Full performance should be the standard, the goal, or something desired that is achievable. Raters should also define what counts as non or substandard performance for each category. We have done both for Project Five when we set the standards. See Standard One, Figure 18, page 17 and Standard Five, Figure 20, page 19 for definitions of performance. Sample goals could include: < < < < < < accomplishment of JCAHO standards or other licensing requirements accomplishment of goals in the strategic plan how well the organization meets the goals of its stakeholders and customers accomplishment of departmental indicators comparison of the organization to benchmarked criteria (a standard developed from comparison with peer organizations, sometimes considered the best in the class) comparison of the organization to the criteria for TQM developed by Deming
After development of the categories and the definitions of full and substandard performance, the task is to construct the chart. 2. Construct the chart
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 23 of 42
I am going to skip what the Memory Jogger II tells us about drawing a radar chart. Since we have computer programs, that discussion seems retro.
3.
Set up a scale for the spokes
The Memory Jogger II says the closer performance falls to the center of the circle the worse the performance. Thus, category H in Figure 24, page 22, being the closest to the center is portrayed as doing the worst. Full achievement of the standard means the mark for performance would be at the outer edge. The scale of zero to ten is typically chosen, as already done way back on page 16 with Figure 17. The same value is used for each spoke, whatever value is chosen.
4.
Rate all performance categories
For Project Five we have provided the rating in Figure 22, page 20. We have ratings from individual team members and we have the average. This follows the Memory Jogger II to first have individuals rate where they think performance is now. The group evaluation is the derived average. The text tells us to consider both the clustering and the spread of the individual ratings. Figure 25, to the right, shows the locations of the individual ratings in small dots and the team consensus in large dots. This is what a drawn radar chart would look like, not the Excel version we shall use.
Figure 25
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 24 of 42 The Memory Jogger II advises us that an improvement in use of the radar chart is to put the amount of the gap in parentheses adjacent to the category description, as seen in Figure 26, to the left.
5.
Interpret and use the results
The Memory Jogger II tells us that the radar chart will show the amount of the gap, if filled out correctly, but not the importance of the gap. The categories would have to be ranked or weighted relative to one another to know which ones are the most important. The radar chart cannot do this, but the weighted multi-voting conducted earlier for Project Five did create the desired weighting of the items the spokes Figure 26 represent. This was done outline section ?, starting on page? and Figure 13, page 14 in terms of the impact of the problem on the patient care experience. I shall show how to use the weights from the weighted multi-voting matrix after completion of the radar chart.
VI. Examples of radar charts
A. Progress towards meeting the vision elements in a strategic plan Figure 27, below, shows how far an organization has to go in meeting the vision elements specified in a strategic plan.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 25 of 42
Figure 27 Note that the descriptions on the spokes are more detailed than prior examples. Note too, that the organization has hardly achieved any of the items listed as vision elements in the strategic plan. A four (4) is the highest score, the lowest a 0.5, with most being a one (1).
B. Using Excel to make radar charts
One of the advantages of getting each member to rate the items alone and then showing them on the graph is that it exposes any major differences in evaluation among the group members. So often, group dynamics are such that a dominant person can sway the group to his or her way of thinking when ratings are based on consensus. This method takes that power away from the dominant person. This is much less likely to occur when the standards are as well developed as Standard One seen on Figure 18, page 17 and Standard Five, Figure 20, page 19. The movies radar.avi or radar.wmv show how to set up the radar chart, following the templates provided and the assignment requirements. Since 2007 and 2003 versions of Excel differ
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 26 of 42 considerably here, the movies are the best way to go. For those items that are common, prior to the radar chart itself, the material below is useful. 1. Creating a table for the inputs to the radar chart A table similar to the one below is found on page Radar Table. The first step is put the data into a table format which allows it to be easily put into a spreadsheet. This has already been done in Figure 22, page 20. For ease of explanation I am copying Figure 22 here and calling it Figure 28.
Figure 28 The items rated are in column B, B4:B8. Columns H to M contains the votes of the members of the team, H4:M8. In this case, since there is no member six, the input area is H4:L8. Column N contains the value that represents meeting the standard completely, in this case, a 10. Column G contains the average of what the members voted. Column F contains the variance of the average from the standard. Column F subtracts column G from column N. Now we can set up the graph. 2. Setting up the radar chart Given the variations between Excel 2007 and Excel 2203, you are better off watching the radar.avi or radar.wmv movies appropriate to your version than for me to attempt to provide lots of instructions here. There are only a couple of things I shall mention below to make sure you do them. Figure 29, below, shows what the finished product should look like. If you cannot get it to look like Figure 29 after watching the movie, then pay attention to the tricks noted below.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 27 of 42
Figure 29
3. Making sure the data are picked up correctly If you are accustomed to making graphs in Excel you may want Uncle Billy Gates to set your graph up for you. To do that the usual process is to highlight what you want on the graph, choose a graph format (bar, line, etc.) and then let Uncle Bill set it up for you. Often that works. Let me warn you. It will not produce the graph I want here. I have tried several different ways to make it work. To create what the graph needs to look like takes a lot more work than that. Yes it is tedious. No it is not fun. Quality and good work sometimes take due diligence to simply gut out getting it. If doing the detail work is not your cup of tea, there are other majors or lower paying jobs within health care. If what we do was supposed to be all that simple why do you think it takes a college degree to become proficient at it? To make these graphs work you are going to have to add each data component one at a time as part of a series. You then have to edit each and every line on the graph to make it look right. See the movie radar.avi or radar.wmv for how to do this.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 28 of 42 4. Tricks to make the axis labels show the variance from the standard The radar chart in Figure 29 shows the variance for each standard next to the label for its name, as you go around the axis. This is a bit tricky to make happen. One way is make the table where the data are have the names of the standards in one column and the variance values next to it. Thus, column A would hold the names and column B the variance values. Then when you set up the X axis for the radar chart you highlight the data in both columns A & B at the same time. However, Figure 28, page 26, has the names in column B and the variance data in column F. In previous semesters students in one class had shown me how to get the axes legend to work even when the components were separated. I could not get it to work, even after finding advice on the Internet about how to do it. So, the fix will be very inelegant, but will work. W hat we want is the graph to show the name of the item evaluated and the variance of that item from the standard of ten, regardless of the methods needed to set up the table. To set up the values so they will be read properly I go back to Figure 28, page 26. I copy out the names of the items we are evaluating (B4:B8) and put them below Figure 28. I then copy out the variance values, F4:F8, and copypaste them as values adjacent to the names. The outcome is seen in Figure 30, to the right. Or I could use the =cell reference method (what I did on the movie) to make sure that if any of the data feeding in to the table changes, this will read it and update the resulting graph automatically.
Figure 30
What has to occur now is that I am going to set up the X axis labels in the radar chart to read B10:C14. This trick will put the name of the item on the axis and will put the amount of variance from the standard of ten next to the name. 5. Final graph: what does it mean? Now we have the final graph. See it in Figure 29, page 27. What do we have after all this work? This one graph can allow managers and employees to see at a glance how well the organization is doing on the items being scored by the graph. Your assignment is to evaluate
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 29 of 42 how the organization is doing overall, and to discuss the specific items that make up the graph. Lets take the specific items first. Only one item, Problem One, is even close to meeting the standard of ten. The variance for Problem One is -2.1. Putting this into a grading context, that is equal to 21 points off of a 100. In other words, Problem One is at a high C. However, the other four problems under evaluation were far worse. All of them were at less than one half of the standard. That would put them into the F- category. Clearly, there is a lot of work to be done to correct these deficiencies. Notice how I was able to discuss four problems at one time. Only if the values are different from the others is a separate item-by-item review required. The grouping of responses here saves time on the analysis. Next, or it could have been first, we want to say how the organization is doing overall relative to these problems. To find that out we have to compute the average score of the items on the graph. I have simply added the average computation to G9 from Figure 30, page 28. (Yes, I know there is no average in G9 on Figure 30, page 28. But, the values are on your template, in column G, at the bottom. The value that should be in G9 on Figure 30 is 4.8.) You do not have to compute it. You only have to read the number from the template and interpret it. The average is 4.8. Given that the scale has ten as meaning the standards were met, the organization is very far away from meeting the standard. Performance overall is at an F- level. Maybe 50% does not always mean A word of caution is appropriate here. Some students think that average. if you are about half way between zero and ten that your performance is average. They are confusing the idea of what average means in a bell shaped curve or normal distribution with what it means to be halfway to somewhere. In a normal distribution the average is the number for which half the values in the data set are higher and half are lower. In that sense it is also the middle or median number. However, average, when making an evaluation, means the score that is most likely. In most academic endeavors that score is a C or a B. In other words, the average evaluation is between 70% and 89%. Average in this case is not 50%. Since we are evaluating performance instead of measuring distribution, use the academic grading scale to interpret the results. If you tell me 50% compliance with a standard is average then I shall decide your knowledge is at about 50% and score it an F. However, you will not likely interpret that F as average and thus okay. As always, thinking is authorized. VII. Evaluation of performance using the weights of the items The last step in the analysis, before writing up the memo, is to see if the weights given to the items from the weighted multi-voting lead to any difference in interpretation than what we have seen on the radar chart on Figure 29, page 27. There is a key difference in methodology between the radar chart as I have constructed it and the weighted evaluation of performance. The radar chart is based on the votes of several people as to how well the organization is
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 30 of 42 performing on certain line items. Even though quantitative standards were developed the voters may not follow the standards. Bias is possible. Thus reliability could be variable. A different group of voters might score the exact same performance a different way, even if they had the same standards to use. The method for the weighted evaluation is to multiply a weight times the most recent performance scores. Although the weight was developed by a vote as to what was the most significant relative to the patient care experience, once that is developed the scores that emerge will not be further dependent on opinions. This method is completely reliable. Whatever the performance scores, the already determined weight would be applied to it. The only possible bias that could occur here is the construction of the weights themselves. This assumes that the reporting of an incident is itself done in a consistent and reliable way. Now lets move on to create the table. Most of what will go on the first several columns of the table will be similar to what was put on the radar chart table, seen in Figure 22, page 20. We shall have to put information from the weighted multi-voting onto the new table, as those are the weights. Since the weighted multivoting was done so long ago, Figure 13 on page 14, I am copying it here and calling it Figure 32.
Figure 32 The components of the weighted multi-voting matrix on Figure 32 that are of interest to us at this point are the percentages in column I. These percentages tell us that Problems One and Eleven are very significant to the experience of patient care when they occur. While we can readily guess that Problem One will come out as the big deal, owing to the fact we know there were a lot of incidents, due diligence calls for us to set up the analysis correctly. We know all of the items that were using Standard FiveProblems Nine to Twelvewere of low volume. However, given that the multi-weighted voting seen in Figure 32 shows us Problem Eleven is so critical to the patient care experience we need to see how it comes out in a weighted evaluation. Keep in mind that if Problem Eleven were something that would fit the definition of a sentinel event, using the JCAHO language from page 18 to 18, we would need to watch it closely if any
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 31 of 42 incidents had taken place or even if they could have taken place. Lets set up the table needed to get the weighted scores. The eventual table you will need is on the last page of the file, Weights. Figure 33, to the right, is copied here from Figure 30, page 28. I am not going to edit Figure 30, I am going to make a new table. The items in Figure 33 are simply copied from Figure 30 for that purpose.
Figure 33 A. Issues in deciding what are the terms of the product for the weighted computation Before we can use Figure 33 we need to pay attention to which performance values will be used in the weighted performance computations. Problem One, with twelve incidents in January 2008 would use the value of twelve. This is the way Standard One was set up. Problems Nine to Twelve are using Standard Five. Because all of the incidents came in with an average of less than one per month I chose a three month average as the value to use when rating performance against the standard. So, should we use the actual January 2008 data (column D) in the weighted computation or the three month average data (column E)? In working through this problem I found it more complex than expected. I created a flow chart to help guide you through the decision making process. It is Figure 34, on page 32. The material following tracks the flow chart. Given that we do not know what the generic terms like Problem One or Twelve mean our ability to make judgments is limited. All of the problems listed in rows five to eight had higher incident levels in January 2008 than the twelve month average reported in column C. For three of the items, Problems Nine and Ten and Problem Twelve, the three month average in column E is a lower value than the incident count in column D for January 2008. This suggests that January was an unusually high month in comparison to the prior twelve months. On the opposite side, Problem Eleven has an average of 1.7 (E7) and a January incident count of one (D7). This suggests that for Problem Eleven, performance improved in comparison to the twelve month average. However, knowing all of this does not help us make the decision about whether to use the three month average or the January data when computing the weighted evaluations.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 32 of 42
Figure 34
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 33 of 42
B. Sentinel events and their influence on the computations of the weighted score The only thing that would tell us we should use the actual most recent incident count is if we can tell that the problem would lead to a sentinel event if it occurs. A sentinel event (see pages18 to 18) is an unexpected occurrence involving death or serious physical or psychological injury, or the risk thereof. Serious injury specifically includes loss of limb or function.7 Thus, any number greater than zero is a bad thing. If we make a judgment that an occurrence of a particular problem would or could lead to a sentinel event then we would weight the most recent performance using the actual values from the most recent data. This means that when looking at data that has a very low incident count you would use the most recent data in the weighted evaluations if it could lead to a sentinel event and use the three month average if it would not lead to a sentinel event. Simply looking at the results of the weighted multi-voting will not tell us if something is a sentinel event. For example, Problem Eleven was seen on Figure 32, page 30, the multi-weighted voting table, as having the highest likelihood of contributing to a negative patient care experience of any of the five items scored. That would incline us to think that it might be a sentinel event if it occurs. However, a negative patient care experience need not lead to death, trauma, or loss of function. Someone inappropriately discriminating against you in some way or otherwise disrespecting you might have a major impact on the patient care experience. But it would not lead to death, loss of function, or trauma. Thus, the only way to decide which data to use in the weighted evaluations is your own judgment as to the consequences of the incident. Knowing whether some process is capable of producing a sentinel event is the deciding factor on whether to use the actual value of the most recent time period or to use an average of the most recent three months of data. Thus, we need to add that decision element to the table in Figure 33, page 31. Since we have no way of knowing what the events are when they are labeled as simply Problem One and I would hope sentinel event processes are rare, I shall assume none of the problems on Figure 33 are sentinel events. The table that was Figure 33 now looks like Figure 35, below, or soon will.
See JCAHO definition at http://www.jointcommission.org/NR/rdonlyres/F84F9DC6-A5DA-490F-A91F-A9FCE26347C4/0/SE_chapter_july07.pdf. Retrieved October 6, 2007.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
7
Page 34 of 42
Figure 35 Compared to Figure 33 on page 31, Figure 35 has added five columns. Column F is where the judgment about whether the item in column B is a sentinel event. Column G contains the weights from Figure 32, page 30, the weighted multi-voting matrix. Those numbers had to be copy pasted in as values. Column H is the weighted value of the performance and columns I and J are set up like the Pareto analysis done previously. Figure 6 on page 9 was where the tables that do these computations was first shown. Figure 8 on page 10 showed the formulas to create Figure 6. Those same formulas are in play here on Figure 35. The normal process is to sort the table in descending order, something not yet done. C. Methods to do the weighted evaluation calculation The only new thing to do relative to learning is how to manage the weighted average computations. I am going to show three ways of doing this. The first way is wrong. The second way is correct but woefully inefficient and error prone. The third wayyou guessed itis robust and prevents errors. Figure 36 shows the first method, which gives the wrong answers for column H. Nonetheless, it is a very easy error to make. The formula to give a weighted result has as the product the value itself and the percentage or other value that gives the weight that item should have. In computing H4 we see that the value in D4 is 12. The weight given to Problem One (A4, not seen in Figure 36) is 23%, G4. If these two are multiplied we get a value of 2.7, as seen in H4. This is also shown in the box titled Computing a Figure 36 weighted average in E12:H15. That answer is correct.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 35 of 42 If we simply copy that formula down, as we have been doing with most formulas so far, the formula in H5 would contain what you see in H15 and give the outcome of 0.3. The other values in H6:H8 are using the same formula of multiplying the performance in column E by the weight in column G. However, all the values in H5:H8 are wrong. Simply copying the formula down as done in Figure 36 overlooks completely that we are supposed to treat items with small averages, less than 3 per month lets say, by using the average of the last three months data. That was correctly done in Figure 35, page 34. The difference is seen more fully in Figure 37, below. In Figure 37 I have shown the difference between the two computations. What was done in Figure 36 is shown in L2:N16 and most especially the box in L12:L16. The correct computations are to the left of column L. The difference in the outcomes is especially pronounced for row seven. Cell I7, the correct answer, is 17.7% and M7 is 10.9%. The incorrect formula considerably understates the extent of the problem with the row seven problem, Problem Eleven. D. Creating a robust formula to handle the options Now, here is the problem that remains with Figure 37, even if it has the right answer. The person setting up the formulas has to remember whether the formula to use will be the values in column D or column E. There is a fix to this such that whatever column is correct would be chosen automatically. In this way, the table becomes much more robust and is not as likely to be wrong from operator errors. Even if you are unlikely to make an error, can you be so sure of those who may be keying in data after you give them the job of keeping these tables updated?
Figure 37 The robust way to make sure you have the right formula in column H is to recognize that the choice of column D or column E in the product depends on whether the item is checked as in a sentinel event category. To write a formula to check for the conditions under which D or E is the correct choice we have to determine the conditions for each. When I started to do this I thought
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 36 of 42 the formula would be easy. It was not. I am only expecting you to read the following if you have an interest in setting up formulas that would test for conditions so that you can ensure data input is correct. This is a very important skill, but not one which everyone will either appreciate or want to do. To set up the formula requires consideration of the conditions under which answers in H4 and below will vary as the inputs to the answer vary. The formula needed is below. Just a glance can tell you it is complicated. However, if you break it down into parts it is not really that complex. =IF(AND(F4="N",CELL("type",E4)="b"),G4*D4,IF(AND(F4="Y", CELL("type",E4)="v"),G4*D4,IF(AND(F4="N",CELL("type",E4)="v"),G4*E4,IF(A ND(F4="Y",CELL("type",E4)="b"),G4*D4,"Enter 'Y' or 'N' in column F")))) Here are the inputs and what might happen. The possible conditions are shown in the table below. All the cell references are to row four. The input cells are as follows: Performance data for the most recent period = D4. Three month average of the performance data, used only with incident counts where the average per the last 12 months is less than three = E4. Indicator as to whether the item is a possible sentinel event=F4. The weight of the item relative to its possible impact on the patient care experience=G4. The outcome of the computation=H4.
The values in G4 are always part of the calculation. The product in H4 will vary according to whether the event is a sentinel event, in which case the actual data are used in D4. Otherwise, if it is not a sentinel event and it is an item with a 12 month average less than three per month it would use the three month average in E4. Those conditions are organized below. 1. Conditions the formula must address 3 mo avg of recent #s E4 blank
Recent # D4 #
Sentinel
Weight
Result in H4
Comments
F4 N
G4 # D4*G4 Not a sentinel event, no values for E4 because avg # of incidents is high. Uses recent period data. A sentinel event, 3 mo avg values ignored. Uses recent period data.
#
#
Y
#
D4*G4
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 37 of 42 3 mo avg of recent #s E4 #
Recent # D4 #
Sentinel
Weight
Result in H4
Comments
F4 N
G4 # E4*G4 Not a sentinel event, E4 not blank as avg values are <3 per mo, uses 3 mo avg. A sentinel event, no values for E4 because avg # of incidents is high. Uses recent period data. If the data input person left F4 blank or entered something other than a Y or a N the formula will return a message that says put a Y or N in column F.
#
blank
Y
Neither N nor Y are given
#
D4*G4
#
blank
#
Returns an error message
2. Handy Excel functions I use often To write the formula that puts all this together and makes the correct decision requires three Excel functions. The if function is the most common. It operates this way. If(condition is met,do this,otherwise do something else) As you can see from the table above, there are four conditions that must be tested. W hen testing in one formula for multiple conditions you are nesting the formulas. Here is an example of how it would look. If(condition is met,do this,if(another condition is met instead, do something else,get outa Dodge)) The nested if formula above says to see if the first condition is true. If it is not, then test for a second condition. If it is true, then do what that requires. If it is not true then neither condition was met. Then get outta Dodge, or whatever else makes sense. In Excel you can have up to six if tests nested together. Some of the formulas I wrote for the grading templates exceeded six if tests (the ones testing for variance between your grades and mine when groups grade other groups). When that happens you make separate formulas and then have one formula refer to each. Then there is no limit on the nesting. Each of the rows in the table above has to make two tests to decide what to do. The formula has to check to see if the item is a sentinel event. That is determined by the presence of a Y or a N in the F column. Normally you would expect anything that could be a sentinel event to have a very low rate of occurrence. Thus, if it were not a sentinel event the computation of the weight would use a three month average of the most recent data since the 12 month average would be less than three and likely less than one. For the formula to test properly it needs to see
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 38 of 42 what is in not only the F column relative to being a Y or N but whether what is in the E column where the three month average data are posted. There are only two options for the E column. It is either blank in the tested cell or it has something in it. I have assumed that if it has something in it, it will be numbers. Thus, each test has to determine if is a sentinel event and if column E is blank. These types of tests are done with the and function. The syntax is AND(logical1,logical2, ...) Up to 30 conditions can be tested. If any of them are not true the and function evaluates the condition as false. The and function has little use apart from the if function. Here is how it would look. =if(AND(F4=Y,E4 is blank),multiply the weight times the recent monthly value, otherwise do something else) All the and functions in the long formula above follow the if just as above. Note the quotation marks around the Y. Any reference to text or strings (as they are called in the trade) must put that text in quotation marks. The various formulas I wrote that read off your name, group number and member number are string formulas. They are neat, but no time here. Now we need one last function to get what we want. We need a way to determine if the contents of the E4 cell is blank or has a number in it. For getting information about the contents of a cell the =cell function does the job. The syntax for it is CELL(info_type,reference). There are many types of information the function can give about the contents of a cell. To see all of them simply select cell from the function list and then click on the question mark in the lower left corner when you get the general information panel concerning what the function does and how it works. Figure 39 shows how this looks. To get this menu I clicked on the function button on the main menu. That button is seen in Figure 40, below and to the left. The Excel help file gives this description for what the word "type" will tell you about a cell when using the cell function.
Figure 40
Returns "b" for blank if the cell is Figure 39 empty. Returns "l" for label if the cell contains text. Returns"v" for value if the cell contains anything else.
Since you can only enter text or formulas or values, then those choices cover the options. A string or text formula would be seen as a value, even if it looks like text on the screen.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 39 of 42 Below is a table similar to the previous one. Instead of saying in the last column what is being tested, the part of the long formula that applies to that test is given. 3 mo avg of recent #s E4 blank # # blank
Recent # D4 # # # #
Sentinel
Weight
Result in H4
Formula needed to extract the portion described on the row in this table
F4 N Y N Y
Neither N nor Y are given
G4 # # # # D4*G4 D4*G4 E4*G4 D4*G4
Returns an error message =if(AND(F4=N,cell(type,E4)=b),D4*G4, goes on to test for the next condition below) if(AND(F4=Y,cell(type,E4)=v),D4*G4, goes on to test for the next condition below) if(AND(F4=N,cell(type,E4)=v),E4*G4, goes on to test for the next condition below) if(AND(F4=Y,cell(type,E4)=b),D4*G4, goes on to test for the next condition below)
#
blank
#
"Enter 'Y' or 'N' in column F")))) This is the last condition. If incorrect text is in F4 this error message signals.
Owing to the complexity of the formula I set up a simplified version of the table above to work out what had to be done. Think not that this all goes as logically as described. When I sat down to write the formula I thought it would be a simple if with no nested conditions. When that did not work I began trying more complex formulas. Some gave the wrong answer. I started to add in the and and the cell functions. I already knew of these from work in this area over many years, dating back to the middle 1980s, when most of you were likely either gleams in the eyes of your parents or toddlers. W riting complex formulas like this is a combination of knowledge, skills, and attitudes. A primary attitude is the willingness to try and to not accept failure. As Edison said when asked how he discovered a substance that would work as to make a lightbulb: I ran out of things that failed. Sometimes these formulas are the same way. Nonetheless, like the pride an Are you thinking what Im thinking? inventor gets, there is a pleasure when it does work.
3. Test your formulas Part of the work of writing formulas is to test them under numerous conditions. What seems obvious to you when working with data may be opaque to others. For example, when I started working with spreadsheets I could not get data base queries to work. It was only when one of my superiors showed me that I
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 40 of 42 had used the letter l for the number 1" that the problem became clear. That was how I learned on a typewriter. It was obvious to me, but it sure messed up the data base. Below, Figure 43, I show the data base test results for the formulas. The correct values are seen on Figure 37, page 35. Look at how the tests worked.
Figure 43 I changed F4 to Y from N and it still did the correct multiplication. I changed F5 to Y from N and it correctly changed the multiplication from E5*G5 to D5*G5. I changed F8 to Yes and the formula correctly told me in H8 that I had not entered the correct text.
This last feature, telling people to correct their entry, is a major boon to making sure the data base works correctly. Now, while it was tough to write the formula in H4, once I have done it then I can rest assured that if the data are entered correctly in columns D, E, F, and G that I do not have to depend on someone to remember which value is multiplied by which other value according to certain conditions. By doing a lot of thinking once, I save a lot of worrying and save the need to check the work too. People get very upset if they discover you have made some decision that affects them and it was based on faulty data. This is true whether it leads to a war or whether it leads to someone not getting a raise and a lot in between and on either side of those things. Now lets head on. The Excel portion of the project is done! The last part of the assignment is to pull together your findings in a memo. That is best reviewed by looking at the grading criteria for it.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 41 of 42 VIII. Memo grading criteria
In the memo you are going to assume you were a consultant in charge of working with the radiation therapy clinic. You are now writing your boss a report. The grading criteria are in two parts, Figure 45 and Figure 46, below. It will probably take three to five pages to fulfill the requirements, single spaced. The first section is what it says, a summary for a busy person. In the second section, you tell the boss how the purpose of the various tools used in the project, starting with Project #2, the affinity diagram. While some of them are redundant, all of them have some purpose in helping solve problems. When giving the Figure 45 answer, dont simply quote to me what it says about the tool in the Memory Jogger II or in my notes. Relate the tool to the real problems in the RT department. The whole purpose of these last four projects has been to see how to apply real tools to real problems. There is little value in simply knowing how to define the tools. The remaining questions on Figure 46 are more specifically directed to what took place in Project Five. They cannot be completed until the analysis is done. I have provided examples of this analysis throughout the notes.
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE
Page 42 of 42
Figure 46
Rev. January 26, 2009 (12:46pm) J:\SWT\HA4315\Class notes\Spring 2009 version\C_15_Spg09.wpd Reduce, reuse, restore, recycle, VOTE