Complete the following tasks and formats:
1. Open the Final Assignment data file and Save the Workbook as Final Assignment Your Name.
2. Bold the Entire Worksheet Set the Theme to Executive
3. On the Lookup Tables Worksheet please name the following 3 listings:
a. Using the cell range A3 through C33 create and apply the Name TaxTable
b. Using the cell range E3 through F4 create and apply the Name MSTable
c. Using the cell range H3 through I8 create and apply the Name DepTable
4. Switch back to the Payroll Worksheet and calculate the Gross pay in cell L5
a. Using an IF function calculate the Gross pay to determine if the user worked Overtime (Hours greater than 40). If the user did work OT then the pay would be calculated as
1.5 times the rate for the hours over 40. If the person did not work OT then the pay would be calculated as the rate times the hours worked.
b. Use the following pseudocode in the creation of your formula with the IF function
i. Equal IF(Hours worked is greater than 40 then (((Hours worked -40)times 1.5) plus 40) times the rate of pay; else Hours worked times rate of pay)
c. Copy the formula down to row 19
5. In cell M5, using the VLOOKUP Function determine the Fed Tax Rate for the Federal Tax from the TaxTable previously created by matching the gross pay to the table's matching column and returning the tax.
6. In cell N5, using the VLOOKUP Function determine the Fed Tax MS rate for the MS from the MSTable previously created by matching the Marital Status to the table's matching column and returning the rate. (hint use the exact match option)
7. In Cell O5, using the VLOOKUP Function determine the Fed Tax DEP rate for the Dep from the DepTable previously created by matching the Number of Dependents to the table's matching column and returning the rate.
8. In Cell P5 determine the Federal tax using the following Algebraic Formula
a. Use the results from the VLOOKUP's calculated above
b. Use the formula
i. =Fed tax Rate minus (Fed Tax rate times(Ms Rate plus Dep Rate))
c. Copy the formulas down to row 19
9. In cells Q5 through T5 perform the same calculations to obtain the State Tax results
a. Copy the formulas down to row 19
10. In cell U5 calculate FICA by multiplying the Gross Pay times 5.65%
a. Copy the formula down to row 19
11. In cell X5 calculate the total deductions by summing all of the deductions from cells, P5, T5, U5, V5 and W5
a. Copy this formula down to row 19
12. In cell Y5 Calculate the Net pay by taking the gross pay (L5) and subtracting the total deductions (X5)
a. Copy this formula down to row 19
13. In cell D2 Enter the system date, change the date format to DD-MON-YR example 30-Jun-11
Perform the following Formats:
1. Merge and center cell A1 through Z1 and set the Title Style and Center Vertically
2. Draw a thick border around the range C2:D2
3. Format as a table the range A4:Z19 using the Table Style Medium 10
a. Name the Table PayrollTable
b. Remove the filter from the Table
4. Set the following Column widths and Row heights:
a. Set Rows 1 and 4 to 30
b. Set Columns A & Z to 8
c. Set Columns B & D and H through Y to 11
d. Set Column C to 16
e. Set Columns E & F to 6
f. Set Column G to 13
5. Hide Columns M,N,O,Q,R & S
6. Wrap all double worded Text in Row 4 using Alt+Enter
a. Center all labels in Row 4 both Vertically and Horizontally
7. Center the contents of Columns E, F, H, I, & Z
8. Set the Currency Style to values in the cells in columns J and L through Y
9. Add the Total Row Option to the table and set the following Subtotals:
a. B20 = Count
b. J20 = Average
c. K20 = Average (format to 0 decimal places
d. L20 through Y20 as SUM
e. Remove total from Cell Z20
Create Sorting and Filtering worksheets
1. Copy the Worksheet 2 times and name the sheets Sort by MS and Filter for MS
2. Using a Sort by MS worksheet apply the Sort using the following 3 levels
a. Primary (first Level) Sort of Mar. Stat. in Ascending Order
b. Alternate (2nd
Level) Sort of Net Pay in Descending Order
c. Alternate (3rd
Level) Sort of Name in Ascending Order
3. Using the Filter for MS worksheet complete the following
a. Turn the Filter on
b. Filter by Mar. Stat. of M and Net Pay Greater than or equal to $225
Create a Marital Status Statistics worksheet
1. Using the Marital Stats worksheet, please complete the following actions:
2. Format the table using the following styles/formats:
a. Bold the entire worksheet
b. A1 to D1 Title Style and Merge and Center Vertically and Horizontally
c. Change the Height of Row 1 to 30
d. A3 through D5 Cell Style of Accent2
e. Row 3 Height to 32
f. All Column Widths 13
g. Wrap text and Center Horizontally and Vertically A3 through D3
h. Center Vertically and Horizontally cells A4 through D5
i. Add a thick border outline to the table and inside borders
j. Cells B4 through C5 as Currency Cell Style
3. Using the SUMIF, AVERAGIF and COUNTIF functions and the Payroll table, calculate the results of the Net Pay each of the Marital Status types from the Payroll table (Hint use the function wizard).
Create a Marital Status Statistics Chart
1. Using the Marital Status table create an Embedded Clustered Cylinder chart plotting the Marital Status, Total, and Average Data Points below the Stats by marital Status Table
a. Change the Chart so that the Marital Status is on the X-Axis
2. Create a style of your choice and layout with title that matches the Table. See completed sample below:
Create a Payroll Pivot Table & Pivot Chart
1. Using the Payroll Worksheet create a Pivot Table using the following settings:
a. Change the Worksheet Name to PivotTable
b. Report Filter as Mar. Stat.
c. Column Label as St.
d. Row Label as Name
e. Values of Net Pay
2. Set the following filer criteria
a. Mar Status of Single
3. Change the style to Pivot Style Medium 3
4. Change Cell A4 to Names
5. Change cell B3 to States
6. Change all values to the style of Currency
7. Best fit all columns
8. Use a Pivot Chart using the chart type of a 3-D Column
a. Change the style to Style 4
Download:- Data File.xlsx