In this issue:

• Data Collection: The Key to Process Improvement
• Excel Tips
• Past E-Zines and Articles

Greetings,

Welcome to the SPC for Excel e-zine. Each month you will receive information on a featured SPC topic, Excel tips and other items. We hope you enjoy this issue.  Please let us know your ideas for topics to cover as well as any ideas you might have for improving the e-zine.

Ever have trouble collecting the data you want? Setting up an effective data collection process takes some thought and time. Below we review the steps in setting up an effective data collection system.

Data Collection: The Key to Process Improvement

Data drive your process improvement efforts. Setting up a good data collection process is critical. The ten steps in effective data collection are given below.

Step 1: Write down what you want to measure.

This sounds fairly trivial, but it provides a good starting point to develop a measure. Simply put down, in writing, what you want to measure.

Step 2: Define the purpose of the data collection.

There should always be a reason why we are collecting the data. It could be that we want to monitor performance over time and take actions on special causes of variation. It may be that we want to use the data in a team environment to work on process improvement. Write down the purpose of the data collection.

Step 3: Determine if other measures are appropriate for the processes involved.

This step helps ensure that you consider the processes involved. There may be other items to consider when setting up the measurement process. Usually, it is good to look at a process from four dimensions: quality, quantity, timeliness, and cost. Sometimes, with a minimum amount of extra effort, you can collect additional good data.

Step 4: Develop the operational definitions for the measure.

An operational definition imparts a clear understanding of the measure. According to Dr. W. Edwards Deming, an operational definition includes:

• a written statement (and/or a series of examples) of criteria or guidelines to be applied to an object or to a group.
• a test of the object or group for conformance with the guidelines that includes specifics such as how to sample, how to test, and how to measure.
• a decision: yes, the object or the group did meet the guidelines; no, the object or group did not meet the guidelines; or the number of times the object or group did not meet the guidelines.

Step 5: Determine if the measurement is currently being taken and if there are historical data available.

If the measurement is currently being taken, the process becomes easier since people are already taking the data. In this case, you will need to check to see if the data are being taken correctly. In some cases, there will be historical data available. These data can be used to determine how the process has worked in the past.

Step 6: Determine who will collect the data.

A decision must be made about who should collect the data. It is usually best if the person closest to the process collects the data. This could be anyone at any level in the organization. For example, it could be the Controller if the data being collected involve monthly profits.

Step 7: Determine how the data will be collected and how it will be displayed.

This is a crucial step. If the process for defining how the data will be collected is not correct, a lot of time and effort can be wasted. Questions to consider include:

• Can the data be automatically collected?
• Can the data be downloaded from the system?
• Will the data have to be manually collected?
• Can the data be collected from reports?
• Should the data be displayed as a control chart or a Pareto diagram?
• What steps do I expect the data collectors to go through to collect the data?

Part of determining how the data will be collected includes writing down the procedure, either as a process flow diagram or a step-by-step procedure. This is definitely required for new data collection processes. It lets the data collectors know what they need to do. A decision must be made on how frequently to collect the data. The more frequent the data collection the better. Daily is best, followed by weekly and then monthly. Data collection less frequent than monthly is not very useful for process improvement. Tools that are needed, such as data collection forms, are designed at this point. Data collection forms should always include the name of the person collecting the data, the date taken, and a place for comments.

It is best to display the data as a time series (control chart) whenever possible. The type of control chart to use depends on the type of data you are collecting. The two types of data are attributes and variables. Attributes data are either yes/no or counting.

· Yes/No Data: For one item, there are only two possible outcomes: either it passes or it fails some preset specification. Each item inspected is either defective (i.e., it does not meet the specifications) or is not defective (i.e., it meets specifications). Examples of yes/no attributes data are:

• Mail delivery: is it on time or not on time?
• Phone answered: is it answered or not answered?
• Invoice correct: is it correct or not correct?
• Stock item: is it in stock or not in stock?
• Flipping a coin: heads or tails?

· Counting Data: With counting data, you count the number of defects. A defect occurs when something does not meet a preset specification. It does not mean that the item itself is defective. For example, a television set can have a scratched cabinet (a defect) but still work properly. When looking at counting data, you end up with whole numbers such as 0, 1, 2, 3; you can't have half of a defect. To be considered counting data, the opportunity for defects to occur must be large; the actual number that occurs must be small. For example, the opportunity for customer complaints to occur is large. However, the number that actually occurs is small. Thus, the number of customer complaints is an example of counting type data.

Variables Data: Variables data consist of observations made from a continuum (such as the temperature today). That is, the observation can be measured to any decimal place you want if your measurement system allows it. Some examples of variables data are contact time with a customer, sales dollars, amount of time to make a delivery, height, weight, and costs.

The control chart to use for each type of data is given below:

• Yes/No data: p control chart
• Counting data: c control chart
• Variables data: Xbar-R or Individuals chart

Set up the measurement as a positive, for example, percent on time instead of percent late.

If you are using a p chart or c chart for attributes, you are either measuring the percentage of defective items or the number of defects. In both cases, there are defects (e.g., errors). For both of these, you will need to include a Pareto diagram with the control chart. The Pareto diagram examines the reasons for the defective items or defects.

A decision must be made on whether to manually keep the charts or use software to develop the charts. Either is acceptable. EACH DATA POINT SHOULD BE PLOTTED IMMEDIATELY AFTER IT IS COLLECTED. Don't wait to get five data points and then plot them all at once.

Regardless of how the charts are generated, a control strategy form should accompany each chart. This is where you record the reasons for any out-of-control points on the chart.

Step 8: Determine how to ensure that the data collection process is carried out.

Data collection usually includes a change of behavior. You are asking associates to do something different and new. Change is never simple. This is particularly true for new data collection systems. Even with existing data collection processes, associates may be wondering about the sudden interest in the data. Some things to consider in implementing the data collection process include:

• Explaining the reason for the data collection to the associates doing the data collecting.
• Letting the associates know what the goal is and what will be done with the data.
• Following up after implementation to ensure that the data collection process is taking place.

Step 9: Determine who will review the data and how often.

Too often we collect data for the sake of collecting data. We don't review the data and no action is taken on it. Remember, the purpose of collecting data is to take some action, to improve a process. In this case, we are helping leadership know where it stands versus the goals it has set. This permits action to be taken if goals are not being reached.

Step 10: Implement the process.

You have planned it. Now go do it. Often you will need to make revisions to the process once it has been implemented to help improve the quality of the data collection.

We offer this module as one of our 17 PowerPoint training modules that you can customize! Click here for more information.

Excel Tips

One of the great features of our software, SPC for Excel, is that it runs as a Microsoft Excel add-in. Entering data, navigating around the workbook, etc. are necessary skills to use Excel. So, we have included some tips to help you with Excel.

1. Sending an Excel workbook through e-mail. (You must use Microsoft Outlook or Exchange)

From the File menu select Send To. Click Mail Recipient. Now, address the blank e-mail form that appears, and send it.

2. Password protecting a workbook.

Open the File menu. Click Save As. Click the Options button. In the Password to Modify field, enter a case-sensitive password. Click on OK. Click OK again. Click Save.

3. Preventing zero values from appearing in your worksheet.

Open the Tools menu. Click Options. Click the View tab. Under Window Options, make sure the "Zero values" check-box is not checked.

Have a tip to share with us? Please click here...

Past E-Zines and Articles

Our website has all our past e-zines as well as free articles for download. The e-zines that are available include:

January 2004 (Variation)
February 2004 (Leadership and Variation)
March 2004 (Operational Definitions/Measurement System Analysis)
April 2004 (Interpreting Control Charts)
May 2004 (Problem Solving Model)
June 2004 (Pareto Diagrams)
July 2004 (c Control Charts)
August 2004 (Control Strategies)

The articles on the website include:

"Using Time Series Charts to Analyze Financial Data" - this paper was presented at the 2002 Annual Quality Congress and describes how one company has used time series charts to analyze financial data (from the monthly profit and loss statement).

"Cutting Costs, Not Employees" - this paper describes how organizations can cut costs without cutting employees; it also describes the role of the quality professional in making this happen.

"Process Management" - this paper was presented at the 2001 Annual Quality Congress and shows how one company is using Process Management as its method to embed quality into the culture of the company, to run its business, to optimize systems and to reach its vision. At the heart of Process Management is a five-step method that is applied to each primary process: define, document, measure, manage and improve. The model represents a blend of process and people skills, which include statistical analysis of processes, teamwork, and team styles.

"Quality Skills Training: A Roadmap For Learning" - this paper was presented at the 2003 Annual Quality Congress. The purpose of this paper is to provide a blueprint for conducting monthly quality training to all associates. The major component of this blueprint is called Quality Skills Training (QST). QSTs are one-and-a-half-hour training sessions. This paper describes how these training sessions are designed and includes a teaching guide for one topic - creativity. This paper is designed to be part of an interactive session in which the QST on creativity is presented as part of the blueprint.

To go to the e-zines and articles, click here.