Essays guru only
Mhuobertson0u
MGMT650HW11Fall20_Questions.xlsxHW11
MGMT 650 Summer 2020 Week 11 Homework Questions (Last updated 7/8/2020)
Chi Square
| Saeko has a yarn shop and wants to test her theory on what types of colors she is selling. | ||
| She believes that Black, White, the Primary Colors, and Tertiary colors sell in equal amounts. | ||
| The primary colors are blue, red, and yellow; while the tertiary colors are Brown, Green, and Purple. | ||
| Test Saeko's theory using the 5 step hypothesis testing analysis and Chi Square at the .10 level of significance. | ||
| Here is a pivot table that shows Saeko the number of yards that were sold in the various yarn types during the busiest weekend of her shop last year. | ||
| Row Labels | Count of Color Type | Sum of Yards |
| Black | 23 | 35856 |
| Blue | 16 | 17053 |
| Brown | 13 | 13426 |
| Green | 12 | 12509 |
| Purple | 12 | 12131 |
| Red | 8 | 8393 |
| White | 26 | 37666 |
| Yellow | 12 | 12874 |
| (blank) | ||
| Grand Total | 122 | 149908 |
| 1) | Using the pivot table, fill in the blanks in the following table: | |
| Primary Colors consists of the sum of Blue, Red, and Yellow yarn sold | ||
| Tertiary Colors consists of the sum of Brown, Green, and Purple Colors Sold. | ||
| The Total in this chart must equal the Grand Total, Cell D19 in the above table. | ||
| Black | ||
| White | ||
| Primary Colors | ||
| Tertiary Colors | ||
| Total | ||
| This table represents the observed data in the Chi Square analysis. | ||
| Find the Expected values for each of the colors. Saeko expects that the colors sell in equal amounts. | ||
| Color Type | Sum of Yards | |
| Black | ||
| White | ||
| Primary Colors | ||
| Tertiary Colors | ||
| Total | ||
| Subtract the Expected values from the observed values | ||
| Color Type | Sum of Yards | |
| Black | ||
| White | ||
| Primary Colors | ||
| Tertiary Colors | ||
| Square the values just found | ||
| Color Type | Sum of Yards | |
| Black | ||
| White | ||
| Primary Colors | ||
| Tertiary Colors | ||
| Divide each square by the expected value and add together | ||
| Color Type | Sum of Yards | |
| Black | ||
| White | ||
| Primary Colors | ||
| Tertiary Colors | ||
| Total | ||
| 2) | This total is your Chi Square test statistic | |
| Use the 5 step hypothesis testing procedure to determine if Saeko's hypothesis that the colors sell in equal amounts is true. | ||
| What is the null hypothesis? | ||
| What is the alternative hypothesis? | ||
| What is the level of significance? | ||
| 3) | What is the Chi Square test statistic? | |
| 4) | What is the Chi Square critical Value? | Use =CHISQ.INV() |
| What is your answer to Saeko? | State both the statstical answer (reject or do not reject, and what hypothesis), and also state your answer in English: What can Saeko learn from the data? | |
ANOVA
| Saeko owns a yarn shop and want to expands her color selection. | |||
| Before she expands her colors, she wants to find out if her customers prefer one brand | |||
| over another brand. Specifically, she is interested in three different types of bison yarn. | |||
| As an experiment, she randomly selected 21 different days and recorded the sales of each brand. | |||
| At the .10 significance level, can she conclude that there is a difference in preference between the brands? | |||
| Misa's Bison | Yak-et-ty-Yaks | Buffalo Yarns | |
| 799 | 776 | 799 | |
| 784 | 640 | 931 | |
| 807 | 822 | 794 | |
| 675 | 856 | 920 | |
| 795 | 616 | 731 | |
| 875 | 893 | 837 | |
| Total | 4,735.00 | 4,603.00 | 5,012.00 |
| 5) | What is the null hypothesis? | ||
| What is the alternative hypothesis? | |||
| What is the level of significance? | |||
| 6) | Use Tools - Data Analysis - ANOVA:Single Factor | ||
| to find the F statistic: | |||
| 7) | From the ANOVA output: What is the F value? | ||
| 8) | What is the F critical value? | ||
| 9) | What is your decision? | ||
| Explain in statistical terms | |||
Regression
| Studies have shown that the frequency with which shoppers browse Internet retailers is related to the frequency with which they actually purchase products and/or services online. The following data show respondents age and answer to the question “How many minutes do you browse online retailers per year?” | |
| Age (X) | Time (Y) |
| 34 | 123,556.00 |
| 17 | 92,425.00 |
| 42 | 250,908.00 |
| 35 | 204,540.00 |
| 19 | 77,897.00 |
| 43 | 197,012.00 |
| 51 | 195,126.00 |
| 50 | 177,100.00 |
| 22 | 83,230.00 |
| 58 | 140,012.00 |
| 48 | 265,296.00 |
| 35 | 189,420.00 |
| 39 | 235,872.00 |
| 39 | 230,724.00 |
| 59 | 238,655.00 |
| 40 | 138,560.00 |
| 60 | 259,680.00 |
| 22 | 93,208.00 |
| 33 | 91,212.00 |
| 36 | 153,216.00 |
| 28 | 77,308.00 |
| 22 | 56,496.00 |
| 28 | 106,652.00 |
| 44 | 242,748.00 |
| 54 | 195,858.00 |
| 30 | 178,560.00 |
| 28 | 190,876.00 |
| 16 | 98,528.00 |
| 52 | 169,572.00 |
| 22 | 79,420.00 |
| 28 | 167,928.00 |
| 35 | 215,705.00 |
| 50 | 146,350.00 |
| 10) | Use Data > Data Analysis > Correlation to compute the correlation checking the Labels checkbox. |
| 11) | Use the Excel function =CORREL to compute the correlation. If answers for #1 and 2 do not agree, there is an error. |
| The strength of the correlation motivates further examination. | |
| 12) | a) Insert Scatter (X, Y) plot linked to the data on this sheet with Age on the horizontal (X) axis. |
| b) Add to your chart: the chart name, vertical axis label, and horizontal axis label. | |
| c) Complete the chart by adding Trendline and checking boxes | |
| Read directly from the chart: | |
| 13) | a) Intercept = |
| b) Slope = | |
| c) R2 = | |
| Perform Data > Data Analysis > Regression. | |
| 14) | Highlight the Y-intercept with yellow. Highlight the X variable in blue. Highlight the R Square in orange |
| 15) | Use Excel to predict the number of minutes spent by a 22-year old shopper. Enter = followed by the regression formula. |
| Enter the intercept and slope into the formula by clicking on the cells in the regression output with the results. | |
| 16) | Is it appropriate to use this data to predict the amount of time that a 9-year-old will be on the Internet? |
| If yes, what is the amount of time, if no, why? | |
Cleaning Data with Outlier
| 17) | On this worksheet, make an XY scatter plot linked to the following data: |
| X | Y |
| 1.01 | 2.8482 |
| 1.48 | 4.2772 |
| 1.8 | 4.788 |
| 1.81 | 5.3757 |
| 1.07 | 2.5252 |
| 1.53 | 3.0906 |
| 1.46 | 4.3362 |
| 1.38 | 3.2016 |
| 1.77 | 4.3542 |
| 1.88 | 4.8692 |
| 1.32 | 3.8676 |
| 1.75 | 3.9375 |
| 1.94 | 5.7424 |
| 1.19 | 2.4752 |
| 1.31 | 26.2 |
| 1.56 | 4.5708 |
| 1.16 | 2.842 |
| 1.22 | 2.44 |
| 1.72 | 5.1256 |
| 1.45 | 4.3355 |
| 1.43 | 4.2471 |
| 1.19 | 3.5343 |
| 2 | 5.46 |
| 1.6 | 3.84 |
| 1.58 | 3.8552 |
| 18) | Add trendline, regression equation and r squared to the plot. |
| Add this title. ("Scatterplot of X and Y Data") | |
| 19) | The scatterplot reveals a point outside the point pattern. Copy the data to a new location in the worksheet. You now have 2 sets of data. |
| Data that are more tha 1.5 IQR below Q1 or more than 1.5 IQR above Q3 are considered outliers and must be investigated. | |
| It was determined that the outlying point resulted from data entry error. Remove the outlier in the copy of the data. | |
| Make a new scatterplot linked to the cleaned data without the outlier, and add title ("Scatterplot without Outlier,") trendline, and regression equation label. | |
| X | Y |
| 1.01 | 2.8482 |
| 1.48 | 4.2772 |
| 1.8 | 4.788 |
| 1.81 | 5.3757 |
| 1.07 | 2.5252 |
| 1.53 | 3.0906 |
| 1.46 | 4.3362 |
| 1.38 | 3.2016 |
| 1.77 | 4.3542 |
| 1.88 | 4.8692 |
| 1.32 | 3.8676 |
| 1.75 | 3.9375 |
| 1.94 | 5.7424 |
| 1.19 | 2.4752 |
| 1.56 | 4.5708 |
| 1.16 | 2.842 |
| 1.22 | 2.44 |
| 1.72 | 5.1256 |
| 1.45 | 4.3355 |
| 1.43 | 4.2471 |
| 1.19 | 3.5343 |
| 2 | 5.46 |
| 1.6 | 3.84 |
| 1.58 | 3.8552 |
| Compare the regression equations of the two plots. How did removal of the outlier affect the slope and R2? Explain why the slope and R Square change the way they did | |
| 20) | |
