|
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:
- I use pareto charts to first analyze the "total" rows and "total" columns.
- Then I use pareto charts to analyze the biggest contributor in each total row or column.
Case Study
Here's a simplified table from a garage door installation company that was having trouble making a profit because of service and warranty calls.
Install Replace Adjust Lube Total
Door 2 5 1 0 8
Motor 2 1 11 6 20
Track 1 0 6 4 12
T-Lock 9 0 1 0 10
Total 25 7 19 10 61
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.
The first focus is total parts.
Motors are 33% of the total rework (20/61 * 100%). Then we can drill down to look at motors by type of service:
Install Replace Adjust Lube Total
Door 2 5 1 0 8
Motor 2 1 11 6 20
Track 1 0 6 4 12
T-Lock 9 0 1 0 10
Total 25 7 19 10 61
Adjusting the motor is 55% of total motor service (11/19 * 100%).
Now we have something to analyze! Now let's pull back and look at the service row:
Install Replace Adjust Lube Total
Door 2 5 1 0 8
Motor 2 1 11 6 20
Track 1 0 6 4 12
T-Lock 9 0 1 0 10
Total 25 7 19 10 61
As you can see, installation is 41% of the total (25/61 * 100%) followed by adjustments. These two are 72% of the total.
Next, we can drill down by looking at the installation column:
Install Replace Adjust Lube Total
Door 2 5 1 0 8
Motor 2 1 11 6 20
Track 1 0 6 4 12
T-Lock 9 0 1 0 10
Total 25 7 19 10 61
Installations of vinyl followed by T-locks are 80% of the total. Now we've got more to analyze. We could have one team analyze vinyl installs and another analyze T-lock installs.
The 4-50 Rule
Pareto's rule is a power law: just as 20% of the processes cause 80% of the rework as little as 4% of any business causes 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. 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 Excel 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.
To see a pareto chart of this data, go to http://www.qimacros.com/qiwizard/pareto.html
Jay Arthur, the KnowWare Man, works with companies that want to fire up their profits using Lean Six Sigma. He is the author of Lean Six Sigma Demystified and the QI Macros SPC Software for Excel.
Article Source: http://EzineArticles.com/?expert=Jay_Arthur
|
