Advanced excel functionality | Human Resource Management homework help

 

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).

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. 

 







Calculate Your Essay Price
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Order your essay today and save 10% with the coupon code: best10