1

QMTH 206 Lab 2: Hypothesis Testing and Simple Linear Regression

Due: Monday, July 7, 2014at the beginning of class

Purpose: Students will learn (1) use Excel formulas to build contingency tables for the Chi-Square test of independence; (2) use Excel built-in data analysis procedures to perform one-way ANOVA test; (3) use Excel built-in data analysis procedures to perform regression analysis and correlation analysis; and (4) make quality control charts. You have to work individually. Please turn in your printouts and a CD with file lab2.xls. Email attachments will not be accepted. Penalty is possible for late work. Download file lab2.xls from my Web site save it to your computer, open it with Excel and type your name. After you complete the assignment, save lab2.xls to your CD.

I. Use Excel Formulas to Build Contingency Table for Chi-Square Test of Independence

First, you use Excel “PivotTable and PivotChart Report” procedure to create a frequency contingency table (PivotTable) for observed frequencies. Second, create a formula and copy it to make the expected frequency table. Third, after you complete the observed frequency and expected frequency tables, you enter Excel function CHITEST(observed frequencies, expected frequencies) to compute the p-value. Finally, report H0, Ha, , degrees of freedom, p-value and conclusion in this worksheet.

Create a contingency table for Party (row variable) and Opinion(column variable, 1 for “Yes” and 2 for “No”) in the existing worksheet (“Contingency Table”). Please pay attention to demonstration in class.

  1. On the Insert tab, in the Tables group, click PivotTable, and then choosePivotTable. To use this function, don’t add any special characters in the file name, such as space, paranthesis, and so on.
  2. Select data range (in Worksheet “Survey Data,” numbers in 3 columns, and labels (Observation, Party, and Opinion), and Enter key.
  3. Select Existing Worksheet, click the selection button to select the cell “Count of Observation” below “Observed Frequencies” in Worksheet “Contingency Table,” Enter key, and OK.
  4. If you see the following table:

Right-click the table and choose PrivotTable Options. Then, click Display tab, check Classical PivotTable Layout, and OK. You see the following layout.

  1. Set variables: Drag the row variable to “Drop Row Fields Here”. Drag the column variable to “Drop Column Fields Here”. Drag the field you count (“Observation” in this exercise) to “Drop Data Items Here”.
  2. Make sure it’s “Count of Observation”. If not, click the arrow under “∑ Values”, choose Value Field Settings, and select Count. and OK.
  3. Copy and paste (Paste Special) the observed frequencies to the same table as where it was. First, to select the entire table, click the cell at the upper left corner of the table and hold shift key, then click the cell at the lower right corner and Copy. Click the arrow below Paste button, choose command Paste Special, and then choose Values. Instead of Paste, this command keeps you from inconvenience when making the formula for the expected frequency table.
  4. Complete the expected frequency table: Create one formula and correctlycopy it to the entire table. Make sure you use the absolute reference and the relative reference correctly.
  5. Enter the number of degrees of freedom.
  6. The p-value for the test of independence. Use Excel function CHISQ.TEST(observed frequencies, expected frequencies) or CHITEST for older version.
  7. Also, report H0, Ha, , and conclusion in this worksheet.

II. Use Data Analysis Procedure “ANOVA: Single Factor” to Perform One-way ANOVA Test

In this part, you solve the problem in worksheet “ANOVA”. You use the Excel data analysis procedure “ANOVA: Single Factor” (refer to p.555 in textbook) to complete this one-way ANOVA test. Click Data tab (Tools menu in older versions), select Data Analysis, choose “ANOVA: Single Factor” and run this procedure. Select the output range right below the cell “Excel Output”. Include labels (Manufactuerer 1, Manufacturer 2, and Manufacturer 3) in Excel output. Enter H0, Ha, , read the test statistic value (F value), the numerator degrees of freedom, the denominator degrees of freedom, the critical value, the p-value from the output, and enter your conclusion.

III. Use Data Analysis Procedure “Regression” for All-Possible-Model Approach

(I) Regression Model 1 (Worksheet Regression1)

1. Set up a scatter diagram for variablesIncome (X) and Amount Charged (Y) and answer a question right below the scatter diagram.

Make a scatter diagram. Your chart has to meet the following requirements:

(1)Use the first subtype of Scatter chart as chart type.

(2)Use Regression Model 1 as chart title.

(3)Enter Income ($1,000s)for X axis title and Amount Charged ($100s)for Y axis title.

(4)Turn off legend.

(5)Move and resize the chart to fit the range of A28:E43 in Worksheet “Regression1”.

Based on your opinion about the diagram, type your answer (Yes or No) to the question right below the diagram.

2. Fit data with a simple linear regression model using Excel procedure "Regression".

Run data analysis procedure “Regression” (refer to p.638 of textbook). Click Data tab (Tools menu in older versions), select Data Analysis, choose “Regression” and run this procedure. Select sample data of variable Y for “Input Y Range” and sample data of variable X for “Input X Range”. Include labels (“Income ($1000s)” and “Amount Charged ($100s)”) in Excel output. Check “OutputRange” radio button and select the cell right below the “Excel Output” for output range. Anwser Questions (1) to (6) in the worksheet.

(II) Regression Model 2 (Worksheet Regression2)

1. Set up a scatter diagram for variables Income (X) and Amount Charged (Y) and answer a question right below the scatter diagram.

Make a scatter diagram. Your chart has to meet the following requirements:

(1)Use the first subtype of Scatter chart as chart type.

(2)Use Regression Model 2 as chart title.

(3)Enter Household Sizefor X axis title and Amount Charged ($100s)for Y axis title.

(4)Turn off legend.

(5)Move and resize the chart to fit the range of A28:E43 in Worksheet “Regression2”.

Based on your opinion about the diagram, type your answer (Yes or No) to the question right below the diagram.

2. Fit data with a simple linear regression model using Excel procedure "Regression".

Run data analysis procedure “Regression” (refer to p.638 of textbook). Click Data tab (Tools menu in older versions), select Data Analysis, choose “Regression” and run this procedure. Select sample data of variable Y for “Input Y Range” and sample data of variable X for “Input X Range”. Include labels (“Household Size” and “Amount Charged ($100s)”) in Excel output. Check “OutputRange” radio button and select the cell right below the “Excel Output” for output range. Anwser Questions (1) to (7) in the worksheet.

(III) Regression Model 3 (Worksheet Regression3)

The data is given in Worksheet “Regression3”. You are required to complete the followings in Worksheet “Multiple Regression”.

  1. Run the Excel Data Analysis procedure “Regression” and use Excel output to develop an estimated regression equation with annual income (X1) and household size (X2) as the independent variables.
  2. What are the value of and the predicted annual credit card charge (in dollar amount) for a three-person household with an annual income of $55,000?
  3. Find the value of the multiple coefficient of determination and interpret it.
  4. Find the value of the adjusted multiple coefficient of determination.
  5. Use the F test to determine the overall significance of the relationship ( = 0.05).
  6. Is 1 significant ( = 0.05)? Should the annual income be dropped from the model?
  7. Is 2 significant ( = 0.05)? Should the household size be dropped from the model?
  8. Compute correlation coefficient for two independent variables and use the rule of thumb to determine whether or not multicollinearity is a potential problem.
  9. Which regression model is the best among three?

IV. Statistical Process Control

In the worksheet “SPC”, the data is given. You are required to develop the R and charts in this worksheet.

(I) Prepare data:

Samples are given in Worksheet “SPC”. Enter formulas to compute and R for each sample. Next, use the Excel function “Average” to compute and . Then find A2, D3 and D4 in Table 20.3 (p.915). Finally, enter Excel formulas to compute CL, UCL and LCL for (p.914 (20.8)) and R (p.916 (20.14) and (20.15)), respectively.

(II) Develop an R-chart:

The R-chart includes CL, UCL, LCL, and sample data R’s connected by lines. The chart title is “R Chart”. The title for x-axis is “Sample” and the title for y-axis is “R”. If you are not familiar with making chart in Excel, refer to the following instructions.

Insert Tab  Chart (click the arrow ) XY (Scatter) Select the fourth Sub-type OK Move and resize the chart to fit the bordered range Right-click on the chart and choose “Select Data”  If anything is in the Legend Entries (Series) window, use Remove button to remove it

Click Add button  Type: CL in Series Name window Click Selection button for Series X Values A4:A25 Enter Click Selection button for Series Y Values J4:J25 OK

Click Add button  Type: UCL in Series Name window Click Selection button for Series X Values A4:A25 Enter Click Selection button for Series Y Values K4:K25OK

Click Add button  Type: LCL in Series Name window Click Selection button for Series X Values A4:A25 Enter Click Selection button for Series Y Values L4:L25OK

Click Add button  Type: R in Series Name window Click Selection button for Series X Values A4:A25 Enter Click Selection button for Series Y Values F4:F25OKOK.

Add Titles:

Select Chart Choose a layout with titles EnterR Chart for Chart title EnterSample for X Axis title EnterR for Y Axis title  Delete legend.

Scale X-Axis from 1 to 22 with the major unit of 1.

Move and resize the chart to fit the bordered range.

Excel Older Versions:

Insert menu  Chart  Standard Types tab  XY (Scatter)  Chart Sub-type (Row 3, Column 1)  Next button  check Series in Columns  Series tab  If anything is in the Series window, use Remove button to remove it

 Add button  Type: CL in Name window  Selection button for X Values A4:A25 Enter  Selection button for Y Values J4:J25 Enter

 Add button  Type: UCL in Name window  Selection button for X Values A4:A25 Enter  Selection button for Y Values K4:K25 Enter

 Add button  Type: LCL in Name window  Selection button for X Values A4:A25 Enter  Selection button for Y Values L4:L25 Enter

 Add button  Type: R in Name window  Selection button for X Values A4:A25 Enter  Selection button for Y Values F4:F25  Enter  Next button

 Titles tab  Type: R Chart for Chart title  Type: Sample for Value (X) Axis  Type: R for Value (Y) Axis  Legend tab  Deselect Show legend box  Next button  Select As object in  Finish button.

Scale X-Axis from 1 to 22 with the major unit of 1.

Move and resize the chart to fit the bordered range.

(III) Develop an !Unexpected End of Expression-chart:

The -chart includes CL, UCL, LCL, and sample data ’s connected by lines. The chart title is “Xbar Chart”. The title for x-axis is “Sample” and the title for y-axis is “Xbar”. Delete the legend box. Scale X-Axis from 1 to 22 with the major unit of 1. Move and resize the chart to fit the bordered range.

(IV) Review the control charts to answer the questions in Worksheet “SPC”.