[ad_1]
Statistics 5 tabs
Week 2 HW Questions
MGMT 650 Summer 2020 Week 2 Homework Questions
Q1 – Descriptive Stat
Here is sample data showing the weight of coffee beans in bags labeled 5 pounds. The data is in pounds.
5.75 4.82 5.25 5.96 4.59 4.52 5.06 5.67 5.73 4.71
5.5 5.84 4.82 5.98 4.89 5.39 4.73 4.96 5.59 4.86
4.94 4.9 4.71 4.99 5.61 5.26 5.3 4.92 4.54 4.58
4.59 4.72 4.6 4.67 5 4.97 5.47 4.88 5.02 5.27
5.85 4.67 4.91 5.63 5.1 5.7 4.91 5.62 4.99 5.95
4.76 5.95 4.91 5.81 4.54 5.91 4.91 4.63 4.81 5.73
4.93 5.81 4.7 5.82 5.84 5.9 5.75 4.61 5.77 5.83
4.58 4.63 4.66 5.57 4.76 5.5 5.84 5.1 5.63 5.72
For the following questions, you must use Excel formulas in the cells so that Excel calculates the answers for you.
1) Compute the mean:
Compute the median
Find the mode
2) Compute the first quartile; use =QUARTILE.EXC()
First Quartile
Compute the third quartile; use =QUARTILE.EXC()
Third Quartile
Compute the interquartile range
3) Find the largest number
Find the smallest number
What is the range?
4) What is the difference between the Excel functions, STDEV.P and STDEV.S? (Hint: Use the Excel help files.)
5) What is the Variance?
6) What is the standard deviation?
7) What is the Coefficient of Variation, or the CV?
https://www.statisticshowto.datasciencecentral.com/probability-and-statistics/how-to-find-a-coefficient-of-variation/
When is the Coefficient of Variation especially useful?
Copy all of the data into a column, use Column M and go from cell M1:M80
8) Use the Data Analysis tool on the numbers just copied to find the Descriptive Statistics:
Click on Data Analysis and Choose Descriptive Statistics
Click on the Summary Statistics box.
Highlight the mean, median, mode, Standard deviation, Range, Minimum, and Maximum
Notice that the Data Analysis tool gives you all of the info needed for this problem except for the quartiles, variance, and CV.
For questions 9 and 10, you are a consultant who is brought in and given these numbers and asked to generate a
report to management. What would your recommendation be?
These two parts are your chance to show your understanding of the material.
9) Interpret the measures of central tendency within the context of this problem.
Should the company producing the coffee be concerned about the central tendency?
10) Interpret the measures of variation within the context of this problems.
Should the company producing the coffee be concerned about variation?
&D
https://www.statisticshowto.datasciencecentral.com/probability-and-statistics/how-to-find-a-coefficient-of-variation/
Pivot Table Data
Movie Rank Domestic Gross (in millions) Rating Type
1 $ 999.00 PG-13 Fantasy
2 $ 988.00 PG Documentary
3 $ 984.00 PG-13 Thriller
4 $ 984.00 Unrated SuperHero
5 $ 981.00 R Fantasy
6 $ 979.00 PG Fantasy
7 $ 972.00 PG Fantasy
8 $ 970.00 PG Family
9 $ 970.00 PG Romance
10 $ 957.00 PG-13 Musical
11 $ 956.00 R Horror
12 $ 953.00 PG-13 SuperHero
13 $ 952.00 PG Documentary
14 $ 951.00 Unrated Sci-Fi
15 $ 948.00 PG-13 Horror
16 $ 939.00 G Documentary
17 $ 934.00 G Family
18 $ 932.00 Unrated Action
19 $ 932.00 PG Comedy
20 $ 930.00 R Horror
21 $ 907.00 R Western
22 $ 905.00 PG-13 Romance
23 $ 904.00 R Sci-Fi
24 $ 903.00 Unrated Family
25 $ 898.00 PG-13 Horror
26 $ 896.00 PG-13 Sci-Fi
27 $ 882.00 Unrated Cartoon
28 $ 878.00 Unrated Horror
29 $ 875.00 Unrated Action
30 $ 874.00 PG Unknown
31 $ 873.00 Unrated Horror
32 $ 860.00 R Thriller
33 $ 856.00 Unrated Thriller
34 $ 851.00 R Drama
35 $ 848.00 R Horror
36 $ 839.00 PG Thriller
37 $ 835.00 Unrated Drama
38 $ 830.00 PG-13 Comedy
39 $ 829.00 R Horror
40 $ 824.00 R Horror
41 $ 821.00 R Comedy
42 $ 819.00 R Unknown
43 $ 815.00 R Comedy
44 $ 809.00 PG-13 Documentary
45 $ 802.00 PG Thriller
46 $ 798.00 PG Cartoon
47 $ 792.00 G Family
48 $ 787.00 Unrated Family
49 $ 785.00 PG Musical
50 $ 775.00 PG-13 Fantasy
51 $ 770.00 R Comedy
52 $ 758.00 PG Drama
53 $ 755.00 PG-13 Family
54 $ 750.00 PG-13 Western
55 $ 744.00 PG-13 Thriller
56 $ 740.00 R Action
57 $ 736.00 PG-13 Thriller
58 $ 736.00 R Thriller
59 $ 731.00 G Cartoon
60 $ 721.00 PG Sci-Fi
61 $ 721.00 R Musical
62 $ 717.00 R Sci-Fi
63 $ 700.00 G Sci-Fi
64 $ 692.00 Unrated SuperHero
65 $ 684.00 PG Fantasy
66 $ 682.00 PG-13 Fantasy
67 $ 671.00 PG Action
68 $ 667.00 G Romance
69 $ 658.00 Unrated Horror
70 $ 645.00 R Horror
71 $ 642.00 G Musical
72 $ 642.00 G Family
73 $ 637.00 PG-13 Thriller
74 $ 637.00 PG-13 Comedy
75 $ 634.00 PG-13 Horror
76 $ 632.00 Unrated Horror
77 $ 629.00 Unrated SuperHero
78 $ 623.00 R Documentary
79 $ 623.00 Unrated Romance
80 $ 620.00 PG-13 SuperHero
81 $ 615.00 Unrated Sci-Fi
82 $ 612.00 Unrated Documentary
83 $ 610.00 PG Cartoon
84 $ 610.00 Unrated Musical
85 $ 605.00 PG-13 Action
86 $ 600.00 G Family
87 $ 594.00 PG-13 SuperHero
88 $ 590.00 G Family
89 $ 589.00 G Family
90 $ 586.00 R Musical
91 $ 582.00 R Unknown
92 $ 581.00 R Drama
93 $ 571.00 PG-13 Comedy
94 $ 570.00 G Sci-Fi
95 $ 564.00 PG Documentary
96 $ 562.00 PG Unknown
97 $ 556.00 PG-13 Thriller
98 $ 545.00 PG-13 Thriller
99 $ 545.00 R Thriller
100 $ 531.00 G Family
101 $ 527.00 R Horror
102 $ 520.00 G Fantasy
103 $ 503.00 G Documentary
104 $ 494.00 G Sci-Fi
105 $ 494.00 PG-13 Thriller
106 $ 485.00 PG-13 Thriller
107 $ 478.00 R Drama
108 $ 474.00 R Documentary
109 $ 474.00 PG-13 Horror
110 $ 470.00 R Romance
111 $ 453.00 R Horror
112 $ 449.00 PG Horror
113 $ 446.00 PG Unknown
114 $ 445.00 PG Family
115 $ 434.00 PG Sci-Fi
116 $ 429.00 PG Family
117 $ 423.00 R Drama
118 $ 401.00 G Sci-Fi
119 $ 398.00 R SuperHero
120 $ 393.00 G Family
121 $ 391.00 G Musical
122 $ 388.00 R Action
123 $ 387.00 PG Romance
124 $ 386.00 PG-13 Unknown
125 $ 381.00 G Romance
126 $ 380.00 PG Musical
127 $ 378.00 PG-13 Family
128 $ 378.00 R Action
129 $ 376.00 R Sci-Fi
130 $ 375.00 PG Family
131 $ 364.00 PG-13 Drama
132 $ 352.00 PG-13 Horror
133 $ 335.00 PG-13 SuperHero
134 $ 335.00 PG-13 Action
135 $ 332.00 R Documentary
136 $ 326.00 G Family
137 $ 321.00 PG-13 SuperHero
138 $ 317.00 PG-13 SuperHero
139 $ 313.00 G Romance
140 $ 306.00 Unrated Fantasy
141 $ 300.00 PG Cartoon
142 $ 298.00 PG-13 Thriller
143 $ 296.00 R Thriller
144 $ 293.00 G Comedy
145 $ 284.00 PG Comedy
146 $ 278.00 G Sci-Fi
147 $ 269.00 R SuperHero
148 $ 253.00 R Documentary
149 $ 253.00 R Cartoon
150 $ 250.00 PG-13 Action
151 $ 245.00 R Horror
152 $ 241.00 R SuperHero
153 $ 233.00 R Comedy
154 $ 230.00 R Comedy
155 $ 228.00 PG-13 Horror
156 $ 224.00 PG Family
157 $ 219.00 PG-13 Western
158 $ 218.00 R Fantasy
159 $ 216.00 PG-13 Fantasy
160 $ 202.00 G Romance
161 $ 202.00 R Action
162 $ 197.00 PG-13 Documentary
163 $ 194.00 PG Drama
164 $ 193.00 PG Action
165 $ 187.00 PG Action
166 $ 183.00 PG-13 Cartoon
167 $ 182.00 G Family
168 $ 181.00 G Comedy
169 $ 171.00 PG-13 Comedy
170 $ 169.00 PG-13 Documentary
171 $ 167.00 PG-13 Romance
172 $ 166.00 PG-13 Cartoon
173 $ 164.00 PG Romance
174 $ 157.00 R Cartoon
175 $ 130.00 PG-13 Documentary
176 $ 129.00 PG Comedy
177 $ 129.00 G Family
178 $ 125.00 PG-13 Thriller
179 $ 106.00 PG-13 Romance
180 $ 82.00 PG Family
181 $ 81.00 G Action
182 $ 79.00 R Horror
183 $ 79.00 R Romance
184 $ 78.00 R Cartoon
185 $ 73.00 G Documentary
186 $ 70.00 PG-13 Sci-Fi
187 $ 69.00 PG-13 SuperHero
188 $ 69.00 G Cartoon
189 $ 63.00 PG-13 Drama
190 $ 60.00 R Horror
191 $ 57.00 PG Documentary
192 $ 54.00 PG-13 SuperHero
193 $ 36.00 PG Thriller
194 $ 30.00 R Romance
195 $ 26.00 R Fantasy
196 $ 15.00 G Documentary
197 $ 14.00 R Action
198 $ 4.00 R Fantasy
199 $ 4.00 PG Documentary
200 $ 1.00 R SuperHero
Q2 – 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); using columns B and D from the Pivot Table Data Sheet
Show 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?
13) 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?)
Q3 – Frequency
Use the raw Data on the Pivot Table Data Sheet to create a Frequency Chart:
Follow these steps to get the Frequency chart. The following website also has instructions to create bins.
https://www.statisticshowto.datasciencecentral.com/choose-bin-sizes-statistics/
14) Step One: (Find the lowest and highest numbers in the data.) Use the Excel sheet titled Pivot Table Data
What is the Total DomesticGross of the lowest movie sales? Hint – use either =MIN()or just choose the movie at the bottom of the list
What is the Total DomesticGross of the highest movie sales? Hint – use either =MAX()or just choose the movie at the top of the list
15) Step Two: (Find the range by subtracting the lowest number from the highest number.)
Subtract the lowest from the highest to find the range of the Domestic Gross take for the Movies
The range of Total Domestic Gross for these movies is
16) Step Three: (Find the bin widths by dividing the range by the number of bins that you want to have.)
We will use 10 bins so divide the range by 10:
Each bin will be : wide. Do not round.
Step Four: (Find the highest number for the first bin by starting with the lowest number in the data set and adding the bin width.)
Start with the minimum number:
Add the width of the bins
This number is the highest number that is used in the first bin.
Excel will use this number when it counts the number of pieces of data in the raw data set that is lower than this number.
Put this number in cell C38 for the first bin.
Step Five: (Find the rest of the highest bin numbers. Start with the highest number in the first bin. Add the bin width. This number is the highest total for bin 2.)
The next bin’s’s highest number starts with the first bin highest number and adds the size of the bins
Therefore, the second bin begins with and adds the bin size to get Place this number in cell C39.
Successive bins start with the previous bin’s highest number and adds the width of thebins. The last bin will have the maximum number in the data set as its highest value.
Therefore, continue adding to get the Bins array for the =FREQUENCY() function.
The last bin number in cell C47 will equal the highest Domestic Gross movie total
17) Here are the highest numbers for each bin:
Bins:
18) Follow the instructions in the youtube videos to use the =FREQUENCY() array function. https://www.youtube.com/watch?v=c4b1F4-tv8Q
You know that you have correctly used the =FREQUENCY() function if Excel automatically puts {} around the function.
Don’t forget to push Control-Shift-Enter at the same time to enter the =FREQUENCY function.
Bins: Frequency:
https://www.youtube.com/watch?v=c4b1F4-tv8Q https://www.statisticshowto.datasciencecentral.com/choose-bin-sizes-statistics/
Q4 – Charts
Copy the Bins and Frequency Data from the Q4 – Frequency sheet
Bins: Frequency:
19) Histogram
Create a Histogram of the Bins and Frequency data by first creating a column Chart and then removing the spaces between the columns.
20) Format the historgram so there are no spaces between the bars. Histograms do not have spaces and the graph does not become a Histogram until the spaces are removed.
Add a title to the Histogram
Add horizontal and Vertical Axes titles
21) Explain the difference between a histogram and a bar graph:
22)
Make a pie chart of the raw frequency data with a title and Legend:
Q5-Two Way
23 As part of the marketing group of a film company, you are asked to find out the age distribution of the audience of the latest film.
You ask questions of customers who exit the theatre. From 470 responses, you find that 45 are younger than 6 years old, 83 are 6 to 9 years,
154 are 10 to 14, 18 are 15 to 21, and 170 are older than 21.
a) Make a frequency table of these categorical data.
b) Make a relative frequency table.
c) Make a bar chart using counts in the frequency table.
d) Would a bar chart of relative frequencies look any different?
e) Make a pie chart.
f) Write a few sentences summarizing the distribution demonstrated by your charts and tables.
In addition to age grouping information, the audiences interviewed were also asked if they had seen the movie before (Never, Once, More than Once).
under 6 6 to 9 10 to 14 15 to 21 over 21
never 39 60 84 16 151
once 3 20 38 2 15
more than once 3 3 32 0 4
24 a) Find the marginal distributions of their previous viewing of the movie.
b) Verify that the marginal distribution of the ages is the same as that given previously.
c) Find column percentages.
d) Looking at these percentages, does the distribution of how many times someone has seen the movie look the same for each age group?
e) Make a stacked bar chart showing the distribution of viewings for each age level
What Students Are Saying About Us
.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐"Honestly, I was afraid to send my paper to you, but splendidwritings.com proved they are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"
.......... Customer ID: 14***| Rating: ⭐⭐⭐⭐⭐
"The company has some nice prices and good content. I ordered a term paper here and got a very good one. I'll keep ordering from this website."
