Chapter 8: Is There an Improvement Project in My Data?

INTRODUCTION

Lean Six Sigma professionals often talk about picking the "low-hanging fruit," but what if the low-hanging fruit is invisible. I've noticed that one of the biggest challenges improvement professionals face is figuring out how to develop improvement stories from the volumes of data that reside in financial accounting systems, electronic medical records, and other data sources. In most cases, the data look like Figures 8.1 and 8.2. They show dates, hospitals, denied charges, physicians, diagnoses, patient age, length of stay (LOS), adverse events, and discharge status. Do some hospitals have higher denied charges? Why are they denied? Do some physicians have more complications, higher costs, or longer lengths of stay?

The data in Figure 8.1 resulted in $5 million a year in increased revenue. It only took an afternoon of data mining and root-cause analysis to figure out how to solve the problem and prevent these denied charges.

These kinds of data remind me of the old joke about the father who finds his daughter digging through a pile of horse manure. When he asks her what she's doing, she replies: "There has to be a pony in here somewhere!"

Is there an improvement pony in your pile of data? With data such as these, you will want to do some data mining with Excel to find the hidden low-hanging fruit. Every multi-million-dollar improvement project I've worked on began with these kinds of data. Haven't you waited long enough to learn how to use Excel PivotTables to eliminate defects, mistakes, and errors that cause patient harm, aggravate clinicians, and weaken the bottom line?

DATA MINING WITH EXCEL AND THE QI MACROS PIVOTTABLE WIZARD

Excel's PivotTable function can count or sum the number of times a word or phrase occurs with a value. It's pretty remarkable what it can summarize, but I have found that most people just don't know how to use it. Many gave up trying to learn. After years of learning how to turn these kinds of data into a pivot table, I developed a mental strategy for organizing the rows and columns. I coded this strategy into the QI Macros PivotTable Wizard to make pivot tables simple. Simply select up to four headings using the mouse and the ALT key (e.g., Region, Entity, Admit Date, and Denied Charges using the data in Figure 8.1). Then choose the QI Macros Data Mining—PivotTable Wizard to create the pivot table (Figure 8.3).

Data Mining with Excel