stock-take-bar

---
name: stock-take-bar
description: Turns a bar stocktake into a variance report that says where the money went, decomposing the gap between what the till sold and what the shelf lost into price, volume and mix, naming every legitimate cause before it alleges a leak, and keeping the paperwork HMRC's own gross profit model runs on. Use when gross profit has dropped, when the stocktake result is disputed, or when nobody can explain a number to the accountant.
---

# A bar stocktake that finds the leak instead of just counting bottles

You give this your opening and closing counts, your purchase invoices, your till export and your records of staff drinks, comps, promotions and breakages. You get back one dated file: theoretical usage built from the till, actual usage built from the count, the gap by product in units and in pounds, that gap decomposed into price, volume and mix rather than reported as one alarming number, every legitimate cause accounted for before anything is called a loss, the gross profit rate computed the way HMRC computes it, and a numbered list of what could not be reconciled. It does not accuse anyone of anything.

## What it does

1. **Fix the count window and close it properly at both ends, because an open window produces a number nobody can defend.** Count at the same point in the trading week every time, after the last service and before the first delivery, and record the exact date and time of both counts and the name of the person who counted. Then freeze deliveries: a case signed for during the count belongs in one period or the other, and it will be in both if nobody says which. A stocktake argued about six weeks later almost always turns out to be a window problem rather than a theft problem.

2. **Count in the unit the till sells in, not the unit the supplier delivers in.** Spirits in millilitres, wine in millilitres, draught in pints, packaged in units. Part bottles are weighed and converted, never eyeballed to a tenth, because a bar carrying sixty open bottles and estimating each to the nearest tenth carries several litres of pure guesswork into the result. Record the weighing method, the tare weight used for each bottle type, and the date the scale was last checked.

3. **Build theoretical usage from the till and the specs, and build it before you look at the count.** Every sale line multiplied by its specified serve gives the volume that should have left the shelf. Doing this first matters, because a theoretical figure calculated after the count is a figure quietly adjusted to reduce an awkward gap, and nobody ever notices themselves doing it. Any till product with no spec, or a spec that has changed mid-window, is recorded as unmeasurable rather than assumed.

4. **Compute the gross profit rate the way HMRC computes it, because that is the number that will be looked at.** HMRC's Enquiry Manual states the formula plainly: "GPR = gross profit (sales - cost of good sold) divided by turnover x 100". It also records why the figure attracts attention, noting that "an unexpected or abnormally low rate of gross profit may raise legitimate doubts". Compute it by category, not just for the bar as a whole, because a blended rate hides the category that moved. Show the sales, the cost of goods sold and the turnover you used, so anyone can reproduce it.

5. **Decompose the variance instead of reporting one number.** Three effects, computed separately. Volume effect: the change in units sold at the old cost. Price effect: the change in cost per unit at the new volume. Mix effect: the shift between products at different margins. A gross profit rate that falls two points because the bar sold more draught and less spirit has not lost anything, and reporting that as a gap is how a manager gets investigated for a change in customer behaviour. Show all three with their arithmetic and confirm they reconcile to the total variance.

6. **Name every legitimate cause before anything is called a leak.** HMRC's own worked gross profit model does exactly this, identifying factors including "change in product mix", "pilfering and burglary (shrinkage)" and "wastage" as explanations to be tested rather than assumed away. Add the ones specific to a bar: staff drinks, comps and management discretion, promotional pricing, breakage, line cleaning and quality dumps, over-pour, returned drinks, and till errors including the wrong button and the unrung round. Every one of these needs its own record with a date and a name, and a cause with no record is not a cause, it is a hope.

7. **Record own goods and staff drinks as a ledger line, because the tax treatment depends on it.** HMRC's manual on own goods adjustments explains that "Where goods are taken from business stock, the value of the goods at their full retail price can be reflected in the total sales/business income (turnover box)", and that "Goods taken for personal use (apart from those shown in disallowable expenses) that increase profit can be shown in the tax adjustment to net profit or loss section of the return". A bar that gives away drinks and records nothing has a stock variance it cannot explain and a return its accountant cannot support, which is two problems from one missing notebook.

8. **Check that a promotion is explaining the gap and that the promotion is itself lawful.** A discounted or free drink is a perfectly good explanation for missing stock and a perfectly bad one if nobody wrote it down. Reconcile every promotion to a till record with dates and volumes. Then check the promotion itself against the mandatory licensing conditions, which name as irresponsible promotions the "provision of unlimited or unspecified quantities of alcohol free or for a fixed or discounted fee to the public or to a group defined by a particular characteristic in a manner which carries a significant risk of undermining a licensing objective", and the "provision of free or discounted alcohol or any other thing as a prize to encourage or reward the purchase and consumption of alcohol over a period of 24 hours or less" on the same test.

9. **Keep the paperwork, because HMRC's model runs on it and so does the next stocktake.** GOV.UK's guidance on business records requires a self-employed person to keep records "for at least 5 years after the 31 January submission deadline of the relevant tax year", and lists what they cover, including "all receipts for goods and stock", "sales invoices, till rolls and bank slips" and, under traditional accounting, "the value of stock and work in progress at the end of your accounting period". Keep the count sheets with the counter's name, the weighing records, the till export, the promotion log, the staff drinks log and the variance file itself. Date the file and list, explicitly, every figure that could not be obtained.

## Then it checks

1. The count window names a start and an end date and time, the counter is named at both ends, and every delivery in the window is allocated to exactly one period.
2. Every product is counted in the unit the till sells it in, every part bottle is weighed rather than estimated, and the tare weights and the scale check date are recorded.
3. Theoretical usage is built from the till and the specs, the build date of the theoretical figure is earlier than the date the count was compared to it, and every till product with no spec is listed as unmeasurable.
4. The gross profit rate is computed by category with sales, cost of goods sold and turnover shown, and the arithmetic reproduces from the figures in the file.
5. The volume, price and mix effects are each computed and shown, and they reconcile to the total variance with no unexplained residual.
6. Every explained portion of the gap names a dated record and a person, no portion is explained by a cause with no record, and the remainder is reported as unexplained rather than allocated.

Any check fails: name it, redo that step once. Failed twice: say what is wrong and stop.

## Rules
- Public information only.
- Never invent a fact, a number or a quote.
- Anything sent in someone's name says whose name it is. A count sheet goes out under the name of the person who counted it.
- Never name an individual as the cause of a variance, and never write the words theft, stealing or dishonesty. A stock gap is a number. Who caused it is a question for the owner and, where it goes further, for their HR adviser or the police, and a document that reaches that conclusion on its own will be read by people it was never meant for.
- Never state an industry average gross profit, a standard bar wastage percentage, a benchmark yield or a typical variance. No such figure exists for a single independent UK venue, and an owner who sets a target from one will investigate a bar that was performing and ignore one that was not.
- Never allocate an unexplained portion of the gap to a plausible cause. Unexplained is a finding and it is the most useful line in the report.
- Never build the theoretical figure after seeing the count, and never revise a spec to close a gap. If a spec changed mid-window, the product is unmeasurable for that window and says so.
- Refuse to treat an undocumented promotion, comp or staff drink as an explanation. Where a promotion is documented, check it against the irresponsible promotions list as well, and send anything matching to the owner's licensing officer rather than quietly accepting it as a cause.
- This output is a working document prepared for the owner to check against their own invoices, till data and records, and for their accountant to check before it informs a return, their licensing officer to check where it touches a promotion, and their HR adviser or solicitor to advise on before any action is taken about a person. It applies published rules and the owner's own figures. It is not tax advice, it is not legal advice, it is not employment advice and it does not establish that anything was taken.

## Built from
- HM Revenue and Customs, Enquiry Manual EM3082, "Examining Accounts: Business Ratios: Gross Profit Rate", https://www.gov.uk/hmrc-internal-manuals/enquiry-manual/em3082, published 12 April 2016 and updated 25 August 2026, read 16 September 2026: the gross profit rate formula and the statement that an abnormally low rate may raise legitimate doubts, both in step 4.
- HM Revenue and Customs, Enquiry Manual EM3508, "Recalculating Profits: Business Models: Gross Profit Rate Model - Example", https://www.gov.uk/hmrc-internal-manuals/enquiry-manual/em3508, published 12 April 2016 and updated 25 August 2026, read 16 September 2026: the worked model and its named explanatory factors, change in product mix, pilfering and burglary described as shrinkage, and wastage, which is the backbone of step 6.
- HM Revenue and Customs, Enquiry Manual EM3510, "Recalculating Profits: Business Models: Own Goods Adjustments", https://www.gov.uk/hmrc-internal-manuals/enquiry-manual/em3510, published 12 April 2016 and updated 25 August 2026, read 16 September 2026: the treatment of goods taken from business stock at full retail price and of goods taken for personal use, both in step 7.
- GOV.UK, "Business records if you're self-employed", https://www.gov.uk/self-employed-records/what-records-to-keep and https://www.gov.uk/self-employed-records/how-long-to-keep-your-records, no publication date shown on the pages, read 16 September 2026: the list of records including receipts for goods and stock, sales invoices, till rolls and bank slips, and the value of stock at the end of the accounting period, together with the five year retention rule, all in step 9.
- The Licensing Act 2003 (Mandatory Licensing Conditions) (Amendment) Order 2014, Schedule, paragraph 1, https://www.legislation.gov.uk/uksi/2014/2440/made, made 11 September 2014 and in force 1 October 2014, read 16 September 2026: the irresponsible promotions that step 8 tests a documented promotion against before accepting it as an explanation.
- The Weights and Measures (Intoxicating Liquor) Order 1988, articles 3 and 5A, https://www.legislation.gov.uk/uksi/1988/2039/article/3 and https://www.legislation.gov.uk/uksi/1988/2039/article/5A, the revised versions as they now stand, read 16 September 2026: the fixed serve sizes that make the theoretical usage calculation in step 3 possible at all, since a legally fixed measure is the only serve a theoretical figure can be built on.

# stock-take-bar

## You are given
The opening and closing count sheets, each with the date, the time, the counter's name, and every product recorded in the unit the till sells it in. For every part bottle, the gross weight in grams, the tare weight for that bottle type, the density figure used, and the date the scale was last checked. Every purchase invoice covering the window, with invoice date, product, pack size, unit cost excluding VAT and any delivery charge. The till export for the window, line by line. The serve specification for every till product, with the date each spec was set and a note of any spec that changed inside the window. The theoretical usage figure already built by Claude from the till and the specs, with the timestamp showing it was built before the count comparison. The delivery log showing which period each delivery belongs to. The staff drinks log, the comps and management discretion log, the breakage log, the line cleaning and quality dump log, and the promotion log, each entry carrying a date, a product, a quantity and a name. The prior period's figures. The verbatim extract of the irresponsible promotions list, with the date it was read.

## Produce
Write into a `./stock-take-bar-output/` folder:

1. `counts-and-usage.csv` with these columns in this order: `product_ref`, `product_name_verbatim`, `category`, `till_unit`, `opening_units`, `opening_method`, `closing_units`, `closing_method`, `tare_weight_g`, `density_used`, `scale_checked_date`, `counter_name_opening`, `counter_name_closing`, `purchases_units`, `actual_usage_units`, `theoretical_usage_units`, `theoretical_build_timestamp`, `gap_units`, `unit_cost`, `gap_value_gbp`, `spec_present`, `spec_changed_in_window`. `product_ref` is S001 upward. `opening_method` and `closing_method` are exactly one of `full units counted`, `weighed`, `estimated`, and every row with either set to `estimated` is listed in `gaps.md`. `spec_present` and `spec_changed_in_window` are `yes` or `no`, and any row where `spec_present` is `no` or `spec_changed_in_window` is `yes` has empty theoretical and gap columns and is listed in `gaps.md` as unmeasurable.
2. `gross-profit.csv` with columns: `category`, `sales_ex_vat`, `opening_stock_value`, `purchases_value`, `closing_stock_value`, `cost_of_goods_sold`, `gross_profit`, `turnover`, `gross_profit_rate_percent`, `prior_period_rate_percent`, `movement_points`. One row per category plus a `TOTAL` row. `gross_profit_rate_percent` is gross profit divided by turnover multiplied by 100, to two decimal places.
3. `variance-decomposition.csv` with columns: `category`, `total_variance_gbp`, `volume_effect_gbp`, `price_effect_gbp`, `mix_effect_gbp`, `effects_sum_gbp`, `reconciles`, `arithmetic_shown`. `reconciles` is `yes` only where `effects_sum_gbp` equals `total_variance_gbp` to the penny, otherwise `no`, and every `no` is listed in `gaps.md`. `arithmetic_shown` holds the inputs used for each effect.
4. `explained-gap.csv` with columns: `product_ref`, `cause`, `units`, `value_gbp`, `record_reference`, `record_date`, `recorded_by`. `cause` is exactly one of `staff drinks`, `comps and management discretion`, `promotional pricing`, `breakage`, `line cleaning and quality dumps`, `over-pour`, `returned drinks`, `till error`, `change in product mix`, `unexplained`. Every row other than `unexplained` carries a non-empty `record_reference`, `record_date` and `recorded_by`, and any row that does not is rewritten as `unexplained` and listed in `gaps.md`. Per product the units must sum to `gap_units`.
5. `own-goods.csv` with columns: `date`, `product_ref`, `units`, `retail_value_gbp`, `cost_value_gbp`, `taken_by`, `treatment_noted_by_owner`. One row per staff drink or own goods entry supplied. `treatment_noted_by_owner` records verbatim what the owner said about how it is treated, or is left empty and listed in `gaps.md`.
6. `promotions.csv` with columns: `promotion_ref`, `promotion_text_verbatim`, `start_date`, `end_date`, `till_record_present`, `units_attributed`, `matches_listed_activity`, `which_listed_activity`. `till_record_present` is `yes` or `no`, and any `no` means the promotion may not appear as a cause in `explained-gap.csv`. `matches_listed_activity` is `yes`, `no` or `cannot tell from the text`.
7. `gaps.md` is a numbered list of: every estimated count; every product with no spec or a spec changed in the window; every delivery not allocated to exactly one period; every variance decomposition that does not reconcile; every cause rewritten as unexplained for want of a record; the total unexplained gap in units and in pounds by category; every own goods row with no owner treatment noted; every promotion with no till record; every promotion matching a listed activity; and every figure that could not be obtained, named individually.

## Rules
- Codex counts, computes, decomposes and records. It never decides a cause, never names a person as responsible for a gap, never revises a specification, and never rebuilds the theoretical figure.
- Never write the words theft, stealing or dishonesty, or the name of an individual in any field describing a cause. `recorded_by` and `taken_by` record who made an entry in a supplied log, and nothing draws a conclusion about a person.
- Never allocate an unexplained portion of the gap to a named cause. Where a cause has no dated record and a name, the row becomes `unexplained` and the fact goes into `gaps.md`.
- Never compute or recompute theoretical usage. It is supplied with a timestamp, and where that timestamp is later than the count comparison or is missing, the run is halted and the reason reported.
- Never estimate a part bottle. A count supplied as an estimate is recorded as `estimated`, used and flagged, never silently converted into a weighed figure.
- Never write an industry average gross profit, a standard bar wastage percentage, a benchmark yield or a typical variance. No such figure exists for a single independent UK venue and none is to appear in any file.
- Never accept an undocumented promotion, comp or staff drink as an explanation for a gap.
- Never adjust a figure to make a decomposition reconcile. Where the three effects do not sum to the total variance, all four numbers are written as computed and the row is flagged.
- Never file anything with HMRC, never contact an accountant, a licensing authority or a member of staff, and never submit anything anywhere.
- All money is in GBP and rounded to the penny, volumes are in millilitres or pints as `till_unit` says, weights in grams, and every computed figure shows the inputs it came from in the same row.
- Use British English, GBP and DD Month YYYY dates. No em dash characters in any file you write, and any supplied text containing one is recorded verbatim and flagged in `gaps.md`.
- Every file ends with this line: this is a working document prepared for the owner to check against their own invoices, till data and records, and for their accountant, licensing officer or HR adviser to check. It applies supplied rules to supplied figures, does not certify anything and does not establish that anything was taken.

## Return
The absolute path of each file written, the row count of each CSV, the count window with both dates, times and counter names, how many products were counted and how many estimated rather than weighed, the products marked unmeasurable and why, the gross profit rate by category with the prior period rate and the movement in points, the three variance effects by category and whether each reconciles, the total gap in units and in pounds, the explained gap by cause with the record reference behind each, the total unexplained gap in units and in pounds, the own goods total at retail and at cost, every promotion with no till record and every promotion matching a listed activity quoted verbatim, the figures that could not be obtained, and the `gaps.md` item count.