Sample Analytical Assessment A

Background

• Purpose: Demonstrate analytical competence by turning raw data into information that aids decisions.
• Tools Provided: Excel spreadsheet & executive-level instructions
• Tools Not Provided: software, detailed instructions, helpful hints
• Estimated Time to Completion: 16 full working hours (2 working days)
• Deliverables: memo and PowerPoint presentation (or suitable alternative to PowerPoint)

Scenario

The CEO of an office supply company (StapleTown) has called a meeting of her top managers to prep for a conference call with investors. She wants to discuss the company’s performance with her management team prior to that call. The investors always have excellent questions and she needs to be at the top of her game – demonstrating a mastery of details while keeping the conversation high-level and strategic. You are Chief Analytics Officer of the company, so your job is to make sure the CEO is prepared for her call, using the latest sales data. A member of your staff sent you an extract of the company’s sales transactions since the company was started in January 2010, and you are going to spend the next two days analyzing that data to see what insights it reveals. The CEO provided you a list of questions she thinks the investors will ask, but she expects you to highlight any useful information that might be relevant.

Data

The sales data is available here. Note that it includes two tabs: one for sales and another for returns.

Questions from the CEO

• What are the factors that influence the company’s profitability?
  • Which product categories are the most profitable? Which are the least?
• What does our customer segmentation look like, and how does that relate to profitability?
  • How do purchasing behaviors change by customer segment and region?
• What are the factors that influence the company’s sales, and how do they relate to profitability?
• Where are we losing money?
• How do sales and profitability change over time?
• How is our performance getting customer orders shipped quickly?
• What do we need to understand about trends among returns?
• What geographic regions should we be targeting based on sales and profitability?

Deliverables

The CEO would like answers to all of her questions above. She typically likes brief memos and rarely has time to read long reports. In addition, many people on the management team are visual learners, so she wants you to prepare a PowerPoint presentation showing your key insights. Once you’ve finished, submit both deliverables to [email protected]. Your work will be reviewed to determine if an in-person interview is the appropriate next step. If so, you will present your findings at the beginning of the interview.