Bar Inventory & Sales Tracking

Cheatsheet Content

Daily Sales & Inventory Tracking Use one sheet per day for detailed tracking of specific products. Item Starting Inventory (Units) Purchases (Units) Ending Inventory (Units) Units Sold Unit Price ($) Daily Sales ($) Vodka (1L) $B4$ $C4$ $D4$ $=B4+C4-D4$ $F4$ $=E4*F4$ Beer (Case) $B5$ $C5$ $D5$ $=B5+C5-D5$ $F5$ $=E5*F5$ ... ... ... ... ... ... ... Total Daily Sales $=SUM(G4:G100)$ Key Metrics for Daily Tracking Variance: Compare "Units Sold" from inventory vs. POS sales data. Formula: $= \text{Units Sold (Inventory)} - \text{Units Sold (POS)}$ Cost of Goods Sold (COGS) per item: Formula: $= \text{Units Sold} \times \text{Cost per Unit}$ Weekly Sales Summary Summarize daily sales and track key performance indicators over a week. Date Total Daily Sales ($) Daily COGS ($) Gross Profit ($) Labor Cost ($) Net Profit ($) Mon (e.g., 2023-10-23) $B4$ $C4$ $=B4-C4$ $E4$ $=D4-E4$ Tue (e.g., 2023-10-24) $B5$ $C5$ $=B5-C5$ $E5$ $=D5-E5$ ... ... ... ... ... ... Weekly Totals $=SUM(B4:B10)$ $=SUM(C4:C10)$ $=SUM(D4:D10)$ $=SUM(E4:E10)$ $=SUM(F4:F10)$ Weekly Inventory Check Perform a full inventory count at the end of each week. Item Starting Inventory (Units) Received (Units) Ending Inventory (Units) Units Used Cost per Unit ($) Value Used ($) Vodka (1L) $B4$ $C4$ $D4$ $=B4+C4-D4$ $F4$ $=E4*F4$ ... ... ... ... ... ... ... Total Weekly Inventory Value Used $=SUM(G4:G100)$ Weekly Performance Ratios Gross Profit Margin: Formula: $= \text{Total Weekly Gross Profit} / \text{Total Weekly Sales}$ Pour Cost: (For alcoholic beverages) Formula: $= \text{Value of Alcohol Used} / \text{Total Alcohol Sales}$ Labor Cost Percentage: Formula: $= \text{Total Weekly Labor Cost} / \text{Total Weekly Sales}$ Monthly Sales & Inventory Summary Aggregate weekly data for a monthly overview. Week Total Sales ($) Total COGS ($) Gross Profit ($) Total Labor ($) Net Profit ($) Inventory Value Used ($) Week 1 $B4$ $C4$ $=B4-C4$ $E4$ $=D4-E4$ $G4$ Week 2 $B5$ $C5$ $=B5-C5$ $E5$ $=D5-E5$ $G5$ ... ... ... ... ... ... ... Monthly Totals $=SUM(B4:B7)$ $=SUM(C4:C7)$ $=SUM(D4:D7)$ $=SUM(E4:E7)$ $=SUM(F4:F7)$ $=SUM(G4:G7)$ Monthly Inventory Valuation Conduct a full physical inventory count at month-end. Item Previous Month Ending Inv. (Units) Total Received This Month (Units) Current Month Ending Inv. (Units) Units Used This Month Average Cost per Unit ($) Total Value Used ($) Gin (1L) $B4$ $C4$ $D4$ $=B4+C4-D4$ $F4$ $=E4*F4$ ... ... ... ... ... ... ... Total Monthly Inventory Value Used $=SUM(G4:G100)$ Monthly Performance Indicators Inventory Turnover Ratio: Formula: $= \text{Total Monthly COGS} / \text{Average Inventory Value}$ Average Inventory Value: $= (\text{Beginning Inventory Value} + \text{Ending Inventory Value}) / 2$ Break-Even Point: Formula: $= \text{Total Fixed Costs} / (1 - (\text{Total Variable Costs} / \text{Total Sales}))$ Actual Pour Cost vs. Ideal Pour Cost: Ideal Pour Cost: Based on menu prices and actual ingredient costs. Actual Pour Cost: From inventory usage and sales. General Tips for Excel Tracking Data Validation: Use data validation to ensure consistent entry of item names. Named Ranges: Name ranges for easier formula creation (e.g., "DailySales_Mon"). Dashboard: Create a summary tab with charts for visual representation of trends. Backup: Regularly back up your Excel files. POS Integration: If possible, integrate with your Point of Sale (POS) system for sales data. Unit Consistency: Always track inventory in consistent units (e.g., bottles, cases, liters).