On this page
Use a bar inventory template that converts sealed bottles and every open-bottle fraction into milliliters before comparing stock. Record one count line per product and location, then sum each open container’s fraction against that product’s bottle capacity. This makes the physical count comparable with expected volume from recorded receipts and usage.
Download the Excel workbook · Open bottles (CSV) · Example rows (CSV) · Stock movements (CSV) · Application field specification (JSON)
The sample is synthetic and independently checkable: two sealed 750 mL bottles plus open bottles at 0.50 and 0.25 equal 2,062.5 mL. The example’s expected volume is 2,300 mL, so observed minus expected is -237.5 mL. That variance identifies a reconciliation gap to investigate.
Set one count line for each product and location
Use a stable product_id for each beverage and a location_id for each storage area, such as the front bar, back bar, beer cooler, or storage room. A count session records when the count happened and who performed it. A count line links one product to one location in that session.
Store the bottle capacity in milliliters, the whole number of sealed bottles, and each open bottle’s remaining fraction separately. A fraction must be between 0 and 1 inclusive: 0 is empty and 1 is full. A value of 1.5 needs two physical containers, recorded separately as 1.0 and 0.5. Count a bottle as sealed only when its seal is intact. Keeping open bottles as separate rows makes invalid values and recounts easier to find.
Convert bottle fractions into milliliters

AI-generated editorial illustration.
Use the same formula for every product:
Count volume (mL) = (sealed bottles + sum of open-bottle fractions) × bottle capacity (mL)
For the synthetic 750 mL bottle, two sealed bottles plus fractions of 0.50 and 0.25 equal 2.75 bottle-equivalents. 2.75 × 750 mL = 2,062.5 mL. If the same product comes in a different bottle size, assign a separate product-size ID and use its own positive capacity; do not apply a single size across the category.
A template can sum fractions for reporting, but retain each individual fraction in the source rows. The sum does not show how many bottles were inspected or whether any fraction fell outside the valid range.
Compare observed stock with expected volume
Calculate expected stock from a documented opening count plus receipts and transfers in, minus recorded sales, waste, and transfers out, with adjustments tied to a source reference. Use signed milliliters for each movement: receipts are positive, depletion and waste are negative. Store an effective timestamp and source ID. For each product and location, use one opening count and movements after that opening through the physical-count cutoff; exclude later movements and never count the opening twice.
| Synthetic calculation | Volume |
|---|---|
| Opening stock | 3,000 mL |
| Recorded sales/depletion | −675 mL |
| Recorded waste | −25 mL |
| Expected volume | 2,300 mL |
| Physical count | 2,062.5 mL |
| Observed − expected | −237.5 mL |
Use variance = observed − expected. A negative value means the physical count is below the records’ expected volume; a positive value means it is above. First check the count, bottle size, location transfers, sales feed, waste entries, and timing. If a movement is missing or uncertain, leave the reconciliation unresolved and record what evidence is needed. A variance alone is not proof of theft, misuse, or intent.
Use the synthetic sample files
sample.csv shows the product-level count and expected-volume arithmetic. open_bottles.csv contains the two individual fractions, and stock_movements.csv contains the opening stock, sales/depletion, and waste used to calculate expected stock. Every row is invented for illustration.
For a spreadsheet count, protect the capacity and movement references from accidental edits, use data validation for whole sealed-bottle counts and fractions from 0 through 1, and keep one location per count line. At the end of the count, save the count date, staff member, and any unresolved movement reference with the session.
Move location counts into an app
Start with these CSVs in a spreadsheet and reconcile one location at a time. If the workflow later moves to an app, preserve stable product, location, session, line, and movement IDs so importing the files does not create duplicate records. Atoms supports Atoms Cloud or a connected Supabase project as backend options; its guide states they are alternatives for a single Atoms project. A database connection by itself does not implement inventory entry or persistence, so verify a saved count after reload when an app is actually built. Atoms Supabase connection guide
See Spreadsheet to web app for a one-time CSV migration path, or open the Atoms AI app builder to plan a dedicated workflow.
Build a location-based bar inventory app in Atoms
Copy this complete prompt into your Atoms project chat:
Copy the build brief and use it to create your version in Atoms.