Six Sigma Assignment
runningman1106
six_sigma_black_belt_assignment.xlsxThe project
| AboveWay Sandwich - Project | |
| You are a Master Black Belt working with | |
| AboveWay Sandwich, Inc. to improve the franchises' customer satisfaction. | |
| The process is simple. | |
| Your assignment is to improve customer satisfaction by working with various | |
| Green Belts and Black Belts. To successfully pass the course, you must | |
| complete all of the deliverables (tabs below) to the satisfaction of your instructor. | |
Select Bread
Assemble
Wrap and Pay
Excel examples
| How you can use Excel | |||||||||||
| This spreadsheet tab provides illustrations of adding the DATA ANALYSIS capability to your Excel software. | |||||||||||
| If you see Data Analysis already listed under the Tools tab, you are set. If you do not see Data Analysis | |||||||||||
| follow the instructions in the Green tab. | Excel 2003 instructions Diane Johnson: Instructor Excel 2003: Click on the TOOLS tab in the top bar of Excel. If your computer already has the “Data Analysis” option listed, you are ready to go. Your Data Analysis tools have already been added. Under the Data Analysis Function you will find some of the more advanced functions that we will be discussing such as ANOVAs, and t tests. If you do not see Data Analysis listed under the TOOLS tab, I encourage you to go to the Help function in Excel for specific instructions on how to load “the Data Analysis Toolpak” in your version of Excel. Here are the easy standard instructions for Excel 2003 if you need to load the Add-Ins. 1) On the Tools menu, click Add-Ins. 2) In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK. 3) When you load the Analysis Toolpak, the DATA ANALYSIS command is automatically added to the TOOLS menu. If your version if slightly different than the above, refer to your HELP function for details on loading Tookpak. Remember that the Data Analysis function is NOT necessary for the course, but it is helpful. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing. |
||||||||||
| Excel 2003 or earlier versions of Excel example | |||||||||||
| This spreadsheet tab provides illustrations of adding the DATA ANALYSIS capability to your 2007 Excel software. | |||||||||||
| If you see Data Analysis already listed under the DATA tab, you are set. If you do not see Data Analysis | |||||||||||
| follow the instructions in the Green tab. | Excel 2007 instructions Diane Johnson: 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. |
||||||||||
|
Diane Johnson: Instructor Excel 2003: Click on the TOOLS tab in the top bar of Excel. If your computer already has the “Data Analysis” option listed, you are ready to go. Your Data Analysis tools have already been added. Under the Data Analysis Function you will find some of the more advanced functions that we will be discussing such as ANOVAs, and t tests. If you do not see Data Analysis listed under the TOOLS tab, I encourage you to go to the Help function in Excel for specific instructions on how to load “the Data Analysis Toolpak” in your version of Excel. Here are the easy standard instructions for Excel 2003 if you need to load the Add-Ins. 1) On the Tools menu, click Add-Ins. 2) In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK. 3) When you load the Analysis Toolpak, the DATA ANALYSIS command is automatically added to the TOOLS menu. If your version if slightly different than the above, refer to your HELP function for details on loading Tookpak. Remember that the Data Analysis function is NOT necessary for the course, but it is helpful. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing. | Excel 2007 example | ||||||||||
COPQ
| Objective: | |||||
| Your first action as the new Master Black Belt is to collect data on the quality costs associated with the current system. | |||||
| Accounting provides you with the following results for the past year. | |||||
| Instructions for you: | |||||
| You think it would be useful to get an idea of the Cost of Poor Quality for the organization. | |||||
| Your mission is to put the costs into the appropriate buckets. | |||||
| Buckets meaning, the cost of doing business, internal failures, external failures, appraisal, and prevention costs. | |||||
| Once your analysis matches your instructor's analysis, you can determine 'what's next.' | |||||
| Data: | |||||
| Raw Materials (ingredients for sandwiches) | $53,200.00 | ||||
| Scrapped Meat and Vegetables (damaged during food preparation) | $2,416.00 | ||||
| Incoming Inspection (of ingredients) | $3,700.00 | ||||
| Six Sigma Projects | $420.00 | ||||
| Obsolete / expired food | $864.00 | ||||
| Training for counter help | $2,097.00 | ||||
| Corporate audits to verify store compliance with policies | $3,789.00 | ||||
| Procedures for counter help | $1,574.00 | ||||
| Electricity | $1,196.00 | ||||
| Fines from Health Inspectors | $2,000.00 | ||||
| Rent | $24,020.00 | ||||
| Scrapped Burned bread | $485.00 | ||||
| Cost to pre-package meats in individual serving portions | $297.00 | ||||
| Free meals given to customers with complaints | $1,457.00 | ||||
| Supervisor sign-off/approval for credit card sales | $348.00 | ||||
| Business Bureau membership | $500.00 | ||||
| Training for incoming inspection | $368.00 | ||||
| Loss of business due to customer dissatisfaction | $6,603.00 | ||||
| TOTAL | $105,334.00 | ||||
| AboveWay Sandwich, Inc. Customer Satisfaction Improvement | |||||
| Deliverable: | Cost of poor quality | ||||
| Student last name: | Your last name here | ||||
| The categories are: Cost of doing business, Internal Failures, External Failures, Appraisal, and Prevention. | |||||
| For each of the following, please choose the appropriate COPQ category. Type in your responses. If you are | |||||
| 100% correct when you choose each catagory, your analysis will match your instructor's analysis of the same data. | |||||
| Raw Materials (ingredients for sandwiches) | Type here | ||||
| Scrapped Meat and Vegetables (damaged during food prep) | Type here | ||||
| Incoming Inspection (of ingredients) | Type here | ||||
| Six Sigma Projects | Type here | ||||
| Obsolete / expired food | Type here | ||||
| Training for counter help | Type here | ||||
| Corporate audits to verify store compliance with policies | Type here | ||||
| Procedures for counter help | Type here | ||||
| Electricity | Type here | ||||
| Fines from Health Inspectors | Type here | ||||
| Rent | Type here | ||||
| Scrapped Burned bread | Type here | ||||
| Cost to pre-package meats in individual serving portions | Type here | ||||
| Free meals given to customers with complaints | Type here | ||||
| Supervisor sign-off/approval for credit card sales | Type here | ||||
| Business Bureau membership | Type here | ||||
| Training for incoming inspection | Type here | ||||
| Loss of business due to customer dissatisfaction | Type here | ||||
| Self check daniel-munson: Instructor: For Cost of Doing Business, your instructor calculated ~$80k For Prevention, your instructor calculated ~$4k For Appraisal, your instructor calculated ~$8k For Internal Failure, your instructor calculated ~$4k For External failures, your instructor calculated ~$10k |
|||||
| What is the total cost of doing business? | Type here : Your answer should be between $78,000 and $81,000. |
||||
| What is the total internal failures cost? | Type here : Your answer should be between $3,000 and $5,500. |
||||
| What is the total external failures cost? | Type here : Your answer should be between $8,000 and $11,000. |
||||
| What is the total appraisal cost? | Type here : Your answer should be between $8,000 and $11,000. |
||||
| What is the total prevention cost? | Type here : Your answer should be between $3,000 and $5,000. |
||||
| What is the total cost of quality? | Type here : Your answer should be between $25,000 and $29,000. |
||||
| Based on these findings and COQ philosophy, what would you recommend to your Black Belt as the best strategy for addressing any problem areas shown and why? | Type your recommendation here. (Use at most 50 words.) | ||||
FMEA
| Objective: | |||||||||
| To provide a Black Belt with sound advice on how to address problems identified by an FMEA. | |||||||||
| Instructions for you: | |||||||||
| You are given this filled out FMEA for baking bread by a Black Belt who has been working with your store. Your assignment is to use the results of this FMEA to advise your Black Belt on which failure mode to focus on first and why, based on FMEA philosophy. | |||||||||
| Data: | |||||||||
| POTENTIAL FAILURE MODES | POTENTIAL EFFECTS OF FAILURE | SEVERITY OF EFFECT | POTENTIAL CAUSES OF FAILURE | PROBABILITY OF OCCURRENCE | CURRENT PROCESS CONTROLS | PROBABILITY OF DETECTION / EASE OF CORRECTION | RPN | ||
| Wrong oven temperature set. | Bread overcooked and must be scrapped. | 4 | Didn’t know that different types of bread required different baking temperatures – lack of training. | Instructions on package. | 6 | ||||
| 2 | 48 | ||||||||
| Didn’t read label on dough package. | |||||||||
| 6 | 144 | ||||||||
| Label on dough package is wrong. | |||||||||
| 1 | 24 | ||||||||
| Temperature dial on oven is defective. | |||||||||
| 3 | 72 | ||||||||
| Bread not put into ovens on time. | Bread is not done when customers arrive. | 6 | Didn’t know how long the bread needs to bake – lack of training. | Checklist for store opening procedures. | 10 | ||||
| 1 | 60 | ||||||||
| Got busy and forgot. | 6 | 360 | |||||||
| Person normally responsible is out of work that day. | |||||||||
| 2 | 120 | ||||||||
| Bread is forgotten in an oven left on overnight. | Fire | 10 | Put bread in oven without setting the timer. | Checklist for store closing procedures. | 6 | ||||
| 1 | 60 | ||||||||
| Got busy and forgot to shut oven off after the timer went off. | |||||||||
| 6 | 360 | ||||||||
| Person normally responsible is out of work that day. | |||||||||
| 2 | 120 | ||||||||
| AboveWay Sandwich, Inc. Customer Satisfaction Improvement | |||||||||
| Deliverable: | Failure Modes and Effects Analysis (FMEA) | ||||||||
| Student last name: | Your last name here | ||||||||
| Based on FMEA philosophy, which failure mode (be specific) should your Black Belt focus on first and why? | Type your recommendation here. (Use at most 25 words.) | ||||||||
Value Stream Map
| We're getting there… | ||||||||||||||||||||
| The team has made some major strides as can be seen below, but improvement was still needed, so the team created a Value Stream Map. You do not need to do anything with this spaghetti diagrams below - they are there simply for your entertainment! But, you have to admit, the one on the right is a whole lot more streamlined! (Smile!) | ||||||||||||||||||||
| Original spaghetti diagram | Spaghetti diagram after realignment | |||||||||||||||||||
| Objective: | ||||||||||||||||||||
| The team decided to create a Value Stream Map to better understand the material and information flow of the process. The team observed the process for one hour and documented their findings on the VSM shown below. The wait time between process steps varied with the number of customers in line, which ranged from 1 to 5 throughout the one-hour observation period. The team wants to better understand the elements of their process and identify any opportunities to reduce the lead time. To help create the future state map, the team will also need to calculate the takt time. | ||||||||||||||||||||
| Instructions for you: | ||||||||||||||||||||
| We want to make sure you can analyze a value stream map (VSM). Please refer to the VSM | ||||||||||||||||||||
| provided below and answer the following questions. | ||||||||||||||||||||
| 1. What is the total lead time (min and max values)? | Help! : Instructor: The minimum lead time (process time + wait time) represents the wait time experienced if there was one customer (minimum inventory between process steps). The maximum lead time would be the time it would take if there were many customers or a large order (maximum inventory between process steps). |
|||||||||||||||||||
| 2. What is the total processing time? | Help! : Instructor: The sum of all of the cycle times for all PROCESS steps. |
|||||||||||||||||||
| 3. What is the Process Cycle Efficiency (PCE)? | Help! : Instructor: PCE = Value Add Time / Total Lead Time |
|||||||||||||||||||
| 4. What is the takt time (sec) based on the information provided? | Help! : Instructor: Takt Time = Available Time / Customer Demand |
|||||||||||||||||||
| Data: | ||||||||||||||||||||
| AboveWay Sandwich, Inc. Customer Satisfaction Improvement | ||||||||||||||||||||
| Deliverable: | Value Stream Map | |||||||||||||||||||
| Student last name: | Your last name here | |||||||||||||||||||
| 1a) What is the total lead time (min value)? | Type here | Check daniel-munson: Instructor: You should get a value between 100 and 200 seconds. |
||||||||||||||||||
| 1b) What is the total lead time (max value)? | Type here | Check daniel-munson: Instructor: You should get a value between 300 and 400 seconds. |
||||||||||||||||||
| 2) What is the total processing time? | Type here | Check daniel-munson: Instructor: You should get a value between 0 and 100 seconds. |
||||||||||||||||||
|
: Instructor: The minimum lead time (process time + wait time) represents the wait time experienced if there was one customer (minimum inventory between process steps). The maximum lead time would be the time it would take if there were many customers or a large order (maximum inventory between process steps). |
: Instructor: The sum of all of the cycle times for all PROCESS steps. |
: Instructor: PCE = Value Add Time / Total Lead Time | 3) What is the Process Cycle Efficiency (PCE) for the maximum lead time? | |||||||||||||||||
| Type here | Check daniel-munson: Instructor: You should get a value between 0 and 25%. |
|||||||||||||||||||
|
: Instructor: Takt Time = Available Time / Customer Demand | 4) What is the takt time (T/T)? | Type here | Check daniel-munson: Instructor: You should get a value between 1 and 60 seconds. |
|||||||||||||||||
| 5) Which process step should your Black Belt focus on first to improve the process and why? HINT: There are 2 very good reasons for the correct first step based on VSM philosophy and the calculations made in this study - use them both in your answer! | ||||||||||||||||||||
| Type your recommendation here. (Use at most 50 words.) | ||||||||||||||||||||
Control Chart
| Objective: | |||||||||||
| Your Black Belts developed the following data and created this control chart for sandwich preparation times. | |||||||||||
| They say that the process is stable and shows that the average prep time is 1.7512 minutes and that | |||||||||||
| individual prep times will not exceed 1.9205 minutes. They also say that it's very good because it shows very little variation. | |||||||||||
| Instructions for you: | |||||||||||
| Part I | |||||||||||
| Do you agree with their interpretation? (see chart below) HINT: Use the 11 Shewhart Rules for Stability as the basis for your decision. | |||||||||||
| Data: | |||||||||||
| Current Sandwich Prep | |||||||||||
| Times (in minutes) | |||||||||||
| 1.6434 | |||||||||||
| 1.8623 | |||||||||||
| 1.7611 | |||||||||||
| 1.7049 | |||||||||||
| 1.7888 | |||||||||||
| 1.7469 | |||||||||||
| 1.8223 | |||||||||||
| 1.7793 | |||||||||||
| 1.7122 | |||||||||||
| 1.7562 | |||||||||||
| 1.6798 | |||||||||||
| 1.7013 | |||||||||||
| 1.7750 | |||||||||||
| 1.7400 | |||||||||||
| 1.8122 | |||||||||||
| 1.7778 | |||||||||||
| 1.7233 | |||||||||||
| 1.7410 | |||||||||||
| 1.7143 | |||||||||||
| 1.7807 | |||||||||||
| Part II | |||||||||||
| You and your team work to eliminate all known assignable causes from this process. You think you have | |||||||||||
| accomplished this and collect the following data on the new process. Calculate the control chart center line | |||||||||||
| and control limit values for an I-MR chart and use the 11 Shewhart Rules for Stability to determine if this process is now stable. | |||||||||||
| Revised Process Prep | |||||||||||
| Times (in minutes) | |||||||||||
| 1.536 | |||||||||||
| 1.589 | |||||||||||
| 1.482 | |||||||||||
| 1.561 | |||||||||||
| 1.547 | |||||||||||
| 1.502 | |||||||||||
| 1.506 | |||||||||||
| 1.500 | |||||||||||
| 1.550 | |||||||||||
| 1.542 | |||||||||||
| 1.432 | |||||||||||
| 1.573 | |||||||||||
| 1.592 | |||||||||||
| 1.557 | |||||||||||
| 1.502 | |||||||||||
| 1.534 | |||||||||||
| 1.482 | |||||||||||
| 1.492 | |||||||||||
| 1.600 | |||||||||||
| 1.518 | |||||||||||
| AboveWay Sandwich, Inc. Customer Satisfaction Improvement | |||||||||||
| Deliverable: | Control Chart | ||||||||||
| Student last name: | Your last name here | ||||||||||
| Do you agree with their interpretation that the original process is stable? | Type "YES" or "NO" here | ||||||||||
| If "NO", please identify BY NAME which of the 11 Shewhart Tests for Stability EACH chart fails. | I CHART: | ||||||||||
| Type here | |||||||||||
| MR CHART: | |||||||||||
| Type here | |||||||||||
| Revised Process Prep Data | |||||||||||
| INDIVIDUALS CHART: | |||||||||||
| What did you get for a center line? | Type here (use 4 decimal places) | ||||||||||
| What did you get for an upper control limit? | Type here (use 4 decimal places) | ||||||||||
| What did you get for a lower control limit? | Type here (use 4 decimal places) | ||||||||||
| Revised Process Prep Data | |||||||||||
| MOVING RANGE CHART: | |||||||||||
| What did you get for a center line? | Type here (use 4 decimal places) | ||||||||||
| What did you get for an upper control limit? | Type here (use 4 decimal places) | ||||||||||
| What did you get for a lower control limit? | Type here (use 4 decimal places) | ||||||||||
| Is the revised process stable? | Type "YES" or "NO" here | ||||||||||
| If "NO", please identify BY NAME which of the 11 Shewhart Tests for Stability EACH chart fails. | I CHART: | ||||||||||
| Type here | |||||||||||
| MR CHART: | |||||||||||
| Type here | |||||||||||
Regression
| Objective: | |||||||||||||
| A team member has been saying since day one that there is a relationship between wait time and customer | |||||||||||||
| satisfaction ratings (1 being the worst and 10 being the best). Should the team have listened? | |||||||||||||
| Perform correlation and regression analysis to see if she is correct. | |||||||||||||
| Instructions for you: | |||||||||||||
| PART 1 - Do you suspect the variables may be related? Determine the correlation coefficient as well as perform a regression analysis on the data provided to answer the questions below. | |||||||||||||
| PARTS 2 & 3 - The team suspects that the time of day may have an effect on the results, so the team decides to | |||||||||||||
| perform another analysis by analyzing the same results sorting by the time of day (lunch or dinner). | |||||||||||||
| Perform the same analysis as PART 1, however be sure to use only the data for the respective time of | |||||||||||||
| day. Perform one analysis with the 30 data points for Lunch and another analysis for the 30 data points | |||||||||||||
| for Dinner and answer the questions below. | |||||||||||||
| Data: | |||||||||||||
| Time of Day | Wait Time (minutes) | Customer Satisfaction (1-10) | |||||||||||
| Lunch | 9.5548 | 7 | Scatter diagrams in Excel 2003 Diane Johnson: Instructor: 1) Click on Chart icon on top task bar, OR Click on the INSERT menu option at the top menu bar. 2) Click on SCATTER from the Standard Types tab 3) Click Next 4) Highlight both columns of data and finish according to the directions. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing. |
||||||||||
| Lunch | 21.4950 | 3 | |||||||||||
| Lunch | 26.3915 | 1 | Scatter diagrams in Excel 2007 Diane Johnson: 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. |
||||||||||
| Lunch | 11.7057 | 6 | |||||||||||
| Lunch | 15.5191 | 5 | Correlation Coefficient - Excel 2003 Diane Johnson: Instructor: 1) Click on the fx in the top bar and CORREL, or click on INSERT, function, CORREL. 2) Highlight each column of data as an ARRAY 3) Click Okay. 4) Excel will calculate the correlation coefficient. The correlation coefficient ranges between zero and one. Zero is no correlation and '1' is a perfect correlation. __________________________________________ -1.0 to -0.7 strong negative association. -0.7 to -0.3 weak negative association. -0.3 to +0.3 little or no association. +0.3 to +0.7 weak positive association. +0.7 to +1.0 strong positive association. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing. |
||||||||||
| Lunch | 28.7761 | 1 | |||||||||||
| Lunch | 27.7146 | 1 | Correlation Coefficient - Excel 2007 Diane Johnson: Instructor: 1) Click on the fx in the top bar and CORREL, or click on FOMULAS, INSERT function, CORREL. 2) Highlight each column of data as an ARRAY 3) Click Okay. 4) Excel will calculate the correlation coefficient. The correlation coefficient ranges between zero and one. Zero is no correlation and '1' is a perfect correlation. __________________________________________ -1.0 to -0.7 strong negative association. -0.7 to -0.3 weak negative association. -0.3 to +0.3 little or no association. +0.3 to +0.7 weak positive association. +0.7 to +1.0 strong positive association. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing. |
||||||||||
| Lunch | 8.5575 | 7 | |||||||||||
| Lunch | 8.9540 | 8 | Regression Analysis - Excel 2003 Diane Johnson: Instructor: 1) To access the regression data analysis function in Excel, select the Data tab from the Tools menu above, and then choose Data Analysis. 2) Then click on Regression from the dialog box and then click OK. 3) The first box is the Input Y range. Here, we tell Excel about our dependent variable. You can highlight the dependent variable column of data, including the column label. 4) Because we have included the column Label (row 21), which includes the variable name, we are going to have to tell Excel this, by clicking on the "Labels" checkbox. If you choose not to include the Label row (which may be the case for the Part 2 - Dinner analysis), you do not need to select the "Labels" option! 5) The second box is the Input X range, where we input the independent variables. You can highlight the independent variable column of data, including the column label. 6) Then click OK 7) The first part of the output is the regression statistics followed by the ANOVA table. 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. |
||||||||||
| Lunch | 18.0349 | 4 | |||||||||||
| Lunch | 4.0550 | 9 | Regression Analysis - Excel 2007 Diane Johnson: Instructor: 1) To access the regression data analysis function in Excel, select the Data tab from the Tools menu above, and then choose Data Analysis. 2) Then click on Regression from the dialog box and then click OK. 3) The first box is the Input Y range. Here, we tell Excel about our dependent variable. You can highlight the dependent variable column of data, including the column label. 4) Because we have included the column Label (row 21), which includes the variable name, we are going to have to tell Excel this, by clicking on the "Labels" checkbox. If you choose not to include the Label row (which may be the case for the Part 2 - Dinner analysis), you do not need to select the "Labels" option! 5) The second box is the Input X range, where we input the independent variables. You can highlight the independent variable column of data, including the column label. 6) Then click OK 7) The first part of the output is the regression statistics followed by the ANOVA table. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing. |
||||||||||
|
Diane Johnson: Instructor: 1) Click on Chart icon on top task bar, OR Click on the INSERT menu option at the top menu bar. 2) Click on SCATTER from the Standard Types tab 3) Click Next 4) Highlight both columns of data and finish according to the directions. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing. |
Diane Johnson: 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. |
Diane Johnson: Instructor: 1) Click on the fx in the top bar and CORREL, or click on INSERT, function, CORREL. 2) Highlight each column of data as an ARRAY 3) Click Okay. 4) Excel will calculate the correlation coefficient. The correlation coefficient ranges between zero and one. Zero is no correlation and '1' is a perfect correlation. __________________________________________ -1.0 to -0.7 strong negative association. -0.7 to -0.3 weak negative association. -0.3 to +0.3 little or no association. +0.3 to +0.7 weak positive association. +0.7 to +1.0 strong positive association. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing. |
Diane Johnson: Instructor: 1) Click on the fx in the top bar and CORREL, or click on FOMULAS, INSERT function, CORREL. 2) Highlight each column of data as an ARRAY 3) Click Okay. 4) Excel will calculate the correlation coefficient. The correlation coefficient ranges between zero and one. Zero is no correlation and '1' is a perfect correlation. __________________________________________ -1.0 to -0.7 strong negative association. -0.7 to -0.3 weak negative association. -0.3 to +0.3 little or no association. +0.3 to +0.7 weak positive association. +0.7 to +1.0 strong positive association. If you would like to print this tip, right click on the cell and select EDIT COMMENT. Then just highlight and copy the text, and paste in a document for printing. |
Diane Johnson: Instructor: 1) To access the regression data analysis function in Excel, select the Data tab from the Tools menu above, and then choose Data Analysis. 2) Then click on Regression from the dialog box and then click OK. 3) The first box is the Input Y range. Here, we tell Excel about our dependent variable. You can highlight the dependent variable column of data, including the column label. 4) Because we have included the column Label (row 21), which includes the variable name, we are going to have to tell Excel this, by clicking on the "Labels" checkbox. If you choose not to include the Label row (which may be the case for the Part 2 - Dinner analysis), you do not need to select the "Labels" option! 5) The second box is the Input X range, where we input the independent variables. You can highlight the independent variable column of data, including the column label. 6) Then click OK 7) The first part of the output is the regression statistics followed by the ANOVA table. 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. | Lunch | 20.5527 | 4 | ||||||
| Lunch | 8.2985 | 8 | |||||||||||
| Lunch | 23.3592 | 3 | |||||||||||
| Lunch | 14.4198 | 6 | |||||||||||
| Lunch | 18.0643 | 3 | |||||||||||
| Lunch | 11.9445 | 6 | |||||||||||
| Lunch | 11.4930 | 7 | |||||||||||
| Lunch | 14.1029 | 4 | |||||||||||
| Lunch | 12.5650 | 6 | |||||||||||
| Lunch | 15.8737 | 5 | |||||||||||
| Lunch | 17.7516 | 4 | |||||||||||
| Lunch | 13.4854 | 7 | |||||||||||
| Lunch | 13.5948 | 6 | |||||||||||
| Lunch | 7.1309 | 5 | |||||||||||
| Lunch | 5.4951 | 8 | |||||||||||
| Lunch | 25.4996 | 2 | |||||||||||
| Lunch | 9.6127 | 7 | |||||||||||
| Lunch | 12.8767 | 5 | |||||||||||
| Lunch | 26.2330 | 3 | |||||||||||
| Dinner | 18.8415 | 5 | |||||||||||
| Dinner | 16.9362 | 3 | |||||||||||
| Dinner | 5.1428 | 5 | |||||||||||
| Dinner | 21.6323 | 3 | |||||||||||
| Dinner | 2.0000 | 4 | |||||||||||
| Dinner | 16.4363 | 5 | |||||||||||
| Dinner | 22.7496 | 6 | |||||||||||
| Dinner | 19.4474 | 1 | |||||||||||
| Dinner | 19.4078 | 4 | |||||||||||
| Dinner | 12.4674 | 2 | |||||||||||
| Dinner | 13.6108 | 3 | |||||||||||
| Dinner | 16.0315 | 6 | |||||||||||
| Dinner | 26.4012 | 5 | |||||||||||
| Dinner | 11.1324 | 2 | |||||||||||
| Dinner | 29.9518 | 6 | |||||||||||
| Dinner | 8.3968 | 3 | |||||||||||
| Dinner | 26.1407 | 5 | |||||||||||
| Dinner | 5.3498 | 3 | |||||||||||
| Dinner | 6.7547 | 5 | |||||||||||
| Dinner | 18.9429 | 4 | |||||||||||
| Dinner | 19.3040 | 3 | |||||||||||
| Dinner | 2.0946 | 6 | |||||||||||
| Dinner | 19.7982 | 4 | |||||||||||
| Dinner | 14.4754 | 5 | |||||||||||
| Dinner | 19.3599 | 3 | |||||||||||
| Dinner | 26.0793 | 4 | |||||||||||
| Dinner | 6.9378 | 7 | |||||||||||
| Dinner | 29.2652 | 5 | |||||||||||
| Dinner | 25.4606 | 2 | |||||||||||
| Dinner | 28.4090 | 3 | |||||||||||
| YOU DO NOT NEED TO SUBMIT THE SCATTER DIAGRAM | |||||||||||||
| AboveWay Sandwich, Inc. Customer Satisfaction Improvement | |||||||||||||
| Deliverable: | Scatter Diagram & Regression Analysis | ||||||||||||
| Student last name: | Your last name here | ||||||||||||
| PART 1: Using Lunch & Dinner Data | |||||||||||||
| What is the correlation coefficient? | Type here (use 3 decimal places) | ||||||||||||
| What is the regression equation? | Type here (use 3 decimal places) | ||||||||||||
| What percentage of variation is explained by the model? HINT: There are 2 related values that can be used to answer this question. Show them BOTH and explain which value you would recommend using and why. | Type your answer here. (Use at most 25 words.) | ||||||||||||
| Based on an ANOVA of the regression equation, is the regression equation statistically significant at 5% alpha risk? | Type "YES" or "NO" here | ||||||||||||
| Why do you you say that it is either statistically significant or not? HINT: Use the results of your ANOVA as the basis for your answer. | Type your answer here. (Use at most 25 words.) | ||||||||||||
| What is the null hypothesis for ANOVA for a regression equation? | |||||||||||||
| Type your answer here. (Use at most 15 words.) | |||||||||||||
| PART 2: Using Only Lunch Data | |||||||||||||
| What is the correlation coefficient for Lunch? | Type here (use 3 decimal places) | ||||||||||||
| What is the regression equation for Lunch? | Type here (use 3 decimal places) | ||||||||||||
| What percentage of variation is explained by the Lunch model? HINT: There are 2 related values that can be used to answer this question. Show them BOTH and explain which value you would recommend using and why. | Type your answer here. (Use at most 25 words.) | ||||||||||||
| Based on an ANOVA of the regression equation, is the regression equation statistically significant at 5% alpha risk? | Type "YES" or "NO" here | ||||||||||||
| Why do you you say that it is either statistically significant or not? HINT: Use the results of your ANOVA as the basis for your answer. | Type your answer here. (Use at most 25 words.) | ||||||||||||
| Given the results of this study, what should your Black Belt do next to improve customer satisfaction at lunch? | Type your answer here. (Use at most 25 words.) | ||||||||||||
| PART 3: Using Only Dinner Data | |||||||||||||
| What is the correlation coefficient for Dinner? | Type here (use 3 decimal places) | ||||||||||||
| What is the regression equation for Dinner? | Type here (use 3 decimal places) | ||||||||||||
| What percentage of variation is explained by the Dinner model? HINT: There are 2 related values that can be used to answer this question. Show them BOTH and explain which value you would recommend using and why. | Type your answer here. (Use at most 25 words.) | ||||||||||||
| Based on an ANOVA of the regression equation, is the regression equation statistically significant at 5% alpha risk? | Type "YES" or "NO" here | ||||||||||||
| Why do you you say that it is either statistically significant or not? HINT: Use the results of your ANOVA as the basis for your answer. | Type your answer here. (Use at most 25 words.) | ||||||||||||
| Given the results of this study, what should your Black Belt do next to improve customer satisfaction at dinner? | Type your answer here. (Use at most 25 words.) | ||||||||||||
Process Capability
| Objective: | |||||||||||||||||||
| Determine process capability | |||||||||||||||||||
| Instructions for you: | |||||||||||||||||||
| The company's goal is that every sandwich can be completed in less than 1.5 minutes. Based on the data | |||||||||||||||||||
| from the revised process control chart study, evaluate our shop's ability to meet this goal using the following | |||||||||||||||||||
| Evaluate our shop's ability to meet this goal using the following cabability measures. | |||||||||||||||||||
| Cp | |||||||||||||||||||
| Cpk | |||||||||||||||||||
| Pp | |||||||||||||||||||
| Ppk | |||||||||||||||||||
| z score | |||||||||||||||||||
| Sigma Level | |||||||||||||||||||
| % of sandwiches made within the 1.5 minute goal | |||||||||||||||||||
| PPM beyond the 1.5 minute goal | |||||||||||||||||||
| Data: See the revised data from the control chart tab. | |||||||||||||||||||
| AboveWay Sandwich, Inc. Customer Satisfaction Improvement | |||||||||||||||||||
| Deliverable: | Process Capability | ||||||||||||||||||
| Student last name: | Your last name here | ||||||||||||||||||
| What did you get for Cp? | Type here | Self check daniel-munson: Instructor: Hint: If you got a value, I would like to see it. |
|||||||||||||||||
| What did you get for Cpk? | Type here (use 2 decimal places) | Self check daniel-munson: Instructor: You should get a value between -2 and +2 |
|||||||||||||||||
| What, specifically, does this particular Cpk tell you about the process that other Cpk's may not? | Type here (use at most 25 words) | ||||||||||||||||||
| What did you get for Pp? | Type here | Self check daniel-munson: Instructor: Hint: If you got a value, I would like to see it. |
|||||||||||||||||
| What did you get for Ppk? | Type here (use 2 decimal places) | Self check daniel-munson: Instructor: You should get a value between -3 and +3 |
|||||||||||||||||
| What did you get for Z-score? | Type here (use 2 decimal places) | Self check daniel-munson: Instructor: You should get a value between -2 to +2 |
|||||||||||||||||
| What does this Z-score mean? HINT: Use the z score you calculated in a sentence that explains what it tells you about this process. | Type here (use at most 25 words) | ||||||||||||||||||
| Using the z score calculated above and a z table, estimate the % of sandwiches that will be made within the 1.5 minute goal. | Type here (use 2 decimal places) | Self check daniel-munson: Instructor: You should get a value <30% |
|||||||||||||||||
|
daniel-munson: Instructor: Hint: If you got a value, I would like to see it. | PPM (aka DPMO) beyond the 1.5 minute goal | Type here | Self check daniel-munson: Instructor: You should have gotten a really big number. Also, this number should be "consistent" with the % within goal found above. |
||||||||||||||||
|
daniel-munson: Instructor: You should get a value between -3 and +3 |
daniel-munson: Instructor: You should get a value between -2 to +2 |
Questions
| Objective: | ||||||||||
| An enthusiastic Black Belt comes to you and states that next Friday morning at 10:15 AM the Project Champion is going to collect 20 temperature measurements inside one of the new Suntouch conveyor ovens at Store 243 to see if these ovens perform the same as the older model currently in use at other stores. She says, "We will be running Minitab to crunch the numbers." You say to her, "Before you do this, I have some questions to ask you about your strategy for this study." She tells you that she only has a few minutes before a meeting with the Champion to discuss the data collection, so you need to make your questions count! | ||||||||||
| Instructions for you: | ||||||||||
| (1) List 10 questions that you think are the most important questions to ask to ensure that your Black Belt has a good stragegy in place for this study and has addressed all important issues. Provide a brief (less than 15 word) rationale for each question. NOTE: Follow-up questions for clarification do NOT count as separate questions. (2) Identify 5 tools that could be used to help answer the question posed by your Black Belt in this study. Justify your choice of each tool in 15 words or less. | ||||||||||
| AboveWay Sandwich, Inc. Customer Satisfaction Improvement | ||||||||||
| Deliverable: | Questions | |||||||||
| Student last name: | Your last name here | |||||||||
| An enthusiastic Black Belt comes to you and states that next Friday morning at 10:15 AM the Project Champion is going to collect 20 temperature measurements inside one of the new Suntouch conveyor ovens at Store 243 to see if these ovens perform the same as the older model currently in use at other stores. She says, "We will be running Minitab to crunch the numbers." You say to her, "Before you do this, I have some questions to ask you about your strategy for this study." She tells you that she only has a few minutes before a meeting with the Champion to discuss the data collection, so you need to make your questions count! | ||||||||||
| Question 1 | Type the question here. | |||||||||
| Eplain your rationale for this question in 15 words or less. | ||||||||||
| Question 2 | Type the question here. | |||||||||
| Eplain your rationale for this question in 15 words or less. | ||||||||||
| Question 3 | Type the question here. | |||||||||
| Eplain your rationale for this question in 15 words or less. | ||||||||||
| Question 4 | Type the question here. | |||||||||
| Eplain your rationale for this question in 15 words or less. | ||||||||||
| Question 5 | Type the question here. | |||||||||
| Eplain your rationale for this question in 15 words or less. | ||||||||||
| Question 6 | Type the question here. | |||||||||
| Eplain your rationale for this question in 15 words or less. | ||||||||||
| Question 7 | Type the question here. | |||||||||
| Eplain your rationale for this question in 15 words or less. | ||||||||||
| Question 8 | Type the question here. | |||||||||
| Eplain your rationale for this question in 15 words or less. | ||||||||||
| Question 9 | Type the question here. | |||||||||
| Eplain your rationale for this question in 15 words or less. | ||||||||||
| Question 10 | Type the question here. | |||||||||
| Eplain your rationale for this question in 15 words or less. | ||||||||||
| List five tools your Black Belt might use to answer her question in this study | Type here | and why? | Type here (use at most 15 words) | |||||||
| and give a brief (10 words | Type here | and why? | Type here (use at most 15 words) | |||||||
| or less) explanation of why each tool would be useful in this particular study. HINT: Each tool listed must be appropriate for THIS particular study. Do NOT go beyond the scope of the question being investigated by your Black Belt! | ||||||||||
| Type here | and why? | Type here (use at most 15 words) | ||||||||
| Type here | and why? | Type here (use at most 15 words) | ||||||||
| Type here | and why? | Type here (use at most 15 words) | ||||||||
|
: : |
191715131197531
1.9
1.8
1.7
1.6
Observation
I
n
d
i
v
i
d
u
a
l
V
a
l
u
e
_
X=1.7512
UCL=1.9205
LCL=1.5818
191715131197531
0.20
0.15
0.10
0.05
0.00
Observation
M
o
v
i
n
g
R
a
n
g
e
__
MR=0.0637
UCL=0.2080
LCL=0
1
I-MR Chart of Current Sandwich Prep Times
