Problem set 1 solutions
Week 2 HW
MGMT 650 Summer 2020
Week 2 Homework Questions
(Last updated 7/8/2020)
Q1 - Grade Calculato
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 co
ect 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
Here is sample data showing the weight of coffee beans in bags labeled 5 pounds. The data is in pounds.
5.83 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.41 5.26 5.13 4.72 4.54 4.58
4.59 4.62 4.6 4.67 5.13 4.97 5.27 4.93 5.12 5.14
5.85 4.67 4.91 5.45 5.19 5.14 4.91 5.37 4.89 5.95
4.76 5.95 4.91 5.81 4.59 5.91 4.91 4.69 4.81 5.41
4.93 5.31 4.63 5.62 5.84 5.82 5.57 4.61 5.47 5.33
4.58 4.83 4.73 5.57 4.76 5.5 5.84 5.1 5.63 5.22
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 numbe
Find the smallest numbe
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?
What is the standard deviation?
6) Explain why the standard deviation is a better measure to use than the variance
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?
Give an example of when you will use Coefficient of Variation in real life. This can be from your work, from the popular press, or from a statistics website
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.
Compare your answers in the previous sections with what you got here. If there is a difference, you probabkly made a mistake, go bak and check your work.
For questions 9 and 10, you are a consultant who is
ought 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.
To do a good job (and justify your consultant fees :-)) I would expect at least 1-2 paragpahs on each
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 XXXXXXXXXXin millions) Rating Type
1 $ XXXXXXXXXX PG-13 Fantasy
2 $ XXXXXXXXXX PG Documentary
3 $ XXXXXXXXXX PG-13 Thrille
4 $ XXXXXXXXXX Unrated SuperHero
5 $ XXXXXXXXXX R Fantasy
6 $ XXXXXXXXXX PG Fantasy
7 $ XXXXXXXXXX PG Fantasy
8 $ XXXXXXXXXX PG Family
9 $ XXXXXXXXXX PG Romance
10 $ XXXXXXXXXX PG-13 Musical
11 $ XXXXXXXXXX R Ho
o
12 $ XXXXXXXXXX PG-13 SuperHero
13 $ XXXXXXXXXX PG Documentary
14 $ XXXXXXXXXX Unrated Sci-Fi
15 $ XXXXXXXXXX PG-13 Ho
o
16 $ XXXXXXXXXX G Documentary
17 $ XXXXXXXXXX G Family
18 $ XXXXXXXXXX Unrated Action
19 $ XXXXXXXXXX PG Comedy
20 $ XXXXXXXXXX R Ho
o
21 $ XXXXXXXXXX R Western
22 $ XXXXXXXXXX PG-13 Romance
23 $ XXXXXXXXXX R Sci-Fi
24 $ XXXXXXXXXX Unrated Family
25 $ XXXXXXXXXX PG-13 Ho
o
26 $ XXXXXXXXXX PG-13 Sci-Fi
27 $ XXXXXXXXXX Unrated Cartoon
28 $ XXXXXXXXXX Unrated Ho
o
29 $ XXXXXXXXXX Unrated Action
30 $ XXXXXXXXXX PG Unknown
31 $ XXXXXXXXXX Unrated Ho
o
32 $ XXXXXXXXXX R Thrille
33 $ XXXXXXXXXX Unrated Thrille
34 $ XXXXXXXXXX R Drama
35 $ XXXXXXXXXX R Ho
o
36 $ XXXXXXXXXX PG Thrille
37 $ XXXXXXXXXX Unrated Drama
38 $ XXXXXXXXXX PG-13 Comedy
39 $ XXXXXXXXXX R Ho
o
40 $ XXXXXXXXXX R Ho
o
41 $ XXXXXXXXXX R Comedy
42 $ XXXXXXXXXX R Unknown
43 $ XXXXXXXXXX R Comedy
44 $ XXXXXXXXXX PG-13 Documentary
45 $ XXXXXXXXXX PG Thrille
46 $ XXXXXXXXXX PG Cartoon
47 $ XXXXXXXXXX G Family
48 $ XXXXXXXXXX Unrated Family
49 $ XXXXXXXXXX PG Musical
50 $ XXXXXXXXXX PG-13 Fantasy
51 $ XXXXXXXXXX R Comedy
52 $ XXXXXXXXXX PG Drama
53 $ XXXXXXXXXX PG-13 Family
54 $ XXXXXXXXXX PG-13 Western
55 $ XXXXXXXXXX PG-13 Thrille
56 $ XXXXXXXXXX R Action
57 $ XXXXXXXXXX PG-13 Thrille
58 $ XXXXXXXXXX R Thrille
59 $ XXXXXXXXXX G Cartoon
60 $ XXXXXXXXXX PG Sci-Fi
61 $ XXXXXXXXXX R Musical
62 $ XXXXXXXXXX R Sci-Fi
63 $ XXXXXXXXXX G Sci-Fi
64 $ XXXXXXXXXX Unrated SuperHero
65 $ XXXXXXXXXX PG Fantasy
66 $ XXXXXXXXXX PG-13 Fantasy
67 $ XXXXXXXXXX PG Action
68 $ XXXXXXXXXX G Romance
69 $ XXXXXXXXXX Unrated Ho
o
70 $ XXXXXXXXXX R Ho
o
71 $ XXXXXXXXXX G Musical
72 $ XXXXXXXXXX G Family
73 $ XXXXXXXXXX PG-13 Thrille
74 $ XXXXXXXXXX PG-13 Comedy
75 $ XXXXXXXXXX PG-13 Ho
o
76 $ XXXXXXXXXX Unrated Ho
o
77 $ XXXXXXXXXX Unrated SuperHero
78 $ XXXXXXXXXX R Documentary
79 $ XXXXXXXXXX Unrated Romance
80 $ XXXXXXXXXX PG-13 SuperHero
81 $ XXXXXXXXXX Unrated Sci-Fi
82 $ XXXXXXXXXX Unrated Documentary
83 $ XXXXXXXXXX PG Cartoon
84 $ XXXXXXXXXX Unrated Musical
85 $ XXXXXXXXXX PG-13 Action
86 $ XXXXXXXXXX G Family
87 $ XXXXXXXXXX PG-13 SuperHero
88 $ XXXXXXXXXX G Family
89 $ XXXXXXXXXX G Family
90 $ XXXXXXXXXX R Musical
91 $ XXXXXXXXXX R Unknown
92 $ XXXXXXXXXX R Drama
93 $ XXXXXXXXXX PG-13 Comedy
94 $ XXXXXXXXXX G Sci-Fi
95 $ XXXXXXXXXX PG Documentary
96 $ XXXXXXXXXX PG Unknown
97 $ XXXXXXXXXX PG-13 Thrille
98 $ XXXXXXXXXX PG-13 Thrille
99 $ XXXXXXXXXX R Thrille
100 $ XXXXXXXXXX G Family
101 $ XXXXXXXXXX R Ho
o
102 $ XXXXXXXXXX G Fantasy
103 $ XXXXXXXXXX G Documentary
104 $ XXXXXXXXXX G Sci-Fi
105 $ XXXXXXXXXX PG-13 Thrille
106 $ XXXXXXXXXX PG-13 Thrille
107 $ XXXXXXXXXX R Drama
108 $ XXXXXXXXXX R Documentary
109 $ XXXXXXXXXX PG-13 Ho
o
110 $ XXXXXXXXXX R Romance
111 $ XXXXXXXXXX R Ho
o
112 $ XXXXXXXXXX PG Ho
o
113 $ XXXXXXXXXX PG Unknown
114 $ XXXXXXXXXX PG Family
115 $ XXXXXXXXXX PG Sci-Fi
116 $ XXXXXXXXXX PG Family
117 $ XXXXXXXXXX R Drama
118 $ XXXXXXXXXX G Sci-Fi
119 $ XXXXXXXXXX R SuperHero
120 $ XXXXXXXXXX G Family
121 $ XXXXXXXXXX G Musical
122 $ XXXXXXXXXX R Action
123 $ XXXXXXXXXX PG Romance
124 $ XXXXXXXXXX PG-13 Unknown
125 $ XXXXXXXXXX G Romance
126 $ XXXXXXXXXX PG Musical
127 $ XXXXXXXXXX PG-13 Family
128 $ XXXXXXXXXX R Action
129 $ XXXXXXXXXX R Sci-Fi
130 $ XXXXXXXXXX PG Family
131 $ XXXXXXXXXX PG-13 Drama
132 $ XXXXXXXXXX PG-13 Ho
o
133 $ XXXXXXXXXX PG-13 SuperHero
134 $ XXXXXXXXXX PG-13 Action
135 $ XXXXXXXXXX R Documentary
136 $ XXXXXXXXXX G Family
137 $ XXXXXXXXXX PG-13 SuperHero
138 $ XXXXXXXXXX PG-13 SuperHero
139 $ XXXXXXXXXX G Romance
140 $ XXXXXXXXXX Unrated Fantasy
141 $ XXXXXXXXXX PG Cartoon
142 $ XXXXXXXXXX PG-13 Thrille
143 $ XXXXXXXXXX R Thrille
144 $ XXXXXXXXXX G Comedy
145 $ XXXXXXXXXX PG Comedy
146 $ XXXXXXXXXX G Sci-Fi
147 $ XXXXXXXXXX R SuperHero
148 $ XXXXXXXXXX R Documentary
149 $ XXXXXXXXXX R Cartoon
150 $ XXXXXXXXXX PG-13 Action
151 $ XXXXXXXXXX R Ho
o
152 $ XXXXXXXXXX R SuperHero
153 $ XXXXXXXXXX R Comedy
154 $ XXXXXXXXXX R Comedy
155 $ XXXXXXXXXX PG-13 Ho
o
156 $ XXXXXXXXXX PG Family
157 $ XXXXXXXXXX PG-13 Western
158 $ XXXXXXXXXX R Fantasy
159 $ XXXXXXXXXX PG-13 Fantasy
160 $ XXXXXXXXXX G Romance
161 $ XXXXXXXXXX R Action
162 $ XXXXXXXXXX PG-13 Documentary
163 $ XXXXXXXXXX PG Drama
164 $ XXXXXXXXXX PG Action
165 $ XXXXXXXXXX PG Action
166 $ XXXXXXXXXX PG-13 Cartoon
167 $ XXXXXXXXXX G Family
168 $ XXXXXXXXXX G Comedy
169 $ XXXXXXXXXX PG-13 Comedy
170 $ XXXXXXXXXX PG-13 Documentary
171 $ XXXXXXXXXX PG-13 Romance
172 $ XXXXXXXXXX PG-13 Cartoon
173 $ XXXXXXXXXX PG Romance
174 $ XXXXXXXXXX R Cartoon
175 $ XXXXXXXXXX PG-13 Documentary
176 $ XXXXXXXXXX PG Comedy
177 $ XXXXXXXXXX G Family
178 $ XXXXXXXXXX PG-13 Thrille
179 $ XXXXXXXXXX PG-13 Romance
180 $ XXXXXXXXXX PG Family
181 $ XXXXXXXXXX G Action
182 $ XXXXXXXXXX R Ho
o
183 $ XXXXXXXXXX R Romance
184 $ XXXXXXXXXX R Cartoon
185 $ XXXXXXXXXX G Documentary
186 $ XXXXXXXXXX PG-13 Sci-Fi
187 $ XXXXXXXXXX PG-13 SuperHero
188 $ XXXXXXXXXX G Cartoon
189 $ XXXXXXXXXX PG-13 Drama
190 $ XXXXXXXXXX R Ho
o
191 $ XXXXXXXXXX PG Documentary
192 $ XXXXXXXXXX PG-13 SuperHero
193 $ XXXXXXXXXX PG Thrille
194 $ XXXXXXXXXX R Romance
195 $ XXXXXXXXXX R Fantasy
196 $ XXXXXXXXXX G Documentary
197 $ XXXXXXXXXX R Action
198 $ 4.00 R Fantasy
199 $ 4.00 PG Documentary
200 $ 1.00 R SuperHero
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
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