Your cart is empty.
Keep shopping
Teach on Udemy
Turn what you know into an opportunity and reach millions around the world.
Learn MoreQuality Management in Excel- Control Charts
Quality Management in Excel- Control Charts
Using and Applying Control Charts in Industry
Highest Rated
Created byMichael Parent
Last updated 5/2024
English
What you'll learn
- Statistical Process Control
- how to use and interpret Control Charts
- how to understand Process Variation
- Six Sigma concepts and tools
- Process Improvement concepts and tools
- Continuous Improvement concepts and tools
- how to perform statistical Data Analysis
- Statistics
- Data Visualizations
- Quality Management
- Process Quality
- Quality
Explore related topics
Course content
5 sections • 21 lectures • 3h 50m total length
- What are Control Charts?11:13
In this Lecture, we discuss the history of statistical process control, control charting and the quality management movement. Control charts are introduced as a data visualization tool. Control chart applications are introduced.
Learning Objectives:
Learn what a control chart is and how it is used.
Understand this history of SPC, control charts and the quality management movement
Discuss the uses and applications of control charts in varied industries.
*****
Get more FREE resources at: www.sixsigma-consulting.com
Follow on LinkedIn: https://www.linkedin.com/company/michaelparentconsulting/
Like and subscribe on YouTube: https://www.youtube.com/channel/UCDLNIjI1OQulldhfkZibX7A
Consider my other course on Udemy:
Data Analytics: https://www.udemy.com/course/data-analytics-in-excel/?referralCode=12037394F640164377B9
- Parts of a Control Chart, Making a Control Chart in Excel10:00
In this lecture, we discuss the components of a control chart including the data series, average and control limits. We also look at how to build a control chart within excel and the important effects of changing the control limit "multiplier".
Learning Objectives:
Know the names and components of a control chart
Be able to create a control chart within excel
Know how to change the control limits and why it's important
Note: In this video I picked a generic topic "hospital wait times" or "vehicles manufactured" you may notice that the Lower Specification Limit (LSL) was negative ~19 and you can't have a negative wait time or negative vehicle production. Depending on what you're measuring this often happens. In these situations you can remove the LSL, since no value will be below 0.
Another option you might consider is to change the data you're measuring. Rather than measuring the hospital wait times, you might measure "hospital wait times compared to target". This will change the data itself, and will yield more meaningful control limits.
- Benefits of Control Charting10:02
In this lecture, we discuss the advantages of using control charts to report data rather than traditional visualizations such as bar charts and pie charts.
Learning Objectives:
The benefits of using control charting for operational reporting
The limitations of other types of visualization for reporting and problem solving
- Types of Data and Types of Control Charts10:42
In this lecture, we discuss some higher level concepts about control charts and how they are affected by the type of data that is supplied. In this course we will look at 4 types of control charts: Individual charts, Moving Range Charts, P charts and U charts. The first two charts, Individual and Moving range charts rely on continuous or variable data. In this lecture we briefly look at these charts and how they differ from one another.
Additionally, We discuss the 3 types of data, how they differ and how they dictate the type of analysis that can be conducted.
Learning Objectives:
To distinguish between Continuous and Categorical data.
How to select the appropriate control chart for the data available.
Relevant details related to variable data control charts.
- Appendix: Offset Formula from Lecture 1.43:37
Appendix for Module 1.4
This is a quick video going over the "behind the scenes" formulas that were used in Lecture 1.4.
The "=Offset()" formula is a great formula for creating dynamic visualizations and toggling between sets of calculations.
For our purposes, the =OFFSET() formula takes 3 parameters, the cell reference, the row offset and the column offset.
- Module 1 Quiz
- Control Charting Assumptions10:11
In this lecture, we discuss three important assumptions that make control charting possible and an appropriate and useful tool in modeling processes. The three assumptions are regular intervals, random normal data, and stability in the data.
Learning Objectives:
Know the three key assumptions of control charts for variable data.
Understand what is meant by "random-normal" data.
Understand how to identify an unstable process.
*****
Get more FREE resources at: www.sixsigma-consulting.com
Follow on LinkedIn: https://www.linkedin.com/company/michaelparentconsulting/
Like and subscribe on YouTube: https://www.youtube.com/channel/UCDLNIjI1OQulldhfkZibX7A
Consider my other course on Udemy:
Data Analytics: https://www.udemy.com/course/data-analytics-in-excel/?referralCode=12037394F640164377B9
- Strong Signals11:37
In this lecture, we one of the most important signals in a control chart: External factors or special cause variation. Special cause variation is identified when a data point exceeds the control limits (either above the USL or below the LSL) of a process. Special cause variation alerts us to use a problem solving approach to prevent reoccurrence of the event by first identifying what caused the event, and then by implementing a standard to prevent the cause.
Learning Objectives:
Know the differences between common cause and special cause variation.
Know how to identify special cause variations on a control chart.
Know how to solve problems based on an external cause of variation.
- Moderate Sustained Signals7:59
In this lecture, we discuss the 2nd type of external cause signals: A moderate and sustained effect on the data. This signal occurs when 8 data points fall above OR below the center line average consecutively. The best way to think of why this works, is to think about the likelihood of flipping a coin 8 times in a row and getting heads every time. It's not impossible, and eventually it will happen, but it's far more likely that some external factor is influencing which side the coin lands on.
Moreover, we introduce another concept to the problem solving and countermeasures conversation: controllability. Some factors exert influence on the process but we have little recourse on how to control them. Whether or not the freeway is under construction will greatly impact my commute time, regardless of what I can do about it. In this case, it's still important to identify these factors for purposes of learning as well as preparation.
I once worked in a tire factory that knew the summer heat and humidity impacted the material properties of their rubber. Rather than try to control the heat and humidity of the building, the engineers altered the recipe based on temperature and humidity readings in the factory. control charts played a key role in these adaptations.
Learning Objectives:
Learn how to identify a moderately strong sustained effect on the process.
Understand the differences between a strong effect and a moderate sustained effect.
Review how to solve problems based on a a moderate sustained effect.
- Weak Sustained Signals11:32
In this lecture, we discuss the 3rd and final type type of external cause signals: A weak but sustained effect on the data. This signal occurs when 3 consecutive data points are closer to a control limit than the average.
The best way to think of why this works, is to think about the normal distribution curve. The values that are more likely are near the center. the further away you get from the average the more unlikely it is that it will occur. So if three data points happen to fall in areas of very low probability, it's very likely that something else is causing them to do so. It's not impossible for this to happen by itself, but it's far more likely that some external factor is the cause of this influence.
Moreover, we introduce another concept to the problem solving and countermeasures conversation: interaction effects. factors influencing our outputs do not work in isolation; they interact with one another. Sometimes these interactions augment the impact that the factors have on the process by themselves. We saw such an "interaction effect" when looking at my commute time. bad weather alone was not enough to produce a signal, but bad weather coupled with construction ended up creating a weak but sustained signal.
Learning Objectives:
Learn How to identify a weak sustained effect on the process.
Understand the differences between a weak sustained effect and a moderate sustained effect.
Learn how to solve problems based on a weak sustained effect.
Understand the concept of interaction effects.
- Appendix 2.1: Root Cause Analysis Part 112:20
Appendix 2.1 Part 1 of 2:
In appendix 2.1, we look at the 3 steps of problem solving: Problem Identification, Analysis to a cause, and Implementation of a solution.
Next, we look at 3 types of analysis to identify the root cause of a problem, or Root Cause Analysis (RCA).
Finally, we look at what it takes to make a good action plan to ensure a solution is implemented correctly, and that the results of the solution are sustained.
In this part of the appendix, we look specifically at the 3 steps of problem solving, 5 Why analysis, and Fishbone Analysis.
- Appendix 2.1: Root Cause Analysis Part 213:24
Appendix 2.1 Part 2 of 2:
In appendix 2.1, we look at the 3 steps of problem solving: Problem Identification, Analysis to a cause, and Implementation of a solution.
Next, we look at 3 types of analysis to identify the root cause of a problem, or Root Cause Analysis (RCA).
Finally, we look at what it takes to make a good action plan to ensure a solution is implemented correctly, and that the results of the solution are sustained.
In this part of the appendix, we look specifically at Fault Tree Analysis (FTA) and the Action step of solving problems.
- Appendix 2.2 Improving Stable Processes9:53
Appendix 2.2:
Thus far, in module 2 we have looked at how to identify signals on control charts that alert us that there are external influences affecting the process. But sometimes we still are dissatisfied with process outcomes even though no external factors or impacting them, the process is just deficient.
In appendix 2.2, we look at the 2 ways to improve processes when they are not impacted by external factors, namely through reducing variation and shifting the mean. We also discuss how each of these options will affect the customer and which sequence these two improvement methods should be implemented.
- Module 2 Quiz
- P-Charts15:15
In this lecture, we discuss the some assumptions and key ideas behind P-Charts as well as the steps needed to generate a P-chart in Excel. We also discuss the use cases for P-Charts.
P-charts are best used for process that produce a binary output (Pass/Fail, Yes/No, Go/NoGo) These are most often found in inspection and approval processes like in manufacturing or financial services.
Since these processes produce binary outputs, control charts cannot be used by just looking at the Pass/Fail outputs like we could for the continuous/variable data we looked at in modules 1 and 2. The P-chart solves this problem by looking at how many defects occur in a subgroup of observations. (i.e. 5 out of ever 500 parts are defective = 0.01). We then plot this proportion (0,01) on the chart and draw control limits around these data.
The control limits also differ from what we've observed in the past. This is because one of the underlying assumptions, a random normal distribution (Lecture 2.1) will not be met. Rather, we can model the proportions of defects to total based on a Poisson distribution. The Poisson distribution requires the control limits to be calculated slightly differently, taking in the size of the subgroup as a parameter.
In all, the P-Chart is a creative way to chart categorical data while still using the tools of Control Charts.
Learning Objectives:
Learn how to create a P-Chart
Understand the conditions for using a P-Chart
Understand the differences in assumptions between P-charts and Individual Control Charts
Perform the calculations for the P-chart control limits and Proportion values
*****
Get more FREE resources at: www.sixsigma-consulting.com
Follow on LinkedIn: https://www.linkedin.com/company/michaelparentconsulting/
Like and subscribe on YouTube: https://www.youtube.com/channel/UCDLNIjI1OQulldhfkZibX7A
Consider my other course on Udemy:
Data Analytics: https://www.udemy.com/course/data-analytics-in-excel/?referralCode=12037394F640164377B9
- U-Charts6:33
In this lecture, we discuss the some assumptions and key ideas behind U-Charts as well as the steps needed to generate a U-chart in Excel. We also discuss the use cases for U-Charts.
U-charts are best used for processes that can have more than one defect. Manufacturing a car might not just produce a Pass/Fail but might have defects in different units - interior, exterior, etc. Likewise, in financial services, you might look for a loan applicant raising multiple red flags that might trigger a rejection.
U-charts follow many of the same assumptions of P-charts which do not need to be revisited here.
Learning Objectives:
Learn how to create a U-Chart
Understand the differences between U charts and P charts
- Module 3 Quiz
- Control Charts without charts fundamentals9:53
In this lecture, we the limitations of creating charts within excel and how we can logical statements within the worksheet itself to build out the same signals we would find in control charts.
Learning Objectives:
Understand the limitations of control charting
Learn how to identify signals without control charting
Build logical statements for non-chart signals
Use conditional formatting for visual management
*****
Get more FREE resources at: www.sixsigma-consulting.com
Follow on LinkedIn: https://www.linkedin.com/company/michaelparentconsulting/
Like and subscribe on YouTube: https://www.youtube.com/channel/UCDLNIjI1OQulldhfkZibX7A
Consider my other course on Udemy:
Data Analytics: https://www.udemy.com/course/data-analytics-in-excel/?referralCode=12037394F640164377B9
- Control Charts without charts - Signals continued9:43
In this lecture, we continue the discussion of building control charts without charting visualizations in Excel. Armed with logical statements, we can evaluate conditions for identifying a medium sustained and weak sustained signal in our process without creating a chart itself.
Important Functions:
=if(logical statement, value if true, value if false) This function has three arguments, a logical statement that is evaluated and a value if the statement is found to be false and another if it is found to be true.
=AND(logical statement 1, logical statement 2) This function takes in multiple arguments. In the example of the medium sustained signal, we used 9 logical statements for evlauation. If any statement is found to be false, the whole logical function is false.
=ABS(value) This function returns the magnitude, not the sign of a value. In the examples used in the lecture, we were looking to find the magnitude of the difference between the control limit and data point and compare it against the magnitude of the difference between the average and the data point.
Learning Objectives:
Build logical statements for non-chart signals
a. Medium Sustained Effect/Signal
b. Weak Sustained Effect/Signal
- Nested Logic Part 15:09
Over the this lecture and the next two lectures I use one excel worksheet. the resource attached to this and the next two lectures has all the formulas, conditional formatting, and charts that are reviewed. It will look different than each of the lectures, but you should be able to figure out what formulas correspond to the lectures.
In this lecture, we continue the discussion of building control charts without charting visualizations in Excel. Armed with logical statements, we can evaluate conditions for identifying a medium sustained and weak sustained signal in our process without creating a chart itself.
Here we consolidate, in stages, 6 columns of formulas into one.
We make note of some of the tradeoffs in doing this consolidation especially: mutual exclusivity of signals and the ability to quickly count how many signals are present in a data series.
In part 2 of these lectures we also discuss options for troubleshooting nested logical functions using the Formulas>evaluate formula tool.
Important Functions:
=if(logical statement, value if true, value if false) This function has three arguments, a logical statement that is evaluated and a value if the statement is found to be false and another if it is found to be true.
=AND(logical statement 1, logical statement 2) This function takes in multiple arguments. In the example of the medium sustained signal, we used 9 logical statements for evlauation. If any statement is found to be false, the whole logical function is false.
=ABS(value) This function returns the magnitude, not the sign of a value. In the examples used in the lecture, we were looking to find the magnitude of the difference between the control limit and data point and compare it against the magnitude of the difference between the average and the data point.
Learning Objectives:
How to use nested logical statements to consolidate the signals
How to customize what the signal "response" is
How to troubleshoot formula issues.
- Nested Logic Part 29:46
Over the this lecture and the next two lectures I use one excel worksheet. the resource attached to this and the next two lectures has all the formulas, conditional formatting, and charts that are reviewed. It will look different than each of the lectures, but you should be able to figure out what formulas correspond to the lectures.
In this lecture, we continue the discussion of building control charts without charting visualizations in Excel. Armed with logical statements, we can evaluate conditions for identifying a medium sustained and weak sustained signal in our process without creating a chart itself.
Here we consolidate, in stages, 6 columns of formulas into one.
We make note of some of the tradeoffs in doing this consolidation especially: mutual exclusivity of signals and the ability to quickly count how many signals are present in a data series.
In part 2 of these lectures we also discuss options for troubleshooting nested logical functions using the Formulas>evaluate formula tool.
Important Functions:
=if(logical statement, value if true, value if false) This function has three arguments, a logical statement that is evaluated and a value if the statement is found to be false and another if it is found to be true.
=AND(logical statement 1, logical statement 2) This function takes in multiple arguments. In the example of the medium sustained signal, we used 9 logical statements for evlauation. If any statement is found to be false, the whole logical function is false.
=ABS(value) This function returns the magnitude, not the sign of a value. In the examples used in the lecture, we were looking to find the magnitude of the difference between the control limit and data point and compare it against the magnitude of the difference between the average and the data point.
Learning Objectives:
How to use nested logical statements to consolidate the signals
How to customize what the signal "response" is
How to troubleshoot formula issues.
- Nested Logic Part 38:03
Over the this lecture and the next two lectures I use one excel worksheet. the resource attached to this and the next two lectures has all the formulas, conditional formatting, and charts that are reviewed. It will look different than each of the lectures, but you should be able to figure out what formulas correspond to the lectures.
In this lecture, we continue the discussion of building control charts without charting visualizations in Excel. Armed with logical statements, we can evaluate conditions for identifying a medium sustained and weak sustained signal in our process without creating a chart itself.
Here we consolidate, in stages, 6 columns of formulas into one.
We make note of some of the tradeoffs in doing this consolidation especially: mutual exclusivity of signals and the ability to quickly count how many signals are present in a data series.
In part 2 of these lectures we also discuss options for troubleshooting nested logical functions using the Formulas>evaluate formula tool.
Important Functions:
=if(logical statement, value if true, value if false) This function has three arguments, a logical statement that is evaluated and a value if the statement is found to be false and another if it is found to be true.
=AND(logical statement 1, logical statement 2) This function takes in multiple arguments. In the example of the medium sustained signal, we used 9 logical statements for evlauation. If any statement is found to be false, the whole logical function is false.
=ABS(value) This function returns the magnitude, not the sign of a value. In the examples used in the lecture, we were looking to find the magnitude of the difference between the control limit and data point and compare it against the magnitude of the difference between the average and the data point.
Learning Objectives:
How to use nested logical statements to consolidate the signals
How to customize what the signal "response" is
How to troubleshoot formula issues.
- Module 4 Quiz
- Case 1: Manufacturing Application25:13
In this module, we explore some real world examples for using the control charting tools developed in this course.
Each Lecture will be it's own open-ended prompt, a case, for you to apply critical reasoning and creativity to solving real world tools.
Each lecture will have a blank case for you to try, a solutions document, and a corresponding video walking you through that document.
This part of the course will act as a living document, and will periodically be updated with new cases.
Last update: 1/10/2022
*****
Get more FREE resources at: www.sixsigma-consulting.com
Follow on LinkedIn: https://www.linkedin.com/company/michaelparentconsulting/
Like and subscribe on YouTube: https://www.youtube.com/channel/UCDLNIjI1OQulldhfkZibX7A
Consider my other course on Udemy:
Data Analytics: https://www.udemy.com/course/data-analytics-in-excel/?referralCode=12037394F640164377B9
- Case 2: Ph Testing18:20
In this module, we explore some real world examples for using the control charting tools developed in this course.
Each Lecture will be it's own open-ended prompt, a case, for you to apply critical reasoning and creativity to solving real world tools.
Each lecture will have a blank case for you to try, a solutions document, and a corresponding video walking you through that document.
Requirements
- Basic Excel Skills (Formulas, Charts)
- Basic knowledge of statistics (Mean, St. Dev, normal distribution)
Description
Statistical process control and control charts are an important part of operations management. For years, these tools have been used in all kinds of industries including healthcare, manufacturing, software development finance and Human Resources. With the increasing accessibility and the increasing demand for data analysis and data-based decision making, control charting is an important tool to be able to create, understand and apply.
This course will walk through the fundamentals of what control charts are, what insights can be gathered from them and how different control charts can be used to answered different strategic and operational questions.
Each module features several lectures, downloadable lecture files and a quiz to test your learning.
Additionally, The course features an entire module tackling specific real-world cases for using control charts and statistical process control.
Topics covered include:
Module 1:
The history of statistical process control (SPC) and control charting
Parts of a control chart
Advantages of using control charts
Types of control charts (I chart, U chart, P chart, etc.)
Module 2:
Key assumptions
Change in mean signals
External influence signals
Change in process stability/capability signals
Module 3:
Exercises for building individual range charts
Exercises for building moving range charts
Exercises for building P charts
Exercises for building U charts
Module 4:
Applying control charts to hospital wait times
Applying control charts to manufacturing process control
Applying control charts to A/B testing in software development
Applying control charts to an inspection process
Module 5:
Control Chart Case Examples
Who this course is for:
- Six Sigma Green Belt
- Six Sigma Yellow Belt
- Operations Managers
- Process Engineers
- Design Engineers
- Industrial Engineers
- Data Analysts
- Statisticians
- Quality Engineers
- Quality Managers