Part 1 Six Sigma Black Belt Project

LSSBBHealthcareProject.xlsx

Home >Business & Finance homework help >Operations Management homework help >Part 1 Six Sigma Black Belt Project

Welcome

Welcome!
Please read this page (in particular) very carefully.
Instructions
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 daniel-munson: 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.
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 'objective' is in black font. It describes what you are doing the particular deliverable.
The next segment is in green font. These are your instructions.
The blue font is the data (where applicable) that you will need to complete the deliverable.
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 of Excel.
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 or earlier Diane Johnson: 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.	Excel 2007 or later Diane Johnson: Villanova instructor: Excel 2007 or later 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.
Excel Novice - Please read Diane Johnson: 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 Excel. https://support.office.com/en-us/article/Excel-training-9bc05390-e94c-46af-a5b3-d7c22f6990bb 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.
Project Timeline
Remember that the entire project must be 100% correct
and complete by no later than midnight EST one week (7 days) BEFORE the last day of class.
Deliverables include:
Project charter (target week 3 or sooner)
SIPOC (week 4 or sooner)
Baseline sigma (week 7 or sooner)
Pareto chart (week 8 or sooner)
Expected Variation (week 9 or sooner)
T Test (week 11 or sooner)
Scatter diagram (week 12 or sooner)
DOE (week 13 or sooner)
Control chart (XmR) ( week 14 or sooner)
Pp Ppk (week 15 or sooner)
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:	Healthcare 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
VERY IMPORTANT!! Be sure to submit your assignments for grading as you complete each one and following the submission schedule for each deliverable- DO NOT WAIT UNTIL THE END OF THE COURSE TO SEND THEM ALL IN. NOTE THAT THE ENTIRE PROJECT MUST BE 100% CORRECT BY ONE WEEK BEFORE THE END OF CLASS OR YOU WILL FAIL THE PROJECT AND THE COURSE. This means that you must submit ALL of your assignments far enough in advance of the project completion deadline to allow time for all necessary corrections to be made. Following the submission schedule is the best way to ensure that you have enough time to finish the project by the completion deadline - failure to meet the submission due dates can result in failing the course. Very few assignments are correct on the first submission and many require multiple rework attempts, so don't procrastinate!

Excel examples

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 or earlier instructions Diane Johnson: 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.
	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 2007or later instructions Diane Johnson: Villanova instructor: Excel 2007 or later 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.
		Diane Johnson: 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.	Excel 2007 example

THE PROJECT

Healthcare Project

A recent report from the Centers for Disease Control and Prevention indicates that over the past

decade trips to emergency departments (ED) rose 20 percent, while the number of available

emergency centers fell by 15 percent. Another study from the American Hospital Association

indicated that 62 percent of hospitals feel they are at, or over, operating capacity. That number

jumps to 90 percent when considering Level 1 Trauma Centers and larger (300+ beds) hospitals.

These statistics are frighteningly familiar to many hospitals and patients. The pressures are

mounting, and a faltering economy has swelled the ranks of uninsured -- people who often

rely on the local ED for primary care. Countless emergency departments are literally on life

support as they try to cope with capacity issues and workforce shortages. Preparing for or

responding to emerging threats such as bioterrorism and SARS only increases the strain on

the system. In hospitals across the U.S., EDs face a similar story of delays

and dissatisfaction…from both patients and clinicians.

Not all the news is bad, however. Some hospitals are finding new ways to overcome the

challenges and create safer, more efficient environments. Through a combination of Six

Sigma and Lean, hospitals are targeting critical aspects of patient flow, patient access,

service-cycle time, and admission/discharge processes. A growing number of hospitals

are taking steps to identify and remove bottlenecks or inefficiencies in the system.

As a result, they are seeing a positive impact on patients, staff, and the bottom line.

By using the principles you have learned in the Six Sigma Black Belt course we want to

decrease 'door to doctor time' in our ER to hopefully reduce the number of people who get

tired of waiting and leave without treatment. In fact, last year of the 43,800 patients awaiting

treatment, 6.3% left without treatment--essentially because they were dissatisfied with the wait time.

The nation's emergency care network must remain strong -- not only to maintain its ability to

serve basic community needs, but also to ensure it will have the necessary capacity and

processes in place to respond quickly during a crisis.

The deadline for having all project deliverables 100% correct is 7 days prior to the end of the course

Define (Project Charter)

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?" daniel-munson: Instructor: Information about project charters can be found in recorded lectures in week 3, in your online textbook and study guides, and in the week 2 virtual class.
upon the information in the introduction. (see "The project" tab)
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.
TARGET ASSIGNMENT DATE - Submit in Week 3 or earlier
Project:	Healthcare
Deliverable:	Project charter
Student last name:	Your LAST name here
What is the business case? (Use no more than 2 sentences.)
		"Business Case?" daniel-munson: 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. MAKE IS CLEAR AND CONCISE!
What is the problem statement? (Use no more than 2 sentences.)
		"Problem Statement" daniel-munson: 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. MAKE IT CLEAR AND CONCISE!
What is the goal statement? (Use one sentence.)
		"Goal Statement" daniel-munson: 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? Remember to link your goal to the problem statement and to keep your goal statement "SMART"!
What is your project scope? (This is not your goal statement! Use one sentence.)
		"The project scope?" daniel-munson: 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... 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. Assumption and constraints regarding your team or budget may also be included in your scope statement.
		daniel-munson: 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. MAKE IT CLEAR AND CONCISE!	daniel-munson: 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? Remember to link your goal to the problem statement and to keep your goal statement "SMART"!	daniel-munson: Instructor: Information about project charters can be found in recorded lectures in week 3, in your online textbook and study guides, and in the week 2 virtual class.	daniel-munson: 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. MAKE IS CLEAR AND CONCISE!	DO NOT SEND ANY EXTRA ATTACHMENTS UNLESS YOUR INSTRUCTOR SPECIFICALLY ASKS YOU TO!!
Please 'hand-in' your assignments throughout the course.
DO NOT SAVE THEM FOR THE END.
The deadline for having all project deliverables 100% correct
is 7 days prior to the end of the course.

Define (SIPOC)

Objective:
We want to view the process from a high level in order to see the major process elements.
SIPOC - Suppliers, Inputs, Process, Outputs, Customers
Instructions for you:
Create a SIPOC map of the process based upon the healthcare case study write-up (found at
"The project" tab). Feel free to use your imagination in doing this piece of the project.
Draw upon your own experience from visiting an Emergency Department to come up with
the 'suppliers', 'inputs', 'process', 'outputs', and 'customers.' Note: Normally the
SIPOC map is constructed horizontally. When mailing this deliverable to your instructor,
it formats better vertically.
TARGET ASSIGNMENT DATE - Submit in Week 4 or earlier
Project:	Healthcare
Deliverable:	SIPOC
Student last name:	Your LAST name here
Suppliers:		HINT! Lois Jordan: Suppliers provide things that are used during the process you outlined below.
(At least 3)
Inputs:		HINT! Lois Jordan: Inputs are used during the process you outlined below.
(At least 3)
Process Step 1:
Process Step 2:
Process Step 3:
Process Step 4:
Process Step 5:
Process Step 6:
Process Step 7:
Process Step 8:
Outputs:		HINT! Lois Jordan: Outputs are created by the process you outlined above.
(At least 3)
Customers:		HINT! Lois Jordan: Customers receive the outputs created by the process you outlined above.
		Lois Jordan: Suppliers provide things that are used during the process you outlined below.	Lois Jordan: Outputs are created by the process you outlined above.	Lois Jordan: Inputs are used during the process you outlined below.	(At least 3)
How to submit an Assignment
DO NOT SEND ANY EXTRA ATTACHMENTS UNLESS YOUR INSTRUCTOR SPECIFICALLY ASKS YOU TO!!
Please 'hand-in' your assignments throughout the course.
DO NOT SAVE THEM FOR THE END.
The deadline for having all project deliverables 100% correct
is 7 days prior to the end of the course.

Measure (Baseline Sigma)

Objective:
We want you to determine the baseline sigma with the Motorola 1.5 sigma shift.
Note: You will need to refer to the tab entitled "The Project" for this deliverable.
Instructions for you:
Calculate the PPM/DPMO for this process and determine the baseline sigma with the Motorola shift.
Data:
6.3% of people left without treatment
TARGET ASSIGNMENT DATE - Submit in Week 7 or earlier
Project:	Healthcare
Deliverable:	Baseline Sigma
Student last name:	Your last name here
What is the baseline sigma? (Approximately)		(Use 1 decimal place)	Check Lois Jordan: Your answer should be between 2 and 4.5
DO NOT SEND ANY EXTRA ATTACHMENTS UNLESS YOUR INSTRUCTOR SPECIFICALLY ASKS YOU TO!!
Please 'hand-in' your assignments throughout the course.
DO NOT SAVE THEM FOR THE END.
The deadline for having all project deliverables 100% correct
is 7 days prior to the end of the course.

Analyze (Pareto Chart)

Objective:
You need to determine the 'biggest contributors to the problem.'
One tool to accomplish this is the Pareto Chart.
Instructions for you:
Using the data (blue) below construct a Pareto chart (see the online video lecture for more).
We realize that you could simply pick the two or three process components without actually creating
the Pareto Chart, but we want you to actually create the Pareto Chart because in reality management
likes to see simple charts where they don't have to analyze the data to see the picture. That is why the
Pareto Chart is used in the first place.
SAMPLE
Notice: The shaded section below has a practice problem intended to help you with this assignment.
If you want to skip this hypothetical problem, you can.
Hypothetical problem (This is NOT the project data which is why this is separated in a shaded box)
Let's say that data reveals the following counts for help-desk occurrences for the month of July.
Injury categories	# of occurrences
Cuts from broken glass	12		This green area is just for practice.
Surf boarding injuries	37		The project data are below in blue.
Fishing related injuries	41
Jelly fish stings	45
Skim boarding accidents	29
Hit with flying toy	79
Sprained ankle	43
Jet Ski accidents	34
Burns from grills	52
Misc.	22
The first thing you would have to do is sort the data from the largest count of injuries to the smallest.
It would look like this after sorting.
Then, you would create a cumulative percentage column. Click once on any of the
cumulative percentages to see the formula.
Injury categories	# of occurrences		Cumulative %
Hit with flying toy	79		20%	How did you get 20? daniel-munson: Instructor: Step 1. Click in the adjacent blank cell and enter an equal (=) sign. Step 2. Click on the cell with the 79. Step 3. Type in a division sign (/). Step 4. Click in the cell with the 394. Step 5. Click enter
Burns from grills	52		33%	How did you get 33? daniel-munson: Instructor: Step 1. Click in the adjacent blank cell and enter an equal (=) sign. Step 2. Click on the cell with the 20% in it. Step 3. Type + and a parenthesis sign Step 4. Click on the cell with the 52. Step 5. Type in a division sign (/). Step 6. Click in the cell with the 394. Step 7. Type a close parenthesis sign Step 8. Click enter
Jelly fish stings	45		45%
Sprained ankle	43		56%
Fishing related injuries	41		66%
Surf boarding injuries	37		75%
Jet ski accidents	34		84%
Skim boarding accidents	29		91%
Misc.	22		97%
Cuts from broken glass	12		100%
	394
..Back to the project itself...
Data:
Reasons for customer dissatisfaction:	Total customers choosing this particular response	Cumulative %
Got tired of waiting	6
Had to go / ran out of time	4
Too many people waiting	4
Doctor treatment	3
Staff treatment	2
Environment	2
Elsewhere	1
Ignored me	1
Too expensive	1
Not necessary	1
TOTAL	25
Create a Pareto chart in Excel 2003 or earlier Diane Johnson: Instructor: Instructions for creating a pareto chart in Excel 2003 or earlier 1. Create a table that looks like the practice problem (above), but with the project data (not the practice data). One column is the counts and the second column is the cumulative percentages. 2. Highlight both columns of data without the labels 3. Click INSERT on the top tab bar 4. Click Chart 5. Click Custom types (one of the tabs) 6. Choose Line - Column on 2 axes (scroll down to find it) 7. Finish to create the chart 8. 'Right click' on left Y axis 9. Click on Format Axis 10. Click Scale 11. Change max to 25 and min to zero 12. OK 13. 'Right click' on right Y axis 14. Click Scale 15. Change max to 1 and min to zero 16. OK 17. Now you have a pareto chart. The cumulative percentage line begins at the top of the first column and rises to 100%. When your pareto chart follows this standard business format, it will enhance communication and presentations to management. If you would like to PRINT these instructions, right click on the cell and select EDIT COMMENTS. Then just highlight the text, copy and paste into a document to print.
Create a Pareto chart in Excel 2007 or later Diane Johnson: Instructor: Excel 2007 or later 1. Create a table that looks like the practice problem (above), but with the project data (not the practice data). One column is the counts and the second column is the cumulative percentages. 2. Highlight both columns of data without the labels 3. Click INSERT on the ribbon. 4. Click Clustered Column Chart to insert the chart 5. Right click the columns that represent the percentages (smaller columns) 6. Click Format Data Series 7. Under series options, click Plot Series On Secondary Axis. 8. On the Layout tab, in the Axes group, you may modify the Secondary Vertical Axis. 9. Right click on the percentage columns, Select Change Series Chart type to LINE 10. 'Right click' on left Y axis and click Format Axis 11. In the Bounds Section, change max to the total of all counts from all columns and a min to zero 12. 'Right click' on right Y axis (secondary axis) and click Format Axis 13. In the Bounds Section, change max to 1.0 (100%) and min to zero 14. Right click the columns for the defect categories, and click Select Data. 15. For series 1, click inside the box for Horizontal (Category) axis labels. 16. Select the labels for the defects in the table (the leftmost column) and click ok. 17. Right click the percentages line 18. Select add data labels to add data labels to the percentage line. 19. Now you have a pareto chart. The cumulative percentage line begins at the top of the first column and rises to 100%. When your pareto follows this standard business format, it will enhance communication and presentations to management. If you would like to PRINT these instructions, just right click the cell, and choose EDIT COMMENT. Then, highlight the text, copy and paste into a document to print.
				daniel-munson: Instructor: Step 1. Click in the adjacent blank cell and enter an equal (=) sign. Step 2. Click on the cell with the 79. Step 3. Type in a division sign (/). Step 4. Click in the cell with the 394. Step 5. Click enter	daniel-munson: Instructor: Step 1. Click in the adjacent blank cell and enter an equal (=) sign. Step 2. Click on the cell with the 20% in it. Step 3. Type + and a parenthesis sign Step 4. Click on the cell with the 52. Step 5. Type in a division sign (/). Step 6. Click in the cell with the 394. Step 7. Type a close parenthesis sign Step 8. Click enter	Diane Johnson: Instructor: Instructions for creating a pareto chart in Excel 2003 or earlier 1. Create a table that looks like the practice problem (above), but with the project data (not the practice data). One column is the counts and the second column is the cumulative percentages. 2. Highlight both columns of data without the labels 3. Click INSERT on the top tab bar 4. Click Chart 5. Click Custom types (one of the tabs) 6. Choose Line - Column on 2 axes (scroll down to find it) 7. Finish to create the chart 8. 'Right click' on left Y axis 9. Click on Format Axis 10. Click Scale 11. Change max to 25 and min to zero 12. OK 13. 'Right click' on right Y axis 14. Click Scale 15. Change max to 1 and min to zero 16. OK 17. Now you have a pareto chart. The cumulative percentage line begins at the top of the first column and rises to 100%. When your pareto chart follows this standard business format, it will enhance communication and presentations to management. If you would like to PRINT these instructions, right click on the cell and select EDIT COMMENTS. Then just highlight the text, copy and paste into a document to print.	TARGET ASSIGNMENT DATE - Submit in Week 8 or earlier
YOU DO NOT NEED TO SEND THE ACTUAL PARETO CHART.
Project:	Healthcare
Deliverable:	Pareto Chart
Student last name:	Your LAST name here
Which ONE issue should your team focus on first based upon what the Pareto Chart
is telling you, and why?
DO NOT SEND ANY EXTRA ATTACHMENTS UNLESS YOUR INSTRUCTOR SPECIFICALLY ASKS YOU TO!!
Please 'hand-in' your assignments throughout the course.
DO NOT SAVE THEM FOR THE END.
The deadline for having all project deliverables 100% correct
is 7 days prior to the end of the course.

Analyze (Expected Variation)

Objective:
Your boss wants to know the limits of expected variation for the DSST resolution times.
Assuming that the process is normally distributed, you know you can calculate/predict the limits
of expected variation by calculating the mean and standard deviation for the data and then using
the properties of the normal distribution to estimate the limits of the expected variation.
			The Mean and StDev in Excel 2003 Diane Johnson: Instructor: Want to use Excel 2003 or earlier to find the mean and standard deviation? There are two ways: USING THE DATA ANALYSIS ADD-IN : 1. Click Tools in the top bar 2. Click Data Analysis (If you don't see Data Analysis as an option on the TOOLS menu, go to the 'EXCEL EXAMPLES' tab at the bottom of this spreadsheet for instructions to load the Data Analysis Tookpak.) 3. Click Descriptive statistics 4. OK 5. Drag with mouse to capture the data. 6. New Workbook Ply 7. Summary statistics 8. OK (You probably will need to resize your column widths to fully see the chart) You may also reset the number of decimal values that are visible, by clicking FORMAT - CELLS - NUMBER and setting the decimal place. USING THE CELL FUNCTIONS: 1. In the cell where you want the mean to be placed, type "=average(" 2. Then with your cursor just after the left parenthesis, highlight all of the data cells you want averaged together. The range of cells will be placed in the parenthesis. 3. You can then tab forward and Excel will add the right parenthesis and perform the calculation automatically. Or you can move into the cell where the formula is and add the right parenthesis yourself. 4. The average will appear in the cell. 5. Repeat steps 1 through 4 for the standard deviation but use the function command "=stdev(" instead of average. Note: You can also do this on a simple scientific calculator. 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.
			The Mean and StDev in Excel 2007 Diane Johnson: Instructor: Want to use Excel 2007 or later to find the mean and standard deviation? There are two ways: USING THE DATA ANALYSIS ADD-IN : 1. Select the DATA tab on the ribbon 2. Click Data Analysis (If you don't see Data Analysis as an option on the DATA tab go to the 'EXCEL EXAMPLES' tab at the bottom of this spreadsheet for instructions to load the Data Analysis Tookpak.) 3. Click Descriptive statistics 4. OK 5. Drag with mouse to capture the data. 6. New Workbook Ply 7. Summary statistics 8. OK (You probably will need to resize your column widths to fully see the chart) You may also reset the number of decimal values that are visible, by clicking FORMAT - CELLS - NUMBER and setting the decimal place. USING THE CELL FUNCTIONS: 1. In the cell where you want the mean to be placed, type "=average(" 2. Then with your cursor just after the left parenthesis, highlight all of the data cells you want averaged together. The range of cells will be placed in the parenthesis. 3. You can then tab forward and Excel will add the right parenthesis and perform the calculation automatically. Or you can move into the cell where the formula is and add the right parenthesis yourself. 4. The average will appear in the cell. 5. Repeat steps 1 through 4 for the standard deviation but use the function command "=stdev(" instead of average. Note: You can also do this on a simple scientific calculator. 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.
			Expected variation Microsoft Office User: Instructor: What is expected variation? We know that 68% of data from a normal process is expected to fall + or - 1 sigma from the mean. We know that 95% of data from a normal process is expected to fall + or - 2 sigma from the mean. We know that 99.7% of data from a normal process is expected to fall + or - 3 sigma from the mean. So, the expected variation that we would likely see in the data will fall between the mean and + or - 3 standard deviations. 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.
Instructions for you:
1. Calculate the average for each.
2. Calculate the standard deviation for each.
3. From these calculations, determine the range of expected variation.
You can complete this assignment by hand as taught in the lecture, or
you can use Excel, or you may use a calculator or
other software. We explain the fundamentals of the
calculations, so that you learn the mechanics and the meaning
of standard deviation. This approach gives you an
understanding of standard deviation, which then allows
you to be flexible in the tools that you use.
4. Create a histogram from this data. Use the				Histograms in Excel 2003 or earlier Diane Johnson: Instructor: Excel 2003 and earlier - 2 Options Option #1 (Quick and Dirty draft of a histogram) 1) Click TOOLS in the Excel toolbar 2) Click Data Analysis 3) Select HISTOGRAMS and then click OK 4) The cursor should be blinking in the "Input Range" box 5) Highlight your data 6) Click "New Worksheet Ply" 6) Skip the bin range option, and all other options 7) Check the "Chart Output" box 8) Click OK, and your histogram will appear. 9) You may drag your mouse over the corner of the histogram graph to enlarge it. 10) Do NOT delete the MORE category if there are data values in it. Option #2 (A more polished copy where you determine the bin ranges, rather than Excel) 1) Click TOOLS in the Excel toolbar 2) Click Data Analysis 3) Select HISTOGRAMS and then click OK 4) Determine the range of the data set, from your smallest to your largest data value 5) Add one value (in the unit you are measuring) for 'range with inclusion' (Example: .31 (range) + .01 = .32) 6) Determine the appropriate number of bars for your sample size (Example: 60 data points use 6-10 bars) 7) Calculate both the beginning and ending point of each cell and list the beginning and ending points of each bin in separate cells. For example, if you have a range of .32, you could have 8 cells with .04 data value per cell. 8) Now you have an idiosyncrasy of Excel. Excel will give you the option of designating the bin ranges. But, you should give Excel the ENDING value of each bin rather than the beginning value. Highlight the cells that include the ending value of each bin under the BIN RANGE option. 9) Highlight your complete data set under the INPUT RANGE option 10) Check on CHART OUTPUT and where you want the histogram chart located. (New worksheet or imbedded) 11) Click OK 12) You may drag your mouse over the corner of the histogram chart to enlarge it. 13) When Excel drafts histograms, the bars do not adjoin. Ideally, we want the bars of a histogram to adjoin because we are graphing continuous data. To make the bars adjoin, right click on top of bars and choose FORMAT DATA SERIES. In Excel 2003, you will have to select OPTIONS. Then set the GAP to Zero. 14) To change the color scheme of the histogram, right click somewhere outside of the bars in the graph and choose FORMAT PLOT AREA. You can choose many color formats. 15) When you plan the cell intervals, there are no data points in the MORE category, so you may delete it, because the MORE category is often confusing to people. DO NOT delete the MORE category if it has data points in it. 16) Only go through these more detailed steps if you are interested in a polished Histogram. Otherwise run a quick draft of the histogram with Option #1. 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.
appropriate number of cells to represent the data.
Hint: There is a 'rules of thumb' cell choice table
in the HISTOGRAM (part I) section of your workbook.				Histograms in Excel 2007 or later Diane Johnson: Instructor: Excel 2007 and later - 2 Options Option #1 (Quick and Dirty draft of a histogram) 1) Click DATA tab 2) Go to ANALYSIS category on the DATA tab 3) Click on DATA ANALYSIS (If you do not see DATA ANALYSIS, refer to the EXCEL EXAMPLES tab at the bottom of this spreadsheet for instructions in loading the DATA ANALYSIS features.) 4) Select HISTOGRAMS and then click OK 5) The cursor should be blinking in the "Input Range" box 6) Highlight your data 7) Click in "New Worksheet Ply" 8) Skip the bin range option, and other options 9) Check the "Chart Output" box 10) Click OK 11) You may drag your mouse over the corner of the graph to enlarge it. Option #2 (A More polished copy where you determine the bin ranges) 1) Click DATA tab 2) Go to ANALYSIS category on the DATA tab 3) Click on DATA ANALYSIS (If you do not see DATA ANALYSIS, refer to the EXCEL EXAMPLES tab at the bottom of this spreadsheet for instructions in loading the DATA ANALYSIS features.) 4) Select HISTOGRAMS and then click OK 5) Determine the range of the data set, from your smallest to your largest data value 6) Add one value (in the unit you are measuring) for 'range with inclusion' (Example: .31 (range) + .01 = .32) 7) Determine the appropriate number of bars for your sample size (Example: 60 data points use 6-10 bars) 8) Calculate both the beginning and ending point of each cell and list the beginning and ending points of each bin in separate cells. For example, if you have a range of .32, you could have 8 cells with .04 data value per cell. 9) Now you have an idiosyncrasy of Excel. Excel will give you the option of designating the bin ranges. But, you should give Excel the ENDING value of each bin rather than the beginning value. Highlight the cells that include the ending value of each bin under the BIN RANGE option. 10) Highlight your complete data set under the INPUT RANGE option 11) Check on CHART OUTPUT and where you want the histogram chart located. (New worksheet or imbedded) 12) Click OK 13) You may drag your mouse over the corner of the histogram chart to enlarge it. 14) When Excel drafts histograms, the bars do not adjoin. Ideally, we want the bars of a histogram to adjoin because we are graphing continuous data. To make the bars adjoin, right click on top of bars and choose FORMAT DATA SERIES. In Excel 2007, you go directly to setting GAP WIDTH to zero. 15) To change the color scheme of the histogram, right click somewhere outside of the bars in the graph and choose FORMAT PLOT AREA. You can choose many color formats. 16) When you plan the cell intervals, there are no data points in the MORE category, so you may delete it, because the MORE category is often confusing to people. DO NOT delete the MORE category if it has data points in it. 17) Only go through these more detailed steps if you are interested in a polished Histogram. Otherwise run a quick draft of the histogram with Option #1. 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.
5. By looking at the histogram, is the process normally
distributed? Is there obvious assignable-cause variation?				More on Bin Ranges daniel-munson: Instructor: If you want your histogram to have the appropriate number of bin ranges (also known as cell intervals), you will need to enter some bin values in the dialogue box in DATA ANALYSIS for making Histograms. To determne bin ranges, you first need to find the inclusive range of the data set. This is done by subtracting the smallest value from the largest value, and adding 1 unit of data. In this case, you would add one minute to the result of largest-smallest to find the inclusive range. The recommended number of cell choices for n = 30 is 5 to 7. We use 5 for this deliverable. That means I will have 5 cells to define by their ending values. To find the bin size, divide the inclusive range by the number of bins. Find the ending value of the bin by adding the size of the bin to the previous value. The first bin will be found by adding the bin size to the minimum value of the data set. This gives you the ending value for the first bin. The ending value for the second bin is found by adding the bin size to the previous bin ending value. This continues until you reach the maximum of your data set, and have defined all of your bins. Enter the 'maximum' value (or ending value) from each cell interval into a separate place in this worksheet - location is not important. Then you will be able to drag the cursor across them to satisfy the bin range box in DATA ANALYSIS. IF YOU ARE USING EXCEL, YOU MAY ALSO LET EXCEL CHOOSE THE BIN RANGES FOR YOU!! 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.
			Diane Johnson: Instructor: Want to use Excel 2003 or earlier to find the mean and standard deviation? There are two ways: USING THE DATA ANALYSIS ADD-IN : 1. Click Tools in the top bar 2. Click Data Analysis (If you don't see Data Analysis as an option on the TOOLS menu, go to the 'EXCEL EXAMPLES' tab at the bottom of this spreadsheet for instructions to load the Data Analysis Tookpak.) 3. Click Descriptive statistics 4. OK 5. Drag with mouse to capture the data. 6. New Workbook Ply 7. Summary statistics 8. OK (You probably will need to resize your column widths to fully see the chart) You may also reset the number of decimal values that are visible, by clicking FORMAT - CELLS - NUMBER and setting the decimal place. USING THE CELL FUNCTIONS: 1. In the cell where you want the mean to be placed, type "=average(" 2. Then with your cursor just after the left parenthesis, highlight all of the data cells you want averaged together. The range of cells will be placed in the parenthesis. 3. You can then tab forward and Excel will add the right parenthesis and perform the calculation automatically. Or you can move into the cell where the formula is and add the right parenthesis yourself. 4. The average will appear in the cell. 5. Repeat steps 1 through 4 for the standard deviation but use the function command "=stdev(" instead of average. Note: You can also do this on a simple scientific calculator. 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.	Diane Johnson: Instructor: Want to use Excel 2007 or later to find the mean and standard deviation? There are two ways: USING THE DATA ANALYSIS ADD-IN : 1. Select the DATA tab on the ribbon 2. Click Data Analysis (If you don't see Data Analysis as an option on the DATA tab go to the 'EXCEL EXAMPLES' tab at the bottom of this spreadsheet for instructions to load the Data Analysis Tookpak.) 3. Click Descriptive statistics 4. OK 5. Drag with mouse to capture the data. 6. New Workbook Ply 7. Summary statistics 8. OK (You probably will need to resize your column widths to fully see the chart) You may also reset the number of decimal values that are visible, by clicking FORMAT - CELLS - NUMBER and setting the decimal place. USING THE CELL FUNCTIONS: 1. In the cell where you want the mean to be placed, type "=average(" 2. Then with your cursor just after the left parenthesis, highlight all of the data cells you want averaged together. The range of cells will be placed in the parenthesis. 3. You can then tab forward and Excel will add the right parenthesis and perform the calculation automatically. Or you can move into the cell where the formula is and add the right parenthesis yourself. 4. The average will appear in the cell. 5. Repeat steps 1 through 4 for the standard deviation but use the function command "=stdev(" instead of average. Note: You can also do this on a simple scientific calculator. 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.	Microsoft Office User: Instructor: What is expected variation? We know that 68% of data from a normal process is expected to fall + or - 1 sigma from the mean. We know that 95% of data from a normal process is expected to fall + or - 2 sigma from the mean. We know that 99.7% of data from a normal process is expected to fall + or - 3 sigma from the mean. So, the expected variation that we would likely see in the data will fall between the mean and + or - 3 standard deviations. 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.	Data:
Average ED Wait Times (Door to Doctor)		214
last 30 days (measured in minutes)		356
		237
		144
		146
		206
		107
		267
		208
		335
		204
		298
		213
		227
		187
		269
		216
		268
		242
		199
		251
		202
		245
		186
		229
		141
		251
		63
		232
		267
TARGET ASSIGNMENT DATE - Submit in Week 9 or earlier
YOU DO NOT NEED TO SUBMIT A HISTOGRAM FOR THIS DELIVERABLE
Project:	Healthcare
Deliverable:	Expected Variation			How do I determine if it's approximately normal? Lois Jordan: Instructor: The question is whether the histogram is APPROXIMATELY bell-shaped so that the assumption of normality is a reasonable one. Remember that you will never see perfectly normal distributions in real life! And even if a process is perfectly normally distributed, you will need a VERY large sample size for your histogram to appear normal or symmetric. Close wins the cigar - all we worry about are gross departures from normality. Is this histogram grossly non-normal?
Student last name:	Your last name here
Mean:						Check Lois Jordan: Your answer should be between 190 and 230.
Standard deviation:				ROUND ALL FINAL RESULTS TO 2 DECIMAL PLACES.		Check Lois Jordan: Your answer should be between 50 and 70.
Are the data approximately bell shaped?						Help daniel-munson: Instructor: You will need to create a histogram to answer this question.
What is the lowest point of expected variation?				Help daniel-munson: Instructor: In a normally distributed process, the limits of the expected variation fall at + and - 3 standard deviations from the mean. So, what does this mean? (pun intended) The MEAN + and - one (1) SD in a normally distributed process represents ~68% of what the process is capable of producing. The MEAN + and - two (2) SD in a normally distributed process represents ~95% of what the process is capable of producing. The MEAN + and - three (3) SD in a normally distributed process represents ~99.73% of what the process is capable of producing.		daniel-munson: Instructor: You will need to create a histogram to answer this question.	Check Lois Jordan: Your answer should be between 30 and 50
What is the upper point of expected variation?						Check Lois Jordan: Your answer should be between 390 and 420.
				Diane Johnson: Instructor: Excel 2003 and earlier - 2 Options Option #1 (Quick and Dirty draft of a histogram) 1) Click TOOLS in the Excel toolbar 2) Click Data Analysis 3) Select HISTOGRAMS and then click OK 4) The cursor should be blinking in the "Input Range" box 5) Highlight your data 6) Click "New Worksheet Ply" 6) Skip the bin range option, and all other options 7) Check the "Chart Output" box 8) Click OK, and your histogram will appear. 9) You may drag your mouse over the corner of the histogram graph to enlarge it. 10) Do NOT delete the MORE category if there are data values in it. Option #2 (A more polished copy where you determine the bin ranges, rather than Excel) 1) Click TOOLS in the Excel toolbar 2) Click Data Analysis 3) Select HISTOGRAMS and then click OK 4) Determine the range of the data set, from your smallest to your largest data value 5) Add one value (in the unit you are measuring) for 'range with inclusion' (Example: .31 (range) + .01 = .32) 6) Determine the appropriate number of bars for your sample size (Example: 60 data points use 6-10 bars) 7) Calculate both the beginning and ending point of each cell and list the beginning and ending points of each bin in separate cells. For example, if you have a range of .32, you could have 8 cells with .04 data value per cell. 8) Now you have an idiosyncrasy of Excel. Excel will give you the option of designating the bin ranges. But, you should give Excel the ENDING value of each bin rather than the beginning value. Highlight the cells that include the ending value of each bin under the BIN RANGE option. 9) Highlight your complete data set under the INPUT RANGE option 10) Check on CHART OUTPUT and where you want the histogram chart located. (New worksheet or imbedded) 11) Click OK 12) You may drag your mouse over the corner of the histogram chart to enlarge it. 13) When Excel drafts histograms, the bars do not adjoin. Ideally, we want the bars of a histogram to adjoin because we are graphing continuous data. To make the bars adjoin, right click on top of bars and choose FORMAT DATA SERIES. In Excel 2003, you will have to select OPTIONS. Then set the GAP to Zero. 14) To change the color scheme of the histogram, right click somewhere outside of the bars in the graph and choose FORMAT PLOT AREA. You can choose many color formats. 15) When you plan the cell intervals, there are no data points in the MORE category, so you may delete it, because the MORE category is often confusing to people. DO NOT delete the MORE category if it has data points in it. 16) Only go through these more detailed steps if you are interested in a polished Histogram. Otherwise run a quick draft of the histogram with Option #1. 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.	DO NOT SEND ANY EXTRA ATTACHMENTS UNLESS YOUR INSTRUCTOR SPECIFICALLY ASKS YOU TO!!
Please 'hand-in' your assignments throughout the course.
DO NOT SAVE THEM FOR THE END.
The deadline for having all project deliverables 100% correct
is 7 days prior to the end of the course.

Analyze (T Test)

Objective:

There is thought the major trauma centers and large hospitals with over 300 beds

are producing different amounts of patients

You DO NOT WANT the methods to be different. In fact, you want the methods to be

producing the same amount of patients. If the method averages are significantly

different, you have a problem! To address this, you suggest using a T test.

If the null is NOT rejected--that is good news! The team agrees that a sample size

of 25 is adequate for the sample from the large hospitals. You already know

the mean number of people for the trauma center, which is 126.

The team decides to use a significance level of 0.05 for the test.

Instructions for you:

Step 1: State the null and alternative hypotheses

Hint
Microsoft Office User: Instructor: In this case, we are comparing the new method to the old method. Since the old method mean is known, this is a 1 sample t-test. We compare the new method population mean (µ) to the known popuation mean of the old method.

Step 2: Gather the required information

Hint
Microsoft Office User: Instructor: Refer to the formula to determine the information that you need to complete the calculation of the t value. The formula may be found in the textbook, the study guide, or the recorded video lecture.

Step 3: Calculate the t-test statistic (round to 6 decimal places)

Hint
Microsoft Office User: Instructor: The formula may be found in the textbook, the study guide, or the recorded video lecture. You may set up the formula in Excel, or simply use a calculator to find the result. The Data Analysis add-in in Excel may be used to calculate a two sample t-test, but there is no option for a one sample t-test.

Step 4: Determine the critical t value

Hint
Microsoft Office User: Instructor: Refer to t table in the textbook. Remember you will need to know the degrees of freedom (df) and the confidence level of the test to determine the critical value from the table. You will also need to know whether this is a two-tailed test (the mean is not equal to the target) or a one-tailed test (the mean is greater than or less than the target). The critical value will be different for each type of test.

Step 5: State the statistical conclusion

Hint
Microsoft Office User: Instructor: Based on your calculations, and the comparison of the calculated t value to the critical t value, what is your conclusion concerning your hypothesis? How confident are you?

Step 6: State the practical conclusion

Hint
Microsoft Office User: Instructor: What does this mean in terms of the original question that you wanted to answer? Use the terms of the original problem to describe the result.

Data:

Large Hospitals

100

110

100

110

130

120

100

130

140

120

140

110

150

110

120

150

110

120

Target Assignment Date - Submit in Week 11 or earlier

Project:

Healthcare

Deliverable:

T Test

Student last name:

Your last name here

State the null hypothesis:::

State the alt. hypothesis:::

T test statistic (round to 6 decimal places):

Use 6 decimal places.

Check
daniel-munson: Instructor: You should get a value between 1 and 5. ( This value could be either positive or negative. )

What is the critical value?

Check
daniel-munson: Instructor: You should get a value between 0 and 5.

State your statistical conclusion in the words of the null and alternate hypotheses

HINT!
Lois Jordan: State your answer in terms of how you handle the null hypothesis.

State your practical conclusion in the words of the problem addressed

by this study. (Do not use generic hypothesis test wording.)

HINT!
Lois Jordan: Be sure to use non-statistical terms people without statistics training will be able to understand and be sure to discuss the results specific to this study.

Microsoft Office User: Instructor: Refer to the formula to determine the information that you need to complete the calculation of the t value. The formula may be found in the textbook, the study guide, or the recorded video lecture.

Microsoft Office User: Instructor: The formula may be found in the textbook, the study guide, or the recorded video lecture. You may set up the formula in Excel, or simply use a calculator to find the result. The Data Analysis add-in in Excel may be used to calculate a two sample t-test, but there is no option for a one sample t-test.

Microsoft Office User: Instructor: Refer to t table in the textbook. Remember you will need to know the degrees of freedom (df) and the confidence level of the test to determine the critical value from the table. You will also need to know whether this is a two-tailed test (the mean is not equal to the target) or a one-tailed test (the mean is greater than or less than the target). The critical value will be different for each type of test.

Microsoft Office User: Instructor: Based on your calculations, and the comparison of the calculated t value to the critical t value, what is your conclusion concerning your hypothesis? How confident are you?

Microsoft Office User: Instructor: What does this mean in terms of the original question that you wanted to answer? Use the terms of the original problem to describe the result.

daniel-munson: Instructor: You should get a value between 1 and 5. ( This value could be either positive or negative. )

daniel-munson: Instructor: You should get a value between 0 and 5.

Lois Jordan: State your answer in terms of how you handle the null hypothesis.

DO NOT SEND ANY EXTRA ATTACHMENTS UNLESS YOUR INSTRUCTOR SPECIFICALLY ASKS YOU TO!!

Please 'hand-in' your assignments throughout the course.

DO NOT SAVE THEM FOR THE END.

Procrastinators: The deadline for having all project deliverables 100% correct

is 7 days prior to the end of the course.

Improve (Scatter Diagram)

Objective:
The team is certain there is a correlation between the volume of patients and the number of patients who
"Leave Without Treatment" (LWT). If there really IS correlation with the set of data,
the attention will be directed to determine how to improve the process
considering the cause-effect relationship between the x's and y's. If
there is no correlation evident in the set of data, the team will have to go
back to the drawing board.'
Instructions for you:
1. With the data provided, construct a scatter diagram to see if their hypothesis is correct.
2. Calculate the correlation coefficient between the two variables. (Use 6 decimal places)
Data:
	Number in for treatment per specific day	Leave without treatment incidents
	172	4		Scatter diagrams in Excel 2003 or earlier Diane Johnson: Instructor: 1) Click on Chart icon on top task bar, OR Click on the INSERT menu option at the top menu bar. 2) Click on SCATTER from the Standard Types tab 3) Click Next 4) Highlight both columns of data and finish according to the directions. 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.
	132	6
	130	2		Scatter diagrams in Excel 2007 or later Diane Johnson: Instructor: 1) Highlight both columns of data 2) Click INSERT tab at the top 3) Go to CHART category 4) Click on SCATTER and your scatter diagram appears 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.
	206	4
	199	6		Correlation Coefficient - Excel 2003 or earlier Diane Johnson: Instructor: 1) Click on the fx in the top bar and CORREL, or click on INSERT, function, CORREL. 2) Highlight each column of data as an ARRAY 3) Click Okay. 4) Excel will calculate the correlation coefficient. The correlation coefficient ranges between zero and one. Zero is no correlation and '1' is a perfect correlation. __________________________________________ -1.0 to -0.7 strong negative association. -0.7 to -0.3 weak negative association. -0.3 to +0.3 little or no association. +0.3 to +0.7 weak positive association. +0.7 to +1.0 strong positive association. 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.
	223	4
	201	8		Correlation Coefficient - Excel 2007 or later Diane Johnson: Instructor: 1) Click on the fx in the top bar and CORREL, or click on FOMULAS, INSERT function, CORREL. 2) Highlight each column of data as an ARRAY 3) Click Okay. 4) Excel will calculate the correlation coefficient. The correlation coefficient ranges between zero and one. Zero is no correlation and '1' is a perfect correlation. __________________________________________ -1.0 to -0.7 strong negative association. -0.7 to -0.3 weak negative association. -0.3 to +0.3 little or no association. +0.3 to +0.7 weak positive association. +0.7 to +1.0 strong positive association. 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.
	169	7
	135	5
	200	3
	189	7
	110	8
	203	6
	189	5
	224	8
	197	4
	188	8
	125	2
	199	6
	194	8
	207	7
TARGET ASSIGNMENT DATE - Submit in Week 12 or earlier
YOU DO NOT NEED TO SUBMIT THE ACTUAL SCATTER DIAGRAM
Project:			Healthcare
Deliverable:			Scatter Diagram
Student last name:			Your last name here
Is there strong correlation between the two variables?							HINT! Lois Jordan: HINT: Be sure to use commonly accepted criteria for evaluating correlation coefficients in your answer.
				Diane Johnson: Instructor: 1) Highlight both columns of data 2) Click INSERT tab at the top 3) Go to CHART category 4) Click on SCATTER and your scatter diagram appears 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.	Diane Johnson: Instructor: 1) Click on the fx in the top bar and CORREL, or click on INSERT, function, CORREL. 2) Highlight each column of data as an ARRAY 3) Click Okay. 4) Excel will calculate the correlation coefficient. The correlation coefficient ranges between zero and one. Zero is no correlation and '1' is a perfect correlation. __________________________________________ -1.0 to -0.7 strong negative association. -0.7 to -0.3 weak negative association. -0.3 to +0.3 little or no association. +0.3 to +0.7 weak positive association. +0.7 to +1.0 strong positive association. 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.	Diane Johnson: Instructor: 1) Click on the fx in the top bar and CORREL, or click on FOMULAS, INSERT function, CORREL. 2) Highlight each column of data as an ARRAY 3) Click Okay. 4) Excel will calculate the correlation coefficient. The correlation coefficient ranges between zero and one. Zero is no correlation and '1' is a perfect correlation. __________________________________________ -1.0 to -0.7 strong negative association. -0.7 to -0.3 weak negative association. -0.3 to +0.3 little or no association. +0.3 to +0.7 weak positive association. +0.7 to +1.0 strong positive association. 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.	ROUND ALL FINAL RESULTS TO 6 DECIMAL PLACES.
What is the calculated correlation coefficient?
DO NOT SEND ANY EXTRA ATTACHMENTS UNLESS YOUR INSTRUCTOR SPECIFICALLY ASKS YOU TO!!
Please 'hand-in' your assignments throughout the course.
DO NOT SAVE THEM FOR THE END.
The deadline for having all project deliverables 100% correct
is 7 days prior to the end of the course.

Improve (DOE)

Objective:
You recommend using a design of experiments approach to hopefully realize a breakthrough
improvement. The team brainstorms a long list of possible reasons why the average treatment time is
so long. From that list, the team has reduced it down to five factors they want to include in an
experiment. They do not suspect any interactions, so they conclude a resolution III will be adequate.
They refer to the resolution matrix (in Analyzing Fractional Factorial Designs lecture and the online
textbook) and they find they can learn about the effects of those five factors in as little as
eight experiments.
Instructions for you:
Construct an main effects plot for all five of the factors. We will do the first one for you as
an example:
Take an average of the response time minutes (y) when "temperature" was at (-). Then, do the same thing
for when the results (y) when "temperature" was at (+).
		When at (-)		When at (+)
		7		9
		28		25
		26		8
		6		28
	Average	16.75		17.50
Data:
The factors for the experiment were:
					- level		+ level
	A	Temp. in the waiting room			68 degrees		75 degrees
	B	Order of treatment			FIFO		By priority
	C	Method of treatment			Iterative		All at once
	D	Tracking software			Product A		Product B
	E	Staff size			8		16
	The design below was taken from Minitab. There are other software packages that provide the
	same information. There can also be found in Design of Experiments textbooks.
	Trial	Temp	Order	Method	Software	Staff Size	Results (Y) (treatment time - in minutes)
	1	1	1	-1	-1	-1	9
	2	-1	-1	-1	1	1	7	Excel 2003 Tip Diane Johnson: Instructor: Leave a blank cell between the factors in your Excel 2003 or earlier spreadsheet. 1. Click INSERT on the top tab bar 2. Click Chart 3. Select the LINE graph with "line with markers displayed at each data value" Example of your spreadsheet layout with blank cell between factors: Staff Size - 16.75 Staff Size + 17.50 SKIP CELL Order - Order + SKIP CELL
	3	1	1	1	1	-1	25
	4	-1	-1	1	1	-1	28	Excel 2007 Tip Diane Johnson: Instructor: Excel 2007 or later 1. Highlight your data sets and labels leaving a blank cell between the factors as illustrated below. 2. Click INSERT on the top tab bar 3. In the Chart category, Select the LINE graph with "line with markers displayed at each data value" Example of your spreadsheet layout with blank cell between factors: Staff size - 16.75 Staff size + 17.50 SKIP CELL Order - Order + SKIP CELL
	5	-1	1	1	-1	1	26
	6	1	-1	-1	1	1	8
	7	1	-1	1	-1	-1	28
	8	-1	1	-1	-1	1	6
TARGET ASSIGNMENT DATE - Submit in Week 13 or earlier
YOU DO NOT NEED TO SEND IN THE MAIN EFFECTS PLOTS
Project:			Healthcare
Deliverable:			DOE
Student last name:			Your last name here
Temp. -								Check daniel-munson: Instructor: Temperature (-) should be between 15 and 20 minutes.
Temp. +								Check daniel-munson: Instructor: Temperature (+) should be between 15 and 20 minutes.
Order -				ROUND YOUR FINAL RESULTS TO 2 DECIMAL PLACES.				Check daniel-munson: Instructor: Treatment order (-) should be between 15 and 20 minutes.
Order +								Check daniel-munson: Instructor: Treatment order (+) should be between 15 and 20 minutes.
Method -								Check daniel-munson: Instructor: Method (-) should be between 0 and 10 minutes.
Method +								Check daniel-munson: Instructor: Method (+) should be between 20 and 30 minutes.
Software -								Check daniel-munson: Instructor: Software type (-) should be between 10 and 20 minutes.
Software +								Check daniel-munson: Instructor: Software type (+) should be between 10 and 20 minutes.
Staff sz -								Check daniel-munson: Instructor: Staff size (-) should be between 20 and 25 minutes.
Staff sz +								Check daniel-munson: Instructor: Staff size (+) should be between 10 and 15 minutes.
Which two factors are most significant and what are the optimum settings for these two factors?
								Check daniel-munson: We do not want the results you calculated for the main effects plots here. We want to know which of the two levels you tested these factors at gave the best results when considering what the response variable is. HINT: What is the best result for the characteristic measured here - smaller, larger or nominal?
								Diane Johnson: Instructor: Leave a blank cell between the factors in your Excel 2003 or earlier spreadsheet. 1. Click INSERT on the top tab bar 2. Click Chart 3. Select the LINE graph with "line with markers displayed at each data value" Example of your spreadsheet layout with blank cell between factors: Staff Size - 16.75 Staff Size + 17.50 SKIP CELL Order - Order + SKIP CELL	Diane Johnson: Instructor: Excel 2007 or later 1. Highlight your data sets and labels leaving a blank cell between the factors as illustrated below. 2. Click INSERT on the top tab bar 3. In the Chart category, Select the LINE graph with "line with markers displayed at each data value" Example of your spreadsheet layout with blank cell between factors: Staff size - 16.75 Staff size + 17.50 SKIP CELL Order - Order + SKIP CELL	daniel-munson: Instructor: Temperature (-) should be between 15 and 20 minutes.	DO NOT SEND ANY EXTRA ATTACHMENTS UNLESS YOUR INSTRUCTOR SPECIFICALLY ASKS YOU TO!!
Please 'hand-in' your assignments throughout the course.
DO NOT SAVE THEM FOR THE END.
The deadline for having all project deliverables 100% correct
is 7 days prior to the end of the course.

Control (XmR Chart)

Objective:
The team changed the process so that returning patients were processed ahead of the first-time patients.
This dramatically decreased the overall wait times because the returning patients were
already in the system. The team also learned how to improve the process
through the use of designed experiments. There were other improvements made through the
use of other Six Sigma tools and techniques (aside from your project deliverables) and now the
wait times are acceptable. Now, you want to ensure that they are staying that way and you want
to practice "prevention" versus "detection." Part of your CONTROL strategy is to employ
the use of an on-going capability study to ensure 'the plates are still spinning'--so-to-speak.
And, the next deliverable will do that, but prior to performing a capability study, one
must make sure the process is in statistical control. This is good, because the team
decided to use a control chart as part of the CONTROL phase anyhow, so this will
work out fine. In fact, let's do one now…just to make sure we are in statistical control.
Instructions for you:
Using the data below, create an XmR chart for average resolution times. Use the chart to
answer the following questions and type those responses in the peach colored box below.
1. What is the upper control limit for the range?			Control Charts in Excel 2003 or earlier Diane Johnson: Instructor: 1. Click on chart icon in top menu, OR click on INSERT in menu bar and select CHART. 2. Select LINE chart. 3. Follow menu-driven steps and highlight the data. 4. When the line chart is complete, add the mean, the average moving range, and your control limits with the drawing tools. TOOLBARS - Drawing 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.
2. What is the upper control limit for the individuals?			Control Charts in Excel 2007 or later Diane Johnson: Instructor: Steps for drafting a control chart in Excel 2007 1. Highlight the data. 2. Click on INSERT tab at top of bar. 3. Find the CHARTS category 4. Click on LINE 5. Add control limits and the mean and average moving range with the drawing tools. (INSERT - SHAPES) 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.
3. What is the lower control limit for the individuals?			Help Diane Johnson: Instructor: The formulas for the control limits of the XmR chart are found in your textbook. Do you understand why we are using a XmR chart in this assignment versus an X-barR chart? ANSWER - We only have individual data points. We do not have rational subgroups.
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" daniel-munson: Instructor: How to determine whether or not a measurement system is discriminant is covered in two different recorded lectures. It is covered in the control chart lecture and it is also covered in the 'Measurement System Evaluation" lecture. This topic is also covered in the virtual class on SPC.
c. Is resolution time in statistical control?			"I have no idea about this one" daniel-munson: Instructor: How to determine whether or not a process is stable is covered in the recorded lectures, the online textbook and the virtual class on SPC.
Data:
Average wait times (in minutes) in order of occurrence
1	10
2	17	1a. Calculate R-bar	Calculating R-bar in Excel 2003 or earlier Diane Johnson: Instructor: More help on calculating R-bar using Excel 2003 We really intend on you doing this step by hand, but here is an option in Excel 2003 & earlier. 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. 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 (10) and the second value (17) do the following steps in Excel. 1) Click the empty cell next to, and to the right of the second value (17). 2) INSERT 3) FUNCTION 4) Scroll down to 'ABS' (absolute value) 5) OK 6) Click on the second cell (17) 7) Type in a minus from the keyboard, and click on the first cell (10) 8) OK. You should get 7. 9) Now....grab the bottom right-handed corner of the cell you are working with, and drag it all the way down to the bottom of the list of numbers. This will repeat the formula for you all the way down the line. You should have all of the range values starting at 7 and ending with the last value of 1. 10) For the second step in this process, you will be taking an average of all of your range values to get the average moving range. To do that: 11) Click on any empty cell 12) Insert 13) Function 14) Scroll down to 'AVERAGE' 15) OK 16) Drag down the column of values that you just created in the previous step. 17) OK. You have just calculated the average moving range which you will use in calculating the control limits for the XmR chart. 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.
3	29		Calculating R-bar in Excel 2007 or later Diane Johnson: 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 (10) and the second value (17) do the following steps in Excel. 1) Click the empty cell next to, and to the right of the second value (17) 2) Click the FORMULAS tab at the top bar 3) Under the FUNCTION LIBRARY category, click MATH & TRIG 4) Scroll down to 'ABS' (absolute value) 5) OK 6) Click on the second cell (17) 7) Type in a minus from the keyboard, and click on the first cell (10) 8) Enter. You should get 7. 9) Now....grab the bottom right-handed corner of the cell you are working with, and drag it all the way down to the bottom of the list of numbers. This will repeat the formula for you all the way down the line. You should have all of the range values starting at 7 and ending with the last value of 1. 10) For the second step in this process, you will be taking an average of all of your range values to get the average moving range. To do that: 11) Click on any empty cell 12) Insert 13) Function 14) Scroll down to 'AVERAGE' 15) OK 16) Drag down the column of values that you just created in the previous step. 17) OK. You have just calculated the average moving range which you will use in calculating the control limits for the XmR chart. 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.
4	39	Self check daniel-munson: Self check: Did you get -0.034483? 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 between 10 and 15.
5	55
6	64	1b. Calculate the
7	28	upper control limit
8	6	for the range
9	5	"How do I do that?" daniel-munson: Instructor: Please refer to the lecture on how to calculate control limits for the XmR chart.
10	3	Self Check daniel-munson: Instructor: UCL for the range should be between 40 and 50.
11	39
12	46	2 & 3. Calculate the
13	35	control limits for the
14	30	individuals
15	6	"How do I do that?" daniel-munson: Instructor: Please refer to the lecture on how to calculate control limits for the XmR chart.
16	32	Self check daniel-munson: Self check: UCL between 60 and 70. LCL for the individuals should be 0. The value you got was probably -8.3, but you cannot have a negative wait time, so you just go with zero as your lower control limit.
17	33
18	11
19	20
20	13
21	9
22	14
23	12
24	30
25	56
26	62
27	73
28	54
29	10
30	9
Be sure to review the week 11 virtual class for more information on SPC and measurement discrimination before you submit this!
TARGET ASSIGNMENT DATE - Submit in Week 14 or earlier
YOU DO NOT HAVE TO SUBMIT THE CONTROL CHART. JUST ANSWER THE QUESTIONS.
Project:		Healthcare
Deliverable:		XmR Chart
Student last name:		Your last name here
Upper control limit for the range:			ROUND ALL RESULTS TO 6 DECIMAL PLACES.				Help daniel-munson: Instructor: It is best to start off with calculating the control limit for the range. This is because you need the average moving range number for your calculations. Remember, there will not be a lower control limit for the range with a small subgroup size. (only an upper control limit). Still lost? Re-watch the lecture on XmR charts.	daniel-munson: Self check: UCL between 60 and 70. LCL for the individuals should be 0. The value you got was probably -8.3, but you cannot have a negative wait time, so you just go with zero as your lower control limit.	Check daniel-munson: Instructor: UCL for the range should be between 40 and 50.
Upper control limit for the individuals:							Help daniel-munson: Instructor: Remember the six steps to control charts? 1. Gather data in the order of production. We have given you the data for that. 2. Calculate subgroup averages and ranges. 3. Calculate control limits. 4. Plot the control limits. 5. Plot the points. 6. Act upon what the control chart is telling you. You don't remember that? Re-watch the lectures on control charting.	Check daniel-munson: Instructor: UCL for the individuals should be between 60 and 70.
Lower control limit for the individuals:							Check daniel-munson: Instructor: LCL for the individuals should be 0. The value you got was probably -8.3, but you cannot have a negative wait time, so you just go with zero as your lower control limit.
Is there adequate discrimination? Explain your answer: Which chart did you use and exactly how many strata are there on that chart? How many should there be?
							Help Lois Jordan: Instructor: Adequate discrimination just means that we are measuring fine enough to detect variation if it exists. We want to see room for at least 6 possible points between the control limits of the range chart. Look at your raw data. You are measuring in increments of 1 hour. We may not have data points at each 1 hour interval, but we are measuring fine enough to detect variation at intervals of 1 hour. Do you have room for more than 6 possible points between the control limits of this Range chart?	Check daniel-munson: Instructor: Did you specify which chart you used to assess discrimination? Did you say exactly how many strata there are on that chart? If not, be sure to do this now!
			Diane Johnson: Instructor: 1. Click on chart icon in top menu, OR click on INSERT in menu bar and select CHART. 2. Select LINE chart. 3. Follow menu-driven steps and highlight the data. 4. When the line chart is complete, add the mean, the average moving range, and your control limits with the drawing tools. TOOLBARS - Drawing 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.	Diane Johnson: Instructor: Steps for drafting a control chart in Excel 2007 1. Highlight the data. 2. Click on INSERT tab at top of bar. 3. Find the CHARTS category 4. Click on LINE 5. Add control limits and the mean and average moving range with the drawing tools. (INSERT - SHAPES) 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.	Diane Johnson: Instructor: The formulas for the control limits of the XmR chart are found in your textbook. Do you understand why we are using a XmR chart in this assignment versus an X-barR chart? ANSWER - We only have individual data points. We do not have rational subgroups.	Is the process stable or unstable?
If the process is unstable, what process would you follow to determine what is causing the instability?
							HINT daniel-munson: You've been studying this answer through the entire course! All you need is information about the process - where do you get that when you're using control charts? REMEMBER NOT TO GUESS AT THE CAUSES OF THE PROBLEMS! WE'RE ASKING WHAT PROCESS YOU WOULD FOLLOW TO DETERMINE WHAT IS CAUSING THE INSTABILITY.
			daniel-munson: Instructor: How to determine whether or not a measurement system is discriminant is covered in two different recorded lectures. It is covered in the control chart lecture and it is also covered in the 'Measurement System Evaluation" lecture. This topic is also covered in the virtual class on SPC.	daniel-munson: Instructor: How to determine whether or not a process is stable is covered in the recorded lectures, the online textbook and the virtual class on SPC.	DO NOT SEND ANY EXTRA ATTACHMENTS UNLESS YOUR INSTRUCTOR SPECIFICALLY ASKS YOU TO!!
Please 'hand-in' your assignments throughout the course.
DO NOT SAVE THEM FOR THE END.
The deadline for having all project deliverables 100% correct
is 7 days prior to the end of the course.

Control (Pp Ppk)

Objective:

As mentioned in the last deliverable, one part of the CONTROL phase of DMAIC is to

to use the control chart to control and monitor the process behavior. We use a capability

study to determine how the process is performing in reference to the specifications, once we have

a stable and predictable process as determined by the control chart. Assume that you addressed

all of the assignable causes and the process is now stable.

Your team decided to use a Pp/Ppk study. Reflected in the data below is last

month's wait times under the new, stable process. You need to use this data in your Pp/Ppk study.

You also need to construct a histogram as a part of your capability study to ensure

that the process variation is still acting normal. By the way--finish this deliverable correctly and make all

required corrections to the other assignments and you are finished with the project.

Congratulations!!!!

Instructions for you:

1. Construct a histogram from the data below.

2. Is the processes random, or normally distributed?

Histograms in Excel 2003 or earlier
Diane Johnson: Instructor: Excel 2003 and earlier - 2 Options Option #1 (Quick and Dirty draft of a histogram) 1) Click TOOLS in the Excel toolbar 2) Click Data Analysis 3) Select HISTOGRAMS and then click OK 4) The cursor should be blinking in the "Input Range" box 5) Highlight your data 6) Click "New Worksheet Ply" 6) Skip the bin range option, and all other options 7) Check the "Chart Output" box 8) Click OK, and your histogram will appear. 9) You may drag your mouse over the corner of the histogram graph to enlarge it. 10) Do NOT delete the MORE category if there are data values in it. Option #2 (A more polished copy where you determine the bin ranges, rather than Excel) 1) Click TOOLS in the Excel toolbar 2) Click Data Analysis 3) Select HISTOGRAMS and then click OK 4) Determine the range of the data set, from your smallest to your largest data value 5) Add one value (in the unit you are measuring) for 'range with inclusion' (Example: .31 (range) + .01 = .32) 6) Determine the appropriate number of bars for your sample size (Example: 60 data points use 6-10 bars) 7) Calculate both the beginning and ending point of each cell and list the beginning and ending points of each bin in separate cells. For example, if you have a range of .32, you could have 8 cells with .04 data value per cell. 8) Now you have an idiosyncrasy of Excel. Excel will give you the option of designating the bin ranges. But, you should give Excel the ENDING value of each bin rather than the beginning value. Highlight the cells that include the ending value of each bin under the BIN RANGE option. 9) Highlight your complete data set under the INPUT RANGE option 10) Check on CHART OUTPUT and where you want the histogram chart located. (New worksheet or imbedded) 11) Click OK 12) You may drag your mouse over the corner of the histogram chart to enlarge it. 13) When Excel drafts histograms, the bars do not adjoin. Ideally, we want the bars of a histogram to adjoin because we are graphing continuous data. To make the bars adjoin, right click on top of bars and choose FORMAT DATA SERIES. In Excel 2003, you will have to select OPTIONS. Then set the GAP to Zero. 14) To change the color scheme of the histogram, right click somewhere outside of the bars in the graph and choose FORMAT PLOT AREA. You can choose many color formats. 15) When you plan the cell intervals, there are no data points in the MORE category, so you may delete it, because the MORE category is often confusing to people. DO NOT delete the MORE category if it has data points in it. 16) Only go through these more detailed steps if you are interested in a polished Histogram. Otherwise run a quick draft of the histogram with Option #1. 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.

3. Calculate Pp and Ppk. Note: The upper and lower specifications

are listed below the data. You will need these to do

Histograms in Excel 2007 or later
Diane Johnson: Instructor: Excel 2007 and later - 2 Options Option #1 (Quick and Dirty draft of a histogram) 1) Click DATA tab 2) Go to ANALYSIS category on the DATA tab 3) Click on DATA ANALYSIS (If you do not see DATA ANALYSIS, refer to the EXCEL EXAMPLES tab at the bottom of this spreadsheet for instructions in loading the DATA ANALYSIS features.) 4) Select HISTOGRAMS and then click OK 5) The cursor should be blinking in the "Input Range" box 6) Highlight your data 7) Click in "New Worksheet Ply" 8) Skip the bin range option, and other options 9) Check the "Chart Output" box 10) Click OK 11) You may drag your mouse over the corner of the graph to enlarge it. Option #2 (A More polished copy where you determine the bin ranges) 1) Click DATA tab 2) Go to ANALYSIS category on the DATA tab 3) Click on DATA ANALYSIS (If you do not see DATA ANALYSIS, refer to the EXCEL EXAMPLES tab at the bottom of this spreadsheet for instructions in loading the DATA ANALYSIS features.) 4) Select HISTOGRAMS and then click OK 5) Determine the range of the data set, from your smallest to your largest data value 6) Add one value (in the unit you are measuring) for 'range with inclusion' (Example: .31 (range) + .01 = .32) 7) Determine the appropriate number of bars for your sample size (Example: 60 data points use 6-10 bars) 8) Calculate both the beginning and ending point of each cell and list the beginning and ending points of each bin in separate cells. For example, if you have a range of .32, you could have 8 cells with .04 data value per cell. 9) Now you have an idiosyncrasy of Excel. Excel will give you the option of designating the bin ranges. But, you should give Excel the ENDING value of each bin rather than the beginning value. Highlight the cells that include the ending value of each bin under the BIN RANGE option. 10) Highlight your complete data set under the INPUT RANGE option 11) Check on CHART OUTPUT and where you want the histogram chart located. (New worksheet or imbedded) 12) Click OK 13) You may drag your mouse over the corner of the histogram chart to enlarge it. 14) When Excel drafts histograms, the bars do not adjoin. Ideally, we want the bars of a histogram to adjoin because we are graphing continuous data. To make the bars adjoin, right click on top of bars and choose FORMAT DATA SERIES. In Excel 2007, you go directly to setting GAP WIDTH to zero. 15) To change the color scheme of the histogram, right click somewhere outside of the bars in the graph and choose FORMAT PLOT AREA. You can choose many color formats. 16) When you plan the cell intervals, there are no data points in the MORE category, so you may delete it, because the MORE category is often confusing to people. DO NOT delete the MORE category if it has data points in it. 17) Only go through these more detailed steps if you are interested in a polished Histogram. Otherwise run a quick draft of the histogram with Option #1. 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.

the calculations.

4. Are the processes acceptable?

More on Bin Ranges
daniel-munson: Instructor: If you want your histogram to have the appropriate number of bin ranges (also known as cell intervals), you will need to enter some bin values in the dialogue box in DATA ANALYSIS for making Histograms. To determne bin ranges, you first need to find the inclusive range of the data set. This is done by subtracting the smallest value from the largest value, and adding 1 unit of data. In this case, you would add one minute to the result of largest-smallest to find the inclusive range. The recommended number of cell choices for n = 30 is 5 to 7. We use 5 for this deliverable. That means I will have 5 cells to define by their ending values. To find the bin size, divide the inclusive range by the number of bins. Find the ending value of the bin by adding the size of the bin to the previous value. The first bin will be found by adding the bin size to the minimum value of the data set. This gives you the ending value for the first bin. The ending value for the second bin is found by adding the bin size to the previous bin ending value. This continues until you reach the maximum of your data set, and have defined all of your bins. Enter the 'maximum' value (or ending value) from each cell interval into a separate place in this worksheet - location is not important. Then you will be able to drag the cursor across them to satisfy the bin range box in DATA ANALYSIS. IF YOU ARE USING EXCEL, YOU MAY ALSO LET EXCEL CHOOSE THE BIN RANGES FOR YOU!! 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.

"I'm lost"
daniel-munson: Instructor: The data are in the column below. At the bottom of the column there is a specification limit and a target. Since this is a lower-is-better target, there is no lower specification limit. You need 4 values to calculate Pp and Ppk. You need to know: Mean s (standard deviation) Upper Spec. limit Lower Spec. limit (N/A for this example) You will have to calculate the MEAN and standard deviation for the data Once you have done that, the only caution is to ensure you are using the correct formula for Ppk. Always select the smallest Ppk value from the 2 Ppk formulas because the smaller number represents the closest point of trouble for your process. In this case there is only point of trouble for your process (going over the upper specification limit). Therefore, you only have one choice for your Ppk formula.

Pp, Ppk formulas
Diane Johnson: Instructor: Refer to your lectures and course materials to find the appropriate formulas

Use Excel 2003 or earlier to find mean and Standard Deviation
Diane Johnson: Instructor: Want to use Excel 2003 to find the mean and standard deviation? 1. Click Tools in the top bar 2. Click Data Analysis (If you don't see Data Analysis as an option on the TOOLS menu, go to the 'EXCEL EXAMPLES' tab at the bottom of this spreadsheet for instructions to load the Data Analysis Tookpak.) 3. Click Descriptive statistics 4. OK 5. Drag with mouse to capture the 'resolution times' data 6. New Workbook Ply 7. Summary statistics 8. OK (You probably will need to resize your column widths to fully see the chart) You may also reset the number of decimal values that are visible, by clicking FORMAT - CELLS - NUMBER and setting the decimal place. Note: You can also do this on a simple scientific calculator. 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.

Use Excel 2007 or later to find mean and Standard Deviation
Diane Johnson: Instructor: Want to use Excel 2007 to find the mean and standard deviation? 1. Click the DATA tab in the top bar 2. In the ANALYSIS category, click on Data Analysis (If you don't see Data Analysis as an option go to the 'EXCEL EXAMPLES' tab at the bottom of this spreadsheet for instructions to load the Data Analysis Tookpak.) 3. Click Descriptive statistics 4. OK 5. Drag with mouse to capture the 'resolution times' data 6. New Workbook Ply 7. Check Summary statistics 8. OK (You probably will need to resize your column widths to fully see the chart) You may also reset the number of decimal values that are visible, by going to the NUMBER category on the HOME page. Click on the drop down menu and select MORE NUMBER FORMATS. Select number and specify the number of decimal points that you want to view in your spreadsheet. Remember Excel stores the entire value in the cell. You are just designation how many decimal places are displayed in the cell. Note: You can also do this on a simple scientific calculator. 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.

Data:

Average wait times (contiguous minutes)

Upper Specification Limit

Minutes

Lower Specification Limit

None

Minutes

TARGET ASSIGNMENT DATE - Submit in Week 15 or earlier

YOU DO NOT NEED TO INCLUDE YOUR HISTOGRAM IN THIS ASSIGNMENT

Project:

Healthcare

Deliverable:

Pp Ppk

Student last name:

Your last name here

Pp (round final result to 6 decimal places):

HINT!
daniel-munson: Instructor: Pp: Can it be calculated without a LSL?

Ppk (round final result to 6 decimal places):

Check
daniel-munson: Instructor: Ppk: Some value between 2 and 3.

Is the process reasonably normally distributed?

Is the process acceptable? What is the basis for your decision?

HINT!
Lois Jordan: HINT: Be sure to use the criteria for evaluating Ppk that most companies use today.

Diane Johnson: Instructor: Excel 2003 and earlier - 2 Options Option #1 (Quick and Dirty draft of a histogram) 1) Click TOOLS in the Excel toolbar 2) Click Data Analysis 3) Select HISTOGRAMS and then click OK 4) The cursor should be blinking in the "Input Range" box 5) Highlight your data 6) Click "New Worksheet Ply" 6) Skip the bin range option, and all other options 7) Check the "Chart Output" box 8) Click OK, and your histogram will appear. 9) You may drag your mouse over the corner of the histogram graph to enlarge it. 10) Do NOT delete the MORE category if there are data values in it. Option #2 (A more polished copy where you determine the bin ranges, rather than Excel) 1) Click TOOLS in the Excel toolbar 2) Click Data Analysis 3) Select HISTOGRAMS and then click OK 4) Determine the range of the data set, from your smallest to your largest data value 5) Add one value (in the unit you are measuring) for 'range with inclusion' (Example: .31 (range) + .01 = .32) 6) Determine the appropriate number of bars for your sample size (Example: 60 data points use 6-10 bars) 7) Calculate both the beginning and ending point of each cell and list the beginning and ending points of each bin in separate cells. For example, if you have a range of .32, you could have 8 cells with .04 data value per cell. 8) Now you have an idiosyncrasy of Excel. Excel will give you the option of designating the bin ranges. But, you should give Excel the ENDING value of each bin rather than the beginning value. Highlight the cells that include the ending value of each bin under the BIN RANGE option. 9) Highlight your complete data set under the INPUT RANGE option 10) Check on CHART OUTPUT and where you want the histogram chart located. (New worksheet or imbedded) 11) Click OK 12) You may drag your mouse over the corner of the histogram chart to enlarge it. 13) When Excel drafts histograms, the bars do not adjoin. Ideally, we want the bars of a histogram to adjoin because we are graphing continuous data. To make the bars adjoin, right click on top of bars and choose FORMAT DATA SERIES. In Excel 2003, you will have to select OPTIONS. Then set the GAP to Zero. 14) To change the color scheme of the histogram, right click somewhere outside of the bars in the graph and choose FORMAT PLOT AREA. You can choose many color formats. 15) When you plan the cell intervals, there are no data points in the MORE category, so you may delete it, because the MORE category is often confusing to people. DO NOT delete the MORE category if it has data points in it. 16) Only go through these more detailed steps if you are interested in a polished Histogram. Otherwise run a quick draft of the histogram with Option #1. 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.

daniel-munson: Instructor: The data are in the column below. At the bottom of the column there is a specification limit and a target. Since this is a lower-is-better target, there is no lower specification limit. You need 4 values to calculate Pp and Ppk. You need to know: Mean s (standard deviation) Upper Spec. limit Lower Spec. limit (N/A for this example) You will have to calculate the MEAN and standard deviation for the data Once you have done that, the only caution is to ensure you are using the correct formula for Ppk. Always select the smallest Ppk value from the 2 Ppk formulas because the smaller number represents the closest point of trouble for your process. In this case there is only point of trouble for your process (going over the upper specification limit). Therefore, you only have one choice for your Ppk formula.

Diane Johnson: Instructor: Excel 2007 and later - 2 Options Option #1 (Quick and Dirty draft of a histogram) 1) Click DATA tab 2) Go to ANALYSIS category on the DATA tab 3) Click on DATA ANALYSIS (If you do not see DATA ANALYSIS, refer to the EXCEL EXAMPLES tab at the bottom of this spreadsheet for instructions in loading the DATA ANALYSIS features.) 4) Select HISTOGRAMS and then click OK 5) The cursor should be blinking in the "Input Range" box 6) Highlight your data 7) Click in "New Worksheet Ply" 8) Skip the bin range option, and other options 9) Check the "Chart Output" box 10) Click OK 11) You may drag your mouse over the corner of the graph to enlarge it. Option #2 (A More polished copy where you determine the bin ranges) 1) Click DATA tab 2) Go to ANALYSIS category on the DATA tab 3) Click on DATA ANALYSIS (If you do not see DATA ANALYSIS, refer to the EXCEL EXAMPLES tab at the bottom of this spreadsheet for instructions in loading the DATA ANALYSIS features.) 4) Select HISTOGRAMS and then click OK 5) Determine the range of the data set, from your smallest to your largest data value 6) Add one value (in the unit you are measuring) for 'range with inclusion' (Example: .31 (range) + .01 = .32) 7) Determine the appropriate number of bars for your sample size (Example: 60 data points use 6-10 bars) 8) Calculate both the beginning and ending point of each cell and list the beginning and ending points of each bin in separate cells. For example, if you have a range of .32, you could have 8 cells with .04 data value per cell. 9) Now you have an idiosyncrasy of Excel. Excel will give you the option of designating the bin ranges. But, you should give Excel the ENDING value of each bin rather than the beginning value. Highlight the cells that include the ending value of each bin under the BIN RANGE option. 10) Highlight your complete data set under the INPUT RANGE option 11) Check on CHART OUTPUT and where you want the histogram chart located. (New worksheet or imbedded) 12) Click OK 13) You may drag your mouse over the corner of the histogram chart to enlarge it. 14) When Excel drafts histograms, the bars do not adjoin. Ideally, we want the bars of a histogram to adjoin because we are graphing continuous data. To make the bars adjoin, right click on top of bars and choose FORMAT DATA SERIES. In Excel 2007, you go directly to setting GAP WIDTH to zero. 15) To change the color scheme of the histogram, right click somewhere outside of the bars in the graph and choose FORMAT PLOT AREA. You can choose many color formats. 16) When you plan the cell intervals, there are no data points in the MORE category, so you may delete it, because the MORE category is often confusing to people. DO NOT delete the MORE category if it has data points in it. 17) Only go through these more detailed steps if you are interested in a polished Histogram. Otherwise run a quick draft of the histogram with Option #1. 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.

daniel-munson: Instructor: If you want your histogram to have the appropriate number of bin ranges (also known as cell intervals), you will need to enter some bin values in the dialogue box in DATA ANALYSIS for making Histograms. To determne bin ranges, you first need to find the inclusive range of the data set. This is done by subtracting the smallest value from the largest value, and adding 1 unit of data. In this case, you would add one minute to the result of largest-smallest to find the inclusive range. The recommended number of cell choices for n = 30 is 5 to 7. We use 5 for this deliverable. That means I will have 5 cells to define by their ending values. To find the bin size, divide the inclusive range by the number of bins. Find the ending value of the bin by adding the size of the bin to the previous value. The first bin will be found by adding the bin size to the minimum value of the data set. This gives you the ending value for the first bin. The ending value for the second bin is found by adding the bin size to the previous bin ending value. This continues until you reach the maximum of your data set, and have defined all of your bins. Enter the 'maximum' value (or ending value) from each cell interval into a separate place in this worksheet - location is not important. Then you will be able to drag the cursor across them to satisfy the bin range box in DATA ANALYSIS. IF YOU ARE USING EXCEL, YOU MAY ALSO LET EXCEL CHOOSE THE BIN RANGES FOR YOU!! 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.

Diane Johnson: Instructor: Want to use Excel 2003 to find the mean and standard deviation? 1. Click Tools in the top bar 2. Click Data Analysis (If you don't see Data Analysis as an option on the TOOLS menu, go to the 'EXCEL EXAMPLES' tab at the bottom of this spreadsheet for instructions to load the Data Analysis Tookpak.) 3. Click Descriptive statistics 4. OK 5. Drag with mouse to capture the 'resolution times' data 6. New Workbook Ply 7. Summary statistics 8. OK (You probably will need to resize your column widths to fully see the chart) You may also reset the number of decimal values that are visible, by clicking FORMAT - CELLS - NUMBER and setting the decimal place. Note: You can also do this on a simple scientific calculator. 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.

Diane Johnson: Instructor: Refer to your lectures and course materials to find the appropriate formulas

Diane Johnson: Instructor: Want to use Excel 2007 to find the mean and standard deviation? 1. Click the DATA tab in the top bar 2. In the ANALYSIS category, click on Data Analysis (If you don't see Data Analysis as an option go to the 'EXCEL EXAMPLES' tab at the bottom of this spreadsheet for instructions to load the Data Analysis Tookpak.) 3. Click Descriptive statistics 4. OK 5. Drag with mouse to capture the 'resolution times' data 6. New Workbook Ply 7. Check Summary statistics 8. OK (You probably will need to resize your column widths to fully see the chart) You may also reset the number of decimal values that are visible, by going to the NUMBER category on the HOME page. Click on the drop down menu and select MORE NUMBER FORMATS. Select number and specify the number of decimal points that you want to view in your spreadsheet. Remember Excel stores the entire value in the cell. You are just designation how many decimal places are displayed in the cell. Note: You can also do this on a simple scientific calculator. 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.

DO NOT SEND ANY EXTRA ATTACHMENTS UNLESS YOUR INSTRUCTOR SPECIFICALLY ASKS YOU TO!!

Please 'hand-in' your assignments throughout the course.

DO NOT SAVE THEM FOR THE END.

The deadline for having all project deliverables 100% correct

is 7 days prior to the end of the course.

Example of

a deliverable