Café Harmony is a new and exciting virtual café chain that specializes in offering a variety of drinks, snacks, and light meals across four major locations: Uptown, Airport, Suburbs, and Downtown. As the customer base expands and demand grows, Café Harmony requires actionable insights to improve: sales performance, customer satisfaction inventory control, employee productivity.
This project delivers a comprehensive, interactive, and data-driven Excel dashboard designed to optimize operations and improve profitability across business areas through powerful visualizations and analytics.
- Visualize monthly sales trends and performance over time, products, and locations
- Identify top and under-performing products
- Optimize inventory levels to prevent shortages and reduce waste
- Monitor customer satisfaction and employee efficiency
- Uncover insights for targeted marketing, operational improvements, customer satisfaction, and workforce efficiency
- Microsoft Excel
- Power Query (data integration, transformation, and cleaning)
- PivotTables & PivotCharts (analysis & visualization)
- Slicers (interactive filtering)
- Conditional Formatting (visual insights)
| Stage | Description |
|---|---|
| Data Collection | Imported from Excel sheets: Sales, Inventory, Customer Feedback, and Employee Records |
| Cleaning | Removed duplicates, handled nulls, corrected formatting |
| Validation | Implemented drop-downs and data validation rules |
| Structuring | Organized data into Excel Tables for dynamic reference |
| Transformation | Created helper columns (Month, Revenue, Age Group) and unpivoted datasets |
| Integration | Used Power Query to merge and normalize data |
| Feature Engineering | Calculated key metrics (e.g., Total Sales, Units Sold, Ratings) |
| Date Handling | Extracted Month and Year from transaction dates |
| Analysis | Built PivotTables for product, location, and feedback analysis |
| Visualization | Designed interactive charts with slicers and visual alerts |
| Dashboard Finalization | Assembled a clean, user-friendly layout with KPIs and locked sheets to protect formulas |
| Metric | Value |
|---|---|
| Total Sales | $12,500 |
| Total Transactions | 1,000 |
| Customers Served | 1,003 |
| Employees | 1,000 |
| Units Sold | 3,012 |
| Top Products | Muffin, Cappuccino, Iced Tea |
| Customer Demographics | Female: 54%, Male: 45% |
| Average Product Rating | 3.05 / 5 |
- Top Product: Muffins are the best-sellers across all branches
- Best Location: Downtown branch leads in revenue
- Underperformer: Tea Bags underachieved by 16% vs. targets
- Customer Ratings: Average product rating is 3.05 – indicates improvement potential
🔧 Operational Efficiency
- Perform a workforce audit to address low productivity
- Introduce automation in service and admin workflows
- Optimize staff scheduling and task allocation during off-peak hours
🛍️ Sales & Product Strategy
- Create product bundles (e.g., Muffin + Cappuccino) for promotions
- Use upselling techniques during high-traffic hours
- Integrate product data insights into personalized marketing campaigns
🎯 Customer Engagement
- Run targeted campaigns for underrepresented groups (e.g., males)
- Launch loyalty programs to retain high-value customers
- Analyze location-based behavior for regional marketing
📦 Inventory Optimization
- Implement real-time inventory tracking in Excel
- Prioritize demand-based restocking
- Avoid waste from overstock and product expiry
The dashboard enabled Café Harmony to:
- Identify operational inefficiencies and cost leakages
- Highlight product and location-level performance
- Uncover customer engagement patterns
- Track sales trends by category, month, region, product, and demographic
- Expose inventory management risks and optimize reorder levels
- Identify automation opportunities to scale efficiently
These insights supported smarter, data-backed decisions across the organization to deliver real-time business intelligence, empowering Café Harmony to enhance profitability, streamline operations, and scale efficiently.
Café Harmony is scaling rapidly, with strong sales and customer interest. By applying a structured, data-driven approach, Café Harmony can enhance profitability, optimize resources, and strengthen customer loyalty.
To support continued growth:
Focus Areas
- Streamline operations through automation and staffing efficiency
- Deepen customer understanding through surveys and behavior tracking
- Introduce smarter inventory tools to align with real-time demand
Next Actions
- Conduct operational audits
- Run customer satisfaction surveys and focus groups
- Launch targeted marketing campaigns
- Implement digital inventory systems
- Monitor KPIs and iterate on improvements
With a proactive, data-driven mindset, Café Harmony is well-positioned for sustainable growth, improved profitability, and enhanced customer experiences.
📎 Created by Uche Data Insights
💬 “Transforming data into decisions that drive business growth.”