Six Sigma is a method for process improvement that aims to minimize variation. Organizations with multiple departments such as finance, manufacturing, and sales with different definitions of a defect can use these universal metrics to identify processes that are not operating at a Six Sigma level.

Overview: What is an L1 Spreadsheet?

An L1 spreadsheet is used to calculate the defects per million opportunities (DPMO) and Z score from process data.

3 benefits of an L1 spreadsheet

1. Z score is a flexible measure to use for both variable and attribute data.
2. It helps to create the baseline for the Six Sigma projects. Also, can be used to assess and compare before and after process performance.
3. Z score is a universal measurement, that can be applied for different functions within the organization like production, sales, finance, HR, administration, etc.

Why is an L1 spreadsheet important to understand?

An L1 spreadsheet provides a useful template for entering data about a process and calculating the DPMO and Z score to assess process performance. DPMO and Z score are used in process performance analysis by Six Sigma practitioners to assess how well a process is meeting customer expectations. A process that results in less than 3.4 defects per million opportunities is considered to operate at a Six Sigma quality level. 

5 Steps to calculate defects per million opportunities (DPMO)

1. Choose a sample of relevant data for the process being analyzed2. Determine the total number of opportunities for a defect to occur in the process.
2. Determine the total number of opportunities for a defect to occur in the process.3. Calculate the total number of defect opportunities.

4. Count the total number of defects that occurred in the sample data.

5. Divide the number of defects by the opportunities and multiply by one million to get DPMO.

A data set must be chosen that accurately reflects the variation being observed in the process, yet small enough to be manageable. Multiple unrelated defects can occur within a process that will each lower customer satisfaction and should be assessed as different opportunities for a defect to occur. Since many processes have exacting requirements, the number of defects per opportunity is multiplied by one million to get a more accessible value.

Calculating Z Score from DPMO

If you have analyzed the capability of a process and determined DPMO, then Z score or sigma level can be calculated directly from that number.
The DPMO is divided by one million to get the defects per opportunity or DPO.

The DPO is then entered into the inverse of the standard normal distribution function and the negative value of the output will be the Z score.

In Excel, the formula is Z = -NORM.S.INV(DPO)

Calculating Z score from discrete data

The Z score for a process can also be calculated from any raw data with a valid mean and standard deviation. The sigma level is a Z score that describes the number of standard deviations between the mean from a specification limit. If both an upper and lower limit are set, the sigma level will be calculated using the limit closest to the mean.

Make columns labeled: data, mean, st dev, lower spec limit, upper spec limit, sigma level

  1. Fill process data into data column.
  2. Calculate the mean and standard deviation.
  3. Calculate the Z score for both the lower and upper limit using the formulas.
  4. Z lower = (mean – lower)/StDev and Z upper = (upper – mean)/StDev
  5. Sigma level will be the lower of Z lower and Z upper

Frequently Asked Questions (FAQ) about an L1 spreadsheet

What is a sigma shift?

The sigma level uses data collected over a defined period to make a prediction on long term stability. Variance in the process can be introduced by employee turnover, lack of training, and equipment wear, which may not be reflected on the collected data. A sigma shift of 1.5 is added to the short-term sigma level to predict long term performance.

How is discrete data different from qualitative data?

The L1 spreadsheet is used to analyze discrete data. Discrete data is quantitative information about a process counted in non-negative integers. Data can be understood in a hierarchy in which nominal data is information that has an identity (color, gender, blood type), ordinal data has an identity and order or magnitude (a grade of A, B, C), and discrete data has a domain with identity, magnitude, and equal intervals (number of parts made or defects that occur).
Nominal and ordinal data are qualitative information that describe attributes and can not be treated as typical numbers. Even if numbers are assigned to data categories such as red is 1, blue is 2, and yellow is 3, producing two red shirts does not give blue even though 1 + 1 = 2. The number of red shirts is quantitative and considered discrete data which can be treated numerically.

How is discrete data different than continuous data?

Continuous data describes a measured variable that can take on any value between two points on a real number line – the diameter of a piston is measured as 3.45 inches. The value depends on the accuracy of the measurement and is always divisible. Z score is a statistical tool derived for the analysis of continuous data, however, discrete data with a meaningful variance can give a useful Z score.

What is the ideal distribution for a discrete data histogram?

A process must be stable and provide data with a normal distribution to calculate a meaningful Z score and DPMO. Using data that deviates from a normal distribution can decrease the utility of Z score and DPMO in assessing process performance. The values of discrete data are best represented with a histogram to assess normality.

An L1 spreadsheet predicts long term process performance

Process performance is analyzed using data collected over a short period to make a projection of the failure rate of a process in the long term. These calculations provide metrics of process performance that are universal and readily compared even for vastly different processes. An L1 spreadsheet is used to calculate two of the most important metrics in Six Sigma, DPMO and sigma level.

About the Author

Lori Kinney