File attached
Problem set 1 solutions Week 2 HW MGMT 650 Summer 2021 Week 2 Homework Questions (Last updated 3/7/2021) Q1 - Grade Calculator Create a spreadsheet that automatically calculates your grade in this class as you enter the grades that you receive. It should include: 1) the weights of each graded assignment, 2) your grade in each assignment, and 3) your final grade. Be sure to label everything: such as the places where the information, is stored; the names of each assignment; and where to add the actual grades that you receive. Make this sheet user friendly; especially for users that see the sheet for the first time. To use this for your benefit, you may want to design it so that it can be used to calculate your interim grade before you have all the grades. After this week, you will receive the solution from your professor and you will be able to use your solution or our solution for future classes. To test your calculator, make up grades for all assignments. This will allow you to test it and make sure that you get the correct final grade. 1) “give yourself” 85 on all assignments, make sure the final is 85. Now change all grades to 90 and verify that your final grade is 90. 2) Next change one of the quizzes from 90 to 80, and make sure the final grade is lower. 3) Then change the 80 back to 90 and change the mid term grade to 80. Make sure that the result is different. MGMT 650 Grade Calculator Q2 - Descriptive Stat You are a statistician contracted to sample and analyze weights of the 5 lb bags of coffee. You collect the following data: 5.845.004.485.145.783.986.166.025.524.56 4.335.635.274.255.435.784.575.034.605.00 5.824.235.865.224.895.335.534.725.075.60 5.055.354.285.645.135.565.525.074.305.78 4.794.894.524.534.525.315.275.225.145.20 5.035.395.185.194.455.534.965.445.594.60 4.324.454.915.555.225.654.765.314.395.50 6.094.524.635.224.514.795.504.984.794.51 For all questions, write your answers in the yellow cells. Compute the following statistics using Excel functions so that Excel calculates for you: 1)Mean Median 2)First quartile using QUARTILE.EXC Third quartile using QUARTILE.EXC Interquartile range (IQR) 3)Maximum Minimum Range 4)Variance Standard deviation Coeficient of Variation (CV) 5)For the standard deviation, when do you use STDEV.S and when do you use STDEV.P? 6)Explain why the standard deviation is a better measure to use than the variance. 7)The coefficient of variation (CV) is a measure of relative variability equal to the ratio between the standard deviation divided by the mean, and formatted to %. It is regularly used to compare risk (volatility) in investing, and is especially useful in to compare data on different scales or with different units of measure. Consider weights of 10 oz, 13.4 oz, 15.1 oz with SD of 2.597 oz. The corresponding weights in pounds 0.625 lb, 0.838 lb, 0.944 lb have SD 0.162 lb. The SDs are not equal, yet the sample and its variability are the same. How can we compare variabilities? The CV, unlike other measures of variability, does not depend on the units of measure. The units are divided out in dividing the standard deviation by the mean. For a "rule of thumb", a CV of greater than 5% is considered significant. So, the CV is also used to assess data with no prior history to compare to evaluate any trends. When is the Coefficient of Variation (CV) especially useful? 8)Copy all of the data into cells M1:M80 in order to use Data Analysis Descriptive Statistics. What happens if you don't and instead use B3:K10 for the Input Range? Use the Data Analysis, Descriptive Statistics. Click the Summary Statistics box and put the output at B65. Highlight the mean, median, Standard deviation, Range, Minimum, and Maximum. 9)As a contracted statistician, you have no prior experience with the company's product. The company has no prior process history and data with which to compare the sample to evaluate any trends. The mode is not especially useful for this data, but explain why there is or is not a concern with bag weight in terms of the other measures of central tendency. 10)What statistic should you use to assess variability of the product? Explain why there is or is not a concern with the variability of the product. &D Pivot Table Data Movie RankDomestic Gross (in millions)RatingType 1$ 999.00PG-13Fantasy 2$ 988.00PGDocumentary 3$ 984.00PG-13Thriller 4$ 984.00UnratedSuperHero 5$ 981.00RFantasy 6$ 979.00PGFantasy 7$ 972.00PGFantasy 8$ 970.00PGFamily 9$ 970.00PGRomance 10$ 957.00PG-13Musical 11$ 956.00RHorror 12$ 953.00PG-13SuperHero 13$ 952.00PGDocumentary 14$ 951.00UnratedSci-Fi 15$ 948.00PG-13Horror 16$ 939.00GDocumentary 17$ 934.00GFamily 18$ 932.00UnratedAction 19$ 932.00PGComedy 20$ 930.00RHorror 21$ 907.00RWestern 22$ 905.00PG-13Romance 23$ 904.00RSci-Fi 24$ 903.00UnratedFamily 25$ 898.00PG-13Horror 26$ 896.00PG-13Sci-Fi 27$ 882.00UnratedCartoon 28$ 878.00UnratedHorror 29$ 875.00UnratedAction 30$ 874.00PGUnknown 31$ 873.00UnratedHorror 32$ 860.00RThriller 33$ 856.00UnratedThriller 34$ 851.00RDrama 35$ 848.00RHorror 36$ 839.00PGThriller 37$ 835.00UnratedDrama 38$ 830.00PG-13Comedy 39$ 829.00RHorror 40$ 824.00RHorror 41$ 821.00RComedy 42$ 819.00RUnknown 43$ 815.00RComedy 44$ 809.00PG-13Documentary 45$ 802.00PGThriller 46$ 798.00PGCartoon 47$ 792.00GFamily 48$ 787.00UnratedFamily 49$ 785.00PGMusical 50$ 775.00PG-13Fantasy 51$ 770.00RComedy 52$ 758.00PGDrama 53$ 755.00PG-13Family 54$ 750.00PG-13Western 55$ 744.00PG-13Thriller 56$ 740.00RAction 57$ 736.00PG-13Thriller 58$ 736.00RThriller 59$ 731.00GCartoon 60$ 721.00PGSci-Fi 61$ 721.00RMusical 62$ 717.00RSci-Fi 63$ 700.00GSci-Fi 64$ 692.00UnratedSuperHero 65$ 684.00PGFantasy 66$ 682.00PG-13Fantasy 67$ 671.00PGAction 68$ 667.00GRomance 69$ 658.00UnratedHorror 70$ 645.00RHorror 71$ 642.00GMusical 72$ 642.00GFamily 73$ 637.00PG-13Thriller 74$ 637.00PG-13Comedy 75$ 634.00PG-13Horror 76$ 632.00UnratedHorror 77$ 629.00UnratedSuperHero 78$ 623.00RDocumentary 79$ 623.00UnratedRomance 80$ 620.00PG-13SuperHero 81$ 615.00UnratedSci-Fi 82$ 612.00UnratedDocumentary 83$ 610.00PGCartoon 84$ 610.00UnratedMusical 85$ 605.00PG-13Action 86$ 600.00GFamily 87$ 594.00PG-13SuperHero 88$ 590.00GFamily 89$ 589.00GFamily 90$ 586.00RMusical 91$ 582.00RUnknown 92$ 581.00RDrama 93$ 571.00PG-13Comedy 94$ 570.00GSci-Fi 95$ 564.00PGDocumentary 96$ 562.00PGUnknown 97$ 556.00PG-13Thriller 98$ 545.00PG-13Thriller 99$ 545.00RThriller 100$ 531.00GFamily 101$ 527.00RHorror 102$ 520.00GFantasy 103$ 503.00GDocumentary 104$ 494.00GSci-Fi 105$ 494.00PG-13Thriller 106$ 485.00PG-13Thriller 107$ 478.00RDrama 108$ 474.00RDocumentary 109$ 474.00PG-13Horror 110$ 470.00RRomance 111$ 453.00RHorror 112$ 449.00PGHorror 113$ 446.00PGUnknown 114$ 445.00PGFamily 115$ 434.00PGSci-Fi 116$ 429.00PGFamily 117$ 423.00RDrama 118$ 401.00GSci-Fi 119$ 398.00RSuperHero 120$ 393.00GFamily 121$ 391.00GMusical 122$ 388.00RAction 123$ 387.00PGRomance 124$ 386.00PG-13Unknown 125$ 381.00GRomance 126$ 380.00PGMusical 127$ 378.00PG-13Family 128$ 378.00RAction 129$ 376.00RSci-Fi 130$ 375.00PGFamily 131$ 364.00PG-13Drama 132$ 352.00PG-13Horror 133$ 335.00PG-13SuperHero 134$ 335.00PG-13Action 135$ 332.00RDocumentary 136$ 326.00GFamily 137$ 321.00PG-13SuperHero 138$ 317.00PG-13SuperHero 139$ 313.00GRomance 140$ 306.00UnratedFantasy 141$ 300.00PGCartoon 142$ 298.00PG-13Thriller 143$ 296.00RThriller 144$ 293.00GComedy 145$ 284.00PGComedy 146$ 278.00GSci-Fi 147$ 269.00RSuperHero 148$ 253.00RDocumentary 149$ 253.00RCartoon 150$ 250.00PG-13Action 151$ 245.00RHorror 152$ 241.00RSuperHero 153$ 233.00RComedy 154$ 230.00RComedy 155$ 228.00PG-13Horror 156$ 224.00PGFamily 157$ 219.00PG-13Western 158$ 218.00RFantasy 159$ 216.00PG-13Fantasy 160$ 202.00GRomance 161$ 202.00RAction 162$ 197.00PG-13Documentary 163$ 194.00PGDrama 164$ 193.00PGAction 165$ 187.00PGAction 166$ 183.00PG-13Cartoon 167$ 182.00GFamily 168$ 181.00GComedy 169$ 171.00PG-13Comedy 170$ 169.00PG-13Documentary 171$ 167.00PG-13Romance 172$ 166.00PG-13Cartoon 173$ 164.00PGRomance 174$ 157.00RCartoon 175$ 130.00PG-13Documentary 176$ 129.00PGComedy 177$ 129.00GFamily 178$ 125.00PG-13Thriller 179$ 106.00PG-13Romance 180$ 82.00PGFamily 181$ 81.00GAction 182$ 79.00RHorror 183$ 79.00RRomance 184$ 78.00RCartoon 185$ 73.00GDocumentary 186$ 70.00PG-13Sci-Fi 187$ 69.00PG-13SuperHero 188$ 69.00GCartoon 189$ 63.00PG-13Drama 190$ 60.00RHorror 191$ 57.00PGDocumentary 192$ 54.00PG-13SuperHero 193$ 36.00PGThriller 194$ 30.00RRomance 195$ 26.00RFantasy 196$ 15.00GDocumentary 197$ 14.00RAction 198$ 4.00RFantasy 199$ 4.00PGDocumentary 200$ 1.00RSuperHero Q3 - Pivot Table 11)Using the data on the Pivot Table Data Sheet, create a Pivot table showing: 1) The Movie Type, Count of Type, and Sum of Domestic Gross (in millions); columns B and D from the Pivot Table Data Sheet Have three columns:Movie Type, Count of Type, and Sum of Domestic Gross (in millions) Format the Sum of Domestic Gross (in millions) Field using $ 12)Which type of movie had the highest Domestic Gross Total for 2018? Which type of movie had the highest number of films made of that type in 2018? (You might try making more/different pivot tables to learn about the raw data. What do you want to know about Domestic Movies in 2018?) Q4 - Frequency 13Frequency