Calculate upper control limit in excel

Calculate R-Bar, Upper Control Lime For Range, Control Limit

Please read this page (in particular) very carefully.
Instructions
You need to understand how to send your assignments (deliverables)
to your instructor. The tabs (bottom of each sheet) in this
document contain all of the deliverables expected of you.
If you need help along the way, look for these special cells that have a red indicator
in the corner. It looks like the "Read me" box to the right: Read me
Simply slide your cursor over the red-cornered cell and you will get more information.
The format for all of the deliverables is the same:
The 'assignment objective' is in black font.
The next segment is in green font. These are your instructions for that assignment.
The blue font is the data (where applicable) for completing each the assignment.
Retain 6 decimal places throughout all your calculations. This will prevent rounding errors.
Report ALL results in 6 decimals with the exception of baseline sigma, critical values and degrees of freedom
otherwise the assignment will be returned as incorrect. Your answers must match the answers in the answer key.
Software
We are using Excel software for the projects. You may use other software
to complete your projects, but please 'report' your answers in the Excel format
described below.
You may complete your assignments with any version of Excel software.
All assignments can easily be completed with a basic copy of Excel.
There is also an Add-In feature which is available for Excel that can be helpful,
although it is not required. The Add-In feature comes free with each Excel package,
although it may not be currently loaded into your copy.
Following are the simple instruction for loading the Excel Add-ins for both Excel 2003
and Excel 2007. (Slide your cursor over the red-cornered cells to read)
Excel 2003 Excel 2007
Excel Novice - Please read
Project Timeline
The project due date schedule is in a separate tab.
Deliverables include:
Project charter
Baseline sigma
Histogram
Expected variation
t test
Chi square
ANOVA
DOE
Scatter diagram
Control chart (XmR)
Pp Ppk
How to submit an Assignment
In response to customers like you, we have added a peach-colored box
for each deliverable. We have done this to make it clear (and consistent) the
areas of the project that will be reviewed to your instructor.
Project: Manufacturing Project
Deliverable: Improve phase - gen. sol.
Student last name: Johnson
What is the control chart telling you?
There is a point out of control at subgroup #15. I would try to figure out why that happened. We would also recalculate the control limits because there is evidence the process has changed.
What is the average of subgroup 1? 22
What is the average of subgroup 2? 34
What is the average of subgroup 3? 23
What is the average of subgroup 4? 22
What is the average of subgroup 5? 25
What is the average of subgroup 6? 23
What is the average of subgroup 7? 29
What is the average of subgroup 8? 27
Read this!
To send each assignment to your instructor:
Click-and-hold the LEFT mouse button at the TOP-LEFT corner of the
peach-colored box, then while holding down on that button, drag to
the LOWER-RIGHT corner of the box. This will highlight the entire Read this!
peach-colored box. Release the mouse button. Do a CTRL-C. This will
copy what has been highlighted.
Go to your Villanova website and follow this sequence:
1. COMMUNICATE
2. CLASS ROSTER
3. Click on your instructor's email envelope icon
4. Type "Check My Work - Specific assignment" in the SUBJECT box.
5. Click once inside of the message box
6. Do a CTRL-V. This will paste your deliverable into this box.
Don't be concerned if after you paste it, the appearance of the text is out of
alignment. It will straighten out after you hit SEND.
7. SEND Check your SENT ITEMS folder afterward to see how it straightened out.
Please 'hand-in' your assignments throughout the course.
DO NOT SAVE THEM FOR THE END.
Procrastinators: The deadline for completing all project deliverables
is 7 days prior to the end of the course.
Instructor: These cells contain some hints, tips, and self-checks. If you would like to print any of the tips, right click the cell containing the tip and select Edit Comment. Highlight the text, copy and paste in a any text document like WORD for printing.
Instructor: EXCEL 2003 or earlier Click on the TOOLS tab in the top bar of Excel. If your computer already has the “Data Analysis” option listed, you are ready to go. Your Data Analysis tools have already been added. Under the Data Analysis Function you will find some of the more advanced functions that we will be discussing such as ANOVAs, and t tests. If you do not see Data Analysis listed under the TOOLS tab, I encourage you to go to the Help function in Excel for specific instructions on how to load “the Data Analysis Toolpak” in your version of Excel. Here are the easy standard instructions for Excel 2003 if you need to load the Add-Ins. 1) On the Tools menu, click Add-Ins. 2) In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK. 3) When you load the Analysis Toolpak, the DATA ANALYSIS command is automatically added to the TOOLS menu. If your version if slightly different than the above, refer to your HELP function for details on loading Tookpak. See the next spreadsheet at the bottom of this worksheet entitled EXCEL EXAMPLES for an illustration. The Data Analysis function in Excel is NOT required for the completion of this course.
Villanova instructor: Excel 2007 The Analysis ToolPak is a Microsoft Office Excel add-in program that is available when you install Microsoft Office or Excel. To use it in Excel, however, you need to load it first. 1. Click the Microsoft Office Button , and then click Excel Options. 2. Click Add-Ins, and then in the Manage box, select Excel Add-ins. 3. Click Go. 4. In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK. If you get prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it. 5. After you load the Analysis ToolPak, the Data Analysis command is available in the ANALYSIS group on the DATA tab. See the next spreadsheet EXCEL EXAMPLES for an illustration. The Data Analysis function in Excel is NOT required for this course.
Instructor: It is beyond the scope of this Black Belt course to teach students how to perform all of the functions of Excel. The Microsoft web site has excellent FREE tutorials for both Excel 2003 or Excel 2007. Excel 2007 http://office.microsoft.com/en-us/training/CR100479681033.aspx Excel 2003 http://office.microsoft.com/en-us/training/CR061831141033.aspx Or, ask a business colleague, who is proficient in Excel to show you how to do some of the basic things such as copying, cutting, pasting, copying from sheet to sheet, etc. Please do not expect your Black Belt class instructor to teach you how to use Excel software. That is not the objective of this course. This course will highlight specific Excel commands that are unique to Six Sigma, but it is not the objective of this course to teach basic Excel commands. Please use the suggested resources listed above.
Welcome
0
Excel examples
0
Scatter Diagram
The project
5
2
6
2
1
4
5
8
2
4
5
2
4
3
2
1
Define (Project Charter)
This spreadsheet tab provides illustrations of adding the DATA ANALYSIS capability to your Excel software.
If you see Data Analysis already listed under the Tools tab, you are set. If you do not see Data Analysis
follow the instructions in the Green tab. Excel 2003 instructions
Excel 2003 or earlier versions of Excel example
This spreadsheet tab provides illustrations of adding the DATA ANALYSIS capability to your 2007 Excel software.
If you see Data Analysis already listed under the DATA tab, you are set. If you do not see Data Analysis
follow the instructions in the Green tab. Excel 2007 instructions
Excel 2007 example
Instructor: EXCEL 2003 or earlier Click on the TOOLS tab in the top bar of Excel. If your computer already has the “Data Analysis” option listed, you are ready to go. Your Data Analysis tools have already been added. Under the Data Analysis Function you will find some of the more advanced functions that we will be discussing such as ANOVAs, and t tests. If you do not see Data Analysis listed under the TOOLS tab, I encourage you to go to the Help function in Excel for specific instructions on how to load “the Data Analysis Toolpak” in your version of Excel. Here are the easy standard instructions for Excel 2003 if you need to load the Add-Ins. 1) On the Tools menu, click Add-Ins. 2) In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK. 3) When you load the Analysis Toolpak, the DATA ANALYSIS command is automatically added to the TOOLS menu. If your version if slightly different than the above, refer to your HELP function for details on loading Tookpak. Remember that the Data Analysis function is NOT necessary for the course, but it is helpful. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.
Villanova instructor: Excel 2007 The Analysis ToolPak is a Microsoft Office Excel add-in program that is available when you install Microsoft Office or Excel. To use it in Excel, however, you need to load it first. 1. Click the Microsoft Office Button , and then click Excel Options at the bottom. 2. Click Add-Ins, and then in the Manage box, select Excel Add-ins. 3. Click Go. 4. In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK. If you get prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it. 5. After you load the Analysis ToolPak, the Data Analysis command is available in the ANALYSIS group on the DATA tab. Remember that the Data Analysis function is not required for the course. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.
Control (XmR Chart)
Manufacturing Project
Lovell Levelers, Inc. is a major provider of specialized parts
for the automotive industry. LLI’s biggest customer, Specific Motors
was not a delighted customer this month. In fact, last
Monday, the executive vice president of Specific Motors headquarters,
Phyllis Kendall was diverted from a return trip from Singapore
to drop in unexpectedly at the LLI plant. There was nothing
routine about this visit. She made it explicitly clear that
Specific Motors was disappointed with the level of quality relative
to the leveler plates. In particular, she was disappointed with the
current average of rejects at the rate of 1,350 defects per million
opportunities (DPMO) at a cost of poor quality of just over $256,000
per quarter. She said the industry standard is <50 DPMO and
if we do not get the level of quality to the industry standard (as a
minimum) within the next six months, LLI should not expect
to keep the business next year.
The student will be a Black Belt working for LLI. The specifics about their company:
CEO Bill Lovell
GM Mary Nichols
Sponsor John Hopps
Finance Cindy Jenkins
Process owner Leroy Miller
Master BB Dennis Kens
Manufacturing Mitch Freese
Design Engineer Al Nelson
Quality Debbie Judson
The deadline for completing all project deliverables is 7 days prior to the end of the course
Objective:
A problem statement needs to be developed.
There needs to be a business case so that management will buy-in to having the team
working on the project. The scope of the project also needs to be decided upon. This is
important to ensure a likely successful completion. If the scope is too broad, a
success may not be realized for years, or may not happen at all.
Instructions for you:
Create a project charter based "Create a charter?"
upon the information in the introduction. (see "The project" tab below)
In reality, you would fill in a charter with team members' names, stake holders, etc. We
are not interested in those details for this simulation, but we do want to see what you
come up with for four (4) items: Problem statement, business case, goal and project scope.
How to submit an Assignment
Project: Manufacturing Project
Deliverable: Project charter
Student last name: Your LAST name here
What is the business case?
Type in your business case here. "Business Case?"
What is the problem statement?
Type in your problem statement here. "Problem Statement"
What is your goal statement?
Type in your goal statement here. "Goal Statement"
What is the project scope?
Type in the scope here. "The project scope?"
To send each assignment to your instructor:
Click-and-hold the LEFT mouse button at the TOP-LEFT corner of the
peach-colored box, then while holding down on that button, drag to
the LOWER-RIGHT corner of the box. This will highlight the entire
peach-colored box. Release the mouse button. Do a CTRL-C. This will
copy what has been highlighted.
Go to your Villanova website and follow this sequence:
1. COMMUNICATE
2. CLASS ROSTER
3. Click on your instructor's email envelope icon
4. Type "Check My Work - PROJECT CHARTER" in the SUBJECT box.
5. Click once inside of the message box
6. Do a CTRL-V. This will paste your deliverable into this box.
Don't be concerned if after you paste it, the appearance of the text is out of
alignment. It will straighten out after you hit SEND.
7. SEND Check your SENT ITEMS folder afterward to see how it straightened out.
Please 'hand-in' your assignments throughout the course.
DO NOT SAVE THEM FOR THE END.
Procrastinators: The deadline for completing all project deliverables
is 7 days prior to the end of the course.
Instructor: Information about project charters can be found on pages V-2 through V-5 in the Villanova Six Sigma Black Belt Handbook.
Instructor: I am your sponsor for this project. If I was your actual sponsor, I would be very busy and would need to make decisions quickly. I would need to know what this project is all about and how it impacts the strategic objectives of the organization. Please limit this to a sentence or two.
Instructor: Acting as your real-world sponsor, I would need to be sold on why we need to do this project. I wouldn't have time to read long explanations. I would need a short, to-the-point compelling reason why we need to do this. In the problem statement, we 'sell' the need for the project with specific and measureable data.
Instructor: This needs a lot of thought. As your sponsor, I do not want to see 'scope creep.' "What's scope creep?"…please read on... What the scope is not: -It is not merely a timeline (i.e., when the Six Sigma project is to begin and when it is expected to end.) -It is not restating the problem being attacked. The scope defines the boundaries of the project--usually some beginning point and ending point. For example, if I were leading a project to improve the delivery of course materials to students, I would have the following boundaries: The process (IN THIS PROJECT) starts: When the customers says, "Yes, I am interested in enrolling." The process (IN THIS PROJECT) ends: When the customer receives the box from UPS that contains their handbook and CD's. The project stays within that confinement. Included might be: Enrollment process, warehousing process, accounting process, and UPS delivery process. What would NOT be included: -Errors in the handbooks or CDs. -User friendliness of the materials. Any of these items would be fine for some OTHER PROJECT. “Scope creep” is a common phenomenon with teams, but should be avoided. If the scope was predetermined to be as stated above, the team should stick to the confines of that scope. If they do not stick to it, eventually the team might begin to work on “login times.” Then, they might add to that the time it takes the enrollment rep to “close the sale.” Then, they might add to that making sure all materials are delivered error free. ...and on and on it goes. Thee scope creeps on and on. The team soon becomes frustrated and the probably of failure increases. It is best to establish the scope--then, the team needs to stick to it. You may also include important constraints/assumptions including the staffing allocation (team composition) as well as the budget constraints in a scope statement.
Instructor: What is your target improvement for this project, including a target date? George Eckes mentions a 50% improvement as a possible target for Six Sigma projects. Is a 50% improvement enough in this case?
Objective:
Lovell Levelers just reported that Specific Motors is a satisfied
customer. The leveler plate quality is no longer an issue. In fact, they
have not seen a single defect in six months. Phyllis Kendall made another
surprise visit, but this time it was for a more pleasant reason. She
presented each member of the team with a crisp, new $100 bill
as a show of appreciation. But, she pointed out that we need to
have a way to ensure that this problem will not crop up again. The team
is one step ahead of her and explained that the key process parameters
that effect the thickness of the leveler plates have been controlled with
an on-going XmR chart and is being monitored with a capability study.
Since thickness was the end-product parameter of interest, the team wanted to
determine whether there is any assignable-cause variation. They chose to use
an XmR chart.
Instructions for you:
We want to make sure you can calculate control limits. You may construct the
control chart by hand, or you may use a charting function in Excel. There are blank forms in the
back of your notebook .
Control Charts in Excel 2003
Control Charts in Excel 2007
1. What is the upper control limit for the range? Help
2. What is the upper control limit for the individuals?
3. What is the lower control limit for the individuals?
4. What would you do with the process?
a. What would you recommend?
b. Is the measurement system discriminate? "I have no idea about this one"
c. Is thickness in statistical control?
Data:
Calculating R-bar in Excel 2003
Calculating R-bar in Excel 2007
Thickness data for XMR control chart
0.551
0.546 1a. Calculate R-bar
0.547
0.548 Self check
0.547
0.547 1b. Calculate the
0.538 upper control limit
0.546 for the range
0.542 "How do I do that?"
0.543 Self check
0.545
0.547 2 & 3. Calculate the
0.543 control limits for the
0.548 individuals
0.545 "How do I do that?"
0.554 Self check
0.549
0.546
0.545
0.553
0.549
0.541
0.542
0.545
0.546
0.552
0.546
0.546
0.545
0.547
0.548
0.547
0.545
0.54
0.545
How to submit an Assignment
Report ALL results in 6 decimals with the exception of critical values and degrees of freedom
YOU DO NOT NEED TO SUBMIT THE XmR CHART
Project: Manufacturing - XmR Chart
Deliverable: XmR Chart
Student last name: Your last name here
Calculated R-Bar::: Type in R-bar Check
Upper control limit for the range::: Type in UCL-R Check
Upper control limit for the individuals::: Type in UCL-x Check
Lower control limit for the individuals::: Type in LCL-x Check
Is there adequate discrimination? Is there adequate discrimination? Why do you think so?
Based upon what the control chart is telling you, what would you do?
What would you do based upon what the control chart is telling you? Type it in here. Help
To send each assignment to your instructor:
Click-and-hold the LEFT mouse button at the TOP-LEFT corner of the
peach-colored box, then while holding down on that button, drag to
the LOWER-RIGHT corner of the box. This will highlight the entire
peach-colored box. Release the mouse button. Do a CTRL-C. This will
copy what has been highlighted.
Go to your Villanova website and follow this sequence:
1. COMMUNICATE
2. CLASS ROSTER
3. Click on your instructor's email envelope icon
4. Type "Check My Work - XmR Chart" in the SUBJECT box.
5. Click once inside of the message box
6. Do a CTRL-V. This will paste your deliverable into this box.
Don't be concerned if after you paste it, the appearance of the text is out of
alignment. It will straighten out after you hit SEND.
7. SEND Check your SENT ITEMS folder afterward to see how it straightened out.
Please 'hand-in' your assignments throughout the course.
DO NOT SAVE THEM FOR THE END.
Procrastinators: The deadline for completing all project deliverables
is 7 days prior to the end of the course.
Instructor: This is Step 6 in control charting. Refer to the lectures on Control Charts.
Instructor: You should get a value between 0 and 1.
Instructor: You should get a value between 0 and 1.
Instructor: You should get a value between 0 and 0.0200.
Instructor: You should get a value between 0 and 0.0100.
Self check: UCL between 0.3 and 0.6 LCL between 0.3 and 0.6
Instructor: Please refer to the lecture on how to calculate control limits for the XmR chart.
Self check: You should have gotten a value between 0.0050 and 0.0200.
Instructor: Please refer to the lecture on how to calculate control limits for the XmR chart.
Self check: Did you get 0.000176? If you did, you did not use absolute values. Instead you used values that contained negative and positive numbers. You should have gotten a moving average range value of 0.003471 or rounded to 0.0035.
Instructor: More help on calculating R-bar using Excel 2007 We really intend on you doing this step by hand, but here is an option in Excel. The average moving range is the average of all of the ranges of subgroup size of 2. For more information about a moving range, you need to revisit the lecture on the XmR chart (Lecture 82). Calculating your average moving range with Excel is a 2-step process. First you need to find the absolute range values for the ranges of each subgroup (size =2). Why the absolute value? If you just calculate the difference between any two cells, you would get both positive and negative numbers. You do not want negative numbers. Absolute values are numbers that are only in the 'positive' form. We will do the first one for you. To find the absolute range value for the first value (0.55