This project analyzes customer behavior, product performance, stock management, and employee efficiency for Café Harmony using the Pareto Principle (80/20 rule).
The goal: identify the top 20% of customers, products, and employees that drive 80% of café revenue and provide actionable recommendations for business growth.
Welcome to Café Harmony, a new and exciting virtual café chain! The café specializes in offering a variety of drinks, snacks, and light meals to customers. Over the past year, Café Harmony has seen impressive growth but now faces new challenges as the company is scaling to meet customer demand. The café has multiple locations across the city, each with its own performance metrics and unique customer preferences.
Your job is to dive into the business data of Café Harmony, clean and analyze it, and ultimately build a dashboard that will help the company's management make key decisions for growth.
The café faced several challenges:
- Product performance by gender.
- Customer spending profiles.
- Stock management inefficiencies.
- Employee performance gaps.
- Revenue variations across locations.
We applied the Pareto Principle to identify and focus on the key factors driving most of the revenue:
- Top-selling products contributing most to sales.
- Menu items performing best across different locations.
- Customer demographics that spend the most (age, gender, preferences).
- Stock management improvements to balance supply & demand.
- Location and employee performance issues requiring attention.
This approach highlights the minority (20%) of data points that drive the majority (80%) of results.
- Removed duplicate IDs using conditional formatting.
- Filled and corrected missing values.
- Standardized formats (Numbers, Dates).
- Sorted and organized data by unique IDs.
- Categorized attributes (e.g., age groups: Youth, Adult, Senior; location: Urban, Rural).
- Applied formulas such as
IFS,XLOOKUP, andVLOOKUPfor categorization.
- Sales remained stable between 700–1,000 per month.
- Peaks: April & July (stronger performance).
- Declines: February, October & December.
- Overall: gradual decline towards year-end, requiring new growth strategies.
- Average rating: 3.05 – above average but leaves room for improvement.
- Customer comments suggest products can be improved despite positive ratings.
- Muffins → Top revenue generator, especially in Urban locations (airports, uptown).
- Iced Tea → Best performer in Suburbs/Downtown.
- Sandwiches → Lowest revenue overall, with low appeal across locations.
- Female customers consistently generate the highest revenue across all age groups and locations.
- Marketing campaigns targeting women could maximize sales.
- Overstocked: Coffee beans (risk of waste).
- Understocked: Milk (risk of running out).
- Balanced: Juice concentrate (ideal benchmark).
- Conclusion: Stock imbalance leads to both waste and customer dissatisfaction.
- Expand muffins (add flavors/options) in Urban areas.
- Promote iced tea combos in Suburbs/Downtown with signage or discounts.
- Reduce sandwich waste by testing smaller batches or recipe adjustments.
- Focus campaigns on female customers (loyal and high-spending group).
- Personalize offers by location preferences (Muffins in Urban, Iced Tea in Suburbs).
- Reduce purchases of coffee beans, bread, sugar.
- Reallocate budget to milk, muffins, iced tea, and croissants.
- Use real-time inventory tracking with low-stock alerts to balance supply & demand.
- Raise targets for top performers to match ability.
- Training/coaching for underperformers.
- Consider role adjustments for consistent low performers.
- Urban: Capitalize on Muffin & Cappuccino demand with grab-and-go offers.
- Suburbs/Downtown: Market Iced Tea as a local favorite.
By focusing on the top 20% of products, customers, and employees, Café Harmony can maximize revenue, minimize waste, and improve customer satisfaction.
Through optimized stock management, targeted marketing, and fair employee performance measures, the café is positioned for sustainable long-term growth.