Reference no: EM133812859

Assignment: Transforming Data & Identifying Trends

Scenario

You are working as a business analyst for XYZ, a regional restaurant chain. Recently, your organization was acquired by ABC, another restaurant chain. The joint leadership team of the newly formed business needs to create a profitable three-year plan. They will look to your analysis to inform this plan, given that neither company has any analysis of its own. You will be the first analyst to look at the data of the newly formed business.

Both restaurant chains operate out of multiple locations, sell direct to consumers, and are fast-casual restaurants. However, they offer distinct products that cater to different customer bases. You need to provide a descriptive analysis of what the new joint customer base looks like and how that compares in terms of the demographics of the geographic regions they are now selling in, now that they are a single entity with locations in different geographical regions than their home base.

Your final product will be a suite of visualizations that explain to the joint leadership team the current state of this newly formed business, the customer profiles of the new organization, and the products that have historically sold best and worst and are the most and least profitable. Finally, you have been asked to compare the customer profiles to the regional demographics where the newly formed restaurant chain has locations. Using your analysis, the joint leadership team will craft a three-year plan to make the company profitable within the fast-casual industry segment, which is notorious for being competitive.

You have been given the following information about the organization:

1. ABC-XYZ Data Sets: There are two data sets in this workbook. One sheet contains customer and sales/product data from the ABC restaurant chain prior to the merger. The second sheet contains customer and sales/product data from the XYZ restaurant chain prior to the merger.

2. Stakeholder Requirements: This is a Word document with requirements from various stakeholders.

As the first step toward completing your analysis, you will clean and transform the two data sets and present your initial finding on the trends and patterns in the data sets. First, you must ensure your data is properly prepared by identifying gaps and errors in the data, such as blank cells, duplicate values, mismatched formats, etc. You will then analyze trends using the different variables in the data given to you. You will finally create visualizations, produce summary statistics and present them clearly in a report, with relevant screenshots.

Prompt

Using Power BI, clean and transform the data sets from XYZ and ABC to create a single data set that includes the aggregated data from both organizations to prepare it for analysis. You will then analyze the data to identify and explain patterns and trends and any additional findings.

Specifically, you must address the following criteria:

I. Using the XYZ and ABC customer data sets, identify errors and gaps in the data.

A. Which attributes do the data sets share?

B. Identify whether or not data is either missing or incomplete. Explain how this impacts the integrity of the data.

C. Which strategies did you employ to identify the errors and gaps in the data? Explain your reasoning.

II. Clean and transform the data.

A. Create new variables out of existing ones as needed so that you can address the joint leadership team's questions and concerns. Explain any relationships or trends identified through your analysis.

a. Analyze the spending trends

i. By month and season
ii. By gender
iii. By region
iv. By customers with and without children

b. Analyze the best and worst selling products

i. By region
ii. By month and season
iii. By gender
iv. By customers with and without children

c. Analyze the profitability of products

i. By region
ii. By month and season
iii. By gender
iv. By customers with and without children

III. Create various visualizations for each variable.

A. Identify each variable in the data.

B. For each variable, create appropriate visualizations and explain how they support the narrative.

a. Analyze the shape of the visualizations.

b. Is the data grouped in any particular way, or is it randomly scattered? Explain.

c. Are there any outliers? If so, what might they indicate?

C. Identify and explain the patterns and trends in the customer base.

a. What states have the highest and lowest profit margins?

b. Which restaurant category has the highest and the lowest average check?

c. Is there any pattern in average checks by time of day (AM or PM)?

i. What about by month of the year?

D. Produce summary statistics for each variable.

a. Include the following:

i. Central tendency
ii. Measures of dispersion
iii. Shape of the data's distribution

iv. Is there a large amount of missing data? If so, how does this impact your analysis? Explain.