STORE TYPE OFFER DISTRIBUTION

Problem Statement:

The company currently has stores in 6 states. We are looking to expand stores into additional states. We have determined the location and store type of the stores, but we have not determined what food/beverage offers should be at each location. Senior Leadership recently asked our team to complete a presentation giving recommendations on what offers should be in each store. The offers we are considering at these stores are Chicken, Adv GnG,(Pizza), Bean to Cup (Coffee), Swirl World (Frozen Yogurt), and DoorDash. They want the following to be considered when completing the analysis: Inside Sales, Chicken Sales, Adv GnG (Pizza) Sales, Bean to Cup Sales, Swirl World Sales, DoorDash Orders, Inside Guest Count, and Store Type. The locations are listed below, along with the Store Type:

• Travel Center – St George, SC

• 5.5k – Indianapolis, IN

• EDO – Moss Point, MS

• Travel Center – Cleveland, OH

• 5.5k – Murphy, NC

On the “Performance” tab you have all of the information needed to create an analysis on how stores in other states/locations have performed in the last year per offer based on their store type. All figures are currently Totals. With the current figures being Totals, you may need to create metrics to find averages that show how well as store is doing since they’ve been opened.

We’d like you to create an Excel document (please show all of your work) as well as a PowerPoint or PowerBI Dashboard to present to Senior Leadership with the recommendations on what offers each new location should have. The PowerPoint/dashboard should include a summary page of your findings and then 2-3 slides of additional information.

Overview:

• This project explores the strategic offer distribution for a company's retail expansion, focusing on specific food and beverage options like Chicken, Pizza (Adv GnG), Coffee (Bean to Cup), Frozen Yogurt (Swirl World), and DoorDash services.
• Leveraging data on sales performance, guest counts, and store types from existing locations, the aim is to recommend tailored offers for new stores across various states.
• Analytical insights, drawn from averages and anomalies in the data, will guide the offer selection for three store types: Travel Centers, EDOs, and 5.5k stores, each with unique traffic patterns and customer bases.
• The deliverables include a detailed Excel analysis and a compelling dashboard presentation for senior leadership, emphasizing data-driven storytelling to support strategic decisions.

Conclusion:

• The store type offer distribution strategy is informed by key metrics: EDOs average 362.35K in inside count with high Chicken sales at 160.18K, 5.5K stores excel with 316.10K in Pizza sales, while Travel Centers show strong Bean Coffee sales per day.
• The data indicates tailored offerings can boost performance—DoorDash partnerships may elevate the 15.96K sales in 5.5K stores.
• Emphasizing Bean Coffee and Chicken across all store types aligns with customer patterns, promising an uptick in sales and customer satisfaction, with the potential for a 28% increase in Bean Coffee sales observed in 5.5K stores.

Findings from the Dashboard:

• The findings from the Store Type Offer Distribution Dashboard, aligned with the problem statement of optimizing product offers, indicate a strong correlation between store type and sales performance.
• Travel Centers should focus on Bean Coffee, leveraging their 40.88% contribution to total sales. EDOs, with their diverse traffic, can increase Chicken sales, currently at 160.18K, by enhancing meal variety.
• The 5.5K stores, situated in urban centers, show untapped potential in DoorDash collaborations, aiming to surpass the current 15.96K sales.
• Data underscores the need for targeted offerings, with the inside count and days open serving as pivotal metrics for determining store performance and strategic offer placement.

Suggestion:

Recommended Analysis Questions:

1. How do sales of Bean Coffee correlate with the store type and what specific factors contribute to the 28% sales performance in 5.5K stores?

Hypothesis for Analysis: The convenience and traffic patterns in urban 5.5K stores may contribute to higher Bean Coffee sales. Investigating the time of day, promotional activities, and customer demographics could provide deeper insights into the sales correlation.

2. What impact does DoorDash partnership have on sales across different store types and could expanding this service further enhance sales metrics?

Hypothesis for Analysis: Given the current sales figure of 15.96K in 5.5K stores, examining DoorDash order volumes during peak hours and customer preference trends may reveal the potential for sales uplift across other store types.

3. With EDO stores averaging 160.18K in Chicken sales, how can we replicate this success in other store types, and what offers or promotions might contribute to similar performance?

Hypothesis for Analysis: EDO stores may benefit from specific local demographics or travel patterns that could be emulated in other stores. Analyzing sales data against local events or seasonal trends might uncover effective strategies.

4. Considering the variance in Pizza sales, particularly the 316.10K in 5.5K stores, what drives these differences, and how can we leverage this to optimize offers in other store types?

Hypothesis for Analysis: The sales differences could be influenced by the product mix, bundle offers, and location-based preferences. A comparative analysis of menu offerings and pricing strategies could help in understanding and capitalizing on these variances.

Skills:

Excel, Power BI , Power Query, DAX, Report Building

Dataset:

DATASET

Link To Power BI:

https://app.powerbi.com/view?r=eyJrIjoiOThhZDUxMzEtNDVkOC00ODUyLTg3YmQtZWIxMzUxZDZkNGJiIiwidCI6IjdiMzE2NWMzLTc0NzUtNDliNy1iMWRjLTY4MTdjNjJjZDljYSJ9&pageName=ReportSection

Youtube:

https://youtu.be/7ZulbQRohls?si=u-Wctek0SDNSxy6w