RELIABLE PAPERS ONLY PLEASE

Unit 9    [IT153: Spreadsheet Applications] 

 

Final Assignment Details and Rubric

Outcomes addressed in this activity:

 Use Excel to design multiple worksheets

 Create multiple charts

 Format and produce a professional Excel workbook

Course outcome: IT153-1: Create spreadsheets to solve business problems.

Instructions

This assignment will use all of the concepts from our class. You will be designing several worksheets, charts, calculations and formats to produce a Professional and Accurate Payroll for the Hackers Corporation.

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

Unit 9    [IT153: Spreadsheet Applications] 

 

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

Unit 9    [IT153: Spreadsheet Applications] 

 

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

Finished Payroll Worksheet should resemble the following:

Unit 9    [IT153: Spreadsheet Applications] 

 

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

Unit 9    [IT153: Spreadsheet Applications] 

 

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

Unit 9    [IT153: Spreadsheet Applications] 

 

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

The finished worksheet should look as follows:

Reorder all worksheets in the following order from Left to Right: Payroll, Lookup Tables, Sort by MS, Filter for MS, Marital Stats and Pivot Table. Save and Submit the completed file to the Unit 9 Final Assignment Dropbox prior to 11:59 PM Eastern Time on Tuesday.

Unit 9    [IT153: Spreadsheet Applications] 

 

Unit 9 Final Assignment grading rubric = 100 points

Task to be completed: Points

Possible

Executive Theme set and entire worksheet bolded. 0 - 1

Lookup Tables Lists named. 0 - 3

Gross Pay is calculated using the IF Function. 0 - 5

Federal and State Taxes are calculated using the VLOOKUP Function. 0 - 10

FICA, Total Deductions and Net Pay are calculated using the specified formulas. 0 - 3

Payroll Worksheet is Formatted using the specified styles and format below:

Merge and center cell A1 through Z1 and set the Title Style Row height set and Centered Vertically and Horizontally.

0 - 1

Draw a thick border around the range C2:D2. 0 - 1

Format as a table the range A4:Z19 using the Table Style Medium 10 Table is Named and Remove the filter from the Table.

0 - 2

Set the following Column widths and Row heights: Set Rows 1 and 4 to 30. Set Column A & Z to 8. Set Columns B & D and H through Y to 11. Set Column C to 16. Set Columns E & F to 6. Set Column G to 13.

0 - 2

Hide Columns M,N,O,Q,R & S. 0 - 1

Wrap the Text in Row 4 using Alt+Enter Center all labels in Row 4 both Vertically and Horizontally.

0 - 1

Center the contents of Columns E, F, H, I, & Z. 0 - 1

Set the Currency Style to values in the cells in columns J and L through Y. 0 - 1

Unit 9    [IT153: Spreadsheet Applications] 

 

Add the Total Row Option to the table and set the following Subtotals: B20 = Count. J20 = Average. K20 = Average (format to 0 decimal places. L20 through Y20 as SUM. Remove total from Z20.

0 - 4

2 copies of the completed worksheet are made and named as instructed. 0 - 2

The Sorted Worksheet is sorted using the 3 levels as specified. 0 - 6

The Filtered Worksheet has the filter for the Marital Status and net pay criteria. 0 - 5

Create the following worksheet as shown below named Marital Stats: Format the table using the following styles/formats:

Bold the entire worksheet. 0 - 1

A1 to D1 Title Style and Merge and Center and Centered Vertically and Horizontally. 0 - 1

A3 through D5 Cell Style of Accent2. 0 - 1

Row 3 Height to 32. All Column Widths to A width to 13.

0 - 1

Wrap text and Center Horizontally and Vertically A3 through D3. Center Vertically and Horizontally cells A4 through D5.

0 - 1

Add a thick border outline to the table and inside borders. 0 - 1

Cells B4 through C5 as Currency Cell Style. 0 - 1

Using the SUMIF, AVERAGIF and COUNTIF functions and the Payroll table, calculate the results of the Net Pay each of the Marital Status types.

0 - 6

Create a Marital Status Statistics Chart

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.

0 - 4

Marital Status is set to the X-Axis. 0 - 2

Use a style of your choice and layout with title that matches the Table. 0 - 1

Create a Payroll Pivot Table & Pivot Chart

Using the Payroll Worksheet create a Pivot Table using the following settings: 0 - 2

Unit 9    [IT153: Spreadsheet Applications] 

 

Change the Worksheet Name to PivotTable.

Report Filter as Mar. Stat. Column Label as St. Row Label as Name. Values of Net Pay.

0 - 8

Set the following filter criteria Mar Status of Single.

0 - 2

Change the style to Pivot Style Medium 3. 0 - 1

Change Cell A4 to Names. Change cell B3 to States.

0 - 1

Change all values to the style of Currency. 0 - 1

Best fit all columns. 0 - 1

Use a Pivot Chart using the chart type of a Clustered Column Change the style to Style 4.

0 - 5

Assignment demonstrates knowledge of the concepts of the class. 0 - 10

Total Points 0 - 100

Points deducted for spelling or formatting

Adjusted total points