Reference no: EM132372571

Statistics for Managerial Decision Making Assignment - Questions

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.

Q2. 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()

Compute the third quartile; use = QUARTILE.EXC()

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?

When is the Coefficient of Variation especially useful?

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?

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 D and I 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?

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

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.

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 highest number starts with the first bin highest number and adds the size of the bins

Therefore, the second bin begins with

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:

18) 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.

Don't forget to push Control-Shift-Enter at the same time to enter the =FREQUENCY function.  

Q5. Charts

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.

Attachment:- Assignment File - Questions.rar