Reference no: EM133189704

MGMT 650 Assignment - Homework Questions

Question 1 - 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 midterm grade to 80. Make sure that the result is different.

assignment topic	grade obtained	maximum marks	weighted sum
assignment 1	80	100	8000
assignment 2	75	50	3750
assignment 3	80	100	8000
assignment 4	80	100	8000
assignment 5	80	100	8000
assignment 6	80	100	8000
assignment 7	80	100	8000
assignment 8	80	100	8000
assignment 9	80	100	8000
	715	850	67750

Question 2 - You are a statistician contracted to sample and analyze weights of the 5 lb bags of coffee. You collect the following data:

5.84	5.00	4.48	5.14	5.78	3.98	6.16	6.02	5.52	4.56
4.33	5.63	5.27	4.25	5.43	5.78	4.57	5.03	4.60	5.00
5.82	4.23	5.86	5.22	4.89	5.33	5.53	4.72	5.07	5.60
5.05	5.35	4.28	5.64	5.13	5.56	5.52	5.07	4.30	5.78
4.79	4.89	4.52	4.53	4.52	5.31	5.27	5.22	5.14	5.20
5.03	5.39	5.18	5.19	4.45	5.53	4.96	5.44	5.59	4.60
4.32	4.45	4.91	5.55	5.22	5.65	4.76	5.31	4.39	5.50
6.09	4.52	4.63	5.22	4.51	4.79	5.50	4.98	4.79	4.51

For all questions, write your answers in the yellow cells.

1. Compute the following statistics using Excel functions so that Excel calculates for you:

Mean

Median

2. First quartile using QUARTILE.EXC

Third quartile using QUARTILE.EXC

Interquartile range (IQR)

3. Maximum

Minimum

Range

4. Variance

Standard deviation

Coefficient 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.

Question 3 - 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 $

2) 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?)

Question 4 - 1. Frequency Distribution:

Here is a hypothetical list of the number of caramel popcorn cans sold in 70 scout troops in Maryland. Use Excel's capability (=FREQUENCY()) to create a frequency distribution

Subtract the lowest number of cans sold from the highest number of cans sold:

The range of number of cans sold is:

Decide how many bins to use. Study the data and pick a number between 5 and 10

Now divide the range of the cans sold by the number of bins to find the size that each bin should be

Size of each bin is

Create your bins:

Start with the minimum number of cans sold and add the size of the bins.

Therefore, 100 plus 20 is the highest number that the first bin should be.

The =FREQUENCY() function uses the highest number of each bin when calculating the number in each bin.

The next bin highest number starts with the first bin's highest number and adds the size of the bins.

Therefore, the second bin begins with 120 and adds the bin size 20 to get 140.

2. Continue adding to get the Bins array for the =FREQUENCY() function.

Follow the instructions in the youtube videos to use the =FREQUENCY() array function.

You know that you have correctly used the =FREQUENCY() function if Excel automatically puts {} around the function.

Note: Some versions of Excel treat the last bin a bit differently, so the FREQUENCY function may show the last bin as one higher than it should be. If you get that, don't worry about it, we will accept either answer.

Question 5 - 1. 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 500 responses, you find that 49 are younger than 6 years old, 87 are 6 to 9 years, 165 are 10 to 14, 25 are 15 to 21, and 174 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.

2. Write a few sentences summarizing the distribution demonstrated by your charts and tables.

3. 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	42	62	90	20	152
once	4	21	41	5	17
more than once	3	4	34	0	5

a)  Find the marginal distributions of their previous viewing of the movie. Margin means total. Create a new column labeled "total".

b) Verify that the marginal distribution of the ages is the same as that given previously. Create a bottom row labeled "total".

4. Continue with the data from question 3

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? What can you assume from these percentages?

e) Make a stacked bar chart showing the distribution of viewings for each age level

f) What is the percentage of all audience members who are over 21? And what percentage of audience members who saw the movie more than once are over 21?

g) If these proportions are not the same, what does that mean with respect to the variables "age" and "frequency of viewing movies"?

Attachment:- Pivot Table Data Sheet.rar