Project Objective: This exercise provides an opportunity for you do demonstrate the transformation of data into information by using various MS Excel data aggregation tools, including pivot tables. The project is also intended to teach formatting conventions required for exhibits created in subsequent projects.

What to do:

1. Retrieve a copy of the file https://docs.google.com/spreadsheets/d/1BtyWh6G_xuRZhecl0cjK8IKAw0kc1HJRYMCpB_q7dgc/edit?usp=sharing
2. Use MS Excel to conduct various summary analyses per the instructions below.
3. Format the results as appropriate for the analysis being conducted.

Required Analysis:

1. Using the sumif, averageif, and countif functions, create the exhibit below on page one of your Excel workbook, showing (for each town shown) the number of customers and their average age, average daily commute, and total premium billed. Change the tab label on this worksheet from "Sheet1" to "Q1". NOTE: Pivot tables are not meant to be used for this portion of the project.

	Customers
Town	Number	Avg Age	Avg Daily Commute	Total Premiums
Albion
Brighton
Depew
Holly
Medina

1. On the second sheet of your workbook, change the tab label from "Sheet 2" to "Q2" and then do the following:
   1. Use the frequency function to create frequency distributions for Age, Daily Commute, and Premiums Paid. Class widths and bounds are left to your discretion (use at least five classes though).
   2. Use the countif function to create frequency distributions for make and body type.

1. For parts a) through g) below, create separate pivot tables that provides the information requested.Use separate workbook pages for each table, with pages labeled Qa, Qb, Qc, etc.
   1. Number of cars by Body Typeand Type of Registration.
   2. Average customer age by Make and Use.
   3. Average automobile age by City and Where the car is parked. (hint: year is not the age of the car)
   4. Average Commute by Points and Use
   5. Average policy premium by Color and Parking.
   6. Average policy premium by Points and Gender.
   7. Number of cars by Number of Points and Type of Registration.

• Create (on separate pages of your workbook) pivot tables that will indicate:
  1. The age of the oldest driver in each town.
  2. The number of drivers that have not reported an accident in the last ten years, by gender.
  3. Use a pivot table to generate the information required to create the exhibit shown below: The columns should indicate the number of cars that have antilock breaks/and or anti-theft devices and the rows should show the number of cars by multi-policy discount status. Your exhibit must follow the row/column ordering shown and the exhibit should include only the columns shown.
  4. Create a similar exhibit to that created in part j), analyzing average premium for each category.

	Anti-lock Breaks	Standard Breaks
	Anit-Theft	No Anti-Theft	Anti-theft	No-Anti Theft	Total
Multi-Policy Disc
No Discount
Total

Update the sheet tab labels, for workbook pages used for parts h-k above, to Qk, Qj, etc.

1. Use Excel to Format the pivot tables appropriately, including:
   1. Proper and alignment of numbers and labels;
   2. A reasonable number of decimal places (large numbers do not need decimal places, and integer phenomena should not have decimal places, two decimals max);
   3. Proper formatting (dollar, comma, etc.);