Breakthrough Improvement with QI Macros and Excel

Description

Finding the Invisible Low-Hanging FruitWhen I first got into quality improvement in 1990, I frequently heard consultants speak about “low-hanging fruit” just waiting to be picked. Two years later and thousands of staff hours later, I stillhadn’t found any low-hanging fruit. Over the last two decades, I have heard from hundreds ofcustomers whose companies implemented Six Sigma only to have similar dismal results.In any company, if there really is low-hanging fruit, it’s usually visible everywhere from thefactory floor or nursing unit to the management conference room. When it’s that visible, anyone canpick it with a little common sense and a bit of trial and error.This is why there is no visible low-hanging fruit. Somebody has already picked it! And this iswhat stops most leaders from even considering the tools of breakthrough improvement—they can’tsee any more fruit to be picked. And, because they can’t see it, they come to believe that theirproblems are unsolvable, so they stop looking.In company after company, though, my own included, I have found orchards filled with invisiblelow-hanging fruit. You just can’t see it with the naked eye.You can, however, discern it through the magnifying lens of Excel PivotTables and charts. Theymake the seemingly invisible visible. They are the microscope, the MRI, the EKG of businessdiagnosis.When Louis Pasteur said that there were tiny bugs in the air and water, everyone thought he wascrazy because they weren’t visible ... to the naked eye. Everyone thought it was just an “ill wind” thatmade people sick.In today’s tough economic times, everyone laments about how hard it is, how an “ill wind” hasblown through their business, their industry, their economy. But have they considered using themodern tools of business medicine to root out the infectious agents in their business? Have they takenthe time to look for the invisible low-hanging fruit in their businesses? I doubt it.Someone sent me an e-mail the other day that said that even in the poorest run companies, he’d hadno luck finding the low-hanging fruit. But in every company I’ve ever worked with, I’ve foundmillions of dollars just waiting to be retrieved from the orchards of delay, defects, and deviation—thethree silent killers of productivity and profitability.Are you looking for the obvious? Or investigating the invisible?The low-hanging fruit is always invisible to the naked eye. Turn the magnifying and illuminatingtools of the QI Macros and Excel on your most difficult operational problems and stare into thedepths of the unknown, the unfamiliar. You’ll invariably find bushels of bucks, just waiting for avigilant harvester About the Author......Page 2 Title Page......Page 3 Copyright Page......Page 5 CONTENTS......Page 7 Preface: Finding the Invisible Low-Hanging Fruit......Page 11 CHAPTER 1 Spreadsheet Design for Breakthrough Improvement......Page 18 Horizontal or Vertical?......Page 19 Column and Row Headings......Page 21 Common Heading Mistakes......Page 23 Formatting Mistakes......Page 24 Data Collection for Breakthrough Improvement......Page 27 Dealing with Difficult People......Page 28 How to Set Up a Defect-Tracking Worksheet......Page 29 Data for Service Companies: Defects and Turnaround Times......Page 36 Time Data......Page 43 Summary......Page 47 CHAPTER 2 Excel Formulas for Breakthrough Improvement......Page 48 Calculate Ratios......Page 49 Excel Will Guide You......Page 50 Text Formulas......Page 52 Upper–Proper–Lower......Page 58 Time and Date Formulas......Page 59 Converting Times to Hours......Page 63 Logic......Page 67 Here’s My Point......Page 69 CHAPTER 3 Filtering Data for Breakthrough Improvement......Page 70 Create Subsets of Your Data Using “AutoFilter”......Page 71 Custom Filters......Page 75 Sort the Data......Page 79 Even Huge Spreadsheets Can Be Manageable......Page 81 CHAPTER 4 The Magnificent Seven Tools for Breakthrough Improvement......Page 83 The Magnificent Seven Tools......Page 84 CHAPTER 5 Follow the Money for Breakthrough Improvement......Page 90 The 4-50 Rule......Page 93 Reducing Denied Claims in Five Days......Page 94 Using Pareto Charts of Denials......Page 95 Verify Results......Page 97 Don’t Wait For Storm Season......Page 98 Conclusion......Page 99 CHAPTER 6 Where’s the Pain?......Page 100 The Dirty-30 Process for Breakthrough Improvement......Page 101 Quantify the Costs......Page 102 Understand the Pareto Pattern......Page 104 Analyzing the Dirty 30......Page 107 Analyzing Results......Page 109 Common Problems......Page 111 Fast, Fast, Fast Relief......Page 112 CHAPTER 7 A Picture Is Worth a Thousand Words......Page 114 Say It with Charts......Page 115 Process......Page 117 The Right Picture Is Worth a Thousand Words......Page 119 QI Macros Chart Wizard......Page 120 Here’s My Point......Page 127 CHAPTER 8 Excel Power Tools for Breakthrough Improvement......Page 128 Setting Up Your Data in Excel......Page 129 Prepare Your Data......Page 130 Tips for Selecting Your Data......Page 131 Data Collection and Measurement......Page 132 Check Sheets for Data Collection......Page 133 The QI Macros for Excel......Page 135 The QI Macros Are Easy to Use......Page 136 Creating a Chart Using the QI Macros Menu......Page 137 Create a Control Chart......Page 138 How to Use Custom Excel Chart Formats......Page 140 Creating a Control Chart with a QI Macros Template......Page 144 Data Transformation......Page 149 Summarize Your Data with PivotTables......Page 151 Analyzing Customer-Service Data Hidden in Trouble-Reporting Systems......Page 155 Analyzing Text with Excel......Page 157 QI Macros Word Count Tool......Page 158 Power Tools for Breakthrough Improvement......Page 159 Troubleshooting Problems......Page 160 Technical Support......Page 161 CHAPTER 9 Breakthrough Improvement Using Microsoft Excel......Page 162 Data Mining with Excel......Page 164 QI Macros PivotTable Wizard: The Shortcut to Breakthrough Improvement......Page 170 Breakthrough Improvement Projects......Page 172 Small Data Analytics for Breakthrough Improvement......Page 176 What Small Data Looks Like......Page 177 Turning Data into Improvement Projects......Page 178 Lost Time Analysis......Page 185 Get the Idea?......Page 191 CHAPTER 10 Sustaining Breakthrough Improvement......Page 192 Choosing the Right Control Chart for Sustaining Improvement......Page 193 Choosing a Control Chart......Page 194 The np Chart......Page 195 Other Control Charts......Page 196 Stability Analysis......Page 197 Goalposts and Targets......Page 199 Distributions......Page 200 Histograms and Capability......Page 204 Control Plan......Page 208 Create Control-Chart Dashboards in Minutes......Page 209 Create an XmR Control-Chart Dashboard Using the QI Macros......Page 210 Summary......Page 214 CHAPTER 11 Excel Drawing Tools for Breakthrough Improvement......Page 215 Visual Thinking for Breakthrough Improvement......Page 216 Problem Solving Is a Story Told with Pictures......Page 218 Mind the Gap......Page 220 Breakthrough Improvements in Speed......Page 221 Value-Stream Maps for Breakthrough Improvements in Speed......Page 223 Breakthrough Improvements by Eliminating Movement......Page 225 Time-Tracking Template......Page 227 Breakthrough Improvement Exercises......Page 228 CHAPTER 12 Hypothesis Testing for Breakthrough Improvement......Page 230 Statistics with QI Macros and Excel......Page 231 Hypothesis Testing for Variation......Page 232 Hypothesis Testing for Means......Page 236 Chi-Square Goodness-of-Fit Test......Page 254 Chi-Square Test of a Contingency Table......Page 255 Regression Analysis......Page 256 Door-to-Balloon Time versus Mortality Rate Example......Page 257 Multiple Regression Analysis......Page 259 Exercises......Page 260 Bibliography......Page 262 Learning Resources......Page 264 Index......Page 265