A bar inventory spreadsheet records products, quantities, units, locations, and costs so you can calculate stock value and usage. Separate receiving, transfer, and waste logs explain movements that a closing count alone cannot.
A missing transfer can make one outlet look short while another looks overstocked. That error can trigger unnecessary purchasing or an unjustified accusation against staff. Your spreadsheet should expose the difference between documented movement and unexplained depletion.
Scannabar supports bar inventory control when you need bottle-level tracking and reporting beyond a spreadsheet. The worksheet below explains the data discipline either approach requires.
For the receiving, counting, and cost-review process this worksheet supports, see our liquor inventory and cost-control guide.
Your count sheet should identify each product and its cost basis without relying on memory. Use one row per product and location, with consistent units throughout the period.
Include SKU, product name, bottle size, location, counting unit, beginning quantity, ending quantity, unit cost, and count date. Keep 750 mL and 1 L bottles separate even when the brand is identical.
For partial bottles, document the measurement method and train counters to apply it consistently. Compare inventory methods before choosing your procedure.
Separate movement logs explain how stock entered or left each reporting location. They also make duplicate invoices, missing transfers, and supplier returns easier to reconcile.
Your receiving tab should include date, supplier, invoice, product, quantity, unit, and cost. A transfer tab should include origin, destination, quantity, and approval. Returns need their own documented adjustment so they are deducted once.
Your waste and complimentary-use log should capture date, item, quantity, reason, and approval. Documenting these events supports variance reviews without treating every non-sale pour as misconduct.
Stock value equals quantity on hand multiplied by unit cost. Usage includes all stock available during the period, less stock returned, transferred out, or still on hand.
Usage units = Beginning quantity + Receipts + Transfers in − Transfers out − Supplier returns − Ending quantity.
Closing value = Ending quantity × Unit cost. For a constant-cost example, usage cost equals usage units times unit cost. If costs change, follow a consistent valuation policy agreed with your accountant rather than silently replacing historical values.
Usage reports organize these outputs. Keep documented waste inside total physical usage; subtract it only when calculating the remaining unexplained variance, or you will hide actual consumption.
This hypothetical table uses bottle-equivalent units and constant unit costs. It assumes no transfers or returns. The receipt and count values below are illustrative, not supplier pricing or performance claims.
| Product | Size | Opening | Receipts | Closing | Usage | Unit cost | Usage cost | Closing value |
|---|---|---|---|---|---|---|---|---|
| House vodka | 1 L | 8.0 | 6.0 | 5.3 | 8.7 | $20 | $174 | $106 |
| House gin | 1 L | 4.0 | 3.0 | 3.6 | 3.4 | $22 | $74.80 | $79.20 |
| Total | $248.80 | $185.20 |
If matching net sales for these products were $1,000, the cost ratio would be 24.88%. In a spreadsheet, enter usage cost divided by matching sales and format the cell as a percentage. Do not multiply by 100 and then apply percentage formatting again.
Consistent units prevent quantity errors from flowing into every cost calculation. Record whether an entry means bottles, cases, ounces, or milliliters, and reject incompatible entries.
A three-case receipt is not three bottles. Convert receipts to the counting unit before calculating usage. Keep packaged beer and keg units explicit rather than treating every item as a spirit bottle.
For wine inventory, separate unopened bottles from open-bottle quantities. Record each location so a cellar-to-service transfer does not appear to be a sale.
Protect formula cells, keep dated count snapshots, and limit changes to approved inputs. Freeze headers and use validation rules for units, locations, and product names.
Save each period's counts before starting another. Reconcile receiving against invoices, transfers between both locations, and portion standards against recipe expectations.
Cheryl Stanley, Senior Lecturer at Cornell's Nolan School of Hotel Administration, includes rigorous controls in her beverage program course. A worksheet supports that discipline; it cannot replace it.
Consider a more structured system when counts are delayed, multiple editors overwrite records, or location reconciliation absorbs too much staff time. There is no universal SKU count or revenue threshold that determines the switch.
Scannabar's liquor inventory software supports bottle-level counts, usage analysis, and outlet reporting. Review how those workflows fit your receiving and transfer procedures rather than assuming software will correct missing records automatically.
A repeatable beverage audit helps identify the step that is failing. Use inventory accountability to make the process easier for staff and protect them from unsupported accusations.
A useful spreadsheet connects quantities with units, costs, locations, and documented movements. Build that foundation first. When version control or reconciliation becomes unreliable, evaluate a system that keeps those records connected rather than adding more disconnected tabs.
Yes, if you maintain recipe quantities and matching POS item counts. Multiply each ingredient quantity by the number of drinks sold. Scannabar's usage reports support comparison with physical depletion, but the recipe mapping and documented non-sale usage still need to be accurate.
No. Waste is product consumed without a sale, so it remains in actual usage cost. Deduct documented waste when reconciling unexplained usage, not when measuring total consumption. Scannabar's reports support the review when your waste and movement records are kept consistently.
No. Your inventory records need reliable physical observations and movement entries. Scannabar supports bottle-level measurement and reporting, but the team still needs a documented counting routine. Software organizes the workflow; it does not make missing or inconsistent inputs trustworthy.