Question

In: Statistics and Probability

Consider a study on the number of accidents occurred in 10 states of the United States...

  1. Consider a study on the number of accidents occurred in 10 states of the United States of America in different cities for 3 consecutive years. Create a PivotTable in Excel to answer the following questions. The PivotTable should group the number of accidents into yearly bins and display the sum of accidents occurred each year in columns of Excel. Row labels should include the accident locations and allow for grouping the locations into states or viewing by city. You should also sort the PivotTable so that the states with the greatest number of accidents between 2011 and 2013 appear at the top of the PivotTable.

State

City

Number of accidents

Year

GA

Rock Spring

52

2011

GA

Doraville

44

2011

GA

Ellaville

67

2011

FL

Jacksonville

53

2011

GA

Stockbridge

72

2011

FL

Belleview

63

2012

AZ

Phoenix

69

2011

FL

Crestview

51

2012

IA

Johnston

48

2012

GA

Rockmart

44

2012

CO

Greenwood Village

53

2011

GA

Jonesboro

54

2011

GA

Decatur

76

2013

FL

Clearwater

76

2013

GA

Gray

57

2012

CA

Nevada City

76

2013

FL

Milton

61

2011

GA

Woodstock

78

2013

GA

Cumming

70

2012

GA

Statesboro

47

2013

FL

Palm Beach

42

2011

CO

Greeley

60

2012

FL

Sarasota

40

2011

FL

Apollo Beach

75

2011

AZ

Prescott

40

2012

FL

Port St. Lucie

61

2012

GA

Stockbridge

78

2012

GA

Atlanta

60

2011

CO

Windsor

43

2013

CO

Castle Rock

55

2011

GA

Clayton

58

2011

....

                                                            

  1. Which state had the greatest number of accidents between 2011 and 2013?
  2. How many accidents occurred in the state of Colorado (CO) in 2012? In what cities did these accidents occur?
  3. Use the PivotTable’s filter capability to view only the accidents in Alabama (AL), Arizona (AZ), and Arkansas (AR) for the years 2011 through 2013. What is the total number of accidents in these states between 2011 and 2013?
  4. Create a PivotChart to display a column chart that shows the total number of accidents in each year 2011 through 2013 in the state of California. Adjust the formatting of this column chart so that it best conveys the data. What does this column chart suggest about accidents between 2011 and 2013 in California? Discuss.

Hint: You may have to switch the row and column labels in the PivotChart to get the best presentation for your PivotChart.

  1. Hire-a-Car System rents three types of cars at two different locations. The profit made per day for each car type and company at the two locations is listed below:

Solutions

Expert Solution

  1. Which state had the greatest number of accidents between 2011 and 2013?

Woodstock & Stockbridge

  1. How many accidents occurred in the state of Colorado (CO) in 2012? In what cities did these accidents occur?

The number of accidents = 211

Greenwood Village, Greeley, Windsor & Castle Rock are the cities in which these accidents occurred.

  1. Use the PivotTable’s filter capability to view only the accidents in Alabama (AL), Arizona (AZ), and Arkansas (AR) for the years 2011 through 2013. What is the total number of accidents in these states between 2011 and 2013?
Row Labels Sum of Number of accidents
AZ 109
(blank)
Grand Total 109
  1. Create a PivotChart to display a column chart that shows the total number of accidents in each year 2011 through 2013 in the state of California. Adjust the formatting of this column chart so that it best conveys the data. What does this column chart suggest about accidents between 2011 and 2013 in California? Discuss.

orchestra answered 11 months ago
Next > < Previous

Related Solutions

Describe the hunt for Communists that occurred within the United States.
Describe the hunt for Communists that occurred within the United States.
Because violence, suicide, and accidents are major causes of death for men in the United States,...
Because violence, suicide, and accidents are major causes of death for men in the United States, and perform the following: A). What are the leading risk factors in men for each of the following: A. violence, B. Suicide, and C. accidents. B.) Use statistical data to document incidence from each of the causes of death (violence, suicide and accidents). C.) Name at least one agency that exists in the community to help prevent death or disability from the risk factors....
Shorelake Tours Inc., operates a large number of tours throughout the United States. A study has...
Shorelake Tours Inc., operates a large number of tours throughout the United States. A study has indicated that some of the tours are not profitable, and consideration is being given to dropping these tours in order to improve the company's overall operating performance. One such tour is a two-day Battlefields of the Native American and Francophone Wars bus tour. An income statement from one of these tours is given below: Ticket revenue (100 seats x 45% occupancy x $80 ticket...
Lakeshore Tours Inc., operates a large number of tours throughout the United States. A study has...
Lakeshore Tours Inc., operates a large number of tours throughout the United States. A study has indicated that some of the tours are not profitable, and consideration is being given to dropping these tours in order to improve the company's overall operating performance. One such tour is a two-day Battlefields of the French and Indian Wars bus tour. An income statement from one of these tours is given below: Ticket revenue (100 seats x 45% occupancy x $80 ticket price)...
Case Study 2 (10 Marks) ‘Sports Zone’ is a leading sports retailer in the United States...
Case Study 2 ‘Sports Zone’ is a leading sports retailer in the United States of America. It has a chain of retail outlets which sells sports items and sports-ware across the U.S, Canada, Mexico and Europe. It recently started a retail store in Indonesia and was looking for a right candidate for the post of a retail manager. Many people were interviewed and among them was Ms. Mead, a Master’s Degree holder in Retail Management and with previous experience in...
Researchers aim to study the weights of 10-year-old girls living in the United States (possibly to...
Researchers aim to study the weights of 10-year-old girls living in the United States (possibly to compare to other countries and thus compare growth rates). Based on previous studies, we can assume that weights of 10-year-old girls are Normal. From a small sample of 16 girls, the researchers find a sample average of ?̅ = 91.4 pounds and a sample standard deviation of ? = 2.8 pounds. Create a 99% confidence interval for the true average weight of 10-year-old girls...
Examine two (2) efforts at health reform in the United States that occurred during the 1900s....
Examine two (2) efforts at health reform in the United States that occurred during the 1900s. Determine the major political and social factors that influenced the outcomes for each. Support your rationale with specific examples of such influence. Compare and contrast at least two (2) pros and cons of developing a state health insurance exchange. Speculate on which exchange you believe would be most beneficial for the majority of the insured in your state. Provide support for your rationale. Provide...
The United States is one of the nations in the world with the highest number of...
The United States is one of the nations in the world with the highest number of Economists. They are even some Economic Nobel laureates in the country. Why do you think the country goes through recessions from time to time when we have many Economists? Why did they not prevent the recession of 2007 – 2012 in the first place? Make sure to include some references at the end of the answer. Thank you.
The United States is one of the nations in the world with the highest number of...
The United States is one of the nations in the world with the highest number of Economists. They are even some Economic Nobel laureates in the country. Why do you think the country goes through recessions from time to time when we have many Economists? Why did they not prevent the recession of 2007 – 2012 in the first place? Make sure to include some references in your answers. answers to the questions in about 2 paragraphs Please
Consider the debt ceiling of the United States and the consequences of a default by the...
Consider the debt ceiling of the United States and the consequences of a default by the United States on its national debt.  Some commentators and politicians during the 2013 negotiations over raising the debt ceiling commented that failure to raise the debt ceiling would not have that dramatic an impact on world financial markets.  Comment on the consequences of a default by the US on its debt.
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT