50 words agree or disagree to each question
Q1 In this week’s forum discussion, we’re examining the control chart, which is a graphical representation of a time series. Since this is something I’ve worked with regularly over the years, I’m going to use one of my current examples, but as the data isn’t approved for public release, I’ll be limited to describing the process without further context.
While I’ve used Tableau and Excel for visualization in the past, I’m currently working with Excel for a pass/fail, where I calculate the mean for a series of values filtered by categories. I currently display the values in a line graph, but the upper and lower bounds trigger an alarm (the Cell changes colors) to alert me when the number is outside of the standard deviation.
My current project is based on a complete population, so I’m using the STDEV.P (standard deviation of population) function instead of the STDEV.S (standard deviation of sample) function, but the principle is still the same. The mean of the population is calculated, then the standard deviation is calculated. To calculate the upper limit, I add the standard deviation to the mean. To calculate the lower limit, I subtract the standard deviation from the mean. If either condition is met, a green-colored Cell populated with “Pass” changes to red and the text to “Fail”.
In my report, I begin by stating whether my overall assessment is a pass or fail, then state which of the sub-categories have failed. This requires a manual review of a sample of the population to determine a likely cause of the failure and adjusting the confidence statement based on the size of the sample examined. Once I’ve described and diagnosed the problem, I use trends to predict future performance and prescribe changes to mitigate the issue.
The example involves three possible outcomes for the reported data: current, due, and overdue; however, my report combines the current and due categories so that I end up with a mutually exclusive check, being overdue and not overdue, where my calculation for mean plus standard deviation become the upper limit for what is an acceptable amount to be reported as overdue without prescribing action, or at least weighs on how urgent the prescribed action should be perceived.
Since I primarily only care when the number reported as overdue have exceeded the upper limit, I prefer using the bullet graph, where a bar chart is used in conjunction with a tick at the upper limit. I have yet to create one for my current project, as they’re difficult to create in Excel, and there isn’t a feature for it in Pivot Charts, at least not that I’m aware.
Q2. This is a great post this week! I have some experience with this due to my experience with Lean Six Sigma methodologies. In Lean Six Sigma, the goad is to reduce waste and improve efficiencies which is usually done through the DMAIC process. The DMAIC process is comprised of the following phases: define, measure, analyze, improve and control. Control charts come into play as you can imagine in the control phase to ensure that the project maintains its goal of efficiency after the project is completed. Control charts are considered one of the seven basic quality tools by the American Society for Quality.
As I mentioned previously, control charts are used during the control phase of a process improvement project to measure the process that was improved to ensure continued efficiency. In this application, the outputs of a process are plotted to create a control chart on which an upper and lower control limit are set to monitor the process. After each output is plotted, the chart should be evaluated to see where each of the plotted processes landed on the chart. If the process falls within the upper and lower control limits, then the process is in control. If the process has multiple data points outside of the control limits, then the process is by definition out of control. While one can argue that with a single data point falling out of the upper and lower control limits is out of control, this is usually due to extenuating or unusual circumstances. Control charts are a great tool to ensure that process improvements are working and meeting the benchmarks that you have set.