The full scenario and instructions can be found in the Course Project Scenario Download Course Project Scenario document. Your control chart should be completed in the Northside Project – Control Chart Download Northside Project – Control Chart template and uploaded below.
Project Scenario overview: https://drive.google.com/file/d/1reV8CBRRmPTuzODslbpiNCyEZp9a1TRD/view?usp=drive_link Links to an external site.
Instructions
analyze medallion.png
For this assignment, produce Control Charts (described below) that will display the sample wait time data in a practical way.
Detailed instructions for using the Descriptive Statistics add-in and Excel formulas are provided in the project instructional videos (see below). Proper formatting of the charts is also provided in the videos. The assignment is also available in the Northside Project Student Assignments Download Northside Project Student Assignments Excel file and the “Control Chart Assignment” tab. You can complete your assignment in the separate Northside Project – Control Chart Download Northside Project – Control Chart template.
Further technical information about this assignment can be found in the Course Project Scenario Download Course Project Scenario document.
X-Bar Chart
Information regarding formulas and specific cell entries are as follows:
Avg (Column F): Cell F2 =AVERAGE(B2:E2) Copy this formula to the end of the table (F31)
Grand Mean: Cell F22 =AVERAGE(F2:F21)
Std Dev: Cell F23 =STDEV.S(F2:F21)
NOTE: STDEV.S is used to calculate the standard deviation of a sample; STDEV.P is used for calculating the standard deviation of a population.
IM Charts
The first IM chart UCL/LCL is auto-calculated and the formula is present in the spreadsheet. Pay close attention to where you paste the Descriptive Statistics output in the indicated cells.
The second IM chart Specs are in the project scenario. The mean is 40, the UCL is 60, and the LCL is 20. These need to be copied into all cells for that chart.
The UCL and LCL are calculated using data set 1 as a population. They will provide a more reliable assessment on the manufacturing. To calculate UCL and LCL on a population, you simply add and subtract the mean of each subgroup to 3*population std dev. The spreadsheet should already have the formulas entered; however, it may be necessary to enter them yourself. To get these values, copy the following formulas into the appropriate cells.
These formulas will also be used in “control chart post CPI” – see “OPTIONAL” below.
*** NOTE: Watch the Project video below to see how to enter the formulas and create/format the charts. ***
Create a line chart using the subgroups averages, mean, UCL, and LCL (columns F-I). This will be the X-bar chart for the subgroup. Follow the format instructions in the chart location boxes on the spreadsheet.
The IM charts are columns K-R. You MUST stack the data as instructed on the project document. You must then do descriptive stats on the stacked column and place the results in the area. Make sure you follow the placement help for the mean and stddev!!
The first IM chart is plotted against the natural process mean (K-N) and will use the desc stats for the Create a line chart using the stacked data, process mean, UCL, and LCL just like the x-bar and follow the formatting directions.
Copy the stacked data into column “O” and enter the SPECIFICATION limits into the appropriate cells. Make sure you copy these data for the entire stacked column length!!! Create another IM chart based on these data (O-R).
For all charts, you must provide an analysis. Be as detailed and thorough as possible with the information you’ve been given/learned, but not lengthy. Do your best.
OPTIONAL:
After the analysis of the control chart assignment, you may decide to see an “after” effect of a CPI project to the process. The “after CPI” data is provided in the “control chart post CPI” tab. Follow the instructions for the formulas, data stacking, descriptive statistics, and chart formatting to see the differences. THIS IS OPTIONAL.
How-To Videos
Video #1 Intro to DescStats Formulas
https://drive.google.com/file/d/1lqP99a9AVy_1ja9oi3F6eb_oUWHmkj_4/view?usp=drive_link Links to an external site.
Video #2 Intro to Stacked Data Descriptive Stats
https://drive.google.com/file/d/1Zjnndeqqzgz8yfZ1wQ5sDR8hx__BPQrT/view?usp=drive_link Links to an external site.
Video #3 Intro to Control Chart Formats
https://drive.google.com/file/d/1olQM8cXOYe-NvgJcaVeapySKAh2O9-Z-/view?usp=drive_link Links to an external site.
Video #4 Intro to Control Chart Analysis
https://drive.google.com/file/d/1PyJCfRREIjy8nocM4xx15g78jLED-OEq/view?usp=drive_link Links to an external site.
Video #5 Intro to Control Chart Comparison
https://drive.google.com/file/d/1N8F2ouuWduIw87E6VtZy7_jq4flI7lLD/view?usp=drive_link Links to an external site.
Tips
For a more detailed discussion of the scenario and technical specifications for the Pareto Chart, Histogram, and Control Charts, see the Course Project Scenario Download Course Project Scenario document.
The assignment should be completed in the Northside Project – Control Chart Download Northside Project – Control Chart template.