Sales Analysis of A Cup of Joe and More

Maven Roasters is a fictional coffee shop chain in New York City that currently has three locations.

As a new franchise owner, the goal is to review the months of January-June of 2023 to better understand purchase behavior and review operations to identify some actionable recommendations to optimize operations and increase profitability.

APPROACH:

1. Data Analysis: Collected transaction data for the first six months of 2023, including date, time, quantity, location, unit price, and product information.
2. Data Processing: Calculated additional columns such as revenue per transaction, month, day of the week, and hour of the day.
3. Visualization: Created pivot tables and charts to analyze total revenue by month, total number of transactions by weekday and hour of the day, and breakdowns by product category and type.
4. Interactivity: Added a slicer to filter data by location, allowing for a detailed analysis of each store's performance.

RECOMMENDATIONS:

1. Discontinue Sugar-Free Syrup: Since sales of this item are negligible (less than 1% at each store), removing it would free up shelf space for more popular items.
2. Eliminate Branded and Packaged Chocolate Categories: These categories have low sales volume and can be easily found elsewhere. Removing them could help focus on items with higher profit margins.
3. Saturday Promotion Campaign: Implementing promotions on Saturdays could boost weekend sales and attract more customers.
4. Adjust Store Hours: Closing all stores at 8 PM can reduce expenses during low-sales periods, including utilities, staffing costs, and operational expenses.

DASHBOARD LAYOUT:

1. Header and Slicer: Allows users to select and filter data by store location.
2. Top Recommendations: Provides immediate insights for leadership to understand key actions.
3. Revenue Trendline: Displays revenue trends over the six months, highlighting overall performance.
4. Product Analysis: Shows highest and lowest volume products and categories, drawing attention to areas needing improvement.
5. Sales by Day and Hour: Examines sales patterns throughout the week and hours of the day, highlighting opportunities for optimization.

Utilizing Microsoft Excel's capability to effectively present actional insights leads to recommendations, that if implemented, could potentially enhance efficiency, attract more customers and increase profitability for Maven Roasters.