Question

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
Skip 13-17
		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

3

Total Points

100

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

Answer 1