Overview: What is an L1 Spreadsheet?
3 benefits of an L1 spreadsheet
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)
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
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
- Fill process data into data column.
- Calculate the mean and standard deviation.
- Calculate the Z score for both the lower and upper limit using the formulas.
- Z lower = (mean – lower)/StDev and Z upper = (upper – mean)/StDev
- 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?
How is discrete data different from qualitative data?
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.