A comprehensive analysis of sales data for Maven Roasters, a fictional coffee shop chain operating across three NYC locations. The goal is to uncover actionable insights to enhance business performance by examining trends in customer behavior, product preferences, and store traffic patterns.
To analyze sales data and create an interactive Excel dashboard that reveals trends, patterns, and key insights. The findings aim to inform strategic decisions and boost sales performance.
Using Excel, analyze transaction records to:
- Identify trends in sales by day and hour.
- Discover peak sales times and best-selling products.
- Compare revenue across store locations.
- Explore variations in product categories, sizes, and pricing.
Source: Maven Analytics
- Records: 149,116
- Duration: January 2023 – June 2023
- Locations: 3 stores in NYC
- Fields: Transaction details, product specifics, timestamps, and more
Data cleaning and transformation were performed in Power Query to ensure accuracy and usability. Key steps included:
- Standardizing Formats: Removed inconsistencies, leading/trailing spaces, and abbreviations (e.g., "Sm" → "Small").
- Column Enhancements:
- Extracted
Day of Week,Month, andHourfor time-based analysis. - Added a
Total Billcolumn (Unit Price × Quantity).
- Extracted
- Time Refinement: Isolated transaction times (HH:MM:SS format).
An interactive Excel dashboard visualizing key metrics, trends, and patterns:
- Line Charts: Highlight sales trends over hours of the day, showing peak times.
- Bar Charts: Compare daily sales across the week.
- Pie/Donut Charts: Show the percentage distribution of product sizes and categories.
- Column Charts: Display foot traffic and revenue per store.
- Busiest hours: 8:00 AM – 10:00 AM, driven by the morning rush.
- Top days: Monday and Friday, indicating higher demand at the week’s start and end.
- Large-sized drinks dominate sales; small-sized drinks are least popular.
- Coffee and Tea account for 67% of total sales, with Coffee being the highest contributor (39%).
- Barista Espresso is the best-selling item, reflecting high customer demand for coffee.
- Brewed Chai Tea ranks second in sales.
- Customer traffic is evenly distributed across all store locations, offering equal opportunities for revenue generation.
The Coffee Sales Analysis provided valuable insights into customer behavior, product preferences, and store performance. The key findings, such as peak sales hours, top-performing products, and balanced store traffic, equip Maven Roasters with actionable data to make informed strategic decisions.
-
Optimize Staffing and Inventory:
- Increase staffing and inventory during peak hours (8:00 AM – 10:00 AM) to handle high demand efficiently.
- Stock more large-sized drinks and popular products like Barista Espresso and Brewed Chai Tea.
-
Enhance Promotions:
- Offer targeted promotions on slower days (e.g., Tuesdays and Wednesdays) to drive footfall and sales.
- Introduce loyalty programs for frequent purchases of top-selling items.
-
Expand Product Strategy:
- Focus on the Coffee and Tea categories to maintain dominance.
- Experiment with new product offerings in the underperforming categories to increase diversity.
-
Leverage Consistent Footfall:
- Develop location-specific campaigns to take advantage of balanced customer distribution across stores.
By leveraging the insights from this analysis, Maven Roasters can boost operational efficiency, improve customer satisfaction, and drive overall revenue growth.
For any questions or feedback, please contact me at meabhaykr@gmail.com.