Pricing without data is just guessing.
The café had no structured way to analyze profitability at the item level. Pricing decisions were based on what felt right or what competitors charged — not what the numbers supported.
Without item-level cost data, the business had no way to know which products were driving profit and which were barely breaking even. Revenue looked fine on the surface; the underlying economics were invisible.
A full item-level margin analysis model.
I built a profit margin analysis system that broke down every menu item by its cost components — ingredients, packaging, labor allocation — and compared that against selling price to calculate true gross margin per item.
The model showed not just what margins were, but why — and made it easy to see exactly which cost or price variable to adjust to improve them.
Note: The downloadable file uses anonymized sample data to protect business confidentiality. The analysis was performed using real menu pricing and cost data from the café.
What's inside the system.
- Full ingredient cost breakdown per menu item using unit cost and recipe quantities
- Gross margin calculation per item: (Revenue − COGS) / Revenue
- Margin ranking table sorting all items from highest to lowest profitability
- High vs. low margin classification with threshold-based flagging
- Scenario modeling: adjust cost or price inputs to model margin impact in real time
- Cost percentage analysis identifying items that exceed target food cost %
- Revenue weight analysis combining margin with sales volume to surface true profit drivers
Open the actual model.
The real margin analysis workbook built for Lotus Café — item-level cost breakdown, gross profit % by product, and a dynamic scenario model where any cost or price input recalculates margins instantly.
The numbers exposed what intuition missed.
The model surfaced a clear pattern: espresso-based drinks with standard milks are exceptionally profitable — the Americano came in at 91% margin, lattes averaging around 82%. The problem items were immediately obvious once the math was done. The Hemp Milk Chai Latte had a 27% margin at $7.75 — the chai concentrate alone cost $4.19 per drink. The Housemade Cold Brew came in at 46%, the lowest margin of any beverage, driven by its complex preparation cost.
The analysis also revealed that the top 3 best-selling items — Latte, Americano, and Coffee of the Day — were also among the highest-margin items, which validated the menu strategy. The business wasn't just selling what people wanted; it was selling what made money. The data confirmed what to push and what to reconsider.