Data Analysis 101
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 QI Macros. Select the data and click on QI Macros - 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 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 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 check sheet to collect a week's worth of data about the type of defects? (Click on QI Macros-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:
- Look for data of defects over time. Draw a line graph or control chart of performance over time.
- Draw a pareto chart of the types of defects found.
- 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.
- Analyze the root causes and verify that you have found the true root causes using data.
- Show performance before and after implementing the improvement using a control chart.
- 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.
Isn't it Time to Upgrade Your Excel Charting Skills?
Stop trying to make your spreadsheets easy to read. Nobody wants to read your spreadsheet!
And stop using primitive line, pie and bar charts.
Isn't it time to graduate to Smart Charts: Control Charts, Pareto charts and Histograms?
Stop Struggling with Excel Charts!
Start creating Smart Charts in just minutes.
Download a free 30-day trial. Get Smart Charts for Excel now!
Other Charts Included in QI Macros for Excel