excel hw

 

0

2

4

3

6

4

3

5

6

6

6

7

6

8

3

9

6

10

6

11

4

12

6

13

2

14

6

15

4

16

6

17

7

18

3

19

5

20

6

21

5

22

Start Excel. Open the downloaded Excel file named Skills_e03_Grader_EOC.xlsx.

Rename the Sheet1 worksheet as Courthouse and then change the color of the sheet tab to Ice Blue – Accent 5.

Group all three worksheets. Add the file name field to the left footer and the sheet name field to the right footer so that they will update automatically. Return to Normal view.

With all of the worksheets grouped together, apply the cell style 40% – Accent3 to the range A4:F4.

With the range A4:F4 still selected and the worksheets grouped, center align and wrap the text in the cells.

With all of the worksheets grouped together, in cell F5, create a formula that will subtract the total deductions (in C5, D5, and E5) from the Total Gross Pay (in B5). Copy the formula down through cell F8.

With all of the worksheets grouped together, select the range B9:F9 and use the SUM function to total the values in each column.

With all of the worksheets grouped together, apply the Total cell style to the selected range (B9:F9).

With all of the worksheets grouped, apply the cell style Currency [0] to the ranges B5:F5 and B9:F9. Apply the cell style Comma [0] to the range B6:F8. Ungroup the sheets.

Insert a new worksheet. Rename the sheet tab as Summary and then change the color of the sheet tab to Orange, Accent 1.

Move the Summary sheet to the left of the Courthouse sheet tab.

Copy the range A1:F4 on the Courthouse sheet. Paste the copied values into cell A1 on the Summary sheet, keeping the source column widths.

On the Summary sheet, in cell A2, replace the existing text with City Payroll; in cell A4, replace the existing text with Location; in cell A5, type Courthouse; in cell A6, type City Center; in cell A7, typeCommunity Center; and in cell A8, type Total.

On the Summary sheet, in cell B5, create a formula that will display the value from cell B9 on the Courthouse sheet. Use the same technique in cells B6 and B7 to place the Total Gross Pay values from the City Center and the Community Center sheets on the Summary sheet.

On the Summary sheet, use the fill handle to copy the formulas in the range B5:B7 through column F.

On the Summary sheet, select the range B8:F8 and use the SUM function to total the values in each column. Apply the Total cell style to the selected range.

Create a clustered bar chart using the nonadjacent ranges A4:A7 and C4:E7 on the Summary sheet.

Move the bar chart to a new sheet and rename the chart sheet as Payroll Chart.

Add the chart title Payroll Adjustments by Location above the chart and then switch the row and column data on the bar chart.

Change the style of the bar chart to Style 13 and the layout to Layout 3.

Modify the primary horizontal axis of the bar chart so that the text is aligned with a custom angle of -40°.

Ensure that the worksheets are correctly named and placed in the following order in the workbook: Payroll Chart, Summary, Courthouse, City Center, Community Center. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

Sheet1

Gross Pay

416

Total

		Aspen Falls
Courthouse
		Job Title				Total			Income Tax			Social Security (FICA) Tax			Health Insurance			Net Pay
		Receptionist	31675	3825	2438	2771
		Security Guard	5440	653		416	476
		Maintenance	4760	577	364
		Cleaning Crew	10880	1306	832	952

City Center

Aspen Falls

City Center

Job Title

Income Tax Social Security (FICA) Tax Health Insurance Net Pay

Receptionist

Security Guard

Maintenance

Cleaning Crew

Total

	Total Gross Pay
38472	4616	2943	2308
7660	919	590	459
6240	748	480	374
13420	1610	1028	805

Community Center

Aspen Falls

Community Center

Job Title Total Gross Pay Income Tax Social Security (FICA) Tax Health Insurance Net Pay

Receptionist

Security Guard

Maintenance

Cleaning Crew

Total

48920	5870	3742	2935
9301	1116	712	560
7920	950	608	475
14203	1704	1086	852