Reduce Scrap Case Study

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

One of the biggest challenges improvement professionals face is figuring out how to develop improvement stories from volumes of data. In most cases, the data looks like this:

This data is from a metal manufacturer. The scrap information shows the date scrapped, alloy, cost (i.e., machine) center, cuts, splits, scrap code and description and the actual weights of material and scrap. Clearly there is a lot of scrap, but how do you turn this into something resembling an improvement project?

View 4 minute video demonstrating how to create Pivot tables and an improvement story from this data

Similarly, data from a hospital's maternity ward might look like this:

It shows dates, physicians, diagnosis, patient age, length of stay (LOS), adverse events and discharge status. Do some physicians have more complications, higher costs, or longer lengths of stay?

This kind of data reminds me of the old joke about the father who finds his daughter digging through a pile of horse manure. When he asks her what she's doing, she replies: "There has to be a pony in here somewhere!"

Is there an improvement pony in your pile of data? With data like this, you will want to do some data mining with Excel to find the improvement projects hidden in your data.

Data Mining With Excel

Using Excel's PivotTable function, it's easy to analyze this data to find improvement stories. Here's the process:

1. Click on any cell in the data. (Excel will automatically select all of the rows and columns in your data.)
2. Click on Excel's Data-PivotTable and Pivot Chart Report menu.
   
3. Click Finish to reveal the PivotTable tools:
   
4. Now simply use your mouse to drag-and-drop items onto the PivotTable fields.
   
   
   • Want scrap by day? Drag DATE into Drop Row Fields Here and drag SCRAP_ onto Drop Data Items Here. Then use the QI Macros to create an XmR chart.
     
     
     
   • Want scrap by Cost Center? Drag cost center into Drop Row Fields Here and drag SCRAP_ onto Drop Data Items Here. Then sort the scrap in descending order by clicking on the Total Column and Data-Sort-Descending
     
     
     
   • Want scrap by Alloy by Cost Center? Drag Alloy into Drop Row Fields Here, Drag Cost_center into Drop Column Field Here, and drag SCRAP_ onto Drop Data Items Here. Sort into descending order.
     
5. If you want to see the data that makes up any cell in the PivotTable, just double click on the cell. If we click on Alloy 82M8D and Hot Mill 610355, we get the following:
   
   
   
6. We could then use PivotTables to analyze just the scrap description for this single coating:
   
   You could also double click on "Sum of SCRAP WGT" and change to "Count of SCRAP WGT" if you want to analyze the frequency of of each type of scrap:
   

Improvement Projects

I usually create control and pareto charts as I'm mining the data. From my perspective, most improvement stories consist of using three key tools in the right order:

1. Line or Control Chart
2. Pareto chart (two or more levels of detail)
3. Ishikawa or fishbone Diagram

Control Chart of Scrap

Pareto Chart of Cost Centers

Pareto Chart of the Biggest Cost Center and Biggest Alloy

Hint: Narrow your focus. If you can fix the issues with one alloy in the top cost center, then you can replicate the improvement to the other alloys. Trying to do all alloys at the same time would be too confusing and potentially derail the improvement.

Ishikawa or Fishbone Diagram

If you really wanted to make dramatic and rapid improvement, you could add root cause teams for all of the top five bars of the pareto chart: warm up, bad shape, surface cracks and transition coil.

View 4 minute video demonstrating how to create Pivot tables and an improvement story from this data

Get the Idea?

You can distill mounds of data down into simple counts and sums using Excel's PivotTable function. From there, you can draw many pareto charts and double click on the PivotTable cells that show the biggest "pain" to drill down and do additional analysis.

The data is out there...Start Digging!

Need Help?

Don't have the time or don't feel like doing this yourself? We offer affordable data analysis and project development. For more information see www.qimacros.com/six-sigma-project-excel.html

To sign up for this series tell your friends to send an email to qimacros@aweber.com

To purchase the QI Macros

If you already own the QI Macros, you can purchase an upgrade. You must purchase one upgrade for each license you own.

View 4 minute video demonstrating how to create Pivot tables and an improvement story from this data

Try QI Macros
FREE
For 30-Days

Or Buy It Now!