A neighborhood bar looking to gain better visibility into beverage costs, pricing strategy, and profit margins while eliminating the manual effort required to maintain costing spreadsheets.
07 Pub was managing drink costing and margin analysis through a collection of spreadsheets, distributor invoices, and sales reports.
However, the process had become increasingly difficult to maintain because:
The business needed a practical solution that could accurately calculate pour costs, margins, and recommended pricing while remaining easy to maintain for non-technical users.
We redesigned the entire costing workflow using Excel, Power Query, and VBA automation.
First, we created a standardized data model that automatically imports and cleans pricing data from distributor invoices, regardless of format.
Next, we built an item mapping system that connects supplier product names with menu items and sales reports, creating a single source of truth for inventory and pricing data.
Using this foundation, we developed an automated costing engine that calculates:
To simplify ongoing maintenance, we implemented Power Query refresh workflows and VBA automation, allowing new invoice data to be imported and processed with just a few clicks.
What was previously a collection of disconnected spreadsheets became a centralized costing and profitability system, helping 07 Pub protect margins and make smarter pricing decisions in a changing cost environment.
