Brewery Inventory Spreadsheet: Malt and Hops to Kegs

A brewery inventory spreadsheet keeps four kinds of stock: ingredients and packaging, beer in tanks, packaged beer, and the kegs themselves, some of which are sitting in other people's cellars. Each needs its own sheet, because each is counted differently: malt by the pound and the lot, beer in tanks by the barrel at what its ingredients cost, packaged beer by the keg and case converted back to barrels, and kegs by where they are. In the example below, a small brewery has $3,278.50 of malt, hops, yeast and packaging, 21 barrels in tanks with $2,110 of ingredients in them, 10.96 barrels of packaged beer worth $1,654.60 at cost, and two kegs out long enough to chase. Below is each sheet, the cost a barrel, kegs, what this sheet isn't, and a free template.

The short version

• Count in barrels: a barrel is 31 US gallons, a half-barrel keg 15.5, a case of 24 16-ounce cans 3.
• Keep ingredients by lot, with the hop harvest year and alpha acid, against a par level.
• Record each batch's barrels brewed and ingredient cost; allow for loss to packaging.
• Cost a packaged barrel, then a keg and a case, cans and carriers included.
• Track every keg: where it is, and how long it's been at an account.
• This is a stock and cost sheet, not a TTB report.
• TaroStack keeps ingredients by lot and costs every batch. More below.

Ingredients and packaging

Ridgeline Hollow Brewing, a made-up craft brewery in Fayetteville, West Virginia, I use in these examples, counts its ingredients and packaging at month end:

The order column fills each item back to par in whole bags, boxes or cases. Hops lose aroma and bitterness with age and warmth, so each lot carries its harvest year and alpha acid, and the oldest are used first. The lot on every row is what lets you say which batches a supplier's recalled lot went into; how lot records work is in lot tracking spreadsheet. How to set a par is in how much inventory to keep on hand.

Beer in tanks

A 7-barrel batch doesn't package 7 barrels: yeast, trub, hops and transfers take some, more in a heavily dry-hopped IPA. The sheet takes the loss you expect and spreads the ingredient cost over the barrels that will be packaged, so the IPA's cost a packaged barrel is $147.73, not $130. Beer in tanks is work in process, valued at what went into it; how that's counted is in work in process inventory formula.

Packaged beer

Federal rules define a barrel, as a unit of measure, as 31 US gallons (27 CFR 25.11), so a half-barrel keg is 15.5 gallons, a sixth 5.17, and a case of 24 16-ounce cans 3 gallons, 0.0968 of a barrel. A keg's cost is its beer; a case adds 24 cans and 6 carriers, $8.28. Converting everything back to barrels lets you compare what you brewed with what you packaged and sold. How finished stock is valued is in finished goods inventory formula.

Kegs

A keg is equipment that leaves the building full and has to come back. The sheet works out the days each keg has been out as of the date you set and flags any past 30, so the delivery driver asks for K-01 and K-05 back on the next run. A brewery with few kegs that loses track of several has to buy more or turn down orders.

What this sheet isn't

A brewery files a Brewer's Report of Operations with TTB, monthly, or quarterly for a brewer liable for no more than $50,000 in beer taxes the year before and expecting the same this year (27 CFR 25.297), and pays excise tax on beer it removes. This sheet is for knowing your stock and its cost; it isn't that report and doesn't replace the records a brewery must keep. This is general information, not legal or tax advice.

Get the template

Download the brewery inventory spreadsheet. Four sheets: ingredients and packaging by lot against par, with what to order and the value; batches in tanks, with the cost a barrel brewed and packaged; packaged beer in barrels, with the cost of each keg and case; and kegs, with the days out and the ones to chase. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.

Where this stops working

Ten recipes, hops from three suppliers in lots with their own alpha acids, malt bought by the pallet, beer moving from tanks to kegs and cans, and accounts ordering every week. A sheet can't take the malt off stock when a batch is brewed, say which accounts got beer from a recalled hop lot, or show what a barrel of IPA cost in the last batch.

How TaroStack does it

TaroStack keeps ingredients by lot and costs every batch. Plainly: it doesn't produce the TTB Brewer's Report of Operations or the excise tax return, and it isn't built around tank transfers; a brewery that wants those reports filled in should look at a brewery system, compared in brewery inventory management software.

Ingredients by lot. Malt, hops, cans and carriers are received by supplier lot, at what you paid, and units convert: malt bought by the bag, used by the pound.

Batches at what they used. Each beer is a recipe with versions. Recording a batch takes each ingredient from its lot at its cost, notes the lots automatically, and divides by what was packaged, so the cost of a barrel, keg and case comes from the batch. One report walks a hop lot to every batch it went into and every account that got the beer.

And the accounts. Account orders are sales orders that show whether the kegs and cases to fill them are in the cold room while you're still typing, counts work on a phone with no signal, and margins are reported by product and by customer.

Recipes, batches, lots, recalls and margins by product and customer are on every plan, from $49 a month. The first 30 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.

Questions people also ask

How do you track brewery inventory?

Keep four lists: ingredients and packaging by lot, beer in tanks by batch, packaged beer by keg and case converted to barrels, and kegs by where they are. Count at month end and value each at cost.

How many gallons are in a barrel of beer?

Federal rules define a barrel of beer as 31 US gallons, so a half-barrel keg holds 15.5 gallons and a sixth-barrel keg about 5.17.

How do you calculate cost per barrel of beer?

Divide the batch's ingredient cost by the barrels you expect to package after losses. In the example, a 7-barrel IPA with $910 of ingredients and 12% loss costs $147.73 a packaged barrel.

How do breweries keep track of kegs?

Give each keg a number and record where it is and the day it went to an account. The template flags kegs out more than 30 days so they can be collected.