Data Analysis 101

Jay Arthur
888-468-1537
303-756-9144
KnowWare International, Inc.
DBA LifeStar
2253 S. Oneida
Ste 3D
Denver, CO 80224

We work with companies
that want to fire up their profits using
Lean Six Sigma

Copyright © 2011

For some reason, figuring out where to begin seems to be everyone's biggest problem. Over the years, I've developed a simple method for looking at Excel-based data and deciding how to process it.

The Problem Solving Process

The problem solving process begins with a line graph of current performance. You then use the detail behind the performance data to create a pareto chart.

1. Line Graph

Line graphs show performance over time. So I'm always looking for some orientation of the data that goes from first to last. This could be production sample numbers, dates, times, or whatever.

The problem solving process also implies that there is some kind of error, mistake or defect. So I'm looking for attribute data about defects (i.e., mistakes, errors, or out of spec). I'm not looking for variable data like money or cycle time or length or weight. Let's look at some sample defect data from the AIAG:

This example shows defects, over time, by sample. There were 62 samples taken and the number of defects counted. The natural inclination of most people is to subtract the defects from the sample size and show how many were produced correctly (e.g., 62 -2 = 60 good in the first sample), but this inclination draws your attention away from the problem. To solve a problem, you need to understand the problem.

The line graph or np chart of defects can be drawn easy with the QI Macros. Select the data and click on QIMacros-np chart and enter 62 as the sample size:

2. Pareto Chart

Since there are no red points or lines, the process is stable. The next step is to look for detail about the types of defects. If you look at the data, you'll want to look for totals by type of defect: undersize, cold weld, missing, or off-location.

Just select the titles in column A and then hold down the Control-key on your keyboard and select the totals in column AA as shown. Then you can use the QI Macros to draw a pareto chart:

As you can see, undersize contributes most of the problem. This is clearly a pareto pattern: one defect accounts for 91% of the defects. Because the part is undersized, it probably isn't possible to rework the part, so it may have to be scrapped.

If we had more detailed data about the undersize error, we might be able to draw another more detailed pareto chart of the data inside this one bar, but we don't. So we continue by creating an Ishikawa or fishbone diagram.

3. Ishikawa - Fishbone - Cause and Effect Diagram

Just click on the QI Macros and choose Fill-in-the-blank Templates. Select Ishikawa diagram and then change the problem statement to match the pareto pattern:

Your improvement story is now ready for root cause analysis. Knowing that the problem is undersized, you can more easily choose the right team members to help analyze the problem.

4. Move the Fishbone

If you want, you can move the Ishikawa template into the data workbook to continue developing your improvement story in one Excel Workbook.

Just click on EDIT-Move or Copy Sheet to get this dialog box. Then change the "To Book" to the main data workbook (in this case AIAG SPC.xls) and click OK:

This will move the template into the workbook along with the original data, np chart, and pareto chart:

Barriers to Success

If you don't have the data to draw the pareto, can you use a checksheet to collect a week's worth of data about the type of defects? (Click on QIMacros-Fill in the Blanks Templates and select checksheet to get a template.) A week's worth of data will be more than enough to analyze a stable process.

Analysis is Easy . . . if you know what to look for

The process is simple:

1. Look for data of defects over time. Draw a line graph or control chart of performance over time.
2. Draw a pareto chart of the types of defects found.
3. Use the biggest bar of the pareto chart to create a fishbone diagram for root cause analysis. The problem statement should reflect the problem identified in the pareto chart. You now have enough insight into the problem to choose the right root cause analysis team.
4. Analyze the root causes and verify that you have found the true root causes using data.
5. Show performance before and after implementing the improvement using a control chart.
6. Continue to monitor and improve the process.

I don't know why, but most people try to make it a lot harder than this. You don't have to. You can let your data lead you to dramatic improvements.

All of these charts and graphs can be created by the QI Macros for Excel.

Download the FREE 30-day Evaluation copy of the QI Macros Excel SPC Software for Six Sigma

If you love the QI Macros...
Please ask a handful of friends to download the trial version. Here's what to say:
Need easy to learn, easy to use software for Lean Six Sigma and SPC that works right in Microsoft Excel? Test drive the QI Macros!
Find out more at www.qimacros.com/excel-spc-software.html.
Get a 30-day trial at www.qimacros.com/free-spc-software.html.

Going to a meeting of quality professionals?
We can send you as many QI Macros 30-day trial CDs as you need. Just send an email with the address and quantities needed to orders@qimacros.com.

Try QI Macros
FREE
For 30-Days

Or Buy It Now!