Problem Solving With Manufacturing Data

Here’s a simple way to use Excel PivotTables to dig into your data

Published: Monday, January 25, 2021 - 12:03

There are two ways to increase profits: increase sales or reduce costs. Although most data analysis seeks to find more ways to sell more stuff to more people, addressing preventable problems is an often overlooked opportunity. Preventable problems consume a third or more of corporate expenses and profits.

Data analysis can pinpoint problems and eliminate them forever. Problem solving with data is a much more reliable and controllable way to cut costs and increase profits. Sadly, few people know how to do this consistently.

How do you solve operational problems with 100-percent success rate? Take out the guesswork. The vast majority of improvement projects involve reducing or eliminating defects, mistakes, and errors. If you have raw data about when the defect occurred, where it happened, and what type of defect it was, you can create a world-class improvement project that eliminates the guesswork. And you can do it using a tool you most likely already have: Microsoft Excel.

Go on a raw data diet

Often, when we get data, they have already been summarized by day, week, month, quarter, or year. This offers little or no nutritional value. It’s difficult if not impossible to analyze these data in such a way that you can determine root causes and actually fix something. The solution? Go on a raw data diet.

Following is a way to do this using Excel and your favorite SPC software or an Excel SPC add-on. These are the basic steps and assumes some knowledge of Excel. Your results may not look quite the same in terms of layout and labels, but the data should work out the same. You can download a sample file here to follow along. After downloading and opening, select the Manufacturing tab at the bottom of Excel.

The figure below shows a portion of the raw data of when, where, and what of production scrap. You may also have data for how, how much, who, and why, but these are sufficient to find and fix seemingly insolvable problems.

These data show what happened (Scrap Code), where it happened (Machine), and when it happened (2/1/19). Most people find these kinds of data intimidating, but you can easily make sense of them if you know how to use Excel PivotTables.

Summarize the data using Excel PivotTables

1. In Excel, click anywhere in the data portion of the table and click on Insert > PivotTable to create a PivotTable.

You should see a popup as shown below. The range should be automatically detected. Just click OK, and the PivotTable will appear in a new Excel tab.

2. Start by summarizing the data by date and scrap weight. This will show you the amount of scrap generated each day. Just click on the boxes next to each of those fields in the PivotTable Field List, and Excel will put them into the Row Labels and Values fields, as shown below. Make sure only those two boxes are checked. You may have to drag the scrap weight into the Values field. Excel will now summarize scrap weight by date. Depending on your PivotTable options, the labels might look a little different than what is shown below, but the filtered data will be same.

3. Now you can create an XmR chart. Simply copy and paste the PivotTable results into your favorite SPC software or use an Excel app such as QI Macros. Select only the column labels and data, and do not include the Grand Total. Use an XmR chart to plot the baseline performance of the process.

Why an XmR control chart? Because you will need a control chart to monitor the improvement after it’s implemented. Otherwise the improvement will slide back to prior levels within a few months. (This is the control phase of DMAIC that people so often overlook.) In this control chart, we see that there are some out-of-control points (shown in red). Other than these special causes (which could have been investigated), the process looks fairly stable.

4. Next, reorganize the PivotTable data by unchecking the Date field and checking the Machine field. Excel will recalculate the data to show that amount of scrap generated per machine.

5. Use these data to create a Pareto chart. Again, you can use QI Macros or Excel to do this. To use Excel 2013–365, copy and paste the PivotTable data into a separate work area. Only cut and paste the column labels and data, as shown below. Excel will not let you create a Pareto chart of the PivotTable directly.

Select your newly pasted data and click on Insert > Chart and choose Histogram > Pareto.

Select your newly pasted data and click on Insert > Chart and choose Histogram > Pareto. You will get something like the following Pareto chart, this one created in QI Macros.

Using Excel’s Pareto chart, you will need to adjust the chart titles and estimate the cumulative percent.

From this chart we can see that machine M240 experiences 65.8 percent of the total scrap. Many people stop their analysis at this point, but the secret is to drill down even more. Using PivotTables, we can do this easily.

6. To see all the data for machine M240, double click on the PivotTable cell for that Machine (B4 in the example below).

Excel will pull up all of the data behind this cell into a separate sheet.

7. Now insert another PivotTable using these new data. Click anywhere in the data portion of the table. Insert PivotTable. Click the Scrap Code and Scrap Weight check boxes. If it isn’t already there, click and drag scrap weight into the Values field. This will summarize all of M240’s data by type of scrap code. Don’t worry how the data are sorted. The following Pareto chart will take of that.

8. Now create a Pareto chart of M240’s scrap by code. Copy and paste the PivotTable data into a separate work area. Only select the column labels and data. Excel will not let you create a Pareto chart of the PivotTable directly. Then click on Insert > Chart and choose Histogram > Pareto. You will get a chart something like the following.

Now we see that code H50 accounts for 32.5 percent of M240’s scrap. Drilling down using PivotTables enables the kind of laser focus necessary to create powerful improvement projects.

9. You can now create an Ishikawa (fishbone) diagram using H50 (the “big bar”) on this Pareto chart as the head of the fish. There are fishbone templates available for Excel, but you can create the fishbone however you choose. The important part is that you are now ready to dig in.

With this data analysis, you can tell who should be on the root cause team—people on machine M240 who know something about scrap code H50. Without drilling down to this level, it will be difficult to determine root causes. Teams often end up “whalebone diagramming” and struggling to make improvements. It might also be desirable to start a second team on scrap code M129 scrap.

Once the team has implemented improvements, collect more data and revisit data analysis. Revise the control charts to confirm that the improvement actually reduced scrap.

Then find the next “big bar” and analyze it. Keep drilling down until the problems are eliminated.

My experience

Every multimillion-dollar project I ever worked on started with PivotTables of raw data that lead to control charts, Pareto charts, and fishbones. I also found that by doing the data analysis first, I never struggled to help a team succeed. The data analysis made sure that we got the right people on the team to solve a clearly defined problem. Root cause analysis was often easy because the project was so well focused. And I never had problems getting leadership support because the data told a compelling story that they could all understand.

Where to use this kind of analysis

This analysis will solve the vast majority of problems facing service businesses, which represent the majority of the U.S. economy. This will also help the service side of manufacturing businesses. I’ve used it to pinpoint problems in healthcare, software, telephony, and medical device manufacturing.

It works anywhere there are raw data about defects, mistakes, and errors.

Automating the analysis

I have coded this into the QI Macros Improvement Project Wizard, an Excel plugin. In a matter of seconds, the Improvement Project Wizard does everything that used to take me days. All you need are raw data about the when, where, what, how, who, and how much of each defect. You can find these data in existing systems—purchasing, billing, invoicing, medical record systems, etc.

Just import the raw data into Excel and let the Improvement Project Wizard find every possible improvement project in your data. It’s that easy.

Start making breakthrough, million-dollar improvements. You customers will love you for it. Every dollar you save falls to the bottom line, so investors will love you for it as well.

About The Author

Jay Arthur—The KnowWare Man

Jay Arthur, speaker, trainer, founder of KnowWare International Inc., and developer of QI Macros for Excel, understands how to pinpoint areas for improvement in processes, people, and technology. He uses data to pinpoint broken processes and helps teams understand their communication styles and restore broken connections. Arthur is the author of Lean Six Sigma for Hospitals (McGraw-Hill, 2011), and Lean Six Sigma Demystified (McGraw-Hill, 2010), and QI Macros SPC Software for Excel. He has 30 years experience developing software. Located in Denver, KnowWare International helps service and manufacturing businesses use lean Six Sigma tools to drive dramatic performance improvements.