Six Sigma (Process Capability)
runningman1106
six_sigma_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: | |||||
| 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) | Cost of doing business | ||||
| Scrapped Meat and Vegetables (damaged during food prep) | Internal failure | ||||
| Incoming Inspection (of ingredients) | Appraisal | ||||
| Six Sigma Projects | Prevention | ||||
| Obsolete / expired food | Internal failure | ||||
| Training for counter help | Prevention | ||||
| Corporate audits to verify store compliance with policies | Prevention | ||||
| Procedures for counter help | Prevention | ||||
| Electricity | Cost of doing business | ||||
| Fines from Health Inspectors | External failure | ||||
| Rent | Cost of doing business | ||||
| Scrapped Burned bread | Internal failure | ||||
| Cost to pre-package meats in individual serving portions | Prevention | ||||
| Free meals given to customers with complaints | External failure | ||||
| Supervisor sign-off/approval for credit card sales | Appraisal | ||||
| Business Bureau membership | Cost of doing business | ||||
| Training for incoming inspection | Appraisal | ||||
| Loss of business due to customer dissatisfaction | External failure | ||||
| 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? | 78,916.00 : Your answer should be between $78,000 and $81,000. |
||||
| What is the total internal failures cost? | $4,062.00 : Your answer should be between $3,000 and $5,500. |
||||
| What is the total external failures cost? | $8,951.00 : Your answer should be between $8,000 and $11,000. |
||||
| What is the total appraisal cost? | $4,416.00 : Your answer should be between $8,000 and $11,000. |
||||
| What is the total prevention cost? | $8,177.00 : Your answer should be between $3,000 and $5,000. |
||||
| What is the total cost of quality? | $25,606.00 : 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? | Costs of external failure are very high. Represents a failure of the external control systems as quality control. Measures to reduce the external costs should be put into place. Quality and following regulations should be the area of focus. External focus on the customers should also be considred. Customers are not very satisfied and a result loss of revenue. | ||||
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? | Bread forgotten in an oven left on overnight. Has the most severe potential effects of failure. The rating of severity of effects is also the maximum | ||||||||
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: | ||||||||||||||||||||
| s | ||||||||||||||||||||
| 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)? | 103 | Check daniel-munson: Instructor: You should get a value between 100 and 200 seconds. |
||||||||||||||||||
| 1b) What is the total lead time (max value)? | 399 | Check daniel-munson: Instructor: You should get a value between 300 and 400 seconds. |
||||||||||||||||||
| 2) What is the total processing time? | 89 | 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? | |||||||||||||||||
| 0.13 | 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)? | 30 | 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! | ||||||||||||||||||||
| Wrap and bag sub & payment. The process has the highest seconds of non-value add time. VSM is based on the elimination of all non-value adding activities, improving efficiency and cost reduction. By focusing on it, ways of reducing the non-value time will be proposed. | ||||||||||||||||||||
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 | |||||||||||
| Control Chart assignment completed with one comment: your values are off just a bit. Watch your rounding (use Excel cell references) | |||||||||||
| 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? | YES | ||||||||||
| 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? | 1.5300 | ||||||||||
| What did you get for an upper control limit? | 1.6661 | ||||||||||
| What did you get for a lower control limit? | 1.3939 | ||||||||||
| Revised Process Prep Data | |||||||||||
| MOVING RANGE CHART: | |||||||||||
| What did you get for a center line? | 0.0511 | ||||||||||
| What did you get for an upper control limit? | 0.1672 | ||||||||||
| What did you get for a lower control limit? | 0.0000 | ||||||||||
| Is the revised process stable? | Yes | ||||||||||
| 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 | |||||||||||
| Control Chart assignment completed with one comment: your values are off just a bit. Watch your rounding (use Excel cell references) | |||||||||||
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? | -0.530 | ||||||||||||
| What is the regression equation? | 6.748 - 0.135x | ||||||||||||
| 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. | Multiple R (0.5299) and R squared (0.2808). R squared would be the best to use as it explains the percentage of variations by the model. | ||||||||||||
| Based on an ANOVA of the regression equation, is the regression equation statistically significant at 5% alpha risk? | YES | ||||||||||||
| 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. | Under null hypothesis, F calculated is 22.649. F tabulated from f(1,59) is 3.150. There is significant evidence to reject null hypothesis. | ||||||||||||
| What is the null hypothesis for ANOVA for a regression equation? | |||||||||||||
| There is no relationship between Wait Time and Customer Relationship | |||||||||||||
| PART 2: Using Only Lunch Data | |||||||||||||
| What is the correlation coefficient for Lunch? | -0.933 | ||||||||||||
| What is the regression equation for Lunch? | 9.697 -0.008x | ||||||||||||
| 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. | Multiple R (0.9334) and R squared (0.8713). R squared would be the best to use as it explains the percentage of variations by the model. | ||||||||||||
| Based on an ANOVA of the regression equation, is the regression equation statistically significant at 5% alpha risk? | Yes | ||||||||||||
| 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. | Under null hypothesis, F calculated is 189.556. F tabulated from f(1,28) is 4.196. There is significant evidence to reject null hypothesis. | ||||||||||||
| Given the results of this study, what should your Black Belt do next to improve customer satisfaction at lunch? | Should consider reducing the wait time for customers at lunch hour. | ||||||||||||
| PART 3: Using Only Dinner Data | |||||||||||||
| What is the correlation coefficient for Dinner? | 0.041 | ||||||||||||
| What is the regression equation for Dinner? | 4.214 - 0.302 x | ||||||||||||
| 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. | Multiple R (0.0413) and R squared (0.0017). R squared would be the best to use as it explains the percentage of variations by the model. | ||||||||||||
| Based on an ANOVA of the regression equation, is the regression equation statistically significant at 5% alpha risk? | No | ||||||||||||
| 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. | Under null hypothesis, F calculated is 0.01478. F tabulated from f(1,28) is 4.196. There is significant evidence to accept null hypothesis. | ||||||||||||
| Given the results of this study, what should your Black Belt do next to improve customer satisfaction at dinner? | Since the Regression Analysis shows that "Wait Time" is NOT a good predictor of "Customer Satisfaction" at Dinner, then we should look for other independent variables that might be. | ||||||||||||
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? | Not Applicable | Self check daniel-munson: Instructor: Hint: If you got a value, I would like to see it. |
|||||||||||||||||
| What did you get for Cpk? | 0.93/0.04 | 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? | Shows that there is a variability of 7%. We are 93% close to achieving our target.//////////////////////Cpk is computed using the process standard deviation and only the USL | ||||||||||||||||||
| What did you get for Pp? | Not applicable | Self check daniel-munson: Instructor: Hint: If you got a value, I would like to see it. |
|||||||||||||||||
| What did you get for Ppk? | 1/1.6 | Self check daniel-munson: Instructor: You should get a value between -3 and +3 |
|||||||||||||||||
| What did you get for Z-score? | 0.0009/1.53 | 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. | Explains how the random variables of temp readings are normally distributed within the sample, assuming a mean of 1 and a variance of 0. | ||||||||||||||||||
| Using the z score calculated above and a z table, estimate the % of sandwiches that will be made within the 1.5 minute goal. | 50.4/0.03 | 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 | 49.6/5473 | 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 | What is the range of temperatures? | |||||||||
| To gauge the min and max values of temp recorded within the new oven. | ||||||||||
| Question 2 | What is the variability of the temperatures? | |||||||||
| How often do the temperatures vary within the range. | ||||||||||
| Question 3 | What are the potential failure mode? | |||||||||
| Some of the modes the through which the new oven can fail. | ||||||||||
| Question 4 | What are the effects of the potential failure? | |||||||||
| The effects to the organisation of having the oven failuring. | ||||||||||
| Question 5 | What is the likely severity effect of a failure of the new oven? | |||||||||
| The severity effect of a failure of the new oven. | ||||||||||
| Question 6 | What are some of the potential causes of the failure? | |||||||||
| Examines some of the potential causes of a failure of the new oven. | ||||||||||
| Question 7 | What is the probability of a failure of a new oven? | |||||||||
| Examines the likelihood of a failure of the new oven. | ||||||||||
| Question 8 | What is the probability of detection of a failure/temp going beyond the range? | |||||||||
| Examines the probability of detection of a failure or temperatures going beyond normal range. | ||||||||||
| Question 9 | What is the probability of detection in time? | |||||||||
| Examines the likelihood of detection and correction before the failure becomes severe. | ||||||||||
| Question 10 | What are some of the control systems in place? | |||||||||
| Examines the number of control systems put in place to detect and correct a failure. | ||||||||||
| List five tools your Black Belt might use to answer her question in this study | Temperature recordings | and why? | Recodings would be useful in assessing temp range and variability. | |||||||
| and give a brief (10 words | Manual Instructions | and why? | Would be useful in evaluating the control systems. | |||||||
| 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! | ||||||||||
| Workers' views and opinions | and why? | Would be useful in assessing the causes of failure and its probability. | ||||||||
| Supervisors' reports | and why? | Would be very critical in analysing the probability of failure and control systems. | ||||||||
| Own observations | and why? | Crucial in proving a true and fair opinion and comparing with the failure reports. | ||||||||
|
: : |
Sheet1
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
