Jump to content
Mr.Beer Community

Recommended Posts

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.

  • Like 7

Share this post


Link to post
Share on other sites

I have a box containing loose papers for most of my brews the past 5 years. 

 

I use the invoices from MrB orders to note the OG/FG, sometimes with actual brewing and bottling dates!  I use the Brewers Best instructions for the same.  All are tossed in an old beer box under my kitchen cabinet.

 

 

Seek help Rick.........

  • Like 2

Share this post


Link to post
Share on other sites

I have a box containing loose papers for most of my brews the past 5 years. 

 

I use the invoices from MrB orders to note the OG/FG, sometimes with actual brewing and bottling dates!  I use the Brewers Best instructions for the same.  All are tossed in an old beer box under my kitchen cabinet.

 

 

Seek help Rick.........

I also have a stack of loose papers.  It is growing exponentially lately because I add about 3-4 recipes to it a week.  

 

I daydream about beer recipes and am constantly thinking of new ones.  And I'm not talking about vanilla stouts with orange peel and cinnamon... I'm talking about IPAs with mango that uses 4 different types of hops, what the hop schedule would be, the color, the bitterness, how much mango, FG, carbonation, should I go traditional IPA or new age IPL, and finally, is there a beer out there like this yet that I can taste to get some comparison...  That's 20 minutes on a Tuesday.

 

-edit-

I definitely need to adopt at least the calendar portion.  My OGs are all over the place now and I can just say a beer will be ready in 4 weeks after bottling arbitrarily anymore.

Share this post


Link to post
Share on other sites

 

I'm talking about IPAs with mango that uses 4 different types of hops,

You get real good Mango from Falconer's Flight hops in the flavor and aroma boils

  • Like 2

Share this post


Link to post
Share on other sites

You get real good Mango from Falconer's Flight hops in the flavor and aroma boils

Kewl.  I'm using 1oz in a dry hop for my IIBA next week.

 

But that's good to know for my mango IPA/IPL (undecided).  What is more noticeable? Flavor or aroma?  Well, I guess I could do a 20 and a 7 with the same hop (I've been utilizing different hops to do different things lately)

 

Gah!   Too many options!

Share this post


Link to post
Share on other sites

I just started writing in a note book certain things.  I usually put the date I can taste/bottle the beer and then when I can refrigerate, but with the addition of a second keg it's getting difficult, almost bottled the wrong beer the other day.  Thanks for the advice  

  • Like 1

Share this post


Link to post
Share on other sites

Kewl.  I'm using 1oz in a dry hop for my IIBA next week.

 

But that's good to know for my mango IPA/IPL (undecided).  What is more noticeable? Flavor or aroma?  Well, I guess I could do a 20 and a 7 with the same hop (I've been utilizing different hops to do different things lately)

 

Gah!   Too many options!

I tend to do an even split for flavor and aroma. as smell is more than half your sense of taste

  • Like 1

Share this post


Link to post
Share on other sites

I've been doing both but I'll use one for flavor like Zythos for 20 minutes and Cascade for aroma at 7 minutes.

 

It's crazy how reversing those could create very different beers.

Share this post


Link to post
Share on other sites

Guys, how about moving this conversation elsewhere.  This thread is about keeping track of things, not hop additions.

Share this post


Link to post
Share on other sites

Record keeping is key. I use brew pal to track all recipies and notes. It keeps track of ferment dates, mash schedule, water chemistry, etc.

I do wish I had kept a better record of tasting notes over the years. It's hard to remember how a beer tastes after 5 gallons have come and gone...

  • Like 1

Share this post


Link to post
Share on other sites

For Jim... because I told I think of nothing but recipes all day...

 

 

Mango Pale Lager
Mango Pale Lager

RecipeMango Pale LagerStyleAmerican IPA
BrewerVakkoBatch2.13 gal
Extract

Recipe Characteristics

Recipe Gravity1.072 OGEstimated FG1.018 FG
Recipe Bitterness53 IBUAlcohol by Volume7.0%
Recipe Color7° SRMAlcohol by Weight5.5%

Ingredients

QuantityGrainTypeUse
0.55 lbMr. Beer BrewMax Golden - LMEExtractExtract
2.86 lbMr. Beer Northwest Pale Ale - HMEExtractExtract
1.88 lbOregon Fruit - MangoAdjunctOther
QuantityHopTypeTime
0.25 ozFalconer's Flight - Proprietary blend of the Northwest's most unique hop varietiesPellet7 minutes
0.25 ozFalconer's Flight - Proprietary blend of the Northwest's most unique hop varietiesPellet20 minutes
0.50 ozFalconer's Flight - Proprietary blend of the Northwest's most unique hop varietiesPellet0 minutes
1.00 ozMr. Beer Northwest Pale AlePellet5 minutes
QuantityMiscNotes
1.00 unitSaflager S-23 Dry Lager YeastYeastTemperature Range: 51°-59°(optimal), up to 75°F 11.5 GRAMS

Recipe Notes

2 can mangos after 7 days of fermentation
Dry hop the 0.5oz Falconers Flight after 14 days
Add 2oz mango extract at bottling
Condition eight (5) weeks

Batch Notes

Share this post


Link to post
Share on other sites

now coming from me, if you knew me, you would find this extremely funny but...  rick... that's just too obsessive for me.  I started taking notes but it became too much of a chore. when a hobby starts to become a chore it becomes un-fun so aside from a printed recipe and a couple scribbled notes, I don't worry about all that.  when I'm ready to brew I order what I need. if I'm winging it I just go through my 'stash' and add what I think will come out good.  maybe it's because I seldom brew the same recipe more than once because there's just so many styles and things to try that I can get away without meticulous note taking. 

Share this post


Link to post
Share on other sites

Currently i'm just using the sticky note and calender.  sticky note placed on the LBK with what is brewing.  Entry placed on calender with brew date and go 3 weeks out to alert for bottling.  then 4 weeks out for drinking.  sticking note goes from LBK to case of bottled beer.  write what the beer is on the bottle cap, since i don't want to keep taking off labels.

Share this post


Link to post
Share on other sites

RickBeer, When do you have time to brew beer if you spend all your time doing a spreadsheet?  

Share this post


Link to post
Share on other sites

The spreadsheet has evolved, and never takes more than a few minutes a week.  Adding a new batch is easy, just copying cells.  When we drink some, lower some numbers and increase others.  Quite easy, actually.  

Share this post


Link to post
Share on other sites

Also, for you iphone users out there...there is an app called "Home Brew Journal". Check it out, little easier than making your own spreadsheet. Plus it's free...but has in app purchases. IMO, a better app though is "BeerAlchemy Touch 2"...the price is high for an app but it seems to be well written and exactly what a homebrewer would use to track his batches.

Share this post


Link to post
Share on other sites

Rick - love the idea of keeping track of the 'last' of a batch. I bet it's great trying a couple of the same recipe from different batches spread out over the course of a year. I might have to adopt this one. Thanks for sharing

Share this post


Link to post
Share on other sites

I use a combination of QBrew and my calendar app on my iPod to keep track of dates, ingredients, and additional notes that I feel should be included. That way it is in my pocket at all times, besides the QBrew printouts which I keep in a folder in my (beer) office. I keep all my materials and ingrediants in the closet of that room, but it barely holds it all, even though it's a big closet. My brewing takes place in the basement or a spare bathroom depending on temperatures. I can see where a simple spreadsheet would come in handy, especially once my brewe grow more complex and if I pick up additional LBKs. So far, though, my system is working for my simpleton ways.

Share this post


Link to post
Share on other sites

Rick, are you retired?  Just wondering how you have TIME to do all this data input?  Thanks

Share this post


Link to post
Share on other sites

Time? When I brew a 5 gallon batch, it's 4.5 hours including cleanup. Bottling is 3 hours. Takes me 15 minutes to update my spreadsheet.

Share this post


Link to post
Share on other sites

I used to do that:  keeping spreadsheets, notebooks, good notes, etc.  Now I have very little time and am lucky if I get the bottle caps numbered for reference.  I guess the good thing is that I have stumbled across a lot of good house recipes that use a lot of the house Cascade and Nugget I have, so now that I have been brewing some things over and over, notes do not mean as much as before now that I have the routine down.

  • Like 1

Share this post


Link to post
Share on other sites

Time? When I brew a 5 gallon batch, it's 4.5 hlurs including cleanup. Bottling is 3 hours. Takes me 15 minutes to update my spreadsheet.

I make 10 gallons of MrB in 4 LBK's in about 2 hours...

  • Like 1

Share this post


Link to post
Share on other sites

You get real good Mango from Falconer's Flight hops in the flavor and aroma boils

 

I like the 7c's variety as well.  Both a nice tropical/floral kind of IPA.  Hmm...  I need to finally get brewing, and I seem to remember that I have a gift certificate (provided by some great folks not too long ago).  I'm thinking a FF IPA is on the docket...

  • Like 1

Share this post


Link to post
Share on other sites

I tend to do an even split for flavor and aroma. as smell is more than half your sense of taste

 

Agreed.  I've really taken to back loading the aroma hops.  Makes a big difference in the IPAs, IMHO.  Gotta get that flavor/aroma punch even more than that huge bitter, IMHO.

  • Like 2

Share this post


Link to post
Share on other sites

Haven't had a chance at 7c's. My lhbs don't carry it. I'm already buying Cluster, Falconer's Flight, and Belma by the lb.. Kind of hard to justify ordering an oz. "just for the hell of it". SWMBO's been patient with ordering the 3 lbs I think I'd be pushing my luck. Know what I mean(Vern)?

Share this post


Link to post
Share on other sites

Haven't had a chance at 7c's. My lhbs don't carry it. I'm already buying Cluster, Falconer's Flight, and Belma by the lb.. Kind of hard to justify ordering an oz. "just for the hell of it". SWMBO's been patient with ordering the 3 lbs I think I'd be pushing my luck. Know what I mean(Vern)?

 

I want to try Belma at some point.  And I need to get some bulk ordering again.  I love getting something like "per-lay" in bulk, because it's super-cheap and I tend to use it quite often as my bitter (not only because Yazoo does, but I must admit that that's why I started using it initially).

Share this post


Link to post
Share on other sites

I want to try Belma at some point.  And I need to get some bulk ordering again.  I love getting something like "per-lay" in bulk, because it's super-cheap and I tend to use it quite often as my bitter (not only because Yazoo does, but I must admit that that's why I started using it initially).

FWIW not as pineapple/strawberry/melon as I hoped it would be, but still it works well in a wheat

Share this post


Link to post
Share on other sites

Jim, I guess you weren't there the first night...  I guess the joke may be lost on you....  :)

Share this post


Link to post
Share on other sites

Yeah I missed a lot, like the whole "toc click click" thing...(Beer Lord explained that one to me)  :D

Share this post


Link to post
Share on other sites

My LHBS has Zythos and I really liked it as a flameout in Turncoat IPL.

Share this post


Link to post
Share on other sites

We had a long, in depth discussion about the pronunciation of 'Pearle' hops.  Brewmaster insisted 'per-lay'...  we thought 'pearl'.  Beer guy at a different brewery agreed with us.  Upon researching...  both are acceptable.  ;)

Share this post


Link to post
Share on other sites

My LHBS has Zythos and I really liked it as a flameout in Turncoat IPL.

 

Zythos is quite nice as well.  So many hops, so little time.  I usually fall back to the 'C's,' as well as Simcoe and Amarillo for my pales and IPAs.  I need to branch out...

Share this post


Link to post
Share on other sites

Ah actually I would've been with y'all on that. After all it's spelled pearl(e), silent e and all that. (never understood the point of a silent letter)

Share this post


Link to post
Share on other sites

Ah actually I would've been with y'all on that. After all it's spelled pearl(e), silent e and all that. (never understood the point of a silent letter)

What about a second letter?  ie Jeff

Share this post


Link to post
Share on other sites

And glacier...

 

Never used Glacier.  What kind of flavor profile does it give you?

Share this post


Link to post
Share on other sites

Jim, the new Black Belle that we tasted was bottled and is now released.  I need to get a bottle...  Inky opened the one from last year just this past weekend.  Love that beer...

  • Like 1

Share this post


Link to post
Share on other sites

We had a long, in depth discussion about the pronunciation of 'Pearle' hops.  Brewmaster insisted 'per-lay'...  we thought 'pearl'.  Beer guy at a different brewery agreed with us.  Upon researching...  both are acceptable.   ;)

 

That reminds me of the age-old controversy over the pronunciation of Willamette (rhymes with "Dammit".)

Share this post


Link to post
Share on other sites

because I have a terrible memory, and brewing spans weeks, *and* I'm working on getting multiple brews going at once (someone said pipelining was a good thing?), I feel the need to organize as well.  I am a pretty big fan of excel, but, have you heard of OneNote?  ooooooohhhhhhhh aaaaaahhhhhh... I can put in notes, copy links, drop in photos, and it'll share across any M$ program, if you want to calendar events in outlook for example.  

 

I copy a lot of links from this site and others, so I can quick reference them instead of trying to remember how i searched last time, or review requirements for a recipe or equipment.  OneNote has a pretty straight forward tab system and sub page system to allow me to inventory my equipment, add future ideas/recipes, and track brews either done or ongoing.  

 

I have OneNote at work, on my iphone, and at home, and the program syncs across my skydrive (hotmail account) so any added info from any location is always updated.  Built in search function on it, so when it gains in size I can still find old brews or old notes.  

Share this post


Link to post
Share on other sites

SWMBO got that case of pumpkin ale from them and I ended up drinking most of 'em. and wondering why, oh why she didn't like the APA instead. :)

 

 

 

I'm digging that Blackstone got recognized in the Men's Journal best beers in America list...  linky

 

I still have half or more of my case of the DIPA left.

Share this post


Link to post
Share on other sites

There's no doubt they make some good brews...I liked that barrel aged one, but it'll never make it this far south. I got like 4 of the pumpkin left, now I'm curious about how long they'll remain good @ room temp. Figure to "pop a top" every 2 months(or till it gets nasty, which ever comes first)

Share this post


Link to post
Share on other sites

because I have a terrible memory, and brewing spans weeks, *and* I'm working on getting multiple brews going at once (someone said pipelining was a good thing?), I feel the need to organize as well.  I am a pretty big fan of excel, but, have you heard of OneNote?  ooooooohhhhhhhh aaaaaahhhhhh... I can put in notes, copy links, drop in photos, and it'll share across any M$ program, if you want to calendar events in outlook for example.  

 

I copy a lot of links from this site and others, so I can quick reference them instead of trying to remember how i searched last time, or review requirements for a recipe or equipment.  OneNote has a pretty straight forward tab system and sub page system to allow me to inventory my equipment, add future ideas/recipes, and track brews either done or ongoing.  

 

I have OneNote at work, on my iphone, and at home, and the program syncs across my skydrive (hotmail account) so any added info from any location is always updated.  Built in search function on it, so when it gains in size I can still find old brews or old notes.  

I have a "white board" * I keep a list of what's fermenting hung on the door of the fermentation chamber(a refrigerator). I list the fermenter #, what's in it, and the date it was brewed. I also bottle 2 days before brewing day so if I'm busy on Thursday I can bottle on Friday, and still brew on Saturday. A lot of what your forgetting now, will become second nature after you have enough experience.

 

*

Terminology question: white board is what y'all call the erasable marker boards, right? (yes, I'm that old)

Share this post


Link to post
Share on other sites

Terminology question: white board is what y'all call the erasable marker boards, right? (yes, I'm that old)

 

Yep.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...