EC

Asgn10-Excel BI

Create a Word document to insert screen shots of your work. Name your document CH11BI. Eg. if your last name is Smith, your document name should be SmithAsgn10-BI.

Download the attached spreadsheet named Asgn10-AutoInsuranceCustomers and perform the required anaylsis below:

1. Creat a pivot table and pivot chart to show each Car Category (Estate, Saloon, and Sport) and the count of Automatic and Manual Gearbox. Note, you may construct a different pivot table that gives the same information. Name this worksheet as Gearbox . Take a screen shot of your work and inserted into Word document.

2. Create a conditional formatting rule, to highlight each row that contains “Sports” as Car Category, use any color. Take a screen shot of your work Take a screen shot of your work and inserted into Word document.

.

3. Create a pivot table and pivot chart to show the average age of both genders for Customer types (Agency and online). Format the average to 2 decimal places. Name this worksheet as Age . Take a screen shot of your work Take a screen shot of your work and inserted into Word document.

4. Upload your spreadsheet and Word doc that has the screen shots to BB. You are allowed 2 submissions for this assignment.

Note: No credit will be given if the spreadsheet doesn’t show the pivot tables, charts, and conditional formatting that match the screen shots in your document.