Module 1 Quiz 1) The data set below shows annual healthcare expenditures for 192 countries. Create a histogram of the data using the bins provided in
... [Show More] column D. From the Data menu, select Data Anal ysis, then select Histogram. The Input Range is B1:B193 and the Bin Range is D1:D37. You must check the Labels in first row box to ensure that the histogram’s axes are appropriately labeled. 2) Suppose you actually want to calculate the mean annual healthcare expenditures of the 192 countries. Which of the following Excel functions calculates the mean? SELECT ALL THAT APPLY. =MEAN(B2:B193) =MEAN(B2:B193) is not a function in Excel. =AVERAGE(B2:B193) correct =AVERAGE(B2:B193) calculates the mean of the annual healthcare expenditures. Note that another option is also correct. =MEDIAN(B2:B193) =MEDIAN(B2:B193) finds the median, or middle value, of the annual healthcare expenditures. =SUM(B2:B193)/192 correct =SUM(B2:B193)/192 calculates the sum of the annual healthcare expenditures and divides that sum by 192, the number of data points. This formula calculates the mean of the annual healthcare expenditures. Note that another option is also correct =MODE.SNGL(B2:B193) .=MODE.SNGL(B2:B193) finds the mode, or most common value, of the annual healthcare expenditures.3) Calculate the standard deviation of the 2012 revenue for Forbes’ 100 top companies. STDEV.S (B2:B101)= approximately $67.32 billion. You can also use the descriptive statistics tool, making sure to link directly to values in order to obtain the correct answer. 4) Consider the four outliers in the 2012 revenue data: companies with revenue of $237 billion, $246 billion, $447 billion, and $453 billion. Ifwe removed these companies from the data set, what would happen to the standard deviation? The standard deviation would remain the same. See correct answer for explanation. The standard deviation would increase. See correct answer for explanation. The standard deviation would decrease. correct The standard deviation gives more weight to observations that are further from the mean. Therefore, removing the outliers would decrease the standard deviation. The answer cannot be determined without further information. See correct answer for explanation. 5) The following data set provides the percent of students from the top 100 ranked U.S. MBA programs that are employed upon graduation. Create a histogram to visualize the data. Use the bins provided in column C. From the Data menu, select Data Analysis, then select Histogram. The Input Range is B1:B101 and the Bin Range is C1:C15. You must check the Labels in first row box to ensure that the histogram’s axes are appropriately labeled. 6) A consultant compiled the following data set that shows the number of visits made to the National Museum of American History from 2001 to 2015. The consultant noticed that the number of visits in 2007 and 2008 seemed unusually low compared to the rest of the data set. What should the consultant do about the data points from 2007 and 2008?Delete both data points because they are outliers. The consultant should not delete data points simply because they are outliers. Leave both data points in because one should never delete research-based data. The consultant cannot assume that research-based data sets are without fault. There may be situations where data should be deleted: because of measurement or entry error; because the data are not representative of the population of interest; or any of many other reasons. Research the data points and then make a decision based on the findings. correct The consultant should delete or change data points only if careful examination of the data and the data sources indicates that the data points are incorrect or irrelevant to the research at hand. The consultant must use his or her experience and knowledge of the research question to make decisions on a case-by-case basis. Doing business analytics effectively requires judgment. In this case, the National Museum of American History underwent renovations which reduced significantly the number of visits to the museum in 2007 and 2008. The data points for 2007 and 2008 are correct and should not be changed. However, the fact that the museum was closed during most of that two year period should be considered when drawing conclusions from this data set. Change the data point for 2008 to 4,800,000 and research the data point for 2007. Although data entry errors may occur, the consultant cannot know this without researching the data points first. In this case, the National Museum of AmericanHistory underwent renovations which reduced significantly the number of visits to the museum in 2007 and 2008. The data points for 2007 and 2008 are correct and should not be changed. However, the fact that the museum was closed during most of that two year period should be considered when drawing conclusions from this data set 7) A jeweler takes stock of the diamonds she has available for mounting in the jewelry she sells. Calculate the average and standard deviation of the total number of carats in the diamonds recorded in the spreadsheet.AVERAGE(A2:A100)=0.98 and STDEV.S(A2:A100)=0.42. You can also use the descriptive statistics tool, making sure to link directly to values in order to obtain the correct answer. 8) Which of the following histograms has the smallest range? Assume all values in the data set are integers.Option A correct The range of this histogram is approximately 7–0=7. This is the smallest range in this set of histograms. Option B The range of this histogram is approximately 20–12=8. The smallest range in this set of histograms is 7.Option C The range of this histogram is approximately 27–18=9. The smallest range in this set of histograms is 7. Option D The range of this histogram is 16–6=10. The smallest range in this set of histograms is 7.9) The data below show the number of hours 60 fifth-grade students reported reading last week and each student’s gender. Use the AVERAGEIF function to calculate the average number of hours spent reading last week for boys, and the average number of hours spent reading last week for girls. This is a conditional mean, so you can either use AVERAGEIF(B2:B61,”Boys”,A2:A61)=4.48 and AVERAGEIF(B2:B61,”Girls”,A2:A61)=5.55 or AVERAGEIF(B2:B61,D2,A2:A61)=4.48 and AVERAGEIF(B2:B61,D3,A2:A61)=5.55. You could also just sort the data by gender and compute the averages of each gender, but we want you to learn how to do conditional averages in Excel. As always, it is important that you link to the cells with the data.10) The following data set provides the average driving distance for 185 members of the Professional Golf Association (PGA) Tour. Use the descriptive statistics tool to calculate the summary statistics for the average driving distance. Make sure to set the output range to cell D1 so your table is graded accurately. Use the descriptive statistics tool to calculate all of the summary statistics. The Input Range is B1:B186. You must check the Labels in first row box to ensure that the output table is appropriately labeled. You must select Summary Statistics in order to produce the output table. [Show Less]