HW11 MGMT 650 Spring 2020 Week 11 Homework Questions (Last updated 11/24/2019) Chi Square Saeko has a yarn shop and wants to test her theory on what types of colors she is selling. She believes that...

Excel problem solving/calculation


HW11 MGMT 650 Spring 2020 Week 11 Homework Questions (Last updated 11/24/2019) 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. (Optional)Use the "Pivot Table Data" tab to create 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. Here is the pivot table that you should have created. It is optional so that you can practice your pivot table skills. Row LabelsCount of Color TypeSum of Yards Black2335856 Blue1617053 Brown1313426 Green1212509 Purple1212131 Red88393 White2637666 Yellow1212874 (blank) Grand Total122149908 1)Using the pivot table that you just created, 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 TypeSum of Yards Black White Primary Colors Tertiary Colors Total Subtract the Expected values from the observed values Color TypeSum of Yards Black White Primary Colors Tertiary Colors Square the values just found Color TypeSum of Yards Black White Primary Colors Tertiary Colors Divide each square by the expected value and add together Color TypeSum 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? Pivot Table Data CustomerColor NameColor TypeYardsMeters 1CorianderWhite11551,056.13 2BlackBlack15041,375.26 3DaffodilYellow904826.62 4BlackBlack18501,691.64 5OpalBlue14971,368.86 6ToffeeBrown929849.48 7RubyRed918839.42 8AshBlue584534.01 9BlackBlack23632,160.73 10AshBlue816746.15 11BlackBlack16851,540.76 12WhirlpoolBlue14021,281.99 13VerdeGreen972888.80 14RegalPurple590539.50 15LynxBrown12631,154.89 16Yellow RoseYellow791723.29 17ChocolateBrown13311,217.07 18MistWhite24252,217.42 19WhirlpoolBlue848775.41 20AlfalfaGreen990905.26 21RubyRed12691,160.37 22VerdeGreen14411,317.65 23SkyWhite22692,074.77 24BlackBlack14961,367.94 25WhirlpoolBlue815745.24 26BlackBlack15701,435.61 27MistWhite19991,827.89 28AlfalfaGreen12171,112.82 29JadeGreen737673.91 30Yellow RoseYellow1063972.01 31CreamWhite17991,645.01 32BlackBlack27212,488.08 33RubyRed575525.78 34MistWhite23052,107.69 35Yellow RoseYellow828757.12 36BlackBlack20371,862.63 37SkyWhite21571,972.36 38PeriwinklePurple13631,246.33 39CorianderWhite21791,992.48 40BlackBlack18461,687.98 41Yellow RoseYellow12901,179.58 42BlackBlack18941,731.87 43PeriwinklePurple973889.71 44BlackBlack23932,188.16 45BlackBlack24762,264.05 46MistWhite24282,220.16 47CorianderWhite24882,275.03 48CreamWhite23792,175.36 49VerdeGreen600548.64 50BlackBlack17201,572.77 51DaffodilYellow11601,060.70 52ChocolateBrown12641,155.80 53RegalPurple14411,317.65 54DaffodilYellow915836.68 55CorianderWhite839767.18 56BlackBlack14681,342.34 57BlackBlack831759.87 58LynxBrown936855.88 59PeriwinklePurple854780.90 60DaffodilYellow12501,143.00 61CorianderWhite13521,236.27 62VerdeGreen11631,063.45 63LynxBrown13291,215.24 64AlfalfaGreen11761,075.33 65CreamWhite703642.82 66DaffodilYellow836764.44 67PeriwinklePurple14681,342.34 68CreamWhite742678.48 69BlackBlack13051,193.29 70CreamWhite12541,146.66 71CreamWhite703642.82 72CorianderWhite774707.75 73MistWhite701640.99 74VerdeGreen589538.58 75BlackBlack697637.34 76BlushRed11131,017.73 77OpalBlue732669.34 78DaffodilYellow13931,273.76 79MistWhite14961,367.94 80AlfalfaGreen14401,316.74 81JadeGreen987902.51 82VerdeGreen11971,094.54 83CreamWhite585534.92 84BlackBlack14881,360.63 85ChocolateBrown914835.76 86RegalPurple852779.07 87SkyWhite922843.08 88RegalPurple13391,224.38 89Yellow RoseYellow13111,198.78 90LynxBrown739675.74 91RegalPurple731668.43 92AshBlue14851,357.88 93PeriwinklePurple827756.21 94BlackBlack992907.08 95BlackBlack581531.27 96BlushRed708647.40 97RegalPurple11521,053.39 98WhirlpoolBlue14341,311.25 99Yellow RoseYellow11331,036.02 100SapphireBlue734671.17 101ChocolateBrown12211,116.48 102ToffeeBrown906828.45 103SapphireBlue14231,301.19 104WhirlpoolBlue12871,176.83 105BlackBlack12771,167.69 106RegalPurple541494.69 107OpalBlue501458.11 108BlushRed11041,009.50 109CorianderWhite11871,085.39 110WhirlpoolBlue14081,287.48 111AshBlue820749.81 112BlushRed14271,304.85 113BlackBlack517472.74 114RubyRed12791,169.52 115MistWhite788720.55 116ChocolateBrown508464.52 117OpalBlue12671,158.54 118ToffeeBrown832760.78 119SkyWhite981897.03 120WhiteWhite1056965.61 121BlackBlack11451,046.99 122ChocolateBrown12541,146.66 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 BisonYak-et-ty-YaksBuffalo Yarns 799776799 784640931 807822794 675856920 795616731 875893837 Total4,735.004,603.005,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 ooutput: What is the F value? 8)What is the F critical value? 9)What is your decision? 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) 34123,556.00 1792,425.00 42250,908.00 35204,540.00 1977,897.00 43197,012.00 51195,126.00 50177,100.00 2283,230.00 58140,012.00 48265,296.00 35189,420.00 39235,872.00 39230,724.00 59238,655.00 40138,560.00 60259,680.00 2293,208.00 3391,212.00 36153,216.00 2877,308.00 2256,496.00 28106,652.00 44242,748.00 54195,858.00 30178,560.00 28190,876.00 1698,528.00 52169,572.00 2279,420.00 28167,928.00 35215,705.00 50146,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: XY 1.012.8482 1.484.2772 1.84.788 1.815.3757 1.072.5252 1.533.0906 1.464.3362 1.383.2016 1.774.3542 1.884.8692 1.323.8676 1.753.9375 1.945.7424 1.192.4752 1.3126.2 1.564.5708 1.162.842 1.222.44 1.725.1256 1.454.3355 1.434.2471 1.193.5343 25.46 1.63.84 1.583.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. XY 1.012.8482 1.484.2772 1.84.788 1.815.3757 1.072.5252 1.533.0906 1.464.3362 1.383.2016 1.774.3542 1.884.8692 1.323.8676 1.753.9375 1.945.7424 1.192.4752 1.564.5708 1.162.842 1.222.44 1.725.1256 1.454.3355 1.434.2471 1.193.5343 25.46 1.63.84 1.583.8552 Compare the regression equations of the two plots. How did removal of the outlier affect the slope and R2? 20)
Mar 07, 2021
SOLUTION.PDF

Get Answer To This Question

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here