Tutorials

Bar Inventory Template: Bottles, Open Fractions and Variance

An editable bar count workbook with bottle fractions, signed milliliter movements and observed-versus-expected stock.

Start building for free
4 min readPublished
Illustration: Convert unopened bottles and each open-bottle fraction into the same milliliter base unit before comparing counts and usage.
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

Illustration: Convert unopened bottles and each open-bottle fraction into the same milliliter base unit before comparing counts and usage.

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:

text
Build a location-based bar inventory app for a bar manager. Use one persistent backend: Atoms Cloud or a connected Supabase project. Keep an editable CSV/spreadsheet count workflow available before the app is built.

Create stable-ID tables: products (product_id, name, category, bottle_capacity_ml, acquisition_cost_per_bottle); locations (location_id, name); count_sessions (session_id, location_id, counted_at, counted_by, status); count_lines (line_id, session_id, product_id, sealed_bottle_count, observed_volume_ml, expected_volume_ml, variance_ml); open_bottles (open_bottle_id, line_id, fraction_remaining); and stock_movements (movement_id, location_id, product_id, movement_type, volume_ml, effective_at, source_reference). Use a distinct product-size ID for each bottle capacity. Preserve each open bottle fraction as its own row. Each movement stores signed milliliters, its effective timestamp and source reference. For each product/location count line, sum one opening count plus movements after opening through counted_at; exclude later movements and prevent duplicate movement IDs.

Validate positive bottle capacity, whole nonnegative sealed-bottle counts, and every individual open fraction in the inclusive range 0 to 1. Calculate observed count mL as (sealed bottle count + sum of individual open fractions) multiplied by capacity mL. Calculate variance as observed minus expected. Derive expected volume from a documented opening count and recorded receipts, transfers, sales, waste, and adjustments. Show missing movement evidence as unresolved. A negative or positive variance is a reconciliation signal; never label it theft or infer intent.

Use only labeled synthetic data. Include one 750 mL product with two sealed bottles and open bottles at 0.50 and 0.25, so observed count is 2,062.5 mL. Include an opening volume of 3,000 mL, recorded depletion of 675 mL and waste of 25 mL, so expected volume is 2,300 mL and observed-minus-expected variance is -237.5 mL. Display the formula and source movements. Never add cost or variance as proof of loss or misconduct.

Define server-side roles for organization admin, location manager, and count staff. Enforce organization and assigned-location row access, permitted fields and exports on the server. Do not rely on hidden controls. Use stable IDs, unique constraints, deduplicated imports and immutable count/movement history. Do not assume a POS or accounting API. Save one harmless edit, reload, and verify persistence; test a separate role and report all uncompleted checks.

Copy the build brief and use it to create your version in Atoms.

Build a bar inventory app
Share this article
Made with Atoms

Your next idea starts here.

Turn what you learned into a working app or website.

Start building for free