Objective: After the completion of this assignment, you will be able to:

•  Employ the v-lookup function to merge data from multiple spreadsheets into a single, useable worksheet·         Calculate various metrics using formulas·         Convert text in a single cell into multiple cells
• Use the concatenate function to merge data from multiple cells into a single cell
• Merge and Unmerge cells
• Utilize pivot tables to synthesize data from a spreadsheet

Background: Technology is key to data management and reporting, and Excel is an indispensible tool which allows for the manipulation of data and complex data analysis. This assignment assumes you have a basic working knowledge of Excel and will explore more advanced features that will greatly expand your ability to utilize spreadsheets in the context of HR administration.

Instructions: View the tutorial labeled “Advanced Excel” (linked here (Links to an external site.)Links to an external site. and housed in the “Tutorials Folder” on the course content page).  Then complete the following tasks within the spreadsheet.

1. On the “Personal Information” worksheet: Concatenate the two name fields into a single field so that name is displayed as Last Name, First Name. Copy and “paste as values” and delete the original name fields.3.  On “Salary” Worksheet: Compute the tenure of each employee from the Hire Date field.5.  On “Address” Worksheet: use the “convert text to columns” to divide location field into city and state and transfer to City and State fields in address block. Create fictitious street address and “drag down” to populate for all; do the same for zip code.7.  Using “V-Lookup” function, bring the following fields from the other worksheets into the “Master” worksheet: Pay Rate; Tenure; 2013 Bonus; Job Name; Email; Street Address; City; State; and Zip.a.  Demographics: Include employee counts by gender and ethnicity. Rename the worksheet with the pivot table “Demographics Summary.”
2. Salary by Job: Include employee counts and average tenure, average salary and average bonus by job title. Rename the worksheet with the pivot table “Job Summary.”
3. On “Master” worksheet: select all and create a separate pivot table for each of the following:
4. Create a copy of the “Personal Information” worksheet and rename it “Master.”
5. On “Address” Worksheet: generate an email for each employee consisting of [email protected]. Retain only one email field.
6. On “Salary” Worksheet: Compute hourly rates for all employees listed with an annual salary (hrly rate = salary/2080). Replace salary values with hourly (retain only one pay rate field).
7. Deliverable: Submit your spreadsheet (a single excel file) as an attachment in the assignment dropbox by the due date.  In total, you should have 7 active worksheets (4 original and 3 new) in your file. Retain a copy of your file for use in assignment 3.

## Business Finance : Walden week 4, MMHA-6050, Cont...

To develop the content for your presentation, record detailed notes as you complete the following steps.Review the information...

## Accounting : 20200615155056tax 2020 1.edited 2...

Running head: TAX MEMO1NAME:INSTITUTION:COURSE:DATE:TAX MEMO2Current dateTo:Client fileFacts1. The name of the company is Rico Corporation.2. The com...

## geography discussion for phyllis young...

(Pick 1 of the 3 Questions to answer)Question 1: Compare and contrast S/SE Asia's ...

...

## MGT656-1703D-01...

The Discussion Board (DB) is part of the core of online learning. Classroom discussion in an online environment requires the active participation o...

## Paper...

Write a 3-4 paragraph essay (of at least 250-500 words) which adequately address the topic and requirements stated below. ...

## Plague Has Been Around After Roman Time Essay...

Scientist at cdc believe plague has been around since roman timeswhich means it cant be trac...

## None...

Where we stand today when it comes to the discipline of psychology, in which area can psychologists improve on? What can they do to better the...

## Read the assigned research study abstract and subm...

Read the assigned research study abstract and submit a written summary that includes the following – purpose of the study, type of quantitative s...

...

## question to answer, first one to get it wins 1 m...

x^4 +4x^2y^3+y^2=2y...

### Other samples, services and questions:

Calculate Price

When you use PaperHelp, you save one valuable — TIME

You can spend it for more important things than paper writing.

Approx. price
\$65
Order a paper. Study better. Sleep tight. Calculate Price!
Calculate Price
Approx. price
\$65