Coffee Shop Sales Analysis | Excel Dashboard

Business Problem

A coffee shop had 3,636 transaction records but lacked visibility into sales performance, customer preferences, peak business hours, and product-level revenue contributions. Decision-makers could not easily identify which products and time periods were driving business performance.

Project Goal

Analyze sales data and build an interactive Excel dashboard to answer key business questions related to revenue, customer behavior, product performance, sales trends, and payment preferences.

Analysis Performed

• Cleaned and transformed raw transaction data in Excel — standardized timestamp fields and bucketed transactions into Morning, Afternoon, Evening, and Night periods for time-based analysis
• Built Pivot Tables and Pivot Charts to analyze revenue, orders, product sales, and customer purchasing patterns
• Built an interactive dashboard using Slicers, KPI cards, and dynamic visualizations
• Compared sales performance across weekdays vs. weekends and across time-of-day periods
• Evaluated product-level revenue contribution, peak sales hours, and payment method usage

Key Insights

• Analyzed ₹11.59 Lakhs in revenue across 3,636 orders
• Latte and Americano with Milk together contributed nearly 46% of total revenue
• Product preferences shifted throughout the day, with different items leading sales in each time-of-day period
• Weekdays generated higher revenue per day than weekends, indicating stronger weekday customer activity
• 96–98% of transactions were completed through digital payments

Business Impact

• Inventory & staffing: with two products driving nearly half of revenue, prioritizing Latte and Americano with Milk stock and staffing during their peak hours reduces the risk of running short during high-demand periods
• Weekend strategy: the weekday-skewed revenue pattern points to an opportunity to test weekend-specific promotions to close the gap
• Payment infrastructure: with 96–98% of transactions digital, cash-handling processes and float management can likely be scaled down without impacting customer experience

Tools Used

Microsoft Excel — Pivot Tables, Pivot Charts, Slicers, KPI Cards

Key Takeaway

Turning 3,636 raw transaction rows into a single interactive dashboard made it possible to see, at a glance, which products, times, and days actually drive revenue — insight that raw transaction data alone couldn't surface.