I need a 6 Sixma project

Scatter Diagram

1

Instructor: These cells contain some hints, tips, and self-checks. If you would like to print any of the tips, right click the cell containing the tip and select Edit Comment. Highlight the text, copy and paste in a any text document like WORD for printing.

Instructor: EXCEL 2003 or earlier Click on the TOOLS tab in the top bar of Excel. If your computer already has the “Data Analysis” option listed, you are ready to go. Your Data Analysis tools have already been added. Under the Data Analysis Function you will find some of the more advanced functions that we will be discussing such as ANOVAs, and t tests. If you do not see Data Analysis listed under the TOOLS tab, I encourage you to go to the Help function in Excel for specific instructions on how to load “the Data Analysis Toolpak” in your version of Excel. Here are the easy standard instructions for Excel 2003 if you need to load the Add-Ins. 1) On the Tools menu, click Add-Ins. 2) In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK. 3) When you load the Analysis Toolpak, the DATA ANALYSIS command is automatically added to the TOOLS menu. If your version if slightly different than the above, refer to your HELP function for details on loading Tookpak. See the next spreadsheet at the bottom of this worksheet entitled EXCEL EXAMPLES for an illustration. 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. See the next spreadsheet EXCEL EXAMPLES for an illustration. 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.

Instructor: It is beyond the scope of this Black Belt course to teach students how to perform all of the functions of Excel. The Microsoft web site has excellent FREE tutorials for both Excel 2003 or Excel 2007. Excel 2007 http://office.microsoft.com/en-us/training/CR100479681033.aspx Excel 2003 http://office.microsoft.com/en-us/training/CR061831141033.aspx Or, ask a business colleague, who is proficient in Excel to show you how to do some of the basic things such as copying, cutting, pasting, copying from sheet to sheet, etc. Please do not expect your Black Belt class instructor to teach you how to use Excel software. That is not the objective of this course. This course will highlight specific Excel commands that are unique to Six Sigma, but it is not the objective of this course to teach basic Excel commands. Please use the suggested resources listed above. 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: EXCEL 2003 or earlier Click on the TOOLS tab in the top bar of Excel. If your computer already has the “Data Analysis” option listed, you are ready to go. Your Data Analysis tools have already been added. Under the Data Analysis Function you will find some of the more advanced functions that we will be discussing such as ANOVAs, and t tests. If you do not see Data Analysis listed under the TOOLS tab, I encourage you to go to the Help function in Excel for specific instructions on how to load “the Data Analysis Toolpak” in your version of Excel. Here are the easy standard instructions for Excel 2003 if you need to load the Add-Ins. 1) On the Tools menu, click Add-Ins. 2) In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK. 3) When you load the Analysis Toolpak, the DATA ANALYSIS command is automatically added to the TOOLS menu. If your version if slightly different than the above, refer to your HELP function for details on loading Tookpak. Remember that the Data Analysis function is NOT necessary for the course, but it is helpful. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

Villanova instructor: Excel 2007 The Analysis ToolPak is a Microsoft Office Excel add-in program that is available when you install Microsoft Office or Excel. To use it in Excel, however, you need to load it first. 1. Click the Microsoft Office Button , and then click Excel Options at the bottom. 2. Click Add-Ins, and then in the Manage box, select Excel Add-ins. 3. Click Go. 4. In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK. If you get prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it. 5. After you load the Analysis ToolPak, the Data Analysis command is available in the ANALYSIS group on the DATA tab. Remember that the Data Analysis function is not required for the course. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing.

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 to know what this project is all about and how it impacts the strategic objectives of the organization. I would need a short, to-the-point compelling reason why we need to do this.

Instructor: In the problem statement, we 'sell' the need for the project with specific and measureable data. One or two sentence description of the symptoms arising from the problem to be addressed. It will often parallel the Business Case, but will be more specific and focused. Answer; What’s wrong? Where is the problem appearing? How big is the problem? What is the impact of the problem on the business?

Instructor: The Goal Statement and Problem Statements are a matched pair. 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?

Instructor: Outline the project scope in process terms (where does it start and stop - ie. starts with the point of initial deposit at branch and ends when the funds are accessible in the customers account), and identify any constraints and assumptions. How much of our work time can be devoted to project per week? Do we have any authority to spend money? Who (what) are our internal/external resources? Also be sure to review week #2 archived virtual class session for additional information and helpful hints! What the scope is not: -It is not merely a timeline (i.e., when the Six Sigma project is to begin and when it is expected to end.) -It is not restating the problem being attacked.

Instructor: Additional information about project charters can be found in your Online Textbook. I also HIGHLY recommend reviewing week #2 archived virtual class session for additional information and helpful hints for the assignment!

#REF!

Frequency

Factors Impacting Student MEAP Performance

0

Instructor: Using the information provided in the 'The project' tab you can calculate the DPMO and use the table above. Also please refer to Week #3 virtual class session for additional information and examples on how to use the table above.

#REF!

Frequency

Factors Impacting Student MEAP Performance

0

Instructor: Step 1. Click in a blank adjacent 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

Instructor: Step 1. Click in a blank adjacent 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

Instructor: Instructions for creating a pareto chart in Excel 2003 or earlier versions 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 follows this standard business format, it will enhance communication and presentations to management. If you would like to PRINT these instructions, right click the cell. Choose EDIT COMMENT. Then just highlight the text, copy and paste into a document to print.

Instructor: Excel 2007 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. Right click the columns that represent the percentages (smaller columns) 6. On the top Format tab, click Format Selection. 7. Under Plot Series On, click Secondary Axis and then click Close. 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 11. Click on Format Axis 12. Set Axis Options for MAX and MIN to FIXED 13. Change max to the total of all counts from all columns and a min to zero 14. OK 15. 'Right click' on right Y axis 16. Set Axis Options for MAX and MIN to FIXED 17. Change max to 1.0 (100%) and min to zero 18. OK 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.

Instructor: Is it bell-shaped? Remember, you would need an infinite sample size for it to be perfect. Close wins the cigar. We want to know if the distributions are 'approximately' normal or randomly distributed. In other words, do the distributions appear to be an approximate bell curve. For a distribution to be skewed, the tails should appear 'significantly' distorted. More than one peak in the data indicates the data is not normally distributed.

Instructor: Is it bell-shaped? Remember, you would need an infinite sample size for it to be perfect. Close wins the cigar. We want to know if the distributions are 'approximately' normal or randomly distributed. In other words, do the distributions appear to be an approximate bell curve. For a distribution to be skewed, the tails should appear 'significantly' distorted. More than one peak in the data indicates the data is not normally distributed.

Instructor: Excel 2003 - 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: .14 (range) + .01 = .15) 6) Determine the appropriate number of bars for your sample size (Example: < 50 data points use 5, 6 or 7 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 .15, you could have 5 cells with .03 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 these instructions, right click on the cell, and select EDIT COMMAND. Then just highlight the text, copy and paste into a document to print.

Instructor: Excel 2007 - 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: .14 (range) + .01 = .15) 7) Determine the appropriate number of bars for your sample size (Example: < 50 data points use 5, 6 or 7 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 .15, you could have 5 cells with .03 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 these instructions, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text and then paste into a document for printing.

Instructor: You should get a value between 10 and 15

Instructor: You should get a value between 0.05 and 0.10

Instructor: You should get a value between close to 0.03 and 0.07

Instructor: You should get a value between 0.02 and 0.06

Instructor: You should get a value between 0.08 and 0.10

Instructor: You should get a value between 0.04 and 0.10

Instructor: You should get a value between 0.10 and 0.16

Instructor: You should get a value between 0.08 and 0.13

Instructor: You should get a value between 0.09 and 0.10

Instructor: You should get a value between 0.18 and 0.20

Instructor: You should get a value between 0.09 and 0.11

Instructor: For 5% alpha, you should get a value between 8 and 12. Try checking the critical value at 1% alpha…..

Instructor: A quick refresher on hypothesis statements. I recommend starting off with thinking about what you are testing. The null hypothesis is always what we would expect by chance alone. In this example, we would expect the deposit cycle time to be independent of the choice of bank branch. We would expect the branch policies to be consistent, with similar deposit time results. (Our choice of banking branches doesn't matter.) The alternative hypothesis, in contrast, is attempting to test if the deposit time is NOT independent of the branches, or in other words, the choice of branch matters in regard to deposit times. Now you should be able to write your Null and Alternative Hypothesis in standard format. I HIGHLY recommend attending (reviewing) the Chi-Sqr virtual class session for additional information and helpful hints for the assignment. 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: To write up your conclusion, you would have either concluded that you: a) failed to reject the NULL at 95% confidence, or b) that you have rejected the NULL at 95% confidence. We do not include a statement that we 'accept the null' in hypothesis testing.

Instructor: You may solve this chi square assignment with Excel, but you will be using two different Excel functions, 'Chitest' and 'Chiinv.' Chitest gives you the p-value when you compare the actual range with the expected range. If your p-value is less than your alpha, you may reject the null. If you want to convert the p-value to your chi square test statistic, use the 'chiinv' to transform the 'probability' statistic to your 'chi square test statistic.' Although you may use Excel in this assignment, you first must still calculate the 'Expected Values" as described in the Sample exercise. Here are the steps..... 1. Now you should have a table with the actual counts, and a separate table with the expected values. 2) If you have an fx in your top Excel bar, click on the fx and type in CHITEST, and OK. If you do not have an fx on the home page, click on INSERT in the top menu and then click on FUNCTION. Next type in CHITEST. 3) Once you have the pop up screen for CHITEST, highlight the data for both the ACTUAL RANGE and the EXPECTED RANGE. Click on OK. 4) The statistical result that you are seeing is the p-value. If your p-value is < your alpha level, you may reject the null. 5) If you would like to convert your p-value to a chi square test statistic, click on fx again and select CHIINV. The CHIINV will convert the p-value to your chi square test statistic. You will then compare this result with your chi square critical value. Since this assignment requires that you report the chi square test statistic, you will be using both the CHITEST function and the CHIINV if you solve the assignment with Excel. 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: You may solve this chi square assignment with Excel, but you will be using two different Excel functions, 'Chitest' and 'Chiinv.' Chitest' gives you the p-value when you compare the actual range with the expected range. If your p-value is less than your alpha, you may reject the null. If you want to convert the p-value to your chi square test statistic, use the 'chiinv' to transform the 'probability' statistic to your 'chi square test statistic.' Although you may use Excel in this assignment, you first must still calculate the 'Expected Values" as described in the Sample exercise. Here are the steps... 1. Now you should have a table with the actual counts, and a separate table with the expected values. 2) If you have an fx in your top Excel bar on the HOME tab, click on the fx and type in CHITEST, and OK. If you do not have an fx on the home page, click on the FORMULAS tab and then click on the fx in the Functions Library category. 3) Once you have the pop up screen for CHITEST, highlight the data for both the ACTUAL RANGE and the EXPECTED RANGE. Click on OK. 4) The statistical result that you are seeing is the p-value. If your p-value is < your alpha level, you may reject the null. 5) If you would like to convert your p-value to a chi square test statistic, click on fx again and select CHIINV. The CHIINV will convert the p-value to your chi square test statistic. You will then compare this result with your chi square critical value. Since this assignment requires that you report the chi square test statistic, you will be using both the CHITEST function and the CHIINV if you solve the assignment with Excel. 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: Hint: There is PAIRED data. And, we want to see if the data from the second year has improved from year one. We are looking at the average deposit cycle time from 11 different branches.

The hypothesis statements should be written in terms of the population parameters. Also keep in mind we are looking for an 'improvement'.

Instructor: You should get a value between 1.00 and 2.00

Instructor: You should get a value between 1.50 and 2.00

Instructor: Sum of Branch 1 results

Instructor: n of Branch 1

Instructor: Square the sum of all data from Branch 1 and then divide by n for Branch 1. You should get a number between 290 and 300.

Instructor: Square each of the Branch 1 values and add them. You should get a number between 300 and 320.

Instructor: Sum of Branch 2 results.

Instructor: n of Branch 2

Instructor: Square the sum of all data points for Branch 2 and then divide by the n of Branch 2

Instructor: Do the same as above, but for Branch 2.

Instructor: Sum of Branch 3 results

Instructor: n of Branch 3

Instructor: Square the sum of all data points for Branch 3 and then divide by the n of Branch 3.

Instructor: Do the same as above, but for Branch 3.

Instructor: Sum of Branch 4 results

Instructor: n of Branch 4

Instructor: Square the sum of all data points of Branch 4 and then divide by the n of Branch 4.

Instructor: Do the same as above, but for Branch 4.

Instructor: Total of this column. You should get a number between 170 and 200.

Instructor: Total of this column. You should get a number between 20 and 25.

Instructor: Total of this column. You should get a number between 1300 and 1400.

Instructor: Total of this column. You should get a number between 1300 and 1400.

Instructor: How many data do you have totally? n-1 You should get a number between 20 and 30. Plug that number into the ANOVA table below.

Instructor: What are the factors? The factors are the 4 Branches in this case. df for factors =n-1

Instructor: CM: Mean 'Correction for the Mean.' To get this number, you sum all of your data values and then square that value. Next, you divide by the total number of data points.

Instructor: Refer to the assistance table above. You subtract the CM from the forth column's total. Plug that number into the ANOVA table below.

Instructor: Take the result of Sum^2/n from the table above and subtract the CM. You should get an answer between 5 and 10.

Instructor: Subtract step 6 from step 5 You should get a number between 20 and 25.

Instructor: You should get a number between 1.5 and 2.

Instructor: To calculate the df (error), subtract the df (branch) from the TOTAL df. You should get an answer between 15 and 25.

Instructor: You should get a number between and 1.5.

Instructor: You should get a number between 1 and 2.

Instructor: With ANOVA it is always a one-tail, right-hand tail. You should get a F critical value between 1 and 5.

Instructor: These are rough estimates which you may use to check your results, but not exact values. Please send your exact values. SS-Factor: ~5 SS-Error: ~24 SS-Total: ~29 df-factor: 1-5 df-error: 20 - 30 df-TOTAL: 20 - 30 MS-Factor: ~1.75 MS-Error: ~1.20 Calc-F: ~1.5 F-Crit: 0 - 5

Instructor: "Of course you want to use Excel--who wouldn't?" But.... You need to know how to calculate ANOVA the hard way (below) if you plan to sit for the ASQ test. I guarantee you will be asked at least one question on these calculations. This is perhaps why our students have such an outstanding pass rate (90%+). For Excel 2003, follow this sequence: -Tools -Data Analysis -ANOVA-Single Factor -OK -Input range [To get this, drag from the upper left to the lower right of the data set. In other words, from 'Machine 1 (including the words "Machine 1" diagonally to the bottom right-hand corner 0.572)] -Check 'Labels in first row' -Check 'New workbook Ply -OK 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: "Of course you want to use Excel--who wouldn't?" But.... You need to know how to calculate ANOVA the hard way (below) if you plan to sit for the ASQ test. I guarantee you will be asked at least one question on these calculations. This is perhaps why our students have such an outstanding pass rate (90%+). For Excel 2007, follow this sequence: -Click on the DATA tab -Go to the ANALYSIS category -Click on Data Analysis -Select ANOVA-Single Factor -OK -Input range [To get this, drag from the upper left to the lower right of the data set. In other words, from 'Machine 1 (including the words "Machine 1" diagonally to the bottom right-hand corner 0.572)] -Check 'Labels in first row' -Check 'New workbook Ply -OK 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: Please use the respecitve F-table in your Online Textbook.

Instructor: Select the results when training was set to the low (-). Include both the first experiment and the replication (the first column of data and the second column of data).

Instructor: Select the results when training was set to the high (+). Include both the first experiment and the replication (the first column of data and the second column of data).

Instructor: Use the Excel charting function to create the Main Effects Plot. Select the 'Line' charting function. Keep the same left axis scale for all three factors, (training, mentoring and bonus) so that you can visually compare the effect of the three settings. Leave a blank cell between the factors in your Excel 2003 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: Training Low 2.89875 Training High 2.0575 SKIP CELL Mentor Low xxx Mentor High xxx SKIP CELL Bonus Low xxx Bonus High xxx

Instructor: Excel 2007 1. Highlight your data sets and labels leaving a blank cell between the teaching styles 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: Training Low 2.89875 Training High 2.0575 SKIP CELL Mentor Low xxx Mentor High xxx SKIP CELL Bonus Low xxx Bonus High xxx

Instructor: Be sure to consider the 'type of quality characteristic' of the response variable!

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.

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.

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. You will see points for paired sets of data, such as a point for 67 and 29. 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: 1) Highlight both rows 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.

Instructor: The formulas for the control limits of the XmR chart are found on pages 681-685 of Book 4 of 4 of your white manuals. 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.

Instructor: "Yes you do. Next question." (smile)

Instructor: Whether or not a measurement system is discriminate is covered in the lecture in two places. It is covered in the control chart section and it is also covered in the 'Measurement System Evaluation" lecture. First, look at your data. What unit of measurement is being used? You are measuring in hours. For example, if the UCL of your range chart is 42.xx hours and you are measuring fine enough to see 1 hour intervals in the data, then your measurement system is discriminate. It would be possible to have 43 (42+1 for zero) 'possible' units under the UCL of the range chart because you are measuring in one hour intervals. I HIGHLY recommend attending (reviewing) the Control Chart virtual class session for additional information and helpful hints for the assignment!

Instructor: You do not need fancy software to create a control chart. In the "Appendx' there are control chart forms if you prefer not to use Excel.

Instructor: You may calculate r-bar by hand, but here is an alternative for calculating r-bar with Excel. This is a 2-step process. First you need to find the absolute values for the range of each subgroup. Why do we use absolute values? If we only calculated the 'difference' between the cells, we would get both positive and negative numbers. To measure distance, such as range, we only want to work with positive numbers, such as the absolute value. 1. Click on the empty cell directly right of the second value (33) 2. Either click on the fx function in the task bar and select ABS (for absolute value) (If you do not have an fx function in your task bar, select Insert, and select ABS) 3. Click on the first cell (45) 4. Type in a minus sign. 5. Click on the second cell (33). Make sure that the second cell reference is inside of the ( ). 6. Click OK. You should get 12. 7. Now, grab the bottom right-hand corner of the cell you are working with, and drag it all the way down to the bottom of the list of data. This will repeat the formula for you all the way down the list of data. You should have all the range values starting with 12 and ending with the last value of 8. 8. For the 2nd step of this process, you will be taking an average of all of your range values. 9. Click on any empty cell. 10. Click on fx and select AVERAGE or Insert - Function - AVERAGE. 11. Highlight the column of range values that you just created. 12. Click OK and you will have r-bar or the average of your ranges. 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.

More help on calculating R-bar using Excel We really intend on you doing this step by hand. R-bar 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 moving ranges.

Self check: You should get an r-bar between 12 and 15.

Instructor: Please refer to the lecture on how to calculate control limits. This one in particular is for the range.

Self check: You should have gotten a value between 40 and 45.

Instructor: Please refer to the lecture on how to calculate control limits. This one in particular is for the individuals.

Self check: UCL between 75 and 80. LCL between 5 and 10.

Instructor: You should get a value between 12 and 15.

Instructor: You should get a value between 40 and 45.

Instructor: You should get a value between 75 and 80.

Instructor: You should get a value between 9 and 12.

Instructor: We want to see at least 6 'possible' points under the UCL of the range chart. Look at your raw data. What are the closest measurement intervals? 44, 45 or 1 hour intervals You are measuring at 1 hour intervals. We may not have data points at each 1 hour interval, but we are measuring fine enough to detect variation at the 1 hour interval. Based on the UCL of the range chart, would you have more than 6 possible points under the UCL of the range chart? Adequate discrimination just means that we are measuring fine enough to detect variation if it exists. ** Also refer to the helpful hint provided in the Instructions for 4b above!

Instructor: You may calculate r-bar by hand, but here is an alternative for calculating r-bar with Excel. This is a 2-step process. First you need to find the absolute values for the range of each subgroup. Why do we use absolute values? If we only calculated the 'difference' between the cells, we would get both positive and negative numbers. To measure distance, such as range, we only want to work with positive numbers, such as the absolute value. 1) Click the empty cell next to, and to the right of the second value (33) 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 first cell (45) 7) Type in a minus sign. 8) Click on the second cell (33). Make sure that the second cell reference is inside of the ( ). 9) Click OK. You should get 12. 10) Now, grab the bottom right-hand corner of the cell you are working with, and drag it all the way down to the bottom of the list of data. This will repeat the formula for you all the way down the list of data. You should have all the range values starting with 12 and ending with the last value of 8. 11) For the 2nd step of this process, you will be taking an average of all of your range values. 12) Click on any empty cell. 13) Click on fx and select AVERAGE or FORMULAS - Function Library- AVERAGE. 14) Highlight the column of range values that you just created. 15) Click OK and you will have r-bar or the average of your ranges. 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: 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

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)