Question
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
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 | |||||
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started