Excel Document for Customer Data Analysis - 23/08/2024 15:22 EDT
Budget: ₹750 – ₹1,250 INR
DIRECTIONS: This document is VIEW ONLY. You will need to copy/past the data into a new document, ensure it is sharable, and paste the link to your work in the answer box for Question #7 on the Skills Assessment. Do not request Editor access, as you will be declined.
Exercise 1 - Merging Data from Separate Worksheets (Task)
Worksheet 'Data 1' contains data that from a systems report. In this case, this report is a list of third parties that were involved with screening services.
Worksheet 'Data 2' contains data that was received from a vendor.
1.a. Merge the data from Worksheet 'Data 1' into Worksheet'Data 2' such that all unique columns are kept. For this exercise, please use VLOOKUP.
1.b. List below all cases you were unable to match. Provide the identifier(s) that you believe is best for a non-data colleague to review and follow-up with for investigation.
1.b. Answer:
Exercise 2 - Pivot Table Outputs (Task)
You receive a request to provide metrics and periodic financial snapshots of a company's business functions. These requests are often grouped at the country or division level.
Below are two common tasks that can be resolved by using pivot table outputs.
2.a. Create a Pivot Table in a new Worksheet by using the fully merged Worksheet 'Data 2' from Exercise 1. Rename the new Worksheet "Exercise 2 Pivot."
2.b. Using the Pivot Table, provide the number of reports each country ordered in 2018, and how much money was spent by each country. Order descending by number of reports.
Copy this table output and paste the results into a new Worksheet. Rename the Worksheet "Exercise 2 Answer." Place the name "Answer 2b" above the table.
2.c. Using the Pivot Table, provide a numerical breakdown of report colors by division. Order alphabetically by division.
Copy this table output and paste the results into Worksheet "Exercise 2 Answer" as a new tab. Place the name "Answer 2c" above the table.
Exercise 1 - Merging Data from Separate Worksheets (Task)
Worksheet 'Data 1' contains data that from a systems report. In this case, this report is a list of third parties that were involved with screening services.
Worksheet 'Data 2' contains data that was received from a vendor.
1.a. Merge the data from Worksheet 'Data 1' into Worksheet'Data 2' such that all unique columns are kept. For this exercise, please use VLOOKUP.
1.b. List below all cases you were unable to match. Provide the identifier(s) that you believe is best for a non-data colleague to review and follow-up with for investigation.
1.b. Answer:
Exercise 2 - Pivot Table Outputs (Task)
You receive a request to provide metrics and periodic financial snapshots of a company's business functions. These requests are often grouped at the country or division level.
Below are two common tasks that can be resolved by using pivot table outputs.
2.a. Create a Pivot Table in a new Worksheet by using the fully merged Worksheet 'Data 2' from Exercise 1. Rename the new Worksheet "Exercise 2 Pivot."
2.b. Using the Pivot Table, provide the number of reports each country ordered in 2018, and how much money was spent by each country. Order descending by number of reports.
Copy this table output and paste the results into a new Worksheet. Rename the Worksheet "Exercise 2 Answer." Place the name "Answer 2b" above the table.
2.c. Using the Pivot Table, provide a numerical breakdown of report colors by division. Order alphabetically by division.
Copy this table output and paste the results into Worksheet "Exercise 2 Answer" as a new tab. Place the name "Answer 2c" above the table.