Category Archives: Brew Day

Update: My Brew Day Spreadsheet in Excel, Open Office and Google Docs

The spreadsheet is setup from my perspective, as a batch sparger.  Having said that, almost everything applies equally to a fly sparger with the possible exceptions of the computed mash ratio (more below), planning on a set volume of sparge water and the step by step batch sparge directions.

Brew Day Sheet (click to enlarge):
img_brewday

 This is the main sheet where you will fill in information about your beer and your mash parameters.

Color Coding: Green Cells are information that you can fill in and Blue/Light Gray Cells are calculated.

Beer Info: Basic information about your beer and mash profile.

Mash Ratio Cells: Based on your beer parameters, the spreadsheet suggests a mash ratio (cell E12).  That ratio is designed to allow for no mash out water and should produce equal first and second runnings.  Should you want to override that, just fill in a mash ratio in the “Mash Ratio Manual” cell.

Strike Water Temp: Fill in the Beer Info section and your Grain’s current Temperature (cell B5) and the spreadsheet calculates your strike water temperature (cell B6).

Gravity: This is an adaption of Sean Terrill’s Refractometer Calculator (used by permission).  Thanks Sean for your excellent work on this!

Water Volume Summary: This double checks to make sure you’re mash parameters do not exceed the volume of water you practically have available.

Constants: The constants section has some variables that you can adjust based on your setup.  For example, I’ve found that grain absorption for my crush is right around .11 gallons/lb.  I think that will be close for you but you can tweak it here if you observe something different.

A note on “System Temp Adjustment”, Cell H7: this is the number of the degrees that my mash tun (a 10 gallon Rubbermaid Cooler) generally drops when I put hot water in it.  This gets added to the “Strike Temp” figure cell B6 so that I don’t have to preheat my mash tun to account for this loss.  If you want to figure this for your system, you can put in a typical amount of mash water at a typical (and known) mash temperature.  Wait a minute or two and measure the temperature.  Subtract that temperature from the start temperature and you should have a good value.  You can refine this over time based on your observations.  You can set this to 0 if you preheat your mash tun or if you have a direct fired mash tun.

I suggest reviewing the Constants section to make adjustments for your setup, paying special attention to the mash tun loss, mash tun size and system temp adjustment values.

Age: The age of your beer based on brew date.  Day, Month and Year are totals.  If you see 365 days, 12 months and 1 year, you’re beer is 1 year old.

Efficiency: This calculator is based on recipe or software efficiency.  I did that so I did not have to add up the potential fermentables for every batch.  I use Beersmith and have my efficiency set at 70%.  I figure efficiency when going from the mash tun to the boil kettle.

Conditional Formatting:  “Mash Out Water” turns red if there is any mash out water available.  I don’t usually do a mash out, so I want to know if any water is sitting here.  “Mash Ratio Effective” turns red if the computed value is overridden.  “Total Used Water” turns red if this value exceeds available water.  “Mash Volume – Can I mash it?” turns red if the projected volume of your mash exceeds the size of your mash tun.  This is an adaptation of the formula found on the Green Bay Rackers Calculators page.

Batch Sparge Directions: These are narrative step-by-step directions that you can follow after you enter data for your brew day.

Summary Sheet (click to enlarge):Homebrewing Spreadsheet

This is setup to print on a regular 8 1/2″ x 11″ sheet of paper and gives a rundown of essential brew day tasks and data.  I do not, generally, open up a spreadsheet on brew day.  I use a print out of this sheet.

You can enter information about your starter and other hop, adjunct and miscellaneous additions.

Prepare:  This is a simple to do checklist.  The water amount figured here is rounded up based on your beer parameters.

Water Volumes/Gravity:  This projects the volume and gravity you should have at three stages (start of boil, 15 minutes remaining and end of boil).  The last two columns (lb DME/pt and grams) are meant to allow you to easily correct your gravity at those stages.  Each of those amounts should add 1 gravity point to your beer.  Let’s say you’re three points down at the start of the boil.  With the example in this graphic, you would add .42 lbs of DME to correct the gravity of the 6 gallon batch.  Bam… that’s easy!

Hop/Adjunct Schedule: You can choose either grams or ounces.  If you choose ounces it will also be converted to grams.

Strike Temp:  This is a table version of the calculated strike temp found on the brew day sheet.  The initial temperature can be changed.  This changes subsequent values.  If you’re using a summary printout, you can measure the temp of your grain and refer to this chart for the appropriate strike temp.

Log and Notes Sheet (click to enlarge):homebrewing excel spreadsheet

Log: This section is meant to log actions taken on the beer (fermentation temp changes, dry hopping, oak additions, etc).  It calculates the time that has elapsed since brew day, between actions and since the action took place.

Notes: Simple notes section.

Downloads
I’m making both Excel and Open Office versions available to download.  I’ve only tested the Excel version, but I’ve had no complaints about the Open Office Version.  I would suggest running this through your previous calculation methods to double check that all this makes sense for you.  I don’t want you coming back to me and complaining the your double IPA is a Pale Ale because of me. :)

Microsoft Excel Version
Open Office Version
Google Docs Version (Thanks to HBF Reader Dan for this conversion!)

Select File > Download from the resulting page to download your chosen version.

If you have a question or suggestion for the spreadsheet, send me an email.

Pinned: Thermapen Closeout · Perlick 630SS · Pin Lock Kegs · Poppets · Ball Locks

Recent Great Deals:

toppost:brewingspreadsheet

Stainless Top Professional Cart – $99.99 Shipped

Seville Classics Stainless Steel Professional Kitchen Cart Cutting Table

Stainless Steel Professional Kitchen Cart Cutting Table by Seville Classics

  • Cart Dimensions: 24″ W x 20″ D x 36″ H (S.S. Top: 24″ x 20″ x 1.5″ thick)
  • Includes height adjustable basket and shelf and commercial wheels (2 locking)
  • Weight Capacity: S.S. Top 300 lbs, Shelf 100 lbs, and Sliding Basket 50 lbs
  • Brushed 443 Stainless Steel top with chrome plated steel poles, shelf and sliding basket
  • Easy assembly with no tools required; NSF certified

img_purchasedt

I just picked up this cart because… it looks like a nice, high quality work table cart.  It looks sturdy enough to use my bench style capper on and I like that it’s on casters for easily moving around.  The shelf and basket also mean that I can store items on this cart.  I think it will also make for a handy and space efficient brew day work table.  One more thing… It also seems to be a great size to store, transport and move my PicoBrew Zymatic.

5/20/15 9:40 PM Central:  This is selling for $99.99 with free shipping.  Check the product page for up to the minute price and availability.

Seville Classics Stainless Steel Professional Kitchen Cart Cutting Table

Pinned: Thermapen Closeout · Perlick 630SS · Pin Lock Kegs · Poppets · Ball Locks

Recent Amazon Finds:

24″ Stainless Whisk – for Extract and All Grain Brewers

Winco Stainless Steel French Whip, 24-Inch

This looks similar to my 24″ Stainless Whisk.  That is a Top Find that I recommend for all but small batch only brewers.  Check out my Hands on Review.

Use this on brew day in your mash tun and brew kettle.  In the mash tun… this makes quick work of breaking up up doughballs.  In the brew kettle… Use to incorporate extract, stir the brew kettle or start a kettle whirlpool.

Winco Stainless Steel French Whip, 24-Inch

Also ConsiderUpdate International FW-24 Stainless Steel French Whip, 24-Inch

Pinned: Thermapen Closeout · Perlick 630SS · Pin Lock Kegs · Poppets · Ball Locks

Recent Amazon Finds:

Rubbermaid 1 Gallon Commercial Pitcher (up to 212 F) – Great for Brew Day

Rubbermaid Commercial Products FG321800CLR 4-Quart Bouncer Measuring Cup

1 Gallon Bouncer Pitcher/Measuring Cup by Rubbermaid Commercial.

I use this pitcher on brew day and for general around-the-brewery measuring.  It’s food safe 212 deg F.  I use it for vorlaufing/recirculating, topping up water, collecting wort from my mash tun, holding utensils on brew day and more.  This small upgrade has made a big difference for me on brew day.  This is a great pitcher.  I’ve owned and used it since May of 2013.  Check out my Review for more info.

Rubbermaid Commercial Pitcher

  • Clear measuring cup for use in home or commercial kitchens.
  • Polycarbonate construction is durable, break-resistant, and resists oil and grease.
  • Dual-read markings in red and blue show units in both English and metric units.
  • Temperature range of -40 to +212 degrees F/-40 to 100 degrees C for use with hot or cold contents.
  • Capacity: 4 qt./3.8L. Made in the USA. Item Measurement 10 Inch Length x 9.5 Inch Height OA

Rubbermaid Commercial Products FG321800CLR 4-Quart Bouncer Measuring Cup

Also Consider: Lightning Deals

Pinned: Thermapen Closeout · Perlick 630SS · Pin Lock Kegs · Poppets · Ball Locks

Recent Amazon Finds:

22″ Stainless Mash and Extract Whisk

Update International FW-22 Stainless Steel French Whip, 22-Inch

22″ Stainless Steel French Whip/Whisk by Update International.  Made of 18/8 Stainless.

Use this on brew day in your mash tun and brew kettle.  In the mash tun… this makes quick work of breaking up up doughballs.  In the brew kettle… Use to incorporate extract, stir the brew kettle or start a kettle whirlpool.

This is from the same line as the Top Find – 24″ whisk that I use.  Check out my Hands on Review.  This 22″ version is just 2″ shorter.

Update International FW-22 Stainless Steel French Whip, 22-Inch

Pinned: Thermapen Closeout · Perlick 630SS · Pin Lock Kegs · Poppets · Ball Locks

Recent Amazon Finds:

Commercial Rubbermaid Pitcher (up to 212 F) for Brew Day

Rubbermaid Commercial FG321700CLR Bouncer Measuring Cup, 2-quart

64 Ounce/Half Gallon Measuring Pitcher by Rubbermaid Commercial.

This is the half gallon version of the 1 gallon pitcher that I regularly use, especially on brew day.  NSF Certified and food safe to 212 deg F.  Made in the US.  Check out my hands on review of the 1 gallon version of this pitcher – Brew Day 1 Gallon Pitcher Smackdown.

  • Made of clear, break-resistant polycarbonate, it is easy to identify and precisely measure contents.
  • Long-lasting measurement markings in high-contrast red or blue.
  • For use with temperatures from -40-degrees F/-40-degrees C to 212-degrees F/100-degrees C.
  • Certified to NSF Std. #2.
  • 6.25 inches long by 5.625 inches wide by 7.375 inches high. 0.866 pounds. Made in the USA.

Rubbermaid Commercial Products FG321700CLR 2-Quart Bouncer Measuring Cup

Also ConsiderRubbermaid Commercial Products FG321800CLR 4-Quart Bouncer Measuring Cup

Also ConsiderLightning Deals

Pinned: Thermapen Closeout · Perlick 630SS · Pin Lock Kegs · Poppets · Ball Locks

Recent Amazon Finds:

24″ x 36″ Dry Erase Board – Tap List and Brew Planning

ECR4Kids Message Store Dry Erase Bulletin Board Set, 24" by 36"

24″ x 36″ Dry Erase Board.  Can be hung in portrait or landscape orientation.  Includes markers, erase, magnet and cleaner.

You can use a dry erase board in your beer room or brewing area to list what’s on tap, what’s on deck. what’s aging, what’s fermenting etc.  You could also use one of these boards to plan and track your brew day.

ECR4Kids Message Store Dry Erase Bulletin Board Set, 24″ by 36″

Also Consider: Lightning Deals

Pinned: Thermapen Closeout · Perlick 630SS · Pin Lock Kegs · Poppets · Ball Locks

Recent Amazon Finds: