Assignment

This spreadsheet has data on a few hundred companies and projects that we work with.

The first tab (Account Data) has data on all companies that the business works with that is, companies that have active or inactive projects. Column A contains the Name and location of companies, Column B notes the Industry sector of the company, Column C notes if the project is active or not. As you can see, some of the Accounts are duplicated due to poor data quality and missing duplicate checks.

The second tab (Project & Task Data) has data on active Projects and related Tasks. Column A notes the unique Account names, Column B if the Account has an active project or not, Column C is the start year of the project, Column D is the number of Tasks for that Account/Project, and Column E notes the overall Task status.

The third tab (Project Manager Data) should have data on the active Projects and which Project Manager should be assigned to that Project based on the Industry of the Account - see the assignment table.

Using this data, please answer the following questions. Please note that well be assessing both whether you provide the correct or best answer as well as whether your data-analysis methods are relatively efficient.

Be sure to submit your answers in 1 file in PDF, PPT, or Word format only, and to demonstrate how you arrived at these answers.

Analysis

How many unique Accounts do we have per Industry Sector?

Active projects are defined as Yes or Pending. How many Accounts and Projects are considered Active?

How many are considered inactive? (Marked No or Canceled)

How many Active projects do we have per Year? How many Tasks do we have per Project?

How many projects are assigned to each Project Manager?

Rationale

What assumptions have you made in your analysis?

Is there data missing that you think should be included in the spreadsheet?

What recommendations do you have for the business to ensure data quality and integrity when moving to a new platform like Salesforce?