Mentor Hub

Explore

Advanced Tech Consulting

Week 2 Prompt

⁠

TEAM CHALLENGE #1

Advanced Data Analysis

Conduct further quantitative analyses to draw insights on a European drug store chain.

Badges: Data Analysis, Core Excel for Consultants, Data Visualizations

Prompt:

You have been hired by Smöl, an established Scandinavian drug store chain. The chain operates over 3,000 drug stores in 7 European countries from its home base in Copenhagen, Denmark..

The chain has operated since the mid 1970s, but is recently experiencing increased competition from newer drug store chains. In response, the CEO is looking to modernize its data collection systems so it can better target customers. Currently, Smöl only tracks very basic information, such as total revenue and number of customers per day.

In order to get a better picture of the business, your boss has asked you to do some quantitative research on Smöl stores based on preliminary data the chain has provided. The data consists of records from 1,115 of their stores from April 2019 to July 2020. There are two sets of data—one deals with store information (Store_Info) and the other with sales data (Sales_Data). You’ll be using this data to generate insights about Smöl that you will later use to provide recommendations. Please note that these are large data sets—the sales data contains 50,000 records. Your computer may chug when processing these things, but don’t worry—it may just take a minute.

When Smöl sent over the data, they also sent over this explanation:

Most of the names for our data fields are self-explanatory. However, there are a few that may not be clear. We’ve indicated those in the table below:

Data Field

Explanation

Store

A unique ID for the 1,115 stores tracked in this data

CompetitionDistance

distance in meters to the nearest competitor store

CompetitionOpenSince[Month/Year]

gives the approximate year and month of the time the nearest competitor was opened

StoreType

we have four types of stores:

A - Our smallest (smölest) store - kiosks that are roughly 4-5 square meters, often found at train stations and other transportation hubs

B - Corner convenience stores ranging from 50-200 square meters. Similar to 7-11 and found most commonly in city centers.

C - Stand alone stores, approximately 500 - 1,000 square meters. Typically in city outskirts, smaller towns and suburbs.

D - Stores in malls, with sizes ranging from 200-1,000 square meters

Promo

This data deals with promotions the store was running. We will not be using it for this exercise.

Sales

The total revenue generated by that store per day. For the purposes of this assignment, assume all has been converted to USD ($)

Customers

The total number of customers who purchased something at the store that day.

Open

an indicator for whether the store was open: 0 = closed, 1 = open

Promo

indicates whether a store is running a promo on that day

StateHoliday

indicates a state holiday. Normally all stores, with few exceptions, are closed on state holidays. Note that all schools are closed on public holidays and weekends.

a = public holiday, b = Easter holiday, c = Christmas, 0 = no holiday

SchoolHoliday

indicates if the store was affected by the closure of public schools on a specific date (1 = affected, 0 = not affected)

There are no rows in this table

⁠

You may remember data skills if you completed the initial management consulting skill ladder. As a reminder, here are some of the common data tasks that consultants may do:

Cleaning - Ensuring the data is free of entry and formatting errors (NOTE - this data has been pre-cleaned; we will not be performing this step)

Combining - Connecting data from different sources to create more detailed and useful data sets (ex. using “V-lookups” or “index/match” functions)

Analyzing - Testing your hypotheses with data in order to reach a conclusion

Visualizing - Preparing informative charts or graphics to communicate your conclusions

Optional Teaming: While data analytics is normally a solo task, you can use this challenge to collaborate with and learn from your teammates (that’s OK if you are working by yourself!) to get a teamwork badge! We recommend collaborating via Google Sheets if you are working as a team and work together on the graphs or to brainstorm ways to improve Smöl’s profitability in the market and get a competitive advantage.

Guide:

Note - though many of the tasks in this challenge are typically done individually, if you choose to work in a team, we recommend not splitting up the work. Perhaps try out tasks individually, then come together as a team to discuss strategies and results.

Using the provided transaction and customer tables, perform the following analyses in Excel, Google Sheets, or a similar program and make 3-5 slides showing outputs from the following tasks:

Using a Pivot Table to summarize Stores by Sales, find the top 10 highest-grossing stores (ie the greatest amount of revenue). List the Store ID in your slides.

Next, create a summary statistics table in the Store_Info tab that lists the mean, median, standard deviation, max, and min for the fields “sales” and “customers” by store.

In the Store_Info tab, create a new field called ‘CompetitionMonthsOpen’. This field should contain the number of months the competition has been open up to the start of this data (April 2019). For instance, if a store opened up in June 2018, the CompetitionMonthsOpen will be 13 (1 year * 12 months + 1 month). If a store opened up more recently than April 2019, simply enter 0. (Hint - Find a way to automate this, rather than doing each one manually!)

Use the ‘V-Lookup’ function to pull the fields ‘CompetitionDistance’ and ‘CompetitionMonthsOpen’ from the Store_Info to the ‘Sales_Data’ table. (Hint - you can do this by matching ‘Store’).

After finishing the lookups, figure out what percentage of stores have a competitor within a kilometer (1000 meters) as well as what percentage of stores have a competitor that opened up since this data collection started (April 2019).

In the transactions tab, create a pivot table that shows the mean “customers” by “StateHoliday” and “SchoolHoliday” (Hint - choose “customers” as the row and “StateHoliday” and “SchoolHoliday” as the columns).

Details

Follow the steps provided in the Guide.|

You are curious about the effect of having close competitors on revenue and number of customers. Create a visualization that helps illustrate this—note, this is an open-ended assignment, so be creative!|

Finally, based on your visualizations above, summarize your insights about the effects of close competitors on revenue and number of customers.|

Note: If you are working together as a team, ALL members still need to upload the submission individually.

Resources:

⁠

Skillfully - Smöl Data⁠

⁠

Tableau - What is Data Cleaning?⁠

⁠

Investopedia - Descriptive Statistics⁠

⁠

Ablebits - VLOOKUP in Google Sheets⁠

⁠

Microsoft - VLOOKUP in Excel⁠

⁠

Business Insider - Pivot Tables in Google Sheets⁠

⁠

Microsoft - Pivot Tables in Excel⁠

⁠

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.