Project #14970 - Spreadsheet Applications

The company from Week 1 has merged with another smaller company that operates out of Kobe, Japan. Each company has its own personnel database. In order to take advantage of Microsoft Excel’s database features, these two databases must be merged.

Select the following link for a spreadsheet to complete this assignment: Week 2 Individual Project.

Part 1: For the worksheet labeled "Seattle, Washington," perform the following steps:

  1. Save the spreadsheet using the following naming scheme: FirstInitialLastName_week2IPa (e.g., MRogers_week2IPa.xls).
  2. Add 2 columns to the next to the Full Name column. Label the first new column "First" and the second new column "Last." Using either spreadsheet functions (e.g., Left, Right, or Find) or text-to-columns, split the Full Name column into a First Name column and a Last Name column.
  3. Change the contents of the Full Name column to be in "Last Name, First Name" format.
  4. Add another column, and label it "Location." Place the worksheet name in each row of this column for each employee. Use either Copy and Paste or Fill Down operations.
  5. Change the Manager column contents to be in "Last Name, First Name" format. You may add temporary columns to do this and then delete them afterward. (If you use formulas that reference temporary columns, you cannot delete the temporary columns. Hint: Copy the calculated formula results, and paste values on top of them.)
  6. Adjust column widths for the best fit of all data.

Part 2: For the worksheet labeled "Kobe, Japan," perform the following steps:

  1. Add a column to the right of the Last column. Label it "Full Name."
  2. Using spreadsheet functions or operations, populate the Full Name column using the format "Last Name, First Name."
  3. Add another column, and label it "Location." Place the worksheet name in each row of this column for each employee. Use either Copy and Paste or Fill Down operations.
  4. Change the job titles to match Seattle job titles using the following information:

     

    Kobe Title

    Seattle Title

    Assistant

    Administrative Assistant

    Customer Service Level 1

    Customer Service Representative

    Customer Service Level 2

    Software Analyst

     

  5. Convert the Salary column contents from Japanese yen to U.S. dollars using the conversion rate 1 U.S. dollar = 84 Japanese Yen. You may add temporary columns to do this and delete them afterward.
  6. Add a column, and label it "Manager." Populate this new column. All employees report to the manager in Kobe. The manager in Kobe reports to the CEO in Seattle. Use the format "Last Name, First Name" for this column.

Part 3: Create a new worksheet, and call it "Combined."

  1. Copy the data rows from the Seattle and Kobe sheets into the new worksheet. Make sure the merged data columns align (i.e., contain the correct data).
  2. Sort the worksheet labeled "Combined" by the following levels:
    • Location Ascending
    • Manager Ascending
    • Job Description
    • Full Name
  3. Turn on Filtering. Use Filtering tools to eliminate the job titles.
    • CEO
    • Finance
    • Office Manager
    • Manager
  4. Using the Subtotal tool, at each change in job title, do the following:
    • Use the Average function.
    • Add Subtotal to Salary (and nothing else).
  5. Save your document.

Subject General
Due By (Pacific Time) 10/21/2013 12:00 am
Report DMCA
TutorRating
pallavi

Chat Now!

out of 1971 reviews
More..
amosmm

Chat Now!

out of 766 reviews
More..
PhyzKyd

Chat Now!

out of 1164 reviews
More..
rajdeep77

Chat Now!

out of 721 reviews
More..
sctys

Chat Now!

out of 1600 reviews
More..
sharadgreen

Chat Now!

out of 770 reviews
More..
topnotcher

Chat Now!

out of 766 reviews
More..
XXXIAO

Chat Now!

out of 680 reviews
More..
All Rights Reserved. Copyright by AceMyHW.com - Copyright Policy