Café Sales Data Analysis
Café Sales Data Analysis
Embedded Files
Project Overview
Project Overview
An end-to-end Excel data analysis project based on 10,000 café transactions. I cleaned and transformed the raw dataset using Power Query, identifying and resolving missing and unknown values by cross-referencing relevant fields. I then prepared the data for analysis, created PivotTables and calculated metrics, and developed an interactive dashboard to uncover sales, product, payment, location, and monthly transaction patterns.
An end-to-end Excel data analysis project based on 10,000 café transactions. I cleaned and transformed the raw dataset using Power Query, identifying and resolving missing and unknown values by cross-referencing relevant fields. I then prepared the data for analysis, created PivotTables and calculated metrics, and developed an interactive dashboard to uncover sales, product, payment, location, and monthly transaction patterns.
Data Cleaning & Preparation
Raw Data — Initial Dataset
Power Query — Data Cleaning & Transformation
Cleaned & Prepared Data
Data Cleaning & Preparation
Data Cleaning & Preparation
I began with the raw café transaction dataset and used Power Query to inspect, clean, and transform the data. I identified and resolved missing, unknown, and inconsistent values by cross-referencing relevant fields and applying appropriate transformations. I then prepared the cleaned dataset and calculated the required fields for further analysis.
I began with the raw café transaction dataset and used Power Query to inspect, clean, and transform the data. I identified and resolved missing, unknown, and inconsistent values by cross-referencing relevant fields and applying appropriate transformations. I then prepared the cleaned dataset and calculated the required fields for further analysis.
Data Analysis & PivotTables
Sales Analysis by Item
Monthly Quantity Analysis
Transactions by Payment Method
Sales Analysis by Location
Data Analysis & PivotTables
Data Analysis & PivotTables
Using PivotTables, I analyzed the cleaned dataset across products, time periods, payment methods, and locations. This analysis helped identify product performance, monthly quantity patterns, customer payment preferences, and differences in sales between in-store and takeaway transactions. These findings provided the foundation for the interactive dashboard.
Using PivotTables, I analyzed the cleaned dataset across products, time periods, payment methods, and locations. This analysis helped identify product performance, monthly quantity patterns, customer payment preferences, and differences in sales between in-store and takeaway transactions. These findings provided the foundation for the interactive dashboard.
Interactive Sales Dashboard
I developed an interactive Excel dashboard to present key sales and transaction metrics in a clear, visual format. The dashboard combines KPI cards, charts, and interactive filters to analyze product performance, monthly quantity trends, payment methods, and sales by location.
Key Insights
Key Insights
1. Overall Sales Performance
1. Overall Sales Performance
The café generated $89,272.00 in total sales from 10,000 transactions, with 30,369 items sold and an average transaction value of $8.93. This provides an overall view of the café's sales activity and transaction volume.
2. Product Performance
2. Product Performance
Salad was the top-performing product, generating $17,344.00 in sales, followed by Coffee at $15,641.50. These results highlight the products contributing most significantly to overall revenue.
3. Monthly Sales Volume
3. Monthly Sales Volume
September recorded the highest monthly quantity sold at 3,806 items, indicating the strongest sales volume during the year. Monthly analysis helps identify periods of higher customer demand and potential seasonal patterns.
4. Location Performance
4. Location Performance
In-store sales generated $62,680.50, representing the majority of sales, while Takeaway generated $26,591.50. This indicates that the café's in-store channel contributed substantially more to overall revenue than takeaway transactions.
5. Payment Method Preferences
5. Payment Method Preferences
Cash was the most frequently used payment method, accounting for 5,436 transactions (54.36%). Digital Wallet accounted for 2,291 transactions (22.91%), while Credit Card accounted for 2,273 transactions (22.73%). This shows a clear preference for cash while also demonstrating significant use of digital payment methods.
6. Overall Business Findings
6. Overall Business Findings
The analysis reveals several important patterns across sales, products, monthly demand, payment methods, and sales channels. The combination of PivotTable analysis and the interactive dashboard provides a consolidated view of the café's performance and makes these patterns easier to identify and communicate.
Tools & Skills Used
Tools & Skills Used
Tools Feature/Purpose
Microsoft Excel Data analysis, calculations, and dashboard development
Power Query Data cleaning and transformation
PivotTables Data aggregation and analysis
PivotCharts Interactive data visualization
Slicers Interactive filtering by key categories
Timeline Filtering and exploring data by date
Calculated Fields Deriving required metrics from existing data
KPI Cards Displaying key performance metrics
Conditional Formatting Highlighting important values and patterns
Dashboard Design Presenting findings in an interactive, user-friendly format
Page updated
Google Sites
Report abuse