Wine Merchant Invoice to Cost-Per-Pour Spreadsheet

A 750ml bottle yields about five 5-ounce pours. The standard wine-bar target is for one of those pours to recover the bottle's wholesale cost, which produces a pour cost of roughly 20% across the five glasses. The cost-per-pour spreadsheet derives the by-the-glass price from the merchant invoice itself: per-bottle wholesale (case cost minus case discount, divided by case pack), plus the regional tax line — Wine Equalisation Tax in Australia, alcohol duty in the UK, federal-plus-state excise in the US — recorded per vintage and rolled into the landed bottle cost.

That arithmetic is the spine of every honest by-the-glass list. The artefact you start from is a distributor PDF — Vintus, Kermit Lynch, Liberty Wines, Negociants, Daniel Lambert, Winebow — with case pack, case discount, a separate WET or excise line, GST or VAT, and freight. Most pour-cost guides on the open web start at "you have a bottle cost" and walk a single multiplier from there. This is the wine merchant invoice to cost-per-pour spreadsheet workflow that walks the other direction: from the actual merchant document into the columns the wine list reads from. Wine is one beverage stream within a broader F&B accounts-payable workflow alongside general restaurant invoice scanning across food and beverage suppliers, but the by-the-glass program has its own arithmetic, its own tax-line treatment, and its own re-pricing cadence that sit apart from how the rest of the supplier file is handled.

Regional tax detail is exactly that — detail, not headline. The pour-cost skeleton is the same in Sydney, London, and San Francisco; what changes is which tax line sits on the invoice and how it lands on the bookkeeping. The walk that follows starts at the anatomy of the merchant invoice, lays out the yield math, and states the pour-cost formula. From there it treats AU WET, UK alcohol duty, and US federal-plus-state excise each anchored to the spreadsheet column they produce, walks the full schema column by column, populates it with three worked examples across the three regimes, and closes with the reserve / by-the-glass separation and a re-pricing cadence that catches margin drift before it shows up in next quarter's wine COGS.

---

Anatomy of a wine merchant invoice

A typical distributor invoice falls into four bands of fields, each of which feeds a defined part of the spreadsheet.

Document metadata sits at the top — invoice date, invoice number, merchant or distributor name, the bar's account number, and the delivery address. These are the identity columns of the row in the spreadsheet, the audit trail back to the source PDF.

Per-line product identity follows on each wine line: producer, cuvée, vintage, region or appellation, varietal, case pack, and bottles received. Producer and cuvée are the wine itself; vintage is the year of harvest; appellation is the AOC, AVA, DOCG, IGP, or comparable origin marker; varietal is the grape (Pinot Noir, Sangiovese, Albariño). Case pack is a merchant convention rather than a property of the wine — typically 12 bottles per case for 750ml, 6 for magnums, occasionally 6 for premium 750ml allocations where the importer wants to spread limited stock. Bottles received is the quantity actually delivered, which the spreadsheet tracks separately from bottles ordered for short-shipment reasons.

Per-line pricing carries the case cost (the headline number the merchant quotes), the per-bottle wholesale (sometimes printed alongside, sometimes derived), and the case discount applied conditionally on full-case orders. Many merchants quote both per-bottle and per-case prices on the same invoice with the discount applied at full-case quantities; the spreadsheet should record both the gross per-bottle wholesale and the case discount applied so landed cost reconstructs cleanly when you audit a row weeks later. Per-bottle ex-tax cost is a derived field: case cost × (1 minus case discount) ÷ case pack.

Document-level lines sit at the foot — freight, the regional tax line (WET on an Australian invoice, alcohol duty embedded in a UK invoice, TTB federal plus state excise on a US invoice), GST or VAT, and the document total. The regional tax line is the part most pour-cost templates collapse into "bottle cost" silently; the discipline this spreadsheet imposes is keeping it as its own column. The detailed treatment of each regime sits in a later section; here the point is just that the line is there and it doesn't get absorbed.

Two of these fields earn special attention because they are exactly what manual re-keying drops most often. Vintage is the most consequential: the same wine at two vintages is two distinct rows in the cost-per-pour spreadsheet. Drop vintage on entry and you collapse inventory the operator needs to keep distinct for pricing, for pairing decisions, and for re-ordering. Cuvée sits in the same category — a producer who makes a Reserve, a Single Vineyard, and an estate blend in the same vintage produces three lines on the invoice that share producer and vintage but diverge on cuvée; the spreadsheet has to carry the cuvée field or those lines blur into a single bottle for pricing purposes.

Distributor invoice formats vary across houses. Vintus and Kermit Lynch lay out US allocations differently from how Negociants quotes AU allocations or how Liberty Wines and Daniel Lambert quote UK and continental allocations. The spreadsheet schema is the same across all of them; the per-merchant variation is in which fields the PDF surfaces, where on the page they sit, and which appear as line-level versus document-level entries.

This is the field set AI-powered extraction for wine merchant invoices reads directly off the merchant PDF — vintage, cuvée, appellation, case pack, case discount, the separate WET or excise line, GST or VAT — so the cost-per-pour spreadsheet receives populated columns rather than columns the operator fills in by hand from a PDF on a second monitor. The interaction is a single natural-language prompt naming the fields and the row-per-invoice or row-per-line structure, and the output is an Excel, CSV, or JSON file that drops into the spreadsheet's input tab. There is no template to configure for each new distributor format and no rules engine to maintain — the prompt is the configuration, and the same prompt handles a 5-line Vintus invoice or a 200-line Negociants quarterly delivery without changes.

Yield math: 750ml = five pours

A 750ml bottle holds 25.36 fluid ounces. A 5oz standard pour yields five pours per bottle once a tasting-pour and a small spillage allowance are counted in. Some programs target a 5.5oz or 6oz pour and accept four full pours per bottle instead of five; the spreadsheet logic does not change, only the value in the "pours per bottle" column.

Other bottle formats fall out of the same arithmetic. A magnum (1.5L) at a 5oz pour yields ten pours. A half-bottle (375ml) yields two pours and a tasting-pour worth of remainder. Most by-the-glass programs run on 750ml; magnums and half-bottles tend to live in cellar selections, tasting menus, and reserve pours rather than the standard glass list.

Two pieces of working vocabulary sit alongside the yield. A dump is wine poured off — oxidised, corked, returned by the guest, or the last ounce of a bottle that has been open one night too many. A short pour is a deliberately under-poured glass, typically a tasting-pour offered while a guest decides between two wines or a flight pour where each glass is meant to be small. A working program loses around 0.5oz to 1oz per bottle to dumps and short pours combined. The five-pour convention sits just below the 5.07 the pure 25.36 ÷ 5 arithmetic gives, which is exactly where the dump-and-short-pour loss is absorbed — so it does not need its own line in the spreadsheet.

A worked sentence to anchor the next section: a $14 wholesale bottle yielding five pours has a $2.80 per-pour cost before the tax line and freight are added in. The by-the-glass price is then derived from the operator's target pour-cost percentage, which is what the next section walks.

A note on bottle preservation: wines-by-the-glass programs that use Coravin needles or argon-blanket pour systems can stretch the by-the-glass window across more nights without the bottle oxidising, but the pours-per-bottle count stays at five. Preservation systems extend the time horizon, not the yield. Spreadsheet-side, the assumption holds.

Landed cost, pour cost, and the by-the-glass price

Three formulas chain together to take the invoice fields the prior sections walked into a price the guest sees on the wine list.

Landed per-bottle cost = per-bottle wholesale (after the case discount) + the bottle's share of the tax line + the bottle's share of freight. Tax-line treatment varies by jurisdiction and is the next section's territory; freight is usually allocated evenly across every bottle on the invoice, which is the convention the spreadsheet defaults to. A more careful operator weights freight by case where bottle size or fragility differs materially, but for standard 750ml deliveries the per-bottle average is fine.

Per-pour cost = landed bottle cost ÷ pours per bottle. With the five-pour assumption from the prior section, a $14 wholesale bottle on an invoice with no separately invoiced tax line and $1 per-bottle allocated freight lands at $15, and produces a $3 per-pour cost.

By-the-glass price = per-pour cost ÷ target pour-cost percentage. A 20% target gives a $15 BTG price; a 25% target gives $12; a 30% target gives $10. The spreadsheet should hold the operator's target pour-cost percentage as a tunable column, not a hard-coded constant — different bars run different targets, and the same bar may run different targets across red, white, sparkling, and reserve pours.

The industry rule-of-thumb sits behind the 20% number directly. A single by-the-glass pour, priced right, recovers the full wholesale cost of the bottle. With five pours per bottle, that produces a 20% pour cost across the bottle as a whole — the conventional fine-dining target. Casual concepts often run 25% to 30% to keep glass prices accessible to the room they serve. The rule-of-thumb is a starting point, not an edict; the spreadsheet column is the place to tune it.

Two related terms get used interchangeably in the trade and shouldn't be. Markup is the multiplier from cost to list price (a 5× markup at 20% pour cost; a 4× markup at 25% pour cost). Margin is the percentage of the list price that is not cost, which is the inverse of pour-cost percentage. Either is a valid target column; the spreadsheet should be explicit about which one it holds, and the wine-program convention is to target on margin (or equivalently on pour-cost percentage), where the food side of the same operation typically targets on cost percentage. Mixing the two on the same sheet is the most common source of pricing errors when a new staff member takes over the workbook.

The 4× markup convention exists because the price recovers more than the wine. Stemware breaks. Some glasses get returned. Bottles open for the by-the-glass program don't always finish before they oxidise. Cellar investment is illiquid for years. Service is real labour. The convention pays for all of that. The reader of this article is the operator setting the target column, not the diner reading the list.

---

Tax lines on a wine invoice across AU, UK, and US

Three regional tax regimes touch a wine merchant invoice differently, and each one feeds a single column in the spreadsheet. The treatments below stay anchored to that column and to the operator's bookkeeping rather than restating the full tax law.

Australia — Wine Equalisation Tax. WET is 29% of the wholesale value, applied at the last wholesale sale of wine before retail. On a distributor invoice into a wine bar, WET is generally embedded as a separate line on the document. The operator reports WET payable at label 1C on the Business Activity Statement and claims it back as an input-tax credit at label 1D where the bar is registered for GST and the wine is acquired for the registered enterprise. The "quote-the-ABN" mechanism sits one step upstream: a buyer's Australian Business Number flows up to its wholesaler so WET is not triggered at that earlier sale, and the reseller bears WET only when the wine moves on to the bar or restaurant at final wholesale. For a GST-registered wine bar buying from a distributor, the practical position is straightforward — the WET line on the invoice is recorded as input cost recovered through the BAS, not as margin absorbed into the bottle price. The Australian Taxation Office is the standing authority on the mechanism and rulings.

United Kingdom — alcohol duty post-1 August 2023 reform. UK alcohol duty is now calculated on the alcohol-by-volume strength of the product rather than on the old wine-versus-spirits product categories. Under HMRC's published alcohol duty rate changes from 1 August 2023, all alcoholic products of at least 8.5% but not exceeding 22% ABV are charged duty at £28.50 per litre of pure alcohol in the product, with wine between 11.5% and 14.5% ABV treated as 12.5% ABV during the transitional period to 1 February 2025. The duty is embedded in the merchant's invoice price and passes through to the bar as input cost; there is no separate input-credit mechanism comparable to WET — the operator carries it as cost into landed bottle cost and recovers it through the by-the-glass price. The transitional easement ended 1 February 2025 and the labelled ABV now applies to each individual wine, which means small per-bottle changes in landed cost follow whenever a label sits at 13% or 14% rather than the previous 12.5% assumption. Beer delivered into the same operation runs through a different cask- and keg-shaped duty workflow that has its own delivery-note discipline; the same logic of pricing a draught yield against landed cost applies, but the documentary trail differs — see draught beer delivery-note bookkeeping for UK pubs for the beer-side counterpart.

United States — TTB federal plus state. TTB (the Alcohol and Tobacco Tax and Trade Bureau) charges federal excise on still wine of 14% ABV or under at $1.07 per wine gallon, which works out to about $0.21 per 750ml bottle; the 14% to 16% bracket is taxed at the same rate under the permanent extension of the Craft Beverage Modernization Act provisions. State excise sits on top of federal and varies widely — California is $0.20 per gallon, Washington is approximately $0.23 per litre, New York and several other states sit higher. Sales tax applies downstream at point of sale and is not part of the landed bottle cost calculation. Treat the federal figure as a stable column input across the spreadsheet; treat state excise as a per-jurisdiction input the operator sets once for the bar's state rather than as a table to recite for the country.

The spreadsheet implication is simple. Each regime feeds one column and the pour-cost arithmetic from the prior section is identical across all three. An Australian bar carries a "WET line" column and the BAS treatment is recorded once for the workbook as a whole. A UK bar carries an "alcohol duty embedded" column that has already passed through into the landed cost the merchant invoiced. A US bar carries "federal excise" and a per-state column that combine into the tax allocation. Beyond that one column shift, the formulas don't change.

The cost-per-pour spreadsheet schema, column by column

The spreadsheet falls into four logical blocks. Walking the schema as four blocks rather than one long column list is closer to how the workbook actually reads: each block has a single job, the formulas chain forward through the blocks in order, and the wine list reads from the last one.

Identity columns name the wine and where it came from: invoice date, merchant or distributor, invoice number, producer, cuvée, vintage, region or appellation, varietal. These are the "what is this row" fields. Most of them come straight off the merchant invoice as-is. Vintage and cuvée carry the weight here — they are what keeps a row distinct from another row of the same producer in another year or another label.

Quantity and pricing columns carry what the merchant invoice quoted: case pack, bottles received, per-bottle wholesale, case discount applied. These are the inputs to the per-bottle cost arithmetic. The case discount column matters because it lets you reconstruct landed cost cleanly when you audit a row weeks later — without it, you can't tell whether the per-bottle wholesale stored on the row reflects the headline quote or the discounted figure.

Tax and landed-cost columns are the arithmetic from the prior two sections in spreadsheet form: the regional tax line (WET, alcohol duty, or federal-plus-state excise), GST or VAT, and the landed per-bottle cost that combines wholesale, the tax allocation, and freight. The tax line is operator-input on AU and US workbooks (read from the invoice line) and an embedded passthrough on UK workbooks. Landed per-bottle cost is the formula column the next block depends on.

Pricing-output columns are what the wine list actually reads from: pours per bottle, per-pour cost, target pour-cost percentage, by-the-glass price, last-priced date. Pours per bottle is operator-set (five for a 5oz pour, four for a 6oz pour, ten for a magnum on the cellar list). Per-pour cost and BTG price are formula columns that chain off landed cost, pours, and the target percentage. Last-priced date is operator-input and is the column most often missing from operator-built spreadsheets — without it, the re-pricing cadence in the closing section can't run, because there is no record of when the row was last refreshed against current landed cost.

The natural home for this is an Excel or Google Sheets workbook. Formulas chain across columns; conditional formatting on the per-pour cost percentage above target flags rows that have drifted; a sort by appellation and producer arranges the rows for wine-list layout; a separate tab can hold the cellar / reserve rows that don't yet need pour-cost treatment. The operator owns the file directly. It isn't tied to a specific inventory system, doesn't depend on a SaaS module, and travels with the wine program when the operator changes venues or takes on a consulting client.

Three worked examples — AU Shiraz, French Sancerre, Californian Pinot

Three rows across the three regimes, each starting from a different invoice shape and landing on a by-the-glass price. The figures are illustrative — wholesale prices, importer margins, and duty rates move, and an operator should always re-anchor each row to the actual invoice in front of them.

Example 1 — Australian Shiraz on a Negociants invoice. A case of 12 × 750ml lands at AU$240 case cost with a 5% case discount applied for the full case. Per-bottle wholesale after discount is AU$19 ex-tax. The WET line at 29% of wholesale is recorded on the invoice as AU$5.51 per bottle and treated as input recovered through BAS label 1D for the GST-registered bar — so it does not flow into landed cost for pour-cost purposes. Freight is allocated at AU$1 per bottle. Landed per-bottle cost for pour-cost arithmetic comes out at AU$20. Per-pour cost at five pours is AU$4. At a 22% target pour-cost percentage the BTG price lands around AU$18 per glass.

Example 2 — French Sancerre on a UK importer's invoice. A case of 12 × 750ml at £216 case cost (Liberty Wines, Daniel Lambert, or a comparable specialist importer). Per-bottle wholesale is £18 ex-VAT, with UK alcohol duty already embedded in the merchant's price — a 12.5% ABV Sancerre carries roughly £2.67 per 750ml of duty under the £28.50 per litre of pure alcohol rate, which the importer absorbed into the line price. Freight allocated at £1 per bottle. Landed per-bottle cost is £19. Per-pour cost at five pours is £3.80. At a 25% target pour-cost percentage the BTG price lands around £15 per glass.

Example 3 — Californian Pinot Noir on a Kermit Lynch invoice. A case of 12 × 750ml at $300 case cost. Per-bottle wholesale is $25. TTB federal excise of $0.21 per bottle is embedded in the merchant's price; California state excise at $0.20 per gallon adds about $0.04 per bottle. Freight allocated at $1 per bottle. Landed per-bottle cost is $26.25. Per-pour cost at five pours is $5.25. At a 20% target pour-cost percentage the BTG price lands around $26 per glass.

The through-line across the three rows: the same three operations — per-bottle wholesale into landed cost, landed cost into per-pour cost, per-pour cost into BTG price — produce the answer in every market. Only the tax-line treatment and the merchant's price scale shift. The spreadsheet schema does not change shape between the AU, UK, and US rows; the columns are populated differently and the tax block has a different mechanic, but a single workbook can hold all three regimes side by side for an operator running rooms in more than one country.

---

Reserve stock, the BTG list, and a re-pricing cadence that catches margin drift

Most wine bars carry the cellar in two layers, and only one of them needs pour-cost treatment.

The by-the-glass layer is the open-and-pour list — the wines on the printed glass list, the bottles being opened nightly, the rows the spreadsheet's pricing-output columns drive. The reserve or cellar layer holds bottles not yet on the list — back-vintages waiting for the right moment, allocations being aged into drinking windows, special-occasion bottles held for events. The reserve layer sits at landed cost in inventory (in the same workbook on a separate tab, or in a parallel sheet) until a bottle is re-categorised onto the BTG list, at which point it inherits a row in the pricing-output block and the per-pour arithmetic kicks in. Treating the two layers separately keeps the BTG sheet from being padded with rows that don't have a current price, and keeps the reserve from being repeatedly re-priced when nothing has changed.

The re-pricing cadence on the BTG layer is where most margin drift gets caught or missed. A practitioner default that holds up well in busy programs: re-price a BTG row whenever landed cost on a new case purchase drifts more than 5% from the last priced cost. The mechanism runs off the last-priced date column. A new invoice arrives, the operator updates the row's per-bottle wholesale and the formulas recalculate landed cost; if the new landed cost is more than 5% off the prior figure, the BTG price recomputes against the target pour-cost percentage and the wine list is updated. If the drift is under 5%, the row is touched (last-priced date refreshed) but the price holds.

Invoice-driven re-pricing on this rule beats a quarterly menu refresh at protecting margin. A vintage rollover or a distributor price increase can move landed cost 10% to 15% inside a single case — a bottle that was costing $20 landed now costs $23, and the BTG price set against the old figure suddenly runs at a 23% pour cost rather than the 20% the spreadsheet was tuned to. A quarterly menu refresh catches this on the next refresh; the 5% rule catches it on the case the invoice arrived with. The 5% threshold is a default the operator can tune — programs running on tight margins set a 3% trigger, programs willing to absorb minor drift between menu reprints set a 7% or 10% trigger.

The practical consequence of getting this wrong is quiet. Mis-priced glasses leak margin glass by glass. A vintage rollover that lifts landed cost by 8% on a wine selling at the prior BTG price moves a 22% pour-cost row to roughly a 24% pour-cost row — visible in the next quarter's wine COGS report when it is too late to recover, and invisible at the bar where every glass pours and rings up exactly as before. The wine wholesale invoice bookkeeping cadence — read the invoice, update the row, check the drift, decide whether to re-price — is the loop that closes the gap.

This wine-program workflow is one of three that run in parallel inside the same operation. Spirits and beer programs run the spirits and beer pour-cost workflow that complements this wine program on the same arithmetic spine but with different yield assumptions — measured pour size for spirits, keg yield and gas allowance for draught beer — and a different re-pricing trigger that keys off case-and-keg purchases rather than vintage-and-case. Food side, the cost-percentage convention is different again: kitchens run a higher cost-percentage target, the supplier cadence is weekly rather than allocation-driven, and the documentary trail looks more like the food-side weekly purchase log from wholesaler invoices than a per-bottle pour-cost row. The three streams share an accounts-payable file but live in three separate pricing workbooks, each with its own discipline.