Reduce Scrap

A Six Sigma Manufacturing Case Study

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:

scrap data in Excel for case study

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?

Create Pivot tables + an improvement story from this data

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

maternity data in Excel for case study

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:

    pivot table wizard in Excel for case study
  3. Click Finish to reveal the PivotTable tools:

    pivot table wizard for scrap case study
  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 QI Macros to create an XmR chart:

      pivot table for scrap case study

    • 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:

      pivot table for scrap case study 

    • 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:

      pivot table of scrap by cost center for case study
  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:

    scrap data by cost center for case study
  6. We could then use PivotTables to analyze just the scrap description for this single coating:

    pivot table of scrap by cost center for case study 

    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:

    pivot table of scrap by cost center for case study

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

control chart of scrap data for case study

Pareto Chart of Cost Centers

pareto chart of scrap by cost center for case study

Pareto Chart of the Biggest Cost Center and Biggest Alloy

pareto chart of scrap by alloy mill for case study

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

fishbone diagram for case study

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.

Create Pivot tables + 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!

Data Analysis Assistance

Because so many people struggle with this process of analyzing and graphing their data, I've developed a service that can accelerate your first few projects. It's like anything new; the first few times are slow, time consuming and filled with two-steps-forward-one-step-back.

We now offer data analysis assistance for a fee. Just send us your data and we'll analyze it, graph it and pull out any improvement stories, recommended graphs and insights we can find. If we can't find anything, you don't have to pay anything. Click here for more information.

Other Charts Included in QI Macros for Excel