MBA622 Syllabus Individual Work: Assignment 5 Resources Needed: \u0001 \u0001 \u0001 \u0001 Textbook: Review Chapters Software: Use Microsoft Excel to compute statistics and create tables, charts, and graphs. Data: Use the data provided in the problems; Phone.xls; ERWaiting.xls; CatFood.xls; and Pasta.xls Excel Workbook: Pooled-Variance T.xls; Chi-square.xls Recommended Resources: \u0001 \u0001 Microsoft Excel Tutorial: visit https://support.office.com/en-us/article/Office-training-andtutorials-b8f02f81-ec85-4493-a39b-4c48e6bc4bfb?ui=en-US&rs=en-US&ad=US. Instructions for Excel 2013 are displayed at the top of the page. If you are using an earlier version of Excel, scroll to the bottom of the page and select your version. Watch: Data Analysis Tools Excel 2010 T Test (WINDOWS): https://www.youtube.com/watch?v=nqNjNE3jw_I Instructions: Use Microsoft Excel to compute your answers (when necessary) and present your final answers in a Microsoft Word document. If you create tables and figures to present your data, follow APA style guidelines. Problem A: Two-Sample Tests (Chapter 10, Section 10.1) Compute Using: Data provided in the problem and Excel Workbook Pooled-Variance T.xls You are a well-respected organizational consultant, and you have a small business client who is interested in better understanding how to analyze business data from samples. Your client wants to make sure the organization is operating efficiently and effectively. There is no one in the client's business with statistical analysis expertise and who wants to learn from you. The client has provided you with data to compare the means of two independent populations. You have a sample of n1 = 8 with the sample mean 1 = 42 and a sample standard deviation, S = 4, and you have an independent sample of n2 15 from another population with a sample mean of 2 = 34 and a sample standard deviation of S2 = 5. 1. 2. 3. 4. 5. 6. 7. 8. 9. State the null hypothesis. What is the value of the pooled-variance and tSTAT test statistic for texting H0: 1 = 2. In finding the critical value t/2 , how many degrees of freedom are there? What are the lower and upper critical values t/2 ? Report the p-value. State the decision rule. What is your statistical decision? What assumptions about the two populations are necessary? Construct a 95% confidence interval estimate of the population mean difference between 1 and 2 . 1 MBA622 Syllabus 10. Given your initial analysis of the data, do you believe a problem exists at the client facility that requires further examination? If so, what additional data would you need to analyze to understand the nature of the problem in order to make a recommendation for improvement? (200-250 words). Make certain you explain and interpret the entire process to the client because they are interested in learning about how two-sample tests can be used to make business decisions. Problem B: Two-Sample Tests, Chapter 10 (Section 10.1) Data: Phone.xls Compute Using: Excel Workbook Pooled Variance T.xls Given your experience as an organizational consultant, you have a client with a telephone company who has requested that you help diagnose a potential problem at the telephone company. Your client explained there has been a problem with a telephone line that is preventing customers from receiving or making calls. The problem is disconcerting to both customers and the telephone company. You want to understand the extent of the problem. Your client has provided you with preliminary data to examine. You have a sample of 20 cases reported by two different offices of the telephone company. Hint: If you follow the Pooled-Variance T.xls method to answer the questions, use Descriptive Statistics in Data Analysis Toolpak to compute the sample mean, sample deviation, and sample size (i.e., count) for each location OR use formulas to compute these measures in Microsoft Excel (i.e., =average, =stdev, =count). Once you compute the sample means, sample standard deviations, and sample sizes for each location, enter these data into the Pooled-Variance T.xls file to compute statistics. Central Office 1 Time to Clear Problems (minutes) 1.48 1.75 0.78 2.85 0.52 1.60 4.15 3.97 1.48 3.10 1.02 0.53 0.93 1.60 0.80 1.05 6.32 3.93 5.45 0.97 Central Office 2 Time to Clear Problems (minutes) 7.55 3.75 0.10 1.10 0.60 0.52 3.30 2.10 0.58 4.02 3.75 0.65 1.92 0.60 1.53 4.23 0.08 1.48 1.65 0.72 1. Compute the sample size, sample mean, and sample standard deviation for data from Central Office Location 1. 2. Compute the sample size, sample mean, and sample standard deviation for data from Central Office Location 2. 3. Assuming the population variances from both offices are equal, is there evidence of a difference in the mean waiting time between the two offices? (Use = 0.05) Compute the following: a. State the null and alternative hypothesis b. Report the level of confidence c. Report the degrees of freedom d. Report the critical values e. Report the test statistic f. Find the p-value and interpret its meaning g. State the decision rule. h. State the decision. 4. When computing pooled-variance t-tests, what assumptions are necessary? 2 MBA622 Syllabus 5. Assuming that the population variances from both offices are equal, construct and interpret a 95% confidence interval estimate of the difference between the population means in the two offices. 6. Given your initial analysis of the data, do you believe a problem exists at the client facility that requires further examination? If so, what additional data would you need to analyze to understand the nature of the problem? Be specific in your explanations. (200-250 words). Problem C - Analysis of Variance, Chapter 11, Section 11.1 Data: ERWaiting.xls Compute Using: Data Analysis Toolpak, Anova Single Factor You have worked with numerous hospitals on performance improvement initiatives to enhance organizational efficiency and effectiveness. You have a hospital client who contacted you to assist them with emergency room efficiency. The hospital conducted a study of the waiting time in its emergency room. The hospital has a main campus and three satellite locations. The hospital executive managers have established a business objective of reducing waiting time for emergency room cases that do not require immediate attention. To study these cases, a random sample of 15 emergency room cases that did not require immediate attention at each location were selected on a particular day, and the waiting time (measured from check-in to when the patient was called into the clinic area) was measured. The data are provided for you in ERWaiting.xls. 1. At the 0.05 level of significance, is there evidence of a difference in the mean waiting times in the four locations? A. State the null hypothesis B. State the alternative hypothesis C. State the decision rule D. Report the results and findings of your data analysis. Insert a copy of the output table in your report to the client. Label following APA style guidelines. 2. Provide your client with your interpretation of the organizational problem. Based your assessment on the initial organizational diagnostic and assessment data. What additional information do you need in order to complete the organizational diagnosis and make a recommendation for an organizational improvement project? Why is this important to the success of the improvement initiative? Problem D - Analysis of Variance, Chapter 11, Section 11.1 Data: CatFood.xls Compute Using: Data Analysis Toolpak, Anova Single Factor In your consultant business, you engage with a wide range of clients and organizations. Recently, you gained entry as a consultant with a pet food company. The pet food client has a business objective of expanding its product line beyond its current kidney- and shrimp-based cat foods. The company developed two new products, one based on chicken livers and the other based on salmon. The company conducted an experiment to compare the two new products with its existing ones, as well as a generic beef-based product sold in a supermarket chain. For the experiment, a sample of 50 cats from a population at a local animal shelter was selected. Ten cats were randomly assigned to each of the five products being tested. Each of the cats was then presented with 3 ounces of the selected food in a dish at feeding time. The researchers defined the variables to be measured as the number of ounces of food that the cat consumed within a 10-minute time interval 3 MBA622 Syllabus beginning when the filled dish was presented. The results of the experience are summarized for you in the CatFood.xls file. 1. At the 0.05 level of significance, is there evidence of a difference in the mean amount of food eaten among the various products? a. State the null hypothesis b. State the alternative hypothesis c. State the decision rule d. Report the results and findings of your data analysis. Insert a copy of the output table in your report to the client. Label following APA style guidelines. 2. What should the pet company conclude? Fully describe the pet food company's options with respect to the products. 3. If you were going to continue working with this client on this initiative, what additional information would you need to complete your organizational diagnosis? How would this organizational diagnosis influence your project improvement recommendations? Why is the organizational diagnostic and assessment phase important in the recommendation for an improvement process? (250 words) Problem E - Analysis of Variance, Chapter 11, Section 11.2 Data: Pasta.xls Compute Using: Data Analysis Toolpak, Anova Two Factor With Replication A chef in a restaurant that specializes in pasta dishes was experiencing difficulty in getting brands of pasta to be al dente - that is, cooked enough so as to not feel starchy or hard but still feel firm when bitten into. The chef decided to conduct an experiment in which two brands of pasta, one American and one Italian, were cooked for either 4 or 8 minutes. The variable of interest was weight of the pasta because cooking the pasta enables it to absorb water. Pasta with a faster rate of water absorption may provide a shorter interval in which the pasta is al dente, thereby increasing the chance that it might be overcooked. The experiment was conducted by using 150 grams of uncooked pasta. Each trial began by bringing a pot containing 6 quarts of cold, unsalted water to a moderate boil. The 150 grams of uncooked pasta was added and then weighed after a given period of time by lifting the pasta from the pot via a built-instrainer. The results (weight in grams) for two replicates of each type of pasta and cooking time are provided for you in Pasta.xls. At the 0.05 level of significance, 1. Is there an interaction between type of pasta and cooking time? a. State the decision rule b. Report the test statistic c. Report the results/findings of your statistical analysis 2. Is there an effect due to type of pasta? a. State the decision rule b. Report the test statistic c. Report the results/findings of your statistical analysis 3. Is there an effect due to cooking time? a. State the decision rule b. Report the test statistic c. Report the results/findings of your statistical analysis 4 MBA622 Syllabus 4. What conclusions can you reach concerning the importance of these two factors on the weight of the pasta? Problem F - Chi-Square, Chapter 12, Section 12.1 Compute Using: =CHINV(Level of Significance, degree of freedom) Consultants sometimes need to analyze categorical data. In such cases, what is the critical value of 2 with 1 degree of freedom in each of the following circumstances? 1. 2. 3. 4. 5. = 0.01 = 0.005 = 0.10 = 0.05 = 0.25 Problem G - Chi-Square, Chapter 12, Section 12.1 Compute Using: Excel Workbook Chi-square.xls A sample of 500 shoppers was selected in a large metropolitan area to collect information concerning consumer behavior. Among the questions asked was, \"Do you enjoy shopping for clothing?\" The results are summarized in the following table: Enjoy shopping for Clothing? Yes No Total Male 136 104 240 Gender Female Total 224 36 260 360 140 500 1. Is there evidence of a significant difference between the proportion of males and females who enjoy shopping for clothing at the 0.01 level of significance? a. State the decision rule b. Report the test statistic c. Reporting your findings/results of your data analysis. 2. Determine the p-value and interpret its meaning. 3. If 206 males enjoyed shopping for clothing and 34 did not, is there evidence of a significant difference between the proportion of males and females who enjoy shopping for clothing at the 0.01 level of significance? 4. If 206 males enjoyed shopping for clothing and 34 did not, determine the p-value and interpret its meaning. 5. If you were an organizational consultant for the business who conducted this survey, how would you use these data in an organizational diagnosis? What additional information would you want to know to complete the organizational diagnosis and make a project improvement recommendation? Explain the importance of the given and additional needed information. (250 words) 5 MBA622 Syllabus Grading Rubric - Individual Assignment 5 Problem Points Problem A 20 Problem B 20 Problem C 10 Problem D 10 Problem E 15 Problem F 5 Problem G 20 Total 100 6 Kidney Shrimp Chicken Liver Salmon 2.37 2.26 2.29 1.79 2.62 2.69 2.23 2.33 2.31 2.25 2.41 1.96 2.47 2.45 2.68 2.05 2.59 2.34 2.25 2.26 2.62 2.37 2.17 2.24 2.34 2.22 2.37 1.96 2.47 2.56 2.26 1.58 2.45 2.36 2.45 2.18 2.32 2.59 2.57 1.93 Beef 2.09 1.87 1.67 1.64 2.16 1.75 1.18 1.92 1.32 1.94 Chi-Square Test Observed Frequencies Hotel Choose Again? Beachcomber Windsurfer Yes 163 154 No 64 108 Total 227 262 Expected Frequencies Hotel Choose Again? Beachcomber Windsurfer Yes 147.1554 169.8446 No 79.8446 92.1554 Total 227 262 Data Level of Significance Number of Rows Number of Columns Degrees of Freedom Results Critical Value Chi-Square Test Statistic p-Value Reject the null hypothesis Expected frequency assumption is met. 0.05 2 2 1 3.8415 9.0526 0.0026 Total 317 172 489 Total 317 172 489 Calculations fo-fe 15.84458078 -15.8445808 -15.8446 15.8446 (fo-fe)^2/fe 1.7060 1.4781 3.1442 2.7242 Chi - Square Test Choose Again? Yes No Total Observed Frequencies Hotel Beachcomber Windsurfer 163 154 64 108 227 262 Choose Again? Yes No Total Expected Frequencies Hotel Beachcomber Windsurfer 147.1554 169.8446 79.8446 92.1554 227 262 Data Level of Signif Number of Ro Number of Co Degrees of Fr 0.05 2 2 1 Results Critical Value 3.8415 Chi - Square Te 9.0526 p - Value 0.0026 Reject the null hypothesis Expected frequency assumption is met. Total 317 172 489 Total 317 172 489 Calculations fo - fe ### ### ### ### (fo - fe)^2/fe ### 1.4781 ### 2.7242 Main 120.08 81.90 78.79 63.83 79.77 47.94 79.88 48.63 55.43 64.06 64.99 53.82 62.43 65.07 81.02 Satellite 1 30.75 61.83 26.40 53.84 72.30 53.09 27.67 52.46 10.64 53.50 37.28 34.31 66.00 8.99 29.75 Satellite 2 75.86 37.88 68.73 51.08 50.21 58.47 86.29 62.90 44.84 64.17 50.68 47.97 60.57 58.37 30.40 Satellite 3 54.05 38.82 36.85 32.83 52.94 34.13 69.37 78.52 55.95 49.61 66.40 76.06 11.37 83.51 39.17 Type Four A 265 A 270 I 250 I 245 Eight 310 320 300 305 Time Location 1.48 1 1.75 1 0.78 1 2.85 1 0.52 1 1.60 1 4.15 1 3.97 1 1.48 1 3.10 1 1.02 1 0.53 1 0.93 1 1.60 1 0.80 1 1.05 1 6.32 1 3.93 1 5.45 1 0.97 1 7.55 2 3.75 2 0.10 2 1.10 2 0.60 2 0.52 2 3.30 2 2.10 2 0.58 2 4.02 2 3.75 2 0.65 2 1.92 2 0.60 2 1.53 2 4.23 2 0.08 2 1.48 2 1.65 2 0.72 2 Normal EndAisle 22 52 34 71 52 76 62 54 30 67 40 83 64 66 84 90 56 77 59 84 COMPUTE Pooled-Variance t Test for Differences in Two Means (assumes equal population variances) Data Hypothesized Difference 0 Level of Significance 0.05 Population 1 Sample Sample Size 10 Sample Mean 50.3 Sample Standard Deviation 18.7264 Population 2 Sample Sample Size 10 Sample Mean 72 Sample Standard Deviation 12.5433 Intermediate Calculations Population 1 Sample Degrees of Freedom Population 2 Sample Degrees of Freedom Total Degrees of Freedom Pooled Variance Difference in Sample Means t Test Statistic 9 9 18 254.0056 -21.7 -3.0446 Confidence Interval Estimate for the Difference Between Two Means Data Confidence Level Intermediate Calculations Degrees of Freedom t Value Interval Half Width Confidence Interval Interval Lower Limit Interval Upper Limit Two-Tail Test Lower Critical Value Upper Critical Value p-Value Reject the null hypothesis -2.1009 2.1009 0.0070 Calculations Area For one-tailed tests: TDIST value 1-TDIST value Page 2 COMPUTE nce Interval Estimate ence Between Two Means Data 95% ediate Calculations 18 2.1009 14.9743 nfidence Interval -36.6743 -6.7257 lculations Area 0.0035 0.9965 Page 3 Original Data 1.30274 Page 4 What if example, section 8.3.5 2.30274 Page 5 COMPUTE_LOWER Pooled-Variance t Test for Differences in Two Means (assumes equal population variances) Data Hypothesized Difference 0 Level of Significance 0.05 Population 1 Sample Sample Size 10 Sample Mean 50.3 Sample Standard Deviation 18.7264 Population 2 Sample Sample Size 10 Sample Mean 72 Sample Standard Deviation 12.5433 Intermediate Calculations Population 1 Sample Degrees of Freedom Population 2 Sample Degrees of Freedom Total Degrees of Freedom Pooled Variance Difference in Sample Means t Test Statistic 9 9 18 254.0056 -21.7 -3.0446 Confidence Interval Estimate for the Difference Between Two Means Data Confidence Level Intermediate Calculations Degrees of Freedom t Value Interval Half Width Confidence Interval Interval Lower Limit Interval Upper Limit Lower-Tail Test Lower Critical Value p-Value Reject the null hypothesis -1.7341 0.0035 Calculations Area For one-tailed tests: TDIST value 1-TDIST value Page 6 COMPUTE_LOWER nce Interval Estimate ence Between Two Means Data 95% ediate Calculations 18 2.1009 14.9743 nfidence Interval -36.6743 -6.7257 0.0035 0.9965 Page 7 COMPUTE_UPPER Pooled-Variance t Test for Differences in Two Means (assumes equal population variances) Data Hypothesized Difference 0 Level of Significance 0.05 Population 1 Sample Sample Size 10 Sample Mean 50.3 Sample Standard Deviation 18.7264 Population 2 Sample Sample Size 10 Sample Mean 72 Sample Standard Deviation 12.5433 Intermediate Calculations Population 1 Sample Degrees of Freedom Population 2 Sample Degrees of Freedom Total Degrees of Freedom Pooled Variance Difference in Sample Means t Test Statistic 9 9 18 254.0056 -21.7 -3.0446 Confidence Interval Estimate for the Difference Between Two Means Data Confidence Level Intermediate Calculations Degrees of Freedom t Value Interval Half Width Confidence Interval Interval Lower Limit Interval Upper Limit Upper-Tail Test Upper Critical Value p-Value Do not reject the null hypothesis 1.7341 0.9965 Calculations Area For one-tailed tests: TDIST value 1-TDIST value Page 8 COMPUTE_UPPER nce Interval Estimate ence Between Two Means Data 95% ediate Calculations 18 2.1009 14.9743 nfidence Interval -36.6743 -6.7257 lculations Area 0.0035 0.9965 Page 9 COMPUTE_ALL Pooled-Variance t Test for Differences in Two Means (assumes equal population variances) Data Hypothesized Difference 0 Level of Significance 0.05 Population 1 Sample Sample Size 10 Sample Mean 50.3 Sample Standard Deviation 18.7264 Population 2 Sample Sample Size 10 Sample Mean 72 Sample Standard Deviation 12.5433 Intermediate Calculations Population 1 Sample Degrees of Freedom Population 2 Sample Degrees of Freedom Total Degrees of Freedom Pooled Variance Difference in Sample Means t Test Statistic 9 9 18 254.0056 -21.7 -3.0446 Confidence Interval Estimate for the Difference Between Two Means Data Confidence Level Intermediate Calculations Degrees of Freedom t Value Interval Half Width Confidence Interval Interval Lower Limit Interval Upper Limit Two-Tail Test Lower Critical Value Upper Critical Value p-Value Reject the null hypothesis -2.1009 2.1009 0.0070 Lower-Tail Test Lower Critical Value p-Value Reject the null hypothesis -1.7341 0.0035 Upper-Tail Test Upper Critical Value p-Value Do not reject the null hypothesis 1.7341 0.9965 Page 10 Calculations Area For one-tailed tests: TDIST value 1-TDIST value COMPUTE_ALL nce Interval Estimate ence Between Two Means Data 95% ediate Calculations 18 2.1009 14.9743 nfidence Interval -36.6743 -6.7257 lculations Area 0.0035 0.9965 Page 11 COMPUTE_ALL_FORMULAS Pooled-Variance t Test for Differences in Two Means (assumes equal population variances) Data Hypothesized Difference Level of Significance Population 1 Sample Sample Size Sample Mean Sample Standard Deviation Population 2 Sample Sample Size Sample Mean Sample Standard Deviation Confidence Interval Estimate f0or the Difference Between Two Means 0.05 Data 10 Confidence L 95% 50.3 18.7264 Intermediate Calculations Degrees of F 18 10 t Value 2.1009 72 Interval Half 14.9743 12.5433 Confidence Interval Intermediate Calculations Interval Lowe -36.6743 Population 1 Sample Degrees of Freedom 9 Interval Uppe -6.7257 Population 2 Sample Degrees of Freedom 9 Total Degrees of Freedom 18 Pooled Variance 254.0055555556 Difference in Sample Means -21.7 t Test Statistic -3.0446 Two-Tail Test Lower Critical Value Upper Critical Value p-Value Reject the null hypothesis -2.1009 2.1009 0.0070 Lower-Tail Test Lower Critical Value p-Value Reject the null hypothesis -1.7341 0.0035 Upper-Tail Test Upper Critical Value p-Value Do not reject the null hypothesis 1.7341 0.9965 Page 12 Calculations Area For one-tailed tests: TDIST value 0.0035 1-TDIST value 0.9965