Last Name	First Name	Title	Salary	Location	
Aikman	Gary	Account Rep	 $83,600 	Detroit	
Felser	Cynthia	Account Rep	 $81,800 	Boston	
Granger	Kevin	Trainee	 $41,000 	Detroit	
Noble	Linda	Account Rep	 $93,800 	Boston	
Beamer	Larry	Trainee	 $43,400 	Boston	
Powell	Kathy	Account Rep	 $89,800 	Chicago	
Mills	Wendy	Manager	 $141,800 	Detroit	
Watson	Fran	Account Rep	 $65,600 	Chicago	
Pennekamp	Lynn	Account Rep	 $67,400 	New York	
Fegin	Nancy	Account Rep	 $50,400 	Boston	
Erpf	Meredith	Account Rep	 $73,800 	Boston	
Jones	Kim	Account Rep	 $91,600 	New York	
Jackson	Heidi	Account Rep	 $89,400 	Boston	
Bocholis	Wendy	Senior Account Rep	 $112,400 	Boston	
Vergaldi	Deborah	Trainee	 $36,600 	Chicago	
Stack	Andrew	Trainee	 $43,600 	Boston	
Kirsman	Christy	Senior Account Rep	 $99,600 	Chicago	
O'Keefe	Ben	Manager	 $168,800 	Chicago	
Rodriguez	Lisa	Senior Account Rep	 $78,600 	Detroit	
Brett	Phil	Account Rep	 $96,400 	New York	
Brickey	Wiliam	Account Rep	 $55,600 	Chicago	
Dill	Jeanne	Account Rep	 $64,000 	Boston	
Rapee	Paula	Account Rep	 $61,000 	Detroit	
Gander	David	Account Rep	 $66,200 	Detroit	
Felsher	Joellen	Senior Account Rep	 $89,200 	Detroit	
Trice	Burt	Trainee	 $37,400 	Boston	
Watt	George	Trainee	 $44,000 	Detroit	
Edelstein	Martin	Trainee	 $38,400 	New York	
Goldstein	George	Account Rep	 $65,000 	Chicago	
Carlo	Criste	Account Rep	 $63,800 	Chicago	
Hoffman	Marjorie	Account Rep	 $54,400 	Boston	
Mason	Elise	Account Rep	 $90,000 	Chicago	
Rodriguez	Susan	Account Rep	 $88,400 	Detroit	
Denver	Jan	Account Rep	 $87,800 	Boston	
Krissel	Linda	Senior Account Rep	 $88,600 	Boston	
Strafaci	Jodi	Account Rep	 $92,200 	Detroit	
Winnick	Jack	Account Rep	 $71,800 	Detroit	
Smith	Denise	Senior Account Rep	 $82,800 	Boston	
McClendon	Bob	Manager	 $154,200 	Boston	


Last Name	First Name	Title	Salary	Location	Performance
Aikman	Gary	Account Rep	 $83,600 	Detroit	Good
Felser	Cynthia	Account Rep	 $81,800 	Boston	Average
Granger	Kevin	Trainee	 $41,000 	Detroit	Good
Noble	Linda	Account Rep	 $93,800 	Boston	Good
Beamer	Larry	Trainee	 $43,400 	Boston	Average
Powell	Kathy	Account Rep	 $89,800 	Chicago	Average
Mills	Wendy	Manager	 $141,800 	Detroit	Good
Watson	Fran	Account Rep	 $65,600 	Chicago	Good
Fegin	Nancy	Account Rep	 $50,400 	Boston	Excellent
Erpf	Meredith	Account Rep	 $73,800 	Boston	Excellent
Jackson	Heidi	Account Rep	 $89,400 	Boston	Good
Bocholis	Wendy	Senior Account Rep	 $112,400 	Boston	Good
Vergaldi	Deborah	Trainee	 $36,600 	Chicago	Average
Stack	Andrew	Trainee	 $43,600 	Boston	Average
Kirsman	Christy	Senior Account Rep	 $99,600 	Chicago	Average
O'Keefe	Ben	Manager	 $168,800 	Chicago	Good
Rodriguez	Lisa	Senior Account Rep	 $78,600 	Detroit	Excellent
Brickey	Wiliam	Account Rep	 $55,600 	Chicago	Average
Dill	Jeanne	Account Rep	 $64,000 	Boston	Poor
Rapee	Paula	Account Rep	 $61,000 	Detroit	Good
Gander	David	Account Rep	 $66,200 	Detroit	Good
Felsher	Joellen	Senior Account Rep	 $89,200 	Detroit	Good
Trice	Burt	Trainee	 $37,400 	Boston	Excellent
Watt	George	Trainee	 $44,000 	Detroit	Average
Goldstein	George	Account Rep	 $65,000 	Chicago	Average
Carlo	Criste	Account Rep	 $63,800 	Chicago	Average
Hoffman	Marjorie	Account Rep	 $54,400 	Boston	Average
Mason	Elise	Account Rep	 $90,000 	Chicago	Excellent
Rodriguez	Susan	Account Rep	 $88,400 	Detroit	Average
Denver	Jan	Account Rep	 $87,800 	Boston	Good
Krissel	Linda	Senior Account Rep	 $88,600 	Boston	Excellent
Strafaci	Jodi	Account Rep	 $92,200 	Detroit	Excellent
Winnick	Jack	Account Rep	 $71,800 	Detroit	Excellent
Smith	Denise	Senior Account Rep	 $82,800 	Boston	Average
McClendon	Bob	Manager	 $154,200 	Boston	Good




Salary Data

Project Description: 
As the Human Resources Manager, you maintain employee salary data. You exported data from the corporate database into an Excel workbook. You want to display subtotals for salary by city. For further analysis, you want to create a PivotTable to determine what the total salaries would be if employees who earn an Excellent rating get a 5% increase in salary. Finally, you want to create a PivotChart that depicts what percentage of employees earn each performance rating.

Instructions: 
For the purpose of grading the project you are required to perform the following tasks: 
Step	Instructions	Points Possible
1	Start Excel. Open the downloaded Excel file named exploring_e05_grader_a1_start.xlsx.	0
2	In the Subtotal worksheet, sort the data by Location in alphabetical order and then by Title in Alphabetical order.	5
3	In the Subtotal worksheet, use the Subtotal feature to calculate the average Salary by Location.	10
4	In the Subtotal worksheet, display only the Location averages and grand average. Expand the details for Chicago.	5
5	Use the Data worksheet to create a recommended PivotTable. Select the Sum of Salary by Title recommended PivotTable. Rename the new worksheet PivotTable.	10
6	Add the Location field to the COLUMNS area and remove the Last Name field from the ROWS area in the PivotTable.	5
7	Modify the VALUES field by changing the custom name to Salary Totals and formatting the values with Currency number type with zero decimal places.	10
8	Set a Performance filter to display Excellent ratings only.	5
9	Insert a calculated field named Field1 that calculates a 5% increase in salaries. The new value should give the total new salaries, not just the increase.	5
10	Modify the calculated field by changing the custom name to New Salary Totals and formatting the values with Currency number type with zero decimal places.	5
11	Type Job Titles in cell A5 and Cities in cell B3.	5
12	Apply Pivot Style Light 15, and display banded columns.	5
13	Use the Data worksheet to create a PivotChart using the Performance field for both the AXIS (CATEGORY) and VALUES areas on a new sheet named Performance. Change the chart type to Pie.	5
14	Hide the field buttons in the PivotChart and change the chart title to Performance Ratings.	5
15	Sort the data in the Performance PivotTable to display the largest number to the smallest number.	5
16	Add data labels to the Inside End position and change them from values to percentages. Group the data labels together and apply bold and 10-pt size to them.	10
17	Adjust the size of the PivotChart for the range A8:D20.	5
18	Ensure that the worksheets are correctly named and placed in the following order in the workbook: Subtotal, PivotTable, Performance, Data. Save the workbook. Close the workbook, and then exit Excel. Submit the workbook as directed.	0
	Total Points	100