Welcome! Please read this page (in particular) very carefully. Instructions You need to understand how to send your assignments (deliverables) Instructor: to your instructor. The tabs (bottom of each sheet) in this cells contain some hints, tips, and self-checks. These document contain all of the deliverables expected of you. If you print any of the If you need help along the way, look for these special cellstipwould like a Edit Comment. tips, right click the cell containing that have tored indicator the and select in the corner. It looks like the "Read me" box to the right: Read me Highlight the text, copy and paste in Simply slide your cursor over the red-cornered cell and you will get more information. a any text document like WORD for printing. 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 Excel Novice - Please read Instructor: Excel 2007 Villanova instructor: EXCEL 2003 or earlier Excel 2007 Instructor: Click on the TOOLS tab in the top bar of Excel. If your computer alreadyOffice Excel add-in program that is available when The Analysis Toolpak is a Microsoft has the \"Data It is beyond the scope of this Black Belt course to teach Analysisorhow have already been added. Analysis\" option listed, you are ready to install Microsoft Office tools to To use it all Excel, however, you need to load it you go. Your Data students Excel. perform in of the functions of Excel. first. The Microsoft web site has excellent FREE find somefor the more advancedExcel 2007. Under the Data Analysis Function you will tutorials of both Excel 2003 or functions that we will be discussing such as ANOVAs, and t tests.Click the Microsoft Office Button , and then click Excel Options at the bottom. 1. Excel 2007 http://office.microsoft.com/en-us/training/CR100479681033.aspx the Manage box, to the Help Add-ins. If you do not see Data Analysis listed under the TOOLS tab, I encourage you to go select Excel 2. Click Add-Ins, and then in function in Excel for specific instructions on how to load \"the Data Analysis Toolpak\" in your Excel 2003 version of Excel. 3. Click Go. http://office.microsoft.com/en-us/training/CR061831141033.aspx Here are the easy standard instructions for Excel 2003 if you need to load the Add-Ins. Toolpak check box, and then click 4. In the Add-Ins available box, select the Analysis Or, ask a business colleague, who is proficient in Excel to show you how to do some of the basic things such as OK. copying, cutting, pasting, copying from sheet to sheet, etc. 1) On the Tools menu, click Add-Ins. If you get prompted that the Analysis Toolpak is not currently installed on your Please do not expect your box, Beltcomputer, click next to Analysis to use and software. That is not the class instructor to to install how 2) In the Add-Ins available Blackselect the check boxYes teach youit. Toolpak,Excelthen click OK. objective of this course. This course will highlight specific Excel commands that are unique to Six Sigma, but it is not the objective of Analysis Toolpak, After youExcel the Analysis ToolPak, the Data Analysis resources listed teach DATA load commands. Please use the suggested command is available in the 3) When you load the this course to 5. thebasic ANALYSIS command is automatically added to the above. TOOLS menu. ANALYSIS group on the DATA tab. If your version if slightly different than the above, refer to your HELP EXAMPLES for an illustration. See the next spreadsheet EXCEL function for details on loading Tookpak. Remember that the Data Analysis function is not required for the course. See the next spreadsheet at the bottom of this worksheet entitled EXCEL EXAMPLES for an illustration. Remember that the Data Analysis function is NOT necessary for the course, but it is helpful. Click on the TOOLS tab in the top bar of Excel. If your computer alreadyOffice Excel add-in program that is available when The Analysis Toolpak is a Microsoft has the \"Data It is beyond the scope of this Black Belt course to teach Analysisorhow have already been added. Analysis\" option listed, you are ready to install Microsoft Office tools to To use it all Excel, however, you need to load it you go. Your Data students Excel. perform in of the functions of Excel. first. The Microsoft web site has excellent FREE find somefor the more advancedExcel 2007. Under the Data Analysis Function you will tutorials of both Excel 2003 or functions that we will be discussing Discussion Board!! Remember that the entire project must be then correct 1. Follow the submission scheduled posted on the2007 such as ANOVAs, and t tests.Click the Microsoft Office Button , and100% click Excel Options at the bottom. Main Excel and complete by no later than midnight EST you do not(7 days) BEFORE the lastClick the TOOLS tab, I encourage you to go to the Help Add-ins. http://office.microsoft.com/en-us/training/CR100479681033.aspx the Manage box, select Excel If one week see Data Analysis listed under of class.and then in 2. day Add-Ins, function in Excel for specific instructions on how to load \"the Data Analysis Toolpak\" in your Excel 2003 version of Excel. 3. Click Go. Deliverables include: http://office.microsoft.com/en-us/training/CR061831141033.aspx Project charter (target week 3 or sooner) Here are the easy standard instructions for Excel 2003 if you need to load the Add-Ins. Toolpak check box, and then click 4. In the Add-Ins available box, select the Analysis Or, ask a business colleague, who is proficient in Excel to show you how to do some of the basic things such as OK. Baseline sigma (week 4 or sooner) copying, cutting, pasting, copying from sheet to sheet, etc. 1) On the Tools menu, click Add-Ins. SIPOC (week 5 or sooner) If you get prompted that the Analysis Toolpak is not currently installed on your Please do not expect your box, Beltcomputer, click next to Analysis to use and software. That is not the class instructor to to install how Pareto chart (week 6 or sooner) 2) In the Add-Ins available Blackselect the check boxYes teach youit. Toolpak,Excelthen click OK. objective of this course. This course will highlight specific Excel commands that are unique to Six Sigma, but it Expected Variation (week 7 or sooner) is not the objective of Analysis Toolpak, After youExcel the Analysis ToolPak, the Data Analysis resources listed teach DATA load commands. Please use the suggested command is available in the 3) When you load the this course to 5. thebasic ANALYSIS command is automatically added to the Stem & leaf (week 8 or sooner) above. TOOLS menu. ANALYSIS group on the DATA tab. Project Timeline Scatter diagram (week 10 or sooner) DOE (week 11 or sooner) Control chart (XmR) ( week 12 or sooner) Pp Ppk (week 13 or sooner) If your version if slightly different than the above, refer to your HELP EXAMPLES for an illustration. See the next spreadsheet EXCEL function for details on loading Tookpak. Remember that the Data Analysis function is not required for the course. See the next spreadsheet at the bottom of this worksheet entitled EXCEL EXAMPLES for an illustration. Remember that the Data Analysis function is NOT necessary for the course, but it is helpful. 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: Deliverable: Student last name: Healthcare Project Improve phase - gen. sol. 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? What is the average of subgroup 2? What is the average of subgroup 3? What is the average of subgroup 4? What is the average of subgroup 5? What is the average of subgroup 6? What is the average of subgroup 7? What is the average of subgroup 8? 22 34 23 22 25 23 29 27 Read this! To send each assignment to your instructor: Click-and-hold the LEFT mouse button at the TOP-LEFT corner of the peach-colored box, then while holding down on that button, drag to the LOWER-RIGHT corner of the box. This will highlight the entire peach-colored box. Release the mouse button. Do a CTRL-C. This will copy what has been highlighted. Go to your Villanova website and follow this sequence: 1. COURSE MENU 2. CLASS ROSTER 3. Click on your instructor's email envelope icon 4. Type "Check My Work - ASSIGNMENT NAME - PROJECT NAME" in the SUBJECT box. 5. Click once inside of the message box 6. Do a CTRL-V. This will paste your deliverable into this box. Don't be concerned if after you paste it, the appearance of the text is out of alignment. It will straighten out after you hit SEND. 7. SEND Check your SENT ITEMS folder afterward to see how it straightened out. VERY IMPORTANT!! Be sure to submit your assignments for grading as you complete each one and following the submission scheduled posted on the message board - 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! 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 Instructor: follow the instructions in the Green tab. Excel 2003 instructions or earlier EXCEL 2003 Excel 2003 or earlier versions of Excel example Click on the TOOLS tab in the top bar of Excel. If your computer already has the \"Data Analysis\" option listed, you are ready to go. Your Data Analysis tools have already been added. Under the Data Analysis Function you will find some of the more advanced functions that we will be discussing such as ANOVAs, and t tests. If you do not see Data Analysis listed under the TOOLS tab, I encourage you to go to the Help function in Excel for specific instructions on how to load \"the Data Analysis Toolpak\" in your version of Excel. Here are the easy standard instructions for Excel 2003 if you need to load the Add-Ins. 1) On the Tools menu, click Add-Ins. 2) In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK. 3) When you load the Analysis Toolpak, the DATA ANALYSIS command is automatically added to the TOOLS menu. If your version if slightly different than the above, refer to your HELP function for details on loading Tookpak. Remember that the Data Analysis function is NOT necessary for the course, but it is helpful. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing. Villanova instructor: Excel 2007 The Analysis ToolPak is a Microsoft Office Excel add-in program that is available when you install Microsoft Office or Excel. To use it in Excel, however, you need to load it first. 1. Click the Microsoft Office Button , and then click Excel Options at the bottom. 2. Click Add-Ins, and then in the Manage box, select Excel Add-ins. 3. Click Go. 4. In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK. If you get prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it. 5. After you load the Analysis ToolPak, the Data Analysis command is available in the ANALYSIS group on the DATA tab. Remember that the Data Analysis function is not required for the course. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing. Excel 2007 The Analysis ToolPak is a Microsoft Office Excel add-in program that is available when you install This spreadsheet tab provides illustrations of adding the DATA ANALYSIS capability to your 2007 Excel software. Microsoft Office or Excel. To use it in Excel, however, you need to load it first. If you see Data Analysis already listed under the DATA tab, you are set. If you do not see Data Analysis 1. Click the Microsoft Office Button , and then click Excel Options at the bottom. follow the instructions in the Green tab. Excel 2007 instructions 2. Click Add-Ins, and then in the Manage box, select Excel Add-ins. 3. Click Go. Excel 2007 example 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. 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 Objective: A problem statement needs to be developed. There needs to be a business case so that management will buy-in to having the team working on the project. The scope of the project also needs to be decided upon. This is important to ensure a likely successful completion. If the scope is too broad, a success may not be realized for years, or may not happen at all. Instructions for you: Create a project charter based "Create a charter?" upon the information in the introduction. (see "The project" tab) In reality, you would fill in a charter with team members' names, stake holders, etc. We are not interested in those details for this simulation, but we do want to see what you come up with for four (4) items: Problem statement, business case, goal and project scope. How to submit an Assignment TARGET ASSIGNMENT DATE - Submit in Week 3 or earlier Instructor: Information about project charters can be found in your online textbook reading assignments. Project: Deliverable: Student last name: Healthcare 2015 Project charter Your LAST name here What is the business case? (Use no more than 2 sentences.) "Business Case?" Type your business case here. Use no more than 2 sentences. What is the problem statement? (Use no more than 2 sentences.) "Problem Statement" Type your problem statement here. Use no more than 2 sentences. What is the goal statement? (Use one sentence.) Type your goal statement here. Use no more than 2 sentences. "Goal Statement" What is your project scope? (This is not your goal statement! Use one sentence.) What is the project scope? Be sure to express the scope in terms of the process! "The project scope?" 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 Instructor: Instructor: lot of thought. As your to a of the organization. Please limit this sponsor, This needs a Acting as or two.see 'scope creep.' I "What's sentence your real-world sponsor, I do not want to MAKE IS CLEAR AND would creep?"...please read on... CONCISE! to be sold on why we need to scope need do this project. I wouldn't have time to read scope defines theI boundaries a the The long explanations. would need of short, to-the-point some beginning point and project--usually compelling reason why we need to do this. In the problem ending point. statement, we 'sell' the need for the project with specific and measureable For example, if I were leading data. MAKE improve the delivery of course a project to IT CLEAR AND CONCISE! Instructor: students, I would have the materials to What is your target improvement for this project, following boundaries: including a target date? The process (IN THIS PROJECT) starts: George Eckes mentions a 50% improvement as a When the customers says, "Yes, I am possible target for Six Sigma projects. Is a 50% interested in enrolling." improvement enough in this case? The process (IN THIS PROJECT) ends: Remember to link your goal to the problem When the customer receives the box from statement and to keep your goal statement UPS that contains their handbook and CD's. "SMART"! stays within that confinement. The project 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. Read this! To send each assignment to your instructor: Click-and-hold the LEFT mouse button at the TOP-LEFT corner of the peach-colored box, then while holding down on that button, drag to the LOWER-RIGHT corner of the box. This will highlight the entire peach-colored box. Release the mouse button. Do a CTRL-C. This will copy what has been highlighted. Go to your Villanova website and follow this sequence: 1. COURSE MENU 2. CLASS ROSTER 3. Click on your instructor's email envelope icon 4. Type "Check My Work - Charter" in the SUBJECT box. 5. Click once inside of the message box 6. Do a CTRL-V. This will paste your deliverable into this box. Don't be concerned if after you paste it, the appearance of the text is out of alignment. It will straighten out after you hit SEND. 7. SEND Check your SENT ITEMS folder afterward to see how it straightened out. Please 'hand-in' your assignments throughout the course. DO NOT SAVE THEM FOR THE END. The deadline for having all project deliverables 100% correct is 7 days prior to the end of the course. Assumption and constraints regarding your team or budget may also be included in your scope statement. Objective: We want you to determine the baseline sigma level 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 and determine the baseline sigma level with the Motorola shift. How to submit an Assignment TARGET ASSIGNMENT DATE - Submit in Week 5 or earlier Project: Healthcare 2015 Deliverable: Baseline Sigma Student last name: Your last name here What is the baseline sigma? (Approximately) Check Your answer should be between 2 and 4.5 Read this! To send each assignment to your instructor: Click-and-hold the LEFT mouse button at the TOP-LEFT corner of the peach-colored box, then while holding down on that button, drag to the LOWER-RIGHT corner of the box. This will highlight the entire peach-colored box. Release the mouse button. Do a CTRL-C. This will copy what has been highlighted. Go to your Villanova website and follow this sequence: 1. COURSE MENU 2. CLASS ROSTER 3. Click on your instructor's email envelope icon 4. Type "Check My Work - Baseline Sigma" in the SUBJECT box. 5. Click once inside of the message box 6. Do a CTRL-V. This will paste your deliverable into this box. Don't be concerned if after you paste it, the appearance of the text is out of alignment. It will straighten out after you hit SEND. 7. SEND Check your SENT ITEMS folder afterward to see how it straightened out. Please 'hand-in' your assignments throughout the course. DO NOT SAVE THEM FOR THE END. The deadline for having all project deliverables 100% correct is 7 days prior to the end of the course. 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. How to submit an Assignment TARGET ASSIGNMENT DATE - Submit in Week 4 or earlier Project: Deliverable: Student last name: Healthcare 2015 SIPOC Your LAST name here Suppliers: (At least 3) Type suppliers here Type suppliers here Type suppliers here Type suppliers here Type suppliers here Inputs: (At least 3) Type inputs here Type inputs here Type inputs here Type inputs here Type inputs here Process Step 1: Process Step 2: Process Step 3: Process Step 4: Process Step 5: Process Step 6: Process Step 7: Process Step 8: Step 1 Step 2 Step 3 Step 4 Step 5 Step 6 (if necessary) Step 7 (if necessary) Step 8 (if necessary) Outputs: (At least 3) Type outputs here Type outputs here Type outputs here Type outputs here Type outputs here Customers: (At least 3) Type customers here Type customers here Type customers here Type customers here Type customers here Read this! To send each assignment to your instructor: Click-and-hold the LEFT mouse button at the TOP-LEFT corner of the peach-colored box, then while holding down on that button, drag to the LOWER-RIGHT corner of the box. This will highlight the entire peach-colored box. Release the mouse button. Do a CTRL-C. This will copy what has been highlighted. Go to your Villanova website and follow this sequence: 1. COURSE MENU 2. CLASS ROSTER 3. Click on your instructor's email envelope icon 4. Type "Check My Work - SIPOC" in the SUBJECT box. 5. Click once inside of the message box 6. Do a CTRL-V. This will paste your deliverable into this box. Don't be concerned if after you paste it, the appearance of the text is out of alignment. It will straighten out after you hit SEND. 7. SEND Check your SENT ITEMS folder afterward to see how it straightened out. Please 'hand-in' your assignments throughout the course. DO NOT SAVE THEM FOR THE END. The deadline for having all project deliverables 100% correct is 7 days prior to the end of the course. Objective: As stated in the problem, patients are leaving the emergency department before being seen by a doctor for a variety of reasons. The American Medical Association released findings from a study (blue font far below) that addressed the following question of people who left ER's without treatment: "Why did you leave the ER without waiting for treatment?" You need to determine the "biggest contributor" to the problem of people leaving without treatment. One tool to accomplish this is the Pareto Chart Instructions for you: Using the data in blue below construct a Pareto chart (see your online textbook reading assignment 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) Just to show you how this works, let's say the responses were as follows: Let's say that data reveals the following counts for beach-related injuries for the month of July. Injury categories # of incidents Cuts from broken glass 12 This green area is just for practice. The project is below in blue. Surf boarding injuries 37 Fishing related injuries 41 Jelly fish stings 45 This is a hypothetical example Skim boarding accidents 29 You will be doing this using different Hit with flying toy 79 data for your project, but this is merely Sprained ankle 43 showing you how to do it. 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. Instructor: It would look like this after sorting. Then, you would create a cumulative percentage column. Click once on any of the Step 1. Click in the adjacent blank cell Instructor: and enter an equal (=) sign. cumulative percentages to see the formula. Step 2. Click on the cell with the 79. Injury categories # of incidents Cumulative % Step 1. Click in the adjacent blank cell and enter an equal (/). sign. Step 3. Type in a division sign (=) Step 20? Step 20% in in Hit with flying toy 79 20% How did you get2. Click on the cell with the 4. Click it. the cell with the 394. Step 3. Type + and a parenthesis sign Step Burns from grills 52 33% How did you get4. Click on the cell with the 5. Click enter Step 33? 52. Jelly fish stings 45 45% Step 5. Type in a division sign (/). Step 6. Click in the cell with the 394. Sprained ankle 43 56% Step 7. Type a close parenthesis sign Fishing related injuries 41 66% Step 8. Click enter 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 leaving: Got tired of waiting Not necessary Too many people waiting Doctor treatment Staff treatment Environment Elsewhere Ignored me Too expensive Had to go / ran out of time Total choosing this response 6 4 4 3 2 2 1 1 1 1 Create a Pareto chart in Excel 2003 Create a Pareto chart in Excel 2007 Cumulative % Instructor: Instructor: Excel 2007 Instructions for creating a pareto chart in Excel 2003 1. Create a table that looks like the practice problem (above), but with the 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 project data (not the practice data). One column is the counts and the second column is the cumulative percentages. column is the cumulative percentages. 2. Highlight both columns of data without the labels 2. Highlight both columns Submit in Week 6 or the top tabof data without the labels 3. Click INSERT on earlier bar 3. Click INSERT on the top tab bar 4. Click Chart 4. Click Chart 5. Right click the columns that represent the percentages (smaller columns) 5. Click Custom types YOU DO NOT NEED TO SEND THE ACTUAL PARETO CHART.top Format tab,(one of the tabs) 6. On the click Format Selection. 6. Choose Line - Column on 2 axes (scroll down to find it) 7. Under Plot Series On, click Secondary Axis and then click Close. Project: Healthcare 2015 7. Finish to create the chart 8. On the Layout tab, in the Axes group, you may modify the Secondary Vertical Axis. Deliverable: Pareto Chart 8. 'Right click' on left Y axis 9. Right click on the percentage columns, Select Change Series Chart type to LINE 9. Click Student last name: Your LAST name'Righton Format Axis 10. hereclick' on left Y axis 10. Click Scale 11. Click on Format Axis 11. Change max to 25 and min to zero 12. Set Axis Options for MAX and Which ONE issue should your team focus on first, based uponmax to the total of allMIN to FIXED all columns and a min to zero what the Pareto Chart from 12. OK 13. Change counts 13. 'Right click' on right Y axis is telling you, and why? 14. OK 14. Click Scale 15. 'Right click' on right Y axis 15. Change max to 1 and min to zero 16. Set Axis Options for MAX and MIN to FIXED 16. OK 17. Change max to 1.0 (100%) and min to zero Now you have a pareto that looks like the example on page The Type17. OKresponse here. line begins at the top of the first column V-10.rises to 18. your cumulative percentage and 19. Now you have a pareto that looks like the example on page V-10 of your Handbook. The 100%. When your pareto follows this standard business format, it will enhance cumulative percentage line begins at the top of the first column and rises to 100%. When your communication and presentations to management. pareto 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 - How to submit an Assignment 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. Read this! To send each assignment to your instructor: Click-and-hold the LEFT mouse button at the TOP-LEFT corner of the peach-colored box, then while holding down on that button, drag to the LOWER-RIGHT corner of the box. This will highlight the entire peach-colored box. Release the mouse button. Do a CTRL-C. This will copy what has been highlighted. Go to your Villanova website and follow this sequence: 1. COURSE MENU 2. CLASS ROSTER 3. Click on your instructor's email envelope icon 4. Type "Check My Work - Pareto Analysis" in the SUBJECT box. 5. Click once inside of the message box 6. Do a CTRL-V. This will paste your deliverable into this box. Don't be concerned if after you paste it, the appearance of the text is out of alignment. It will straighten out after you hit SEND. 7. SEND Check your SENT ITEMS folder afterward to see how it straightened out. Please 'hand-in' your assignments throughout the course. DO NOT SAVE THEM FOR THE END. The deadline for having all project deliverables 100% correct is 7 days prior to the end of the course. Objective: Your boss wants to know the limits of expected variation. You know there is another shipment coming in next week and based upon this week's run, and assuming that the process is normally distributed, you can calculate/predict the limits of expected variation by calculating the mean and standard deviation for this data and then using the properties of the normal distribution to estimate the limits of the 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 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. 5. By looking at the histogram, is the process approximately normally distributed? Is there obvious assignable-cause variation? What is expected variation? We know that 68% of data from a normal process is expected to fall + or - 1 sigma from the mean. Instructor: 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. Instructor: Expected variation Use Excel 2003 to find mean and Standard Deviation We know that 95% to find the mean and standard deviation? Want to use Excel 2003 of data from a normal Use Excel 2007 to find mean and Standard Deviation Histograms in Excel 2003 Histograms in Excel 2007 More on Bin Ranges Instructor: is expected to fall + or - 2 sigma from process the mean. 1. Click Tools in the top bar Want to use Excel 2007 to find the mean and standard 2. Click Data Analysis (If you don't see Data Analysis as an deviation? optionWe knowTOOLS menu,datato the 'EXCEL EXAMPLES' tab at on the that 99.7% of go from a normal Instructor: process this spreadsheet or 3 sigma from the bottom ofis expected to fall + for- instructions to load the Data 1. Click mean. the the DATA Analysis Tookpak.) tab in the top bar 2. Excel 2007 - 2 Options Click Descriptive statistics click on Data Analysis 3. In the ANALYSIS category, Instructor: (If OKSo, the see Data Analysis as an option go 4. you don't expected variation that we would likely to the 'EXCEL EXAMPLES' the athistogram) Option #1 (Quick and Dirty draft ofdata willbottom of3this spreadsheet for see in tab a the capture 5. Drag with mouse to fall + or - sigma. Excel 2003 - 2 Options the 'wait times' data Data Analysis Tookpak.) instructions to load the 3. Click Descriptive statistics 1) Click DATA tab 6. New Workbook Ply Optionto ANALYSIS category on the DATA tab 4. OK 2) Go #1 (Quick and Dirty draft of a statistics 7. Summary histogram) 3) Click onInstructor:5. OK (Ifwith mouse see DATA ANALYSIS, refer to the EXCEL EXAMPLES tab at the bottom of DATA ANALYSIS (You probably to capture resize your 8. Drag you do not will need to 1) Click TOOLS in the Excel toolbarwidths to fully see the ANALYSIS features.) reset the the 'wait in loading this spreadsheet for instructions times' datathe DATA chart) You may also column If you want your histogram to OK 2) Click Data Analysis 6. and then clickhave the appropriate number of bin 4) Select HISTOGRAMS New Workbook Ply ranges (alsobe Check cell OK known as of decimal values need to 3) Select HISTOGRAMS 7. numberSummary statisticsthat box visible, by clicking then click intervals), you will 5) The cursor should and blinking in the "Input Range" are enter some bin ranges data OK (You probably will need 4) The cursor should be blinking inbox in DATA ANALYSIS for making Histograms. 8. 6) Highlight yourin the dialogue the "Input Range" box to resize your FORMAT 5) Highlight "Newdata column 7) Click in your Worksheet Ply"widths to fully see the chart) You may also reset CELLS - NUMBER and setting the decimal place. Let's range option, and other options 6) Click the bin say that the largest value you have is 10.77. The scientific calculator. the 8) Skip "New Worksheet Ply" You can also do this on a simple smallest is Note: 10.63. So, Output" boxof decimal values it on (as mentioned in the number other options range all 6) Skip thethe "Chart If you and is 0.14. Add +1 for 'inclusion'a calculator, refer to the 9) Check bin range option, want to learn how to dothat are visible, by going to the the on cell intervals) = 0.15.on the 7) Check the "Chart Output" box NUMBER category 10) Click OK lecturebonus lecture MATH PRIMER. HOME page. Click on the drop 8) Click OK, and your histogram will appear. corner of the graph to enlarge it. down 11) You may drag your mouse over the 9) You may The recommended numberto print MORE NUMBERgraphthe 7. I Select number drag your If you wouldand corner of the histogram35 is 5 to enlarge it. mouse over the select choices for n = FORMATS. pick select EDIT menu like of cell this tip, right click on cell and 5 10) Do NOTto make the math category if there are Idata have 5 the text, and paste in a delete the and MORE easier. That means will values cells with 0.03 in in it. COMMENT. Then just highlight and copy specify printing. Option #2 each. (A More polished copy where youof decimal points that you want to view document for the number determine the bin ranges) in So, tab data spreadsheet. Remember Option #2 (A morethe first your set the cell intervals are: bin ranges, rather than Excel) 1) Click DATA for polished copy where you determine the Excel stores the entire value 10.63-10.6, in 2) Go to ANALYSIS category on the DATA tab 1) Click TOOLS in the Excel toolbar You are just DATA ANALYSIS, refer to the EXCEL EXAMPLES tab at the bottom of 3) Click on10.66-10.68 the (If you do not see designation how many decimal places DATA ANALYSIS cell. 10.69-10.71 2) Click Data Analysis are this spreadsheet for instructions in loading the DATA ANALYSIS features.) 10.72-10.74 displayed in 3) Select HISTOGRAMS and then clickclick OK 4) Select HISTOGRAMS and then OKthe cell. 10.75-10.77 4) Determinethe range of of the data also from smallestsimple to your data value Note: data set, from your your a to your largest largest data 5) Determine the range the You can set, do this on smallest scientific calculator. value 5) Add one value (in the unit you you measuring) for 'range 'range with inclusion' to .14 (range) .14 (range) + .01 = .15) 6) Add one value (in If you want to learn how to do it witha calculator, refer (Example: the unit are are measuring) for on inclusion' (Example: the Enter the 'maximum' number of bars for your each cell interval + .01 = .15) bonus lecture MATH PRIMER. 7) Determine the appropriatevalue (or ending value) fromsample size (Example: < 50 data points use 5, 6 or 7 into a separate place in this bars for your sample not important. < 50 6) Determine the appropriate number ofworksheet - location issize (Example: (i.e., data points bars) 10.65, use 5, 6 or 7 bars) 10.68,you wouldand ending point tip, right click on the cell and If 10.71, 10.74, 10.77). 8) Calculate both the beginning like to print this of each cell and list the beginning and ending points of each 7) Calculate both cells. For EDIT COMMENT. have ajust highlight and copy the text, select example, if point of each cell and list the beginning and 5 cells bin in separate the beginning and endingyou Then range of .15, you could have ending with .03 data value per Then you will be able to a document for printing. to satisfy the bin points of each bin in separate cells. drag example, ifacross them range of .15, you could have 5 and paste in For the cursor you have a cell. range an idiosyncrasy of cells with .03 have value DATA ANALYSIS. Excel. Excel will give you the option of designating the bin ranges. 9) Now you databox in per cell. 8) Now you have an idiosyncrasy of Excel. Excel will give you the option of designating the bin But, you should give Excel the ENDING value of each bin rather than the beginning value. Highlight the cells Confused? Re-watch of lecture on histograms and cell intervals. ranges. that include the ending valuethe each bin under the BIN RANGE option. But, Highlight your Excel the ENDING value of each bin rather than the beginning value. 10) you should give complete data set under the INPUT RANGE option Highlight the you wouldOUTPUT and where you want binhighlight and chart located. (New worksheet or imbedded) 11) Check If cells that include print these instructions, just under the BIN RANGE option. on CHART like to the ending value of each the histogram copy, 9) Highlightthen paste into a document forthe INPUT RANGE option your complete data set under printing. 12) Click OK 10) Check on CHARTyour mouse over the corner of histogram chart located. (New worksheet or 13) You may drag OUTPUT and where you want the the histogram chart to enlarge it. imbedded) Excel drafts histograms, the bars do not adjoin. Ideally, we want the bars of a histogram to adjoin 14) When 11) Click OK are graphing continuous data. To make the bars adjoin, right click on top of bars and choose because we 12) You may drag your mouse over the corner of the histogram setting GAP WIDTH to zero. FORMAT DATA SERIES. In Excel 2007, you go directly to chart to enlarge it. 13) When Excel drafts histograms, the bars histogram, right click somewhere outside of the bars in the graph and 15) To change the color scheme of the 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 choose bars and chooseAREA. You can choose many 2003, you will have to select OPTIONS. Then set FORMAT PLOT FORMAT DATA SERIES. In Excel color formats. the GAP to Zero.plan the cell intervals, there are no data points in the MORE category, so you may delete it, 16) When you 14) To change MORE category is often confusing to click somewhere outside of the bars in the because the the color scheme of the histogram, right people. DO NOT delete the MORE category if it has data graph and it. points in choose FORMAT PLOTthrough these choosedetailed steps if you are interested in a polished Histogram. Otherwise run a 17) Only go AREA. You can more many color formats. 15) When you plan the cell intervals, there are#1. data points in the MORE category, so you may quick draft of the histogram with Option no delete it, because the MORE category is often confusing to people. DO NOT delete the MORE category if it has data print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy If you would like to points in it. 16) Only go through these more detailed steps if you are interested in a polished Histogram. the text, and paste in a document for printing. 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. So, tab data spreadsheet. Remember Option #2 (A morethe first your set the cell intervals are: bin ranges, rather than Excel) 1) Click DATA for polished copy where you determine the Excel stores the entire value 10.63-10.6, in 2) Go to ANALYSIS category on the DATA tab 1) Click TOOLS in the Excel toolbar You are just DATA ANALYSIS, refer to the EXCEL EXAMPLES tab at the bottom of 3) Click on10.66-10.68 the (If you do not see designation how many decimal places DATA ANALYSIS cell. 10.69-10.71 2) Click Data Analysis are this spreadsheet for instructions in loading the DATA ANALYSIS features.) 10.72-10.74 displayed in 3) Select HISTOGRAMS and then clickclick OK 4) Select HISTOGRAMS and then OKthe cell. 10.75-10.77 4) Determinethe range of of the data also from smallestsimple to your data value Note: data set, from your your a to your largest largest data 5) Determine the range the You can set, do this on smallest scientific calculator. value 5) Add one value (in the unit you you measuring) for 'range 'range with inclusion' to .14 (range) .14 (range) + .01 = .15) 6) Add one value (in If you want to learn how to do it witha calculator, refer (Example: the unit are are measuring) for on inclusion' (Example: the Enter the 'maximum' number of bars for your each cell interval + .01 = .15) bonus lecture MATH PRIMER. 7) Determine the appropriatevalue (or ending value) fromsample size (Example: < 50 data points use 5, 6 or 7 into a separate place in this bars for your sample not important. < 50 6) Determine the appropriate number ofworksheet - location issize (Example: (i.e., data points bars) 10.65, use 5, 6 or 7 bars) 10.68,you wouldand ending point tip, right click on the cell and If 10.71, 10.74, 10.77). 8) Calculate both the beginning like to print this of each cell and list the beginning and ending points of each 7) Calculate both cells. For EDIT COMMENT. have ajust highlight and copy the text, select example, if point of each cell and list the beginning and 5 cells bin in separate the beginning and endingyou Then range of .15, you could have ending with .03 data value per Then you will be able to a document for printing. to satisfy the bin points of each bin in separate cells. drag example, ifacross them range of .15, you could have 5 and paste in For the cursor you have a cell. range an idiosyncrasy of cells with .03 have value DATA ANALYSIS. Excel. Excel will give you the option of designating the bin ranges. 9) Now you databox in per cell. 8) Now you have an idiosyncrasy of Excel. Excel will give you the option of designating the bin But, you should give Excel the ENDING value of each bin rather than the beginning value. Highlight the cells Confused? Re-watch of lecture on histograms and cell intervals. ranges. that include the ending valuethe each bin under the BIN RANGE option. But, Highlight your Excel the ENDING value of each bin rather than the beginning value. 10) you should give complete data set under the INPUT RANGE option Highlight the you wouldOUTPUT and where you want binhighlight and chart located. (New worksheet or imbedded) 11) Check If cells that include print these instructions, just under the BIN RANGE option. on CHART like to the ending value of each the histogram copy, 9) Highlightthen paste into a document forthe INPUT RANGE option your complete data set under printing. 12) Click OK 10) Check on CHARTyour mouse over the corner of histogram chart located. (New worksheet or 13) You may drag OUTPUT and where you want the the histogram chart to enlarge it. imbedded) Excel drafts histograms, the bars do not adjoin. Ideally, we want the bars of a histogram to adjoin 14) When 11) Click OK are graphing continuous data. To make the bars adjoin, right click on top of bars and choose because we 12) You may drag your mouse over the corner of the histogram setting GAP WIDTH to zero. FORMAT DATA SERIES. In Excel 2007, you go directly to chart to enlarge it. 13) When Excel drafts histograms, the bars histogram, right click somewhere outside of the bars in the graph and 15) To change the color scheme of the 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 choose bars and chooseAREA. You can choose many 2003, you will have to select OPTIONS. Then set FORMAT PLOT FORMAT DATA SERIES. In Excel color formats. the GAP to Zero.plan the cell intervals, there are no data points in the MORE category, so you may delete it, 16) When you 14) To change MORE category is often confusing to click somewhere outside of the bars in the because the the color scheme of the histogram, right people. DO NOT delete the MORE category if it has data graph and it. points in choose FORMAT PLOTthrough these choosedetailed steps if you are interested in a polished Histogram. Otherwise run a 17) Only go AREA. You can more many color formats. 15) When you plan the cell intervals, there are#1. data points in the MORE category, so you may quick draft of the histogram with Option no delete it, because the MORE category is often confusing to people. DO NOT delete the MORE category if it has data print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy If you would like to points in it. 16) Only go through these more detailed steps if you are interested in a polished Histogram. the text, and paste in a document for printing. Otherwise run a quick draft of the histogram with Option #1. Data: Data for 'Wait Times' for last month 24 27 18 11 22 27 17 23 17 5 17 28 24 17 8 21 26 23 17 31 18 27 22 27 17 40 22 18 17 18 28 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. 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. The MEAN + and - two (2) SD in a normally distributed process represents ~95% of what the process is capable of producing. How to submit an Assignment The MEAN + and - three (3) SD in a normally distributed process represents ~99.73% of what the process is capable of producing. TARGET ASSIGNMENT DATE - Submit in Week 7 or earlier YOU DO NOT NEED TO SUBMIT A HISTOGRAM FOR THIS DELIVERABLE Project: Healthcare 2015 Deliverable: Expected Variation How do I determine if it's Student last name: approximately normal? Your last name here Mean: Standard deviation: Is the data approximately bell shaped? What is the lowest point of expected variation? What is the upper point of expected variation? Yes or No Instructor: The question is whether these histograms are APPROXIMATELY bell-shaped so that the assumption of normalityYour answer should be is a reasonable Check between 19 and Your never should ROUND ALL FINAL RESULTS TO one. Remember that you willanswersee 23.be Check Instructor: and 7. between 5 6 DECIMAL PLACES. perfectly normal distributions in real life! Help even if a process is You will need to And perfectly normally Your answer should create a histogram be Help Check distributed, you will need a VERY 0this 1.5 between large to answer and Your answer should be sample size for your histogram to appear Check question.39 and 42. between normal or symmetric. Close wins the cigar all we worry about are gross departures from normality. Is this histogram grossly non-normal? Read this! To send each assignment to your instructor: Click-and-hold the LEFT mouse button at the TOP-LEFT corner of the peach-colored box, then while holding down on that button, drag to the LOWER-RIGHT corner of the box. This will highlight the entire peach-colored box. Release the mouse button. Do a CTRL-C. This will copy what has been highlighted. Go to your Villanova website and follow this sequence: 1. COURSE MENU 2. CLASS ROSTER 3. Click on your instructor's email envelope icon 4. Type "Check My Work - Expected Variation" in the SUBJECT box. 5. Click once inside of the message box 6. Do a CTRL-V. This will paste your deliverable into this box. Don't be concerned if after you paste it, the appearance of the text is out of alignment. It will straighten out after you hit SEND. 7. SEND Check your SENT ITEMS folder afterward to see how it straightened out. Please 'hand-in' your assignments throughout the course. DO NOT SAVE THEM FOR THE END. The deadline forhaving all project deliverables 100% correct is 7 days prior to the end of the course. Objective: The team wants to know if there is an obvious reason for wait times being longer than what is acceptable. One of the team members suggested using a stem-and-leaf diagram to get a visualization of the variation in wait times. Wait times were recorded for the past 70 days. By looking at the numbers below, can you tell if there is assignable-cause variation? Unless you have a graphing function in your head, it is difficult to tell. So, that is the question. "Is there assignable-cause variation that can be realized from the 70 data values listed?" Your job is to answer that question. You could use a histogram (among other tools) to answer that question, but your sponsor wants to keep the actual raw data when reviewing the tool of choice. So, you will be using a stem-and-leaf diagram so that the raw data can be preserved. Construct a stem-and-leaf from the data. Is the process approximately normally distributed? If not, what shape do you see and what should we do about this? Instructions for you: 1. 2. 3. 4. Construct a stem-and-leaf diagram Answer the question: "Is there process approximately normally distributed?" What is the stem-and-leaf diagram telling you? What would you recommend? Data: I'm lost Wait times (last 10 weeks) 16 16 17 37 47 32 48 21 18 75 15 17 13 47 11 47 38 17 20 49 19 16 26 17 65 15 17 48 16 44 48 45 50 49 63 17 22 10 18 51 14 80 6 49 48 47 48 52 46 48 47 20 71 47 50 95 47 20 50 35 21 46 48 20 64 16 44 82 51 58 Instructor: You need to make a stem-and-leaf diagram. What is that? If you do not know, you need to re-watch the lecture on Stemand-Leaf diagrams. You will be using the peach-colored boxed area to make the diagram. The 'stem line' is the bold red line. You will building it from left to right. How to submit an Assignment Instructor: TARGET ASSIGNMENT DATE - Submit in Week 9 or earlier Instructor: Helpful tip - Sorting - Excel 2007 Helpful tip - Sorting - Excel 2003 Excel 2003 Project: Healthcare 2015 Highlight the "leaves" numbers in any one Deliverable: Stem & Leaf row. Student last name: Your last name here -Click Data -Sort -Continue with current selection -Sort -Options -Sort left to right -Ok 9 8 7 6 5 4 3 2 1 0 This will put your numbers in rank order. Is it reasonably normally distributed? Yes or No If it is not normal, what shape is it? (Use the correct term for this shape, not a description.) Based on this analysis, what is the next thing you would do? Type your recommendation here. "What's the purpose of the red Instructor: The red line is the stem. line?"The leaves...you need to make. To use the sorting function in Excel 2007 1) Click on the Data tab at the top 2) Click on the Sort category 3) Follow menu steps to make. Read this! To send each assignment to your instructor: Click-and-hold the LEFT mouse button at the TOP-LEFT corner of the peach-colored box, then while holding down on that button, drag to the LOWER-RIGHT corner of the box. This will highlight the entire peach-colored box. Release the mouse button. Do a CTRL-C. This will copy what has been highlighted. Go to your Villanova website and follow this sequence: 1. COURSE MENU 2. CLASS ROSTER 3. Click on your instructor's email envelope icon 4. Type "Check My Work - Stem and Leaf" in the SUBJECT box. 5. Click once inside of the message box 6. Do a CTRL-V. This will paste your deliverable into this box. Don't be concerned if after you paste it, the appearance of the text is out of alignment. It will straighten out after you hit SEND. 7. SEND Check your SENT ITEMS folder afterward to see how it straightened out. Please 'hand-in' your assignments throughout the course. DO NOT SAVE THEM FOR THE END. The deadline for having all project deliverables 100% correct is 7 days prior to the end of the course. 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: With the data provided, construct a scatter diagram and calculate the correlation coefficient to determine if their hypothesis is correct. Data: Number in for treatment per specific day 172 132 130 206 199 223 201 169 135 200 189 110 203 189 224 197 188 125 199 194 207 Leave without treatment incidents 4 6 2 4 6 4 8 7 5 3 7 8 6 5 8 4 8 2 6 8 7 Scatter Diagrams in Excel 2003 Scatter Diagrams in Excel 2007 Instructor: 1) Click on Chart icon on top task bar, OR Click on the Instructor: INSERT menu option at the top menu bar. 1) Highlight both rows of the Standard Types tab 2) Click on SCATTER from data 2) INSERT tab at the top 3) Click Next Correlation Coefficient - Excel 2003 Correlation Coefficient - Excel 2007 Instructor: both columns of data and finish according 3) Go to CHART category 4) Highlight to the directions. 4) Click on SCATTER and yourbar and CORREL, appears 1) the fx in the top scatter diagram or Instructor: like to print this tip, right click on the cell If you would click on INSERT, function, CORREL. If you would like to print this tip, right click on the cell and and select EDIT COMMENT. Then just highlight and select EDIT 1) Click on the just the top bar and the for printing. or COMMENT. Thenfx inhighlight and copy CORREL,and copy the text, and paste in a documentARRAY paste in a document for 2) Highlight each column of data as an text, click on FOMULAS, INSERT function, CORREL. printing. 3) Click Okay. 2) Highlight each column of data as an ARRAY 4) Excel will calculate the correlation coefficient. 3) Click Okay. The correlation coefficient ranges between zero 4) Excel will calculate the correlation coefficient. and one. Zero is no correlation and '1' is a perfect correlation. 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. -1.0 to -0.7 strong negative association. -0.3 to +0.3 little or no association. -0.7 to -0.3 weak negative association. +0.3 to +0.7 weak positive association. -0.3 to +0.3 little or no association. +0.7 to +1.0 strong positive 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 If you would like to print this tip, right click on the the text, and paste in a document for printing. cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing. -0.7 to -0.3 weak negative association. +0.3 to +0.7 weak positive association. -0.3 to +0.3 little or no association. How to submit an Assignment +0.7 to +1.0 strong positive association. +0.3 to +0.7 weak positive association. TARGET ASSIGNMENT DATE - Submit in Week 12 or earlier +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 If you would like to print this tip, right click on the the text, and paste in a document for printing. cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing. YOU DO NOT NEED TO SUBMIT THE ACTUAL SCATTER DIAGRAM Project: Healthcare 2015 Deliverable: Scatter Diagram Student last name: Your last name here Is there strong correlation? YES or NO IF there is strong correlation, is it positive or negative? Negative, Positive or N/A HINT! HINT: Be sure to use commonly accepted criteria for evaluating correlation coefficients in your answer. Read this! To send each assignment to your instructor: Click-and-hold the LEFT mouse button at the TOP-LEFT corner of the peach-colored box, then while holding down on that button, drag to the LOWER-RIGHT corner of the box. This will highlight the entire peach-colored box. Release the mouse button. Do a CTRL-C. This will copy what has been highlighted. Go to your Villanova website and follow this sequence: 1. COURSE MENU 2. CLASS ROSTER 3. Click on your instructor's email envelope icon 4. Type "Check My Work - Scatter Diagram" in the SUBJECT box. 5. Click once inside of the message box 6. Do a CTRL-V. This will paste your deliverable into this box. Don't be concerned if after you paste it, the appearance of the text is out of alignment. It will straighten out after you hit SEND. 7. SEND Check your SENT ITEMS folder afterward to see how it straightened out. Please 'hand-in' your assignments throughout the course. DO NOT SAVE THEM FOR THE END. The deadline for having all project deliverables 100% correct is 7 days prior to the end of the course. 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 wait times are so long. From that list, the team has reduced it down to 5 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 - see also your online textbook) and they find they can learn about the effects of those five factors in as little as 8 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 wait time minutes (y) when "staff size" was at (-). Then, do the same thing for when the results (y) when "staff size" was at (1). When at (-1) When at (+1) 7 9 28 25 26 8 6 28 Average 16.75 17.50 Data: The factors for the experiment were: A B C D E - level 8 FIFO Iterative Product A 68 degrees Staff size Order of treatment Method of treatment Tracking software Temp. in the waiting room + level 16 By priority All at once Product B 75 degrees 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 1 2 3 4 5 6 7 8 Staff sz 1 -1 1 -1 -1 1 1 -1 Order 1 -1 1 -1 1 -1 -1 1 Method -1 -1 1 1 1 -1 1 -1 Software -1 1 1 1 -1 1 -1 -1 Rm temp Results (Y) (patient wait time measured in minutes) -1 9 1 7 Excel 2003 Tip -1 25 -1 28 Excel 2007 Tip 1 26 1 8 -1 28 1 6 Instructor: Leave a blank cell between the factors in your Excel 2003 spreadsheet. Instructor: Excel 2007 1. Highlight your data sets and bar Click INSERT on the top tab labels leaving a blank 2. Click Chart factors as illustrated below. cell between the 3. Click INSERT on the top with bar with markers 2. Select the LINE graph tab "line displayedChart category, Select the LINE graph with 3. In the at each data value" "line with markers displayed at each data value" Example of your spreadsheet layout with blank cell between factors: spreadsheet layout with blank cell Example of your between factors: Staff Size 16.75 Staff Size size + 16.75 17.50 Staff CELL SKIP size + 17.50 SKIP Order CELL Order + Order + SKIP CELL SKIP CELL between factors: spreadsheet layout with blank cell Example of your between factors: Staff Size 16.75 Staff Size 17.50 size + 16.75 SKIP size + Staff CELL 17.50 Order CELL SKIP Order + SKIP CELL Order + SKIP CELL How to submit an Assignment TARGET ASSIGNMENT DATE - Submit in Week 11 or earlier YOU DO NOT NEED TO SEND IN THE MAIN EFFECTS PLOTS Project: Healthcare 2015 Deliverable: DOE Student last name: Your last name here Staff size Staff size + Treatment ord