Search the Community
Showing results for tags 'records'.
Found 1 result
As you get into this hobby, you become obsessed. Obsessed with making beer, obsessed with tasting beer, obsessed with keeping track of everything. You add bottles, you add LBKs, and pretty soon you don't know which end is up. I'm a very organized guy and wasn't going to let things get that crazy (yeah, right). I made a spreadsheet that is ever-evolving, and questions come up now and then that something like this can handle. While I'm not going to share the spreadsheet (I may make millions off it someday), I thought I'd share some of the things it does. Note - I am NOT a spreadsheet expert, this does nothing fancy. It just keeps things organized. I give the details below so that you can see what's possible, and say "yeah, I'm not doing that", so at some point when you do it you can laugh at yourself. FYI - I use a Google calendar for tracking the age of a beer, I put in when I brew it, an entry 18 days later to check FG, then I add an entry when I bottle it and entries for 4, 6, 8, 10, and 12 weeks. It's a totally separate calendar (you can have many in Google Calendars) that triggers me to remember things. The Excel workbook contains multiple sheets (this easily allows you to pull data from one sheet to another). Sheet One - Brewed Inventory It is divided into three sections - Drinkable, Undrinkable (also known as Conditioning), and Empty Bottle Inventory. Brewed Inventory includes lines for each batch, for example "Rick's Michigan Red". Within the batch it lists each bottle size I used, and calculates the total ounces. Example: Total Qty Size Total Oz Name 31 12 372.0 Rick's Michigan Red I 1 16.9 16.9 Rick's Michigan Red I (last) 4 16.0 64.0 Rick's Michigan Red I 4 33.8 135.2 Rick's Michigan Red I Total Oz is a calculated field. (last) indicates it's the bottle I hold to compare to future batches (and goes in a separate storage box) I do this for each brew, and on the bottom I total everything up in ounces, and then calculate gallons, 1/2 liter bottles 6 packs, and cases. This quickly shows me how much drinkable beer I have. Underneath that section I calculate the 12 oz equivalent I have of each brew (in this case it would be 49.01 bottles), and calculate that as a percent of the total (in this case 19.86%). I do this so I focus on drinking the beer I have the most of. I also have a box that shows the word REBREW when I hit 18 bottles (=IF((cell<=18),"REBREW",""). I also then show how much I have in fruit beers, Reds, Brown/Porter, Peanut Butter, and Other. And I calculate how many cases to make sure it matches the above number. You can see that summary if you click on the SHOW box in my signature. FYI, if you store beers at multiple locations (i.e. a summer cottage, a relative's house, etc.) you can divide the inventory into location columns and then add it together, this allows you to make sure you have a selection in each place. The middle section of this sheet is called Undrinkable and contains the exact same format as the Rick's Michigan Red section above. That adds up into the same ounces, gallons, 1/2 liter bottles, 6 packs and cases. Right now I have 8.23 cases of beer conditioning. Underneath that I have the number of bottle caps I have not yet used. I subtract the amount I use each batch AND note which batch, so I don't have to say "did I subtract the caps used in Oatmeal Stout"? I did this when I bought 2,000 caps on Ebay for a fraction of the cost at my LHBS (1.5 cents per cap versus 3.13 cents per cap). In a year I've used around 600 caps. The next section is my empty bottle inventory, and shows quantity, size, and ounces like the other sections. As I drink a beer, I deplete the first section by 1 and add 1 to the proper bottle size in this section. All bottles are listed as clean, or to be cleaned (bottles I get from friends for example). I calculate the number of clean cases to see if I can bottle the next batch, and I list underneath it bottles I've given as gifts that have a hope of coming back (versus ones I never expect to see again). On the sheet I have little sections of calculations including the amount of beer in any stage (gallons, 1/2 liters, ...) - that's now 18.51 cases. I also total bottles across all stages (882), by size, and have next to that the actual amount typed in, so if the calculation doesn't add up to the total number I know I've missed something somewhere. I do a physical inventory every few months to true up, usually off by a bottle or two. Sheet Two - Brewing History Much simpler sheet. For each batch I keep the name, the number (last was 69 and 70 - these are batches for LBKs, so a 5 gallon batch counts as 2), the date started, date cold crashed, date bottled, 28 days later (4 weeks conditioning) calories per bottle, OG, FG, temp for each, targeted ABV, actual ABV, cost per 12 pack (to compare to what I'd pay in the store), SRM and IBU, and a list of ingredients for the label. I add each batch as I go and then total the relevant numbers at the bottom. Sheet Three - Unbrewed Inventory Not really used anymore, had my Mr. Beer cans listed with expiration dates. Sheet Four - Recipes I list recipes to compare them, putting each grain in columns, so I can see how 5 stout recipes compare. That's it's only purpose, used infrequently. Sheet Five - Investment Although I track my total expenditures in Quicken, this sheet breaks things down into Equipment, Consumables, and Beer. Equipment is self-explanatory (under $200 so far), consumables includes sanitizer, bottle caps, etc. Beer includes ingredients. I list every one with it's cost, and put Unused next to it and then Used when I use it. This is where I can see my inventory of unused hops, yeast, etc. Sheet Six - Priming Simply lists the name of the batch and how many grams of sugar I used for that batch, and the warmest temp. I use 130 grams for most 5 gallon batches, going as low as 45 grams for a stout. Sheet Seven - To Buy Simply a list of what I want to remember to buy. Sheet Eight - Label Data I pull data from other sheets for my labels (see label pic below). It calculates 4 weeks, 6 weeks, 8 weeks, 10 weeks, and 12 weeks (although the label only prints 4, 8, and 12). I then close the spreadsheet and open a Word template that pulls in the data to make the label (see example here: http://community.mrbeer.com/topic/33277-new-brewer-questions-first-batch/#entry409276) So that's it. As you can see, the meat is on the first sheet, and some of the other sheets really don't get used that often. I sometimes forget to add things to the Investment sheet, but can always go back and do it. I put recipes in QBrew, so all that data is in there. I hope this gives others ideas for how they want to track their brewing obsession.