Card Description (10-15 words): Conducting quantitative analyses (usually using Excel spreadsheets) is a foundational consulting skill.
Badges: Data Analysis, Core Excel for Consultants, Data Visualizations
Prompt:
Analyzing quantitative data is one of the most important research skills that consultants use in almost every engagement. Consultants typically use Excel (or similar spreadsheet applications) to conduct the following data tasks:
Cleaning - Ensuring the data is free of entry and formatting errors
Combining - Connecting data from different sources to create more detailed and useful data sets (ex. using “V-lookups” or “index/match” functions). Note - we won’t go into this in depth in this particular challenge, but you might experience this in the future!
Analyzing - Testing your hypotheses with data in order to reach a conclusion
Visualizing - Preparing informative charts or graphics to communicate your conclusions
Your newest client is Bespoke Spokes, a regional bicycle shop that prides itself on providing best-in-class customer service to its “cycling family.” For the past couple of years, Bespoke Spokes has used a standard CRM (Customer Relationship Management) system to record customer and transaction data. Their marketing director has hired your team to help analyze that data and use it to provide a better understanding of their customers’ purchasing behaviors.
Guidelines:
While data analytics is normally a solo task, use this challenge to collaborate with and learn from your teammates (that’s OK if you are working by yourself!). 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:
A short description of recommended steps to “clean” the data
Create a summary statistics table in the transactions tab that lists the mean, median, standard deviation, max, and min for the fields “list_price” and “standard_cost”. Additionally, provide counts (or percentage of total) of each distinct value for the fields “online_order”, “order_status”, and “product_line”.
Add a column after “standard_cost” and name it “standard_profit”. Calculate the value of this field by subtracting “standard_cost” from “list_price”. Add summary statistics for this field to the table you made in Step 2.
In the transactions tab, create a pivot table that shows the mean “list_price” and “standard_profit” by “wealth_segment” (Hint - choose “wealth_segment” as the row and “list_price” and “standard_profit” as the columns).
Create 4 graphs showing the following:
The transaction count by brand (as either a bar or column chart)
A comparison of “list_price” and “standard_profit” by “wealth_segment” (Hint - use the pivot table you made in Step 4)
A histogram of customer ages
One more visualization of your choice - be creative!
Based on your visualizations, brainstorm three hypotheses for improving Bespoke Spokes’ marketing efforts with your team. Then, describe how you would test those hypotheses to confirm whether they were true.
Details:
Follow the guidelines mentioned in the prompt to create a slide deck of 3-5 slides.
Save this as a PDF and upload the file to complete your submission.
Only one person from the team needs to submit. It’ll count towards completion for the entire team.
Week 2 Prompt
