This project is a business-focused data analytics case study for a premium café. The goal was to evaluate menu profitability using pricing, ingredient costs, and simulated sales volume to understand the true drivers of profit.
Instead of only analyzing revenue, this project focused on item-level margins and menu performance.
The café faced unclear profitability despite premium pricing. The key question was:
- Are menu prices too low?
- Are food costs too high?
- Or is low customer demand the real issue?
- Calculate item-level Cost of Goods Sold (COGS)
- Measure contribution margin for each menu item
- Simulate monthly demand for realistic business analysis
- Classify menu items using Menu Engineering
- Identify profit drivers and weak performers
Contains menu items, categories, and selling prices.
Ingredient quantities required for each menu item.
Procurement costs for raw materials.
Converted per gram / ml / piece cost values.
Final profitability model with margins and menu categories.
- Microsoft Excel
- Pivot Tables
- Lookup Functions (XLOOKUP / VLOOKUP)
- IF Logic
- Basic Financial Modeling
- Total COGS
- Contribution = Selling Price - COGS
- Margin %
- Monthly Revenue
- Monthly Gross Profit
- ⭐ Star → High margin, high demand
- 🐎 Workhorse → Low margin, high demand
- 🧩 Puzzle → High margin, low demand
- 🐶 Dog → Low margin, low demand
- Most menu items had healthy gross margins (70%–90%)
- Coffee beverages were strong profit drivers
- Some premium desserts had lower margins
- Profitability issues were more likely caused by low footfall or sales mix rather than pricing
To improve profits, the café should focus on:
- Increasing customer traffic
- Promoting high-margin beverages
- Bundling slow-moving desserts
- Optimizing menu placement and upselling
This project demonstrates how data analytics can support restaurant pricing, menu optimization, and profitability decisions.