Even complicated and confusing topics will be easily developed and covered if you request our help writing an essay. Place an order today!

 

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. 


     

testimonials icon
Order Grade A+ Academic Papers Instantly!...
testimonials icon
To develop the content for your presentation, record detailed notes as you complete the following steps.Review the information...
testimonials icon
Running head: TAX MEMO1NAME:INSTITUTION:COURSE:DATE:TAX MEMO2Current dateTo:Client fileFacts1. The name of the company is Rico Corporation.2. The com...
testimonials icon
(Pick 1 of the 3 Questions to answer)Question 1: Compare and contrast S/SE Asia's ...
testimonials icon
The Discussion Board (DB) is part of the core of online learning. Classroom discussion in an online environment requires the active participation o...
testimonials icon
Write a 3-4 paragraph essay (of at least 250-500 words) which adequately address the topic and requirements stated below. ...
testimonials icon
Scientist at cdc believe “plague has been around since roman times”which means it can’t be trac...
testimonials icon
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...
testimonials icon
Read the assigned research study abstract and submit a written summary that includes the following – purpose of the study, type of quantitative s...

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!
Created with Sketch.
Calculate Price
Approx. price
$65