QI Macros Lesson 12 - Analyzing Your Data Using Pareto Charts | |||||||||||||||||||
We help people think! |
Most companies have lots of data, but sometimes have a hard time figuring out what to do with it. I've found that I often use a common strategy for analyzing a company's data. I usually slice and dice an Excel table in the same way:
Let's look at an example. Here's a simplified table from a garage door installation company that was having trouble making a profit because of service and warranty calls.
Because the company installs doors for builders, they sometimes have multiple service calls to install each door piece-by-piece. They may have to install, replace, adjust, or lubricate some part to get the door working properly. They work with five key parts: door, motor, track, vinyl trim, and T-lock. I've highlighted my first focus in yellow: total parts.
Motors are the big bar at 33%. Then I drill down to look at the motor row by type of service (yellow):
Adjusting the motor is 55% of total motor service. Now we have something to analyze! Now let's pull back and look at the service row:
As you can see, installation is 41% of the total followed by adjustments. These two are 72% of the total. Next, I'll drill down by looking at the installation column:
Installations of vinyl followed by T-locks are 80% of the total. Now we've got something to analyze. We could have one team analyze vinyl installs and another analyze T-lock installs. The 4-50 Rule I keep hammering this point: 4% of any business is causing 50% of the waste, rework, and delay. As you can see from these examples, by slicing and dicing the data horizontally and vertically we can find two or three key problem areas that could benefit from root cause analysis. Start with the total columns and rows. Draw pareto charts with the QI Macros. Then use this information to narrow your attention to one key row and column within the table. Draw the lower-level pareto charts from this data. The "big bars" in the lower-level pareto charts can be turned into problem statements to fill the head of your fishbone diagram. Here's an example: Problem Statement: During 2006, adjustments accounted for 55% of all motor service calls which is higher than desired and caused customer dissatisfaction and a loss of $60 per service call. Start using the QI Macros to slice and dice your tables (no matter how large). You'll find it easy to find the 4-50 and start making breakthrough improvements. QI Macros Pivot Table WizardIf you have raw transaction data, you may need to summarize it before you can run a chart like the Pareto chart. Use the Cross Tab Pivot Table Wizard to summarize your data before you run your charts. Watch Pivot Table Wizard Video
© 2007 KnowWare International Inc. (888) 468-1537 |
It contains over 70 fill in the blank templates such as the Ishikawa diagram, QFD, DOE, FMEA, PPAP, and Gage R&R for MSA. Performs ANOVA, t-test, F-test, and regression analysis.
|
|||||||||||||||||
