On the “Personal Information” worksheet: Concatenate the two designate provinces into a uncombined province so that designate is displayed as Last Name, First Name. Vision and “paste as values” and delete the primordial designate provinces.3. On “Salary” Worksheet: Compute the encroachment of each employee from the Hire Epoch province.5. On “Address” Worksheet: use the “convert passage to columns” to deal-out location province into city and aver and sell to City and Aver provinces in harangue stop. Fashion suppositious street harangue and “drag down” to populate for all; do the corresponding for zip order.7. Using “V-Lookup” capacity, carry the subjoined provinces 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. Redesignate the worksheet after a while the pivot consideration “Demographics Summary.”
Salary by Job: Include employee counts and mean encroachment, mean allowance and mean benefit by job distinction. Redesignate the worksheet after a while the pivot consideration “Job Summary.”
On “Master” worksheet: fine all and fashion a disunited pivot consideration for each of the subjoined:
Create a vision of the “Personal Information” worksheet and redesignate it “Master.”
On “Address” Worksheet: engender an email for each employee consisting of [email protected]. Keep solely one email province.
On “Salary” Worksheet: Compute hourly trounces for all employees listed after a while an annual allowance (hrly trounce = allowance/2080). Replace allowance values after a while hourly (keep solely one pay trounce province).
Deliverable: Submit your spreadsheet (a uncombined achieve finish) as an attachment in the assignment dropbox by the due epoch. In aggregate, you should feel 7 erratic worksheets (4 primordial and 3 new) in your finish. Keep a vision of your finish for use in assignment 3.