See attached files.
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. 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 http://faculty.winthrop.edu/caoq, 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 Data Analysis Procedure "t-Test: Paired Two Sample for Means" to perform Matched-Sample t-test
If the response variable is also affected by an extraneous factor, we may use the paired t-test to block out the effect of that factor. You are required to run the procedure "t-Test: Paired Two Sample for Means" in worksheet "Matched sample". You will complete the followings:
(1) Read the problem, and enter H0, Ha and the value of Alpha ().
(2) Run procedure "t-Test: Paired Two Sample for Means" (p.433 in textbook).
(3) Check "Labels" so that you can select "TV" and "Radio" as the labels in output. Put the output right below the cell "Excel Output".
(4) Read output. You need only the p-value to make your decision.
(5) Conclusion. Choose the p-value for your test and type it in. Make your conclusion based on the p-value you choose. Type in either Rej. H0 or FTR H0 for Conclusion.
II. 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.
1. 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 read the following instructions if you are using Excel 2007.
(1) On the Insert tab (Data menu in older versions), in the Tables group, click PivotTable, and then choose PivotTable. 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," 3 columns, including column headings), and Enter key.
(3) Select Existing Worksheet, click the selection button to select the cell "Count of Observation" in Worksheet "Contingency Table," Enter key, and OK.
(4) 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".
(5) Make sure it's "Count of Observation". If not, click the arrow under "∑ Values", choose Value Field Settings, and select Count. and OK.
(6) Copy and paste (Paste Special) the observed frequencies to the same table. 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.
(7) Complete the expected frequency table: Create one formula and correctly copy it to the entire table. Make sure you use the absolute reference and the relative reference correctly.
(8) Enter a formula to get degrees of freedom.
(9) The p-value for the test of independence. Use Excel function CHITEST(observed frequencies, expected frequencies).
2. Also, report H0, Ha, , and conclusion in this worksheet.
III. 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.539-p.540) 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. Answer all questions about H0, Ha, , the test statistic value (F value), the numerator degrees of freedom, the denominator degrees of freedom, the critical value, the p-value and your conclusion in this worksheet.
IV. Use Data Analysis Procedure "Regression" to Perform Regression and Correlation Analysis
In this part, you work on the sample data in worksheet "Regression":
1. Set up a scatter diagram for variables X and Y and answer questions.
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 Scatter Diagram as chart title.
(3) Enter Shelf Space (feet) for X axis title and Weekly Sales ($1000) for Y axis title.
(4) Turn off legend.
(5) Move and resize the chart to fit the range of A26:E41 in Worksheet "Regression".
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.621-p.623 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 ("(X) (feet)" and "(Y) ($1000)") in Excel output. Check "Output Range" radio button and select the cell right below the "Excel Output" for output range. Based on the output, find the predicted value of , the predicted weekly sales in dollars, the standard error of estimate, coefficient of determination, and coefficient of correlation.
A Complete, Neat and Step-by-step Solution is provided in the attached file.