On the “Personal Information” worksheet: Concatenate the two call opportunitys into a solitary opportunity so that call is displayed as Last Name, First Name. Observation and “paste as values” and delete the primordial call opportunitys.3. On “Salary” Worksheet: Compute the possession of each employee from the Hire End opportunity.5. On “Address” Worksheet: use the “convert passage to columns” to distribute subsidence opportunity into city and specify and remove to City and Specify opportunitys in oration obstruct. Compose suppositious street oration and “drag down” to populate for all; do the corresponding for zip adjudication.7. Using “V-Lookup” duty, produce the subjoined opportunitys 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. Recall the worksheet after a while the pivot consultation “Demographics Summary.”
Salary by Job: Include employee counts and middle possession, middle compensation and middle douceur by job epithet. Recall the worksheet after a while the pivot consultation “Job Summary.”
On “Master” worksheet: prime all and compose a disconnected pivot consultation for each of the subjoined:
Create a observation of the “Personal Information” worksheet and recall it “Master.”
On “Address” Worksheet: originate an email for each employee consisting of [email protected]. Restrain simply one email opportunity.
On “Salary” Worksheet: Compute hourly objurgates for all employees listed after a while an annual compensation (hrly objurgate = compensation/2080). Replace compensation values after a while hourly (restrain simply one pay objurgate opportunity).
Deliverable: Submit your spreadsheet (a solitary yield finish) as an devotion in the assignment dropbox by the due end. In completion, you should bear 7 free worksheets (4 primordial and 3 new) in your finish. Restrain a observation of your finish for use in assignment 3.