Project Homework Help: In this project, you will work with grouped worksheets, unhide a worksheet, delete a worksheet, insert a worksheet, and construct formulas that refer to cells in other worksheets

SK16_XL_CH03_GRADER_SR1_HW – Payroll 1.5

Project Description:

In this project, you will work with grouped worksheets, unhide a worksheet, delete a worksheet, insert a worksheet, and construct formulas that refer to cells in other worksheets. Additionally, you will create and modify a clustered bar chart.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Start Excel 2016. Download and open the file sk16_xl_ch03_grader_SRPayroll_hw.xlsx Save the file as file Last_First_sk16_xl_ch03_grader_SRPayroll_hw.xlsx

0.000

2

Group the worksheets. In cell A19, clear all formats and contents. Format the range A4:F4 with the cell style 40% – Accent3. Center align and wrap the text in the selected cells.

7.500

3

In cell F5, create the formula Total Gross Pay – (Income Tax + Social Security (FICA) Tax + Health Insurance). AutoFill the formula down through cell F12.

9.000

4

Apply the cell style Comma [0] to the range B6:F12. Apply the Total cell style to the range B13:F13. Ungroup the worksheets.

4.500

5

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

7.000

6

Move the Summary sheet to the left of the Community Center worksheet tab.

2.000

7

On the Community Center sheet, copy the range A1:F4. Paste the copied values into cell A1 on the Summary sheet, keeping the source column widths.

6.000

8

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 Community Center In cell A6, type City Center In cell A7, type Courthouse and in cell A8, type Total

6.000

9

On the Summary sheet, in cell B5, create a formula that will display the value from cell B13 on the Community Center sheet. In cell B6, create a formula that will display the value from cell B13 on the City Center sheet. In cell B7, create a formula that will display the value from cell B13 on the Courthouse sheet.

10.000

10

On the Summary sheet, in cell B8, insert a column total. AutoFill the formulas in the range B5:B8 to the right through column F. Apply the Total cell style to the range B8:F8. Apply Comma [0] cell style to B6:F7.

9.000

11

Group all of the worksheets. Add the file name field to the left footer and the sheet name field to the right footer. Ungroup the worksheets.

6.000

12

On the Summary sheet, create a Clustered Bar chart using the nonadjacent ranges A4:A7 and C4:E7.

8.000

13

Move the bar chart to a chart sheet with the sheet name Payroll Adjustments

6.000

14

Enter the chart title Payroll Adjustments by Location above the chart. Switch the row and column data on the bar chart. Change the style of the bar chart to Style 2. Add a primary horizontal axis title with the text Deduction.

9.000

15

On the Summary sheet in cell A12, type Date Created In cell A13 enter the date 6/17/2018

2.000

16

Unhide the Art Center worksheet, and then delete the Art Center worksheet.

4.000

17

Ensure that the worksheets are correctly named and placed in the following order in the workbook: Payroll Adjustments, Summary, Community Center, City Center, Courthouse. Save the file, close Excel, and then submit the file as directed by your instructor.

4.000

Total Points

100.000

Aspen Falls
Community Center
Job Title Total Gross Pay Income Tax Social Security (FICA) Tax Health Insurance Net Pay
Receptionist $48,920 $5,381 $2,642 $2,397
Security Guard $45,415 $4,996 $2,452 $2,225
Director $63,400 $6,974 $3,424 $3,107
Facility Manager $58,600 $6,446 $3,164 $2,871
Maintenance Worker $32,786 $3,606 $1,770 $1,607
Janitor $23,475 $2,582 $1,268 $1,150
Parking Cashier $24,004 $2,640 $1,296 $1,176
Operations Worker $38,062 $4,187 $2,055 $1,865
Total $334,662 $36,813 $18,072 $16,398 $-
Today’s Date
Aspen Falls
Courthouse
Job Title Total Gross Pay Income Tax Social Security (FICA) Tax Health Insurance Net Pay
Receptionist $31,675 $3,484 $1,710 $1,552
Security Guard $25,440 $2,798 $1,374 $1,247
Director $65,507 $7,206 $3,537 $3,210
Facility Manager $45,376 $4,991 $2,450 $2,223
Maintenance Worker $24,760 $2,724 $1,337 $1,213
Janitor $20,880 $2,297 $1,128 $1,023
Parking Cashier $22,785 $2,506 $1,230 $1,116
Operations Worker $34,996 $3,850 $1,890 $1,715
Total $271,419 $29,856 $14,657 $13,300 $-
Today’s Date